Cómo hacer referencia a otra hoja o libro de trabajo en Excel (con ejemplos)

Poder hacer referencia a celdas y rangos es lo que hace que cualquier herramienta de hoja de cálculo funcione. Y Excel es el mejor y más poderoso que existe.

En este tutorial, cubriré todo lo que necesita saber sobre cómo hacer referencia a celdas y rangos en Excel. Aparte de las referencias básicas en la misma hoja, la mayor parte de este tutorial sería sobre cómo hacer referencia a otra hoja o libro de trabajo en Excel.

Si bien no hay mucha diferencia en cómo funciona, cuando hace referencia a otra hoja en el mismo archivo o hace referencia a un archivo de Excel completamente separado, el formato de esa referencia cambia un poco.

Además, hay algunas cosas importantes que debe tener en cuenta al hacer referencia a otra hoja u otros archivos externos.

Pero te preocupes… ¡nada demasiado loco!

Cuando haya terminado con este tutorial, sabrá todo lo que hay que saber sobre la referencia a celdas y rangos en Excel (estar en el mismo libro de trabajo o en otro libro de trabajo).

¡Empecemos!

Hacer referencia a una celda en la misma hoja

Este es el nivel más básico de referencia en el que se refiere a una celda en la misma hoja.

Por ejemplo, si estoy en la celda B1 y quiero hacer referencia a la celda A1, el formato sería:

= A1

Cuando haga esto, el valor en la celda donde usa esta referencia será el mismo que el de la celda A1. Y en caso de que realice algún cambio en la celda A1, estos se reflejarán en la celda donde ha utilizado esta referencia.

Hacer referencia a una celda en la otra hoja

Si tiene que hacer referencia a otra hoja en el mismo libro de trabajo, debe usar el siguiente formato:

Sheet_name! Cell_address

Primero, tiene el nombre de la hoja seguido de un signo de exclamación seguido de la referencia de la celda.

Entonces, si necesita consultar la celda A1 en la Hoja 1, debe usar la siguiente referencia:

= Hoja1! A1

Y si desea hacer referencia a un rango de celdas en otra hoja, debe usar el siguiente formato:

Sheet_name! First_cell: Última_celda

Por lo tanto, si desea hacer referencia al rango A1: C10 en otra hoja del mismo libro de trabajo, debe usar la siguiente referencia:

= Hoja1! A1: C10

Tenga en cuenta que solo le he mostrado la referencia a la celda o al rango. En realidad, los usaría en fórmulas. Pero el formato de las referencias mencionadas anteriormente seguirá siendo el mismo

En muchos casos, la hoja de trabajo a la que se refiere tendría varias palabras en el nombre. Por ejemplo, podría ser Datos del proyecto o Datos de ventas.

En caso de que tenga espacios o caracteres no alfabéticos (como @,!, #, -, etc.), debe usar el nombre entre comillas simples.

Por ejemplo, si desea hacer referencia a la celda A1 en la hoja denominada Datos de ventas, utilizará la siguiente referencia:

= 'Datos de ventas'! A1

Y en caso de que el nombre de la hoja sea Sales-Data, para hacer referencia a la celda A1 en esta hoja, debe usar la siguiente referencia:

= 'Datos de ventas'! A1

Cuando hace referencia a una hoja en el mismo libro de trabajo y luego cambia el nombre de la hoja de trabajo, no tiene que preocuparse de que la referencia se descomponga. Excel actualizará automáticamente estas referencias por usted.

Si bien es bueno conocer el formato de estas referencias, en la práctica, no es una buena idea escribirlas manualmente cada vez. Sería lento y muy propenso a errores.

Déjame mostrarte una mejor manera de crear referencias de celda en Excel.

Creación automática de referencias a otra hoja en el mismo libro de trabajo

Una forma mucho mejor de crear una referencia de celda a otra hoja es simplemente apuntar a Excel a la celda / rango en el que desea crear la referencia y dejar que Excel la cree él mismo.

Esto asegurará que no tenga que preocuparse por el signo de exclamación o las comillas que faltan o por cualquier otro problema de formato que surja. Excel creará automáticamente la referencia correcta para usted.

A continuación se muestran los pasos para crear automáticamente una referencia a otra hoja:

1. Seleccione la celda en el libro de trabajo actual donde necesita la referencia
2. Escriba la fórmula hasta que necesite la referencia (o un signo igual si solo desea la referencia)
3. Seleccione la hoja a la que necesita hacer referencia
4. Seleccione la celda / rango al que desea hacer referencia
5. Presione Enter para obtener el resultado de la fórmula (o continúe trabajando en la fórmula)

Los pasos anteriores crearían automáticamente una referencia a la celda / rango en otra hoja. También podrá ver estas referencias en la barra de fórmulas. Una vez que haya terminado, simplemente presione la tecla Intro y le dará el resultado.

Por ejemplo, si tiene algunos datos en la celda A1: A10 en una hoja llamada Datos de ventas, y desea obtener la suma de estos valores en la hoja actual, los siguientes serán los pasos:

1. Escriba la siguiente fórmula en la hoja actual (donde necesita el resultado): = Suma (
2. Seleccione la hoja "Datos de ventas".
3. Seleccione el rango que desea agregar (A1: A10). Tan pronto como haga esto, Excel creará automáticamente una referencia a este rango (puede ver eso en la barra de fórmulas)
4. Presione la tecla enter.

Cuando esté creando una fórmula larga, es posible que deba hacer referencia a una celda o un rango en otra hoja, y luego tenga la necesidad de volver a la hoja de origen y hacer referencia a alguna celda / rango allí.

Cuando haga esto, notará que Excel inserta automáticamente una referencia de hoja a la hoja donde tiene la fórmula. Si bien esto está bien y no hace daño, no es necesario. En tal caso, puede optar por mantener la referencia o eliminarla manualmente.

Otra cosa que debe saber al crear referencias seleccionando la hoja y luego la celda / rango es que Excel siempre creará un referencia relativa (es decir, referencias con el signo n0 $). Esto significa que si copio y pego la fórmula (una con referencia a otra hoja) en alguna otra celda, automáticamente ajustaría la referencia.

Aquí hay un ejemplo que explica las referencias relativas.

Supongamos que uso la siguiente fórmula en la celda A1 en la hoja actual (para referirme a la celda A1 en un nombre de hoja SalesData)

= SalesData! A1

Ahora, si copio esta fórmula y la pego en la celda A2, la fórmula cambiará a:

= SalesData! A1

Esto sucede porque la fórmula es relativa y cuando la copio y pego, las referencias se ajustarán automáticamente.

En caso de que quiera que esta referencia siempre se refiera a la celda A1 en la hoja SalesData, tendré que usar la siguiente fórmula:

= SalesData! $ A $ 1

El signo de dólar antes del número de fila y columna bloquea estas referencias para que no cambien.

Aquí hay un tutorial detallado donde puede aprender más sobre referencias absolutas, mixtas y relativas.

Ahora que hemos cubierto cómo hacer referencia a otra hoja en el mismo libro de trabajo, veamos cómo podemos hacer referencia a otro libro de trabajo.

Cómo hacer referencia a otro libro de trabajo en Excel

Cuando se refiere a una celda o un rango a otro libro de Excel, el formato de esa referencia dependería de si ese libro está abierto o cerrado.

Y, por supuesto, el nombre del libro de trabajo y las hojas de trabajo también juegan un papel en la determinación del formato (dependiendo de si tiene caracteres espaciados o no alfabéticos en el nombre o no).

Así que veamos los diferentes formatos de referencias externas a otro libro de trabajo en diferentes escenarios.

Referencia externa a un libro de trabajo abierto

Cuando se trata de hacer referencia a un libro de trabajo externo abierto, debe especificar el nombre del libro de trabajo, el nombre de la hoja de trabajo y la dirección de celda / rango.

A continuación se muestra el formato que debe usar cuando se refiere a un libro de trabajo externo abierto

= '[FileName] SheetName! CellAddress

Suponga que tiene un libro de trabajo "ExampleFile.xlsx" y desea hacer referencia a la celda A1 en la Hoja1 de este libro de trabajo.

A continuación se muestra la referencia para esto:

= [ExampleFile.xlsx] SalesData! A1

En caso de que haya espacios en el nombre del libro de trabajo externo o en el nombre de la hoja (o ambos), debe agregar el nombre del archivo (entre corchetes) y el nombre de la hoja entre comillas simples.

A continuación se muestran los ejemplos en los que necesitaría tener los nombres entre comillas simples:

= '[Ejemplo de archivo.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Datos de ventas'! A1 = '[Ejemplo de archivo.xlsx] Datos de ventas'! A1

Cómo crear una referencia a otro libro (automáticamente)

Nuevamente, si bien es bueno conocer el formato, es mejor no escribirlo manualmente.

En su lugar, simplemente apunte a Excel en la dirección correcta y creará estas referencias por usted. Esto es mucho más rápido con menos posibilidades de errores.

Por ejemplo, si tiene algunos datos en la celda A1: A10 en un libro de trabajo llamado 'Archivo de ejemplo' en la hoja llamada 'Datos de ventas', y desea obtener la suma de estos valores en la hoja actual, los siguientes serán los pasos :

1. Escriba la siguiente fórmula en la hoja actual (donde necesita el resultado): = Suma (
2. Vaya al libro de trabajo "Archivo de ejemplo"
3. Seleccione la hoja "Datos de ventas".
4. Seleccione el rango que desea agregar (A1: A10). Tan pronto como haga esto, Excel creará automáticamente una referencia a este rango (puede ver eso en la barra de fórmulas)
5. Presione la tecla enter.

Esto crearía instantáneamente la fórmula con las referencias correctas.

Una cosa que notará al crear una referencia a un libro de trabajo externo es que siempre creará referencias absolutas. Esto significa que hay un signo $ antes de los números de fila y columna. Esto significa que si copia y pega esta fórmula en otras celdas, seguirá refiriéndose al mismo rango debido a la referencia absoluta.

En caso de que desee que esto cambie, debe cambiar las referencias manualmente.

Referencia externa a un libro cerrado

Cuando un libro de trabajo externo está abierto y se refiere a este libro de trabajo, solo necesita especificar el nombre del archivo, el nombre de la hoja y la dirección de celda / rango.

Pero cuando esto está cerrado, Excel no tiene idea de dónde buscar las celdas / rango al que se refirió.

Por eso, cuando crea una referencia a un libro cerrado, también debe especificar la ruta del archivo.

A continuación, se muestra una referencia que se refiere a la celda A1 en la hoja de trabajo Sheet1 en el libro de archivo de ejemplo. Dado que este archivo no está abierto, también se refiere a la ubicación donde se guarda el archivo.

= 'C: \ Users \ sumit \ Desktop \ [Example File.xlsx] Sheet1'! $ A $ 1

La referencia anterior tiene las siguientes partes:

• Ruta del archivo: la ubicación en su sistema o red donde se encuentra el archivo externo
• Nombre de archivo: el nombre del libro de trabajo externo. Esto también incluiría la extensión del archivo.
• Nombre de la hoja: el nombre de la hoja en la que se refiere a las celdas / rangos
• Dirección de celda / rango: la dirección exacta de celda / rango a la que se refiere

Cuando crea una referencia externa a un libro de trabajo abierto y luego cierra el libro de trabajo, notará que la referencia cambia automáticamente. Una vez que se cierra el libro de trabajo externo, Excel también inserta automáticamente una referencia a la ruta del archivo.

Impacto de cambiar la ubicación del archivo en las referencias

Cuando crea una referencia a una celda / rango en un archivo de Excel externo y luego lo cierra, la referencia ahora también usa la ruta del archivo.

Pero luego, si cambia la ubicación del archivo, nada cambiará en su libro de trabajo (en el que crea la referencia). Pero desde que cambió la ubicación, el vínculo se ha roto.

Entonces, si cierra y abre este libro de trabajo, le dirá que el enlace está roto y necesita actualizarlo o romperlo por completo. Le mostrará un mensaje como se muestra a continuación:

Cuando haga clic en Actualizar, le mostrará otro mensaje donde puede elegir las opciones para editar los enlaces (que le mostrará el cuadro de diálogo a continuación)

Si necesita mantener estos archivos vinculados, puede especificar la nueva ubicación del archivo haciendo clic en Actualizar valores. Excel abre un cuadro de diálogo donde puede especificar la nueva ubicación del archivo navegando allí y seleccionándolo.

Referencia a un nombre definido (en el mismo libro de trabajo o en un libro externo)

Cuando tenga que hacer referencia a celdas y rangos, una mejor manera es crear nombres definidos para los rangos.

Esto es útil ya que le facilita hacer referencia a estos rangos usando un nombre en lugar de una dirección de referencia larga y complicada.

Por ejemplo, es más fácil usar = SalesData en lugar de = [Example File.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10

Y en caso de que haya utilizado este nombre definido en varias fórmulas y necesite cambiar la referencia, solo debe hacerlo una vez.

Estos son los pasos para crear un rango con nombre para un rango de celdas:

1. Seleccione todas las celdas que desea incluir en el rango con nombre
2. Haga clic en la pestaña Fórmulas
3. Haga clic en la opción Definir nombre (está en el grupo Nombres definidos)
4. En el cuadro de diálogo Nuevo nombre, asigne un nombre a este rango (estoy usando el nombre SalesData en este ejemplo). Recuerde que no puede tener espacios en el nombre
5. Mantenga el alcance como Libro de trabajo (a menos que tenga una razón sólida para hacerlo a nivel de hoja)
6. Asegúrese de que se refiere al rango sea correcto.
7. Haga clic en Aceptar.

Ahora se ha creado su rango con nombre y puede usarlo en lugar de las referencias de celda con direcciones de celda.

Por ejemplo, si quiero obtener la suma de todas estas celdas en el rango SalesData, puede usar la siguiente fórmula:

= SUMA (SalesData)

¿Y si desea utilizar este rango con nombre en otras hojas de trabajo o incluso en otros libros de trabajo?

¡Usted puede!

Solo necesita seguir el mismo formato que hemos discutido en la sección anterior.

No es necesario volver al principio de este artículo. Déjame darte todos los ejemplos aquí para que te hagas una idea.

Rangos con nombre de nivel de libro de trabajo versus nivel de hoja de trabajo: Cuando crea un nivel de libro de trabajo llamado rango, solo puede haber un rango con ese nombre. Por lo tanto, si lo consulta desde libros de trabajo externos u hojas de trabajo en el mismo libro de trabajo, no es necesario que especifique el nombre de la hoja de trabajo (ya que está disponible para su uso en todo el libro de trabajo). En caso de que use el rango de nivel de la hoja de trabajo, solo podrá usarlo en la hoja de trabajo y, al referirse a él, también tendrá que especificar el nombre de la hoja de trabajo.

Hacer referencia al nombre definido en la misma hoja de trabajo o libro de trabajo

Si ha creado el nombre definido para el nivel del libro de trabajo, puede usarlo en cualquier lugar del libro de trabajo simplemente usando el nombre definido.

Por ejemplo, si quiero obtener la suma de todas las celdas en el rango con nombre que creamos (SaledData), puedo usar la siguiente fórmula:

= SUMA (SaledData)

En caso de que haya creado un nivel de hoja de trabajo llamado rango, puede usar esta fórmula solo si el rango con nombre se crea en la misma hoja donde está usando la fórmula.

En caso de que desee usarlo en otra hoja (por ejemplo, Hoja2), debe usar la siguiente fórmula:

= SUMA (Hoja1! $ A $ 1: $ A $ 10)

Y en caso de que haya espacios o caracteres alfanuméricos en el nombre de la hoja, deberá poner el nombre de la hoja entre comillas simples.

= SUMA ('Hoja 1'! $ A $ 1: $ A $ 10)

Hacer referencia al nombre definido en otro libro de trabajo (Abierto o Cerrado)

Cuando desee hacer referencia a un rango con nombre en otro libro de trabajo, deberá especificar el nombre del libro de trabajo y luego el nombre del rango.

Por ejemplo, si tiene un libro de Excel con el nombre ExampleFile.xlsx y un rango con nombre con el nombre SalesData, puede usar la fórmula siguiente para obtener la suma de este rango de otro libro:

= SUMA (ExampleFile.xlsx! SalesData)

En caso de que haya espacios en el nombre del archivo, debe usarlos entre comillas simples.

= SUMA ('Archivo de ejemplo.xlsx'! SalesData)

En caso de que tenga rangos con nombre a nivel de hoja, debe especificar el nombre del libro de trabajo, así como la hoja de trabajo, al hacer referencia a él desde un libro de trabajo externo.

A continuación se muestra un ejemplo de referencia a un rango con nombre a nivel de hoja:

= SUMA ('[Archivo de ejemplo.xlsx] Hoja1'! SalesData)

Como también mencioné anteriormente, siempre es mejor crear rangos con nombre de nivel de libro de trabajo, a menos que tenga una razón sólida para crear una hoja de trabajo de nivel uno.

En caso de que haga referencia a un rango con nombre en un libro de trabajo cerrado, también deberá especificar la ruta del archivo. A continuación se muestra un ejemplo de esto:

= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)

Cuando crea una referencia a un rango con nombre en un libro abierto y luego cierra el libro, Excel cambia automáticamente la referencia y agrega la ruta del archivo.

Cómo crear una referencia a un rango con nombre

Si crea y trabaja con muchos rangos con nombre, no es posible recordar el nombre de cada uno.

Excel le ayuda mostrándole una lista de todos los rangos con nombre que ha creado y le permite insertarlos en fórmulas con un solo clic.

Suponga que ha creado un rango con nombre SalesData que desea usar en una fórmula para SUMA todos los valores en el rango con nombre.

Estos son los pasos para hacer esto:

1. Seleccione la celda en la que desea ingresar la fórmula.
2. Ingrese la fórmula hasta el punto donde necesita que se inserte el rango con nombre
3. Presiona la tecla F3 en tu teclado. Esto abrirá el cuadro de diálogo Pegar nombre con la lista de todos los nombres que ha creado.
4. Haga doble clic en el nombre que desea insertar.

Los pasos anteriores insertarían el nombre en la fórmula y puede continuar trabajando en la fórmula.

Nota: esta técnica solo funciona para los rangos de nombres que se encuentran en el mismo libro de trabajo. Si desea obtener los nombres en un libro de trabajo externo, tendrá que ir allí, verificar el nombre usando el cuadro de diálogo Pegar nombre y luego regresar y usarlo en su fórmula (en el formato correcto usando el nombre del archivo).

Esto es todo lo que necesita saber sobre cómo hacer referencia a otras hojas o libros de trabajo y cómo crear una referencia externa en Excel.

Espero que hayas encontrado útil este tutorial.