Referencia relativa en Excel: qué es y cómo usarla al copiar fórmulas

Una referencia relativa es la forma «normal» en que Excel apunta a una celda al escribir una fórmula: escribes =A1 y Excel entiende que te refieres a la celda que está una columna a la izquierda y la misma fila. Lo importante es que esa referencia NO está «pegada» a la celda A1: cuando copias la fórmula a otra celda, la referencia se mueve con ella. Por eso, al arrastrar =B2*C2 desde D2 hasta D6, cada fila calcula su propio producto sin que tengas que escribir nada más. En este tutorial verás por qué pasa esto, cómo distinguir una referencia relativa de una absoluta y de una mixta, cómo cambiar de una a otra con la tecla F4 y qué hacer cuando el resultado no es el que esperabas. Funciona en todas las versiones de Excel de escritorio, en Excel para la web y en Excel para Mac.

Qué es una referencia relativa y por qué la usas sin darte cuenta

Una referencia relativa apunta a una celda describiendo la posición relativa respecto a la celda que contiene la fórmula: «una columna a la izquierda, misma fila». Al copiar la fórmula, esa distancia se mantiene y, como la nueva celda está en otro sitio, la referencia apunta a otra celda. Por eso =B2*C2 escrita en D2, al copiarla a D3, se convierte automáticamente en =B3*C3.

Imagina la hoja Ventas de una papelería: en la columna A está el producto, en B el precio unitario, en C las unidades vendidas y en D el importe. En D2 escribes =B2*C2 y Excel muestra 15,00. Al arrastrar el controlador de relleno (el cuadradito de la esquina inferior derecha de la celda) hasta D4, la fórmula se adapta sola: D3 calcula 16,00 y D4 calcula 9,60.

Los tres tipos de referencia y qué cambia entre ellos

Excel maneja tres tipos de referencia, según qué parte esté «fijada» con el símbolo dólar ($):

La diferencia se ve claramente al copiar =$A$1 desde B2 a C5: la referencia sigue señalando a A1 en cualquier celda, mientras que =A1 se convierte en =B2 en C5 (una columna a la derecha, una fila abajo). Las referencias mixtas sirven para casos como una tabla de multiplicar, donde quieres que la columna no cambie al bajar pero la fila sí, o al revés.

Cómo cambiar de relativa a absoluta (o mixta) con F4

La tecla F4 recorre los cuatro estados de una referencia mientras editas una fórmula. Es la forma más rápida de fijar lo que necesitas sin escribir los dólares a mano.

1. Haz clic en la celda que contiene la fórmula para seleccionarla.
2. Pulsa F2 (o haz doble clic) para entrar en modo de edición.
3. En la barra de fórmulas, haz clic sobre la referencia que quieres fijar (por ejemplo, F1).
4. Pulsa F4. La referencia pasa de F1 a $F$1.
5. Pulsa F4 otra vez: pasa a F$1 (fila fija).
6. Pulsa F4 una tercera vez: pasa a $F1 (columna fija).
7. Pulsa F4 una cuarta vez: vuelve a F1 (relativa).
8. Pulsa Entrar para confirmar la fórmula.

Dónde colocar el cursor para que F4 actúe

F4 solo cambia la referencia que tienes bajo el cursor. Si haces clic en una parte de la fórmula que no es una referencia válida (un número, un texto, el signo igual), la tecla no hace nada o repite la última acción fuera de la fórmula. La forma más fiable es clicar directamente sobre la referencia dentro de la barra de fórmulas, no sobre la fórmula en la celda. Si F4 no responde, prueba Fn+F4, cierra cualquier programa que pueda capturar la tecla y vuelve a clicar la referencia.

El primer arrastre: una fórmula relativa que baja fila a fila

Vamos con la hoja Notas de una clase: en A2:A6 están los nombres de cinco alumnos, en B, C y D las notas del primer, segundo y tercer trimestre, y en E la nota media. La fórmula es =PROMEDIO(B2:D2).

1. Escribe =PROMEDIO(B2:D2) en E2 y pulsa Entrar.
2. Vuelve a seleccionar E2.
3. Sitúa el ratón sobre el controlador de relleno (el cuadrado pequeño de la esquina inferior derecha) hasta que el cursor se convierte en una cruz negra fina.
4. Arrastra hacia abajo hasta E6 y suelta.
5. Comprueba que en E3 se ha copiado =PROMEDIO(B3:D3), en E4 `=PROMEDIO(B4:D4) y así hasta E6.

El problema que aparece al fijar un valor único con una relativa

Ahora añade el IVA a la hoja Pedidos. En A2:A6 están los productos, en B2:B6 los precios sin IVA y en F1 el tipo de IVA, 21%. La idea es calcular el importe con IVA en la columna C.

Si escribes =B2*1,21 en C2, la fórmula funciona, pero tienes el 21% «dentro» de la fórmula. Si mañana el IVA cambia al 23%, tienes que tocar todas las celdas. Es mejor escribir =B2*F1 para que el porcentaje viva en una sola celda.

¿Ves lo que ha pasado? Al arrastrar desde C2 hasta C6, la referencia F1 se ha convertido en F2, F3, F4 y F5, y al multiplicar por celdas vacías el resultado se ha descontrolado (una celda vacía cuenta como 0 en una operación, por eso aparecen cifras enormes al multiplicar precio × 0 + algo). El 21% se ha «movido» con la fórmula porque era una referencia relativa. Aquí es donde necesitas una referencia absoluta.

Fijar el IVA con F4 para que no se mueva al copiar

La solución es fijar F1 con dólares para que la fórmula siempre mire a esa celda. Para ello, escribe la fórmula y luego convierte la referencia:

1. En C2 escribe =B2*F1 (todavía sin dólares).
2. Pulsa F2 para editar la fórmula.
3. En la barra de fórmulas, haz clic sobre F1 para seleccionarla.
4. Pulsa F4 una vez: F1 se convierte en $F$1.
5. Pulsa Entrar.
6. Arrastra desde C2 hasta C6 con el controlador de relleno.

=B2*$F$1

Escribir el $ a mano cuando no puedes pulsar F4

Teclear el dólar a mano funciona exactamente igual y es útil en Mac, en portátiles con F4 problemática o cuando editas una fórmula desde el móvil. Solo tienes que poner el símbolo delante de la letra y delante del número: =$F$1. La fórmula queda =B2*$F$1 y, al copiarla, B2 avanza y $F$1 se queda fija. El resultado es el mismo que pulsando F4.

Referencias mixtas: fijar solo la columna o solo la fila

Las referencias mixtas fijan una sola coordenada. Son imprescindibles cuando una fórmula se copia en dos direcciones (a la derecha y hacia abajo) y solo una de las dos direcciones debe variar.

El ejemplo clásico es la tabla de multiplicar del 1 al 5. En B2 está el 1, en C2 el 2, y así hasta F2. En A3 está el 1, en A4 el 2, hasta A7. La idea es que en B3 esté 1×1, en C3 2×1, en B4 1×2, etc. Si la fórmula de B3 fuera =$A3*B$2, al copiarla a la derecha $A3 mantiene la columna A pero la fila avanza (A3, A3, A3… no, avanza al arrastrar hacia abajo, pero no hacia la derecha), y B$2 mantiene la fila 2 pero la columna avanza.

1. En B3 escribe =$A3*B$2.
2. Selecciona B3 y arrastra hacia la derecha hasta F3 con el controlador de relleno.
3. Selecciona el rango B3:F3 y arrastra hacia abajo hasta F7.
4. Comprueba que en F7 aparece =$A7*F$2, que da 25.

Una tabla de precios con IVA por tramo que solo cambia de columna

La hoja Tarifas tiene tres tramos de IVA (10%, 21% y 25%) en B1:D1 y una lista de productos en A2:A6 con sus precios base en B2:D6 (cada producto tiene un precio distinto por tramo). En F2 quieres el resultado de multiplicar el precio de cada producto por su tramo.

La fórmula de F2 es =B2*B$1. Fíjate: la fila 1 se queda fija (porque al copiar hacia abajo no queremos que el tramo baje), pero la columna B sí puede avanzar para mirar al precio de cada tramo. Al arrastrar hacia la derecha y hacia abajo, la fórmula se adapta sola.

Por qué a veces la fórmula baja bien pero da resultados raros

Una referencia relativa bien copiada puede dar resultados «raros» sin que la fórmula esté mal escrita. Casi siempre la causa es una de estas tres: olvidaste fijar con $ una celda que debía quedarse quieta, arrastraste desde una selección de varias celdas o la celda que parece «vacía» en realidad contiene un 0. Las tres se arreglan en menos de un minuto si miras la barra de fórmulas.

El arrastre copia la misma fórmula y no avanza

Si al arrastrar desde D2 hasta D6 todas las celdas muestran el mismo resultado y la barra de fórmulas de D6 sigue diciendo =B2*C2, el problema es que seleccionaste un rango (por ejemplo, D2:D6) en lugar de solo D2 antes de arrastrar. Excel interpreta la selección como un bloque a rellenar con la misma fórmula.

Otra causa posible es que la hoja esté protegida. Ve a Inicio > Celdas > Formato > Proteger hoja, comprueba si la opción está activa y, si lo está, desprotege la hoja o pide la contraseña al autor. Las hojas protegidas no permiten arrastrar fórmulas.

Una celda vacía da 0 en vez de aviso

En la hoja Pedidos, si olvidaste el dólar y arrastraste =B2*F1 hasta C6, las celdas C3, C4, C5 y C6 multiplican el precio por una celda vacía (F2, F3, F4, F5), que Excel interpreta como 0. El resultado es 0,00 sin ningún error que avise. Es el fallo más traicionero de la referencia relativa: todo parece normal hasta que revisas los números.

La solución es mirar la barra de fórmulas de C3: si dice =B3*F2 en vez de =B3*$F$1, ya sabes qué pasa. Vuelve a C2, fija F1 con F4 y vuelve a arrastrar.

F4 no hace nada o abre otra cosa

Si F4 no convierte la referencia en absoluta, las causas habituales son:

• Estás en un portátil y la tecla F4 está compartida con el brillo: pulsa Fn+F4 o activa el bloqueo de Fn en la BIOS.
• Otro programa (una herramienta de captura, un gestor de ventanas) está capturando F4: ciérralo y vuelve a intentarlo.
• No estás editando la fórmula: pulsa F2 y haz clic dentro de la barra de fórmulas sobre la referencia antes de pulsar F4.
• En Mac, la tecla correcta es Cmd+T.

Si aun así nada funciona, escribe el dólar a mano: es igual de rápido y no depende de la tecla.

Lo que ya sabes hacer con la referencia relativa y el siguiente paso

Estas son las cinco ideas para llevarte:

• =A1 es relativa: al copiar, fila y columna avanzan con la celda.
• $A$1 es absoluta: ni fila ni columna se mueven al copiar.
• $A1 y A$1 son mixtas: fijan solo la columna o solo la fila.
• F4 recorre los cuatro estados; el cursor tiene que estar sobre la referencia.
• Una referencia olvidada sin $ da ceros silenciosos al cruzar una celda vacía.

El siguiente paso natural es trabajar con referencias estructuradas dentro de una tabla de Excel. En lugar de escribir =B2*$F$1, puedes crear una tabla desde Insertar > Tablas > Tabla y usar fórmulas como =[@Precio]*[@[IVA%]], que se entienden solas y se ajustan al añadir filas. Es la misma idea que has visto aquí, pero sin tener que pensar en dólares.

Dudas al usar la referencia relativa en Excel