Referencias absolutas y mixtas en Excel: aprende a usarlas

Las referencias absolutas y mixtas es un conocimiento esencial que debes dominar al utilizar Excel. En este post te entregamos todo lo que necesitas saber para aplicarlas de manera correcta.

Información clave sobre referencias absolutas y mixtas

Una REFERENCIA ABSOLUTA es “bloquear” o “fijar” las columnas y/o filas de una celda de Excel para que no cambien cuando se copia la fórmula de una celda a otra. A diferencia de una referencia relativa, se tiene como objetivo a fijar cierta celda para usar en fórmulas.

Lo básico sobre referencias

Concepto: Una referencia es el uso de una celda en una fórmula de Excel. En vez de escribir en la celda A1 “=2+5”, lo que haces es darle el valor 2 la celda A2, el valor 5 a A3 y finalmente en A1 escribes “=A2+A3” para obtener el resultado.

Finalidad: Permite darle flexibilidad a las fórmulas, en el sentido que si cambias el valor de una de las celdas, automáticamente se ajustará el valor que toma la referencia. En el mismo ejemplo anterior si A2 cambiase a 7, se ajusta el valor de A1 a 12 (7+5) sin que hagas nada. Además, al ser automático se evitan errores y se ahorra tiempo.

Introducción: referencias relativas

Para entender una referencia absoluta, primero debemos entender las referencias relativas en Excel. Las referencias relativas son la manera “normal” en la que uno escribe fórmulas, en que se indica una celda (o conjunto de celdas).

Así, la celda a la que se hace referencia, va a ir cambiando a medida que copias la fórmula en otra celda, dado que lo que Excel entiende es la posición relativa de las celdas. Es más fácil entenderlo con el siguiente ejemplo:

Tenemos una tabla en que tenemos el pago por hora y las horas trabajadas con objetivo de obtener el pago total para cada trabajador. Así, necesitamos multiplicar el pago por hora por las horas trabajadas. Para eso, escribimos =C3*D3 en la celda E3:

Para rellenar el resto de la tabla debemos arrastrar la fórmula usando la esquina inferior derecha de la celda E3 o bien haciendo doble click en esta misma esquina

Como se ve en la imagen, Ana trabajó 3 horas y le pagan $13 por hora. Así, su pago total es $39 y vemos que la fórmula en E5 es =C5*D5. Esto es una referencia relativa: Excel recuerda la fórmula como “multiplicar la celda de la izquierda por la que está dos a la izquierda”.

Referencias absolutas

Cuando queremos usar una fórmula de Excel pero queremos fijar una celda para que no se mueva si copiamos o arrastramos la fórmula usamos referencias absolutas. Así podemoss escribir la fórmula una vez y no tenemos que reescribir las fórmulas en cada celda.

Pensemos en el ejemplo anterior, pero que a todos les pagan $11 por hora trabajada, como se ve en la imagen:

Así, todos deben ser multiplicados por el mismo factor. Si es que usaramos referencias relativas en D3 escribiríamos “=C3*G3” y el primer resultado nos dará correcto.

Sin embargo, si arrastramos la fórmula, vemos que los otros valores que obtuvimos son $0.

¿Por qué es esto? Porque la referencia es relativa. Por ejemplo, para Ana vemos que la fórmula es “=C5*G5” y G5 no tiene ningún valor asignado y Excel lo toma como 0.

Entonces, ¿cómo fijamos la celda G3 para que no se mueva al arrastrar o copiar la fórmula? Esto lo haremos usando el signo peso o dólar ($) al escribir la celda que queremos fijar. Aplicándolo al ejemplo, escribimos en la celda D3 “=C3*$G$3”.

Luego, si arrastramos la fórmula (o copiamos la fórmula más abajo) vemos que se multiplica la celda de la izquierda (horas trabajadas) por la celda G3 siempre.

Por ejemplo, para Ana la fórmula es “=D5*$G$3”.

Tip Ninja: No gastes tiempo escribiendo “$” a mano para hacer referencias absolutas en Excel, puedes aplicarlo apretando F4 o Fn+F4 en Windows o Cmd+T en Mac. Puedes avanzar en tu conocimiento respecto de atajos en Excel visitando este post si utilizas Windows o este si utilizas Mac.

Referencias mixtas

Una referencia mixta es una referencia que es parte relativa y parte abosulta. Es decir, se mantiene alguna parte “libre” y otra fija. Este tipo de referencias se usan para copiar o arrastrar fórmulas sin tener que reescribirlas o editarlas manualmente, ahorrando tiempo y evitando errores.

En general es para fijar sólo una columna o sólo una fila:

• Al usar “=$A1” se fija la columna A, dejando que la fila se mueva
• Al usar “=A$1” se fija la fila 1, dejando que las columnas se muevan

Para ilustrar las referencias mixtas, veremos el siguiente ejemplo en que debemos llenar una tabla con las comisiones de cada monto de venta para cada porcentaje de comisión:

Por ejemplo, si queremos llenar la siguiente tabla en que debemos sacar el monto de una comisión para cada precio de producto y cada porcentaje de comisión, si es que usamos referencias relativas obtenemos y arrastramos:

Obtenemos:

Tip Ninja: Cuando en una celda aparece “########” no es que haya un error en la fórmula, sino que el número es tan grande que ocupa más espacio que el ancho de la celda. Si ajustamos el ancho de la celda podemos ver el número completo:

Vemos que, por ejemplo, para la celda D5, se está multiplicando las celdas C5 por D4 y no quedó fija la columna de precios ni la fila de porcentaje de comisiones.

Para resolver este problema, podríamos usar referencias absolutas en C4 escribir “=B4*$C$3” y arrastrar hacia abajo, para que para cada precio se multiplique por 10%.

Esto nos daría el resultado correcto arrastrando hacia abajo, pero si arrastramos hacia al lado no nos da un resultado correcto:

Si nos fijamos, en la celda D5 vemos que usa la celda a su derecha porque sigue con referencia relativas y la multiplica por la comisión de 10% porque fijamos la celda C3.

Una solución es hacer el mismo proceso de referencias absolutas para cada columna, pero eso puede ser mucho trabajo si trabajamos con más datos y hay más probabilidades de cometer errores.

Para solucionar esto, usamos referencias mixtas. Para eso, en la celda C4 escribiremos “=$B4*C$3”.

¿Qué significa esto?

En cuanto “$B4”, estamos fijando la columna B para que cuando arrastremos la fórmula hacia a la derecha siga siendo referenciada la columna B, pero que cuando arrastremos hacia abajo sí se mueva de fila hacia B5, B6, etc.

En cuanto a “C$3”, estamos fijando la fila 3 para que cuando arrastremos hacia abajo se siga haciendo referencia a la fila 3, pero que cuando arrastremos hacia la derecha sí se mueva de columna hacia D3, E3, etc.

Como se ve en en la imagen:

Luego, al arrastrar hacia la derecha:

Tip Ninja: El atajo para hacer referencias mixtas es apretando F4 en Windows o Cmd+T en Mac una segunda o tercera vez. Por ejemplo, si tienes la celda “B2” y apretas F4, vamos a obtener “$B$2”. Si la apretamos por segunda vez obtenemos “B$2”. Si lo hacemos una tercera vez obtenemos “$B2”. Finalmente, una cuarta vez, se vuelve a “B2”. En resumen:

Cantidad de veces F4 o Cmd+T	Resultado
Ninguna	B2
1 vez	$B$2
2 veces	B$2
3 veces	$B2
4 veces	B2