Administrar rangos de datos externos y sus propiedades

Puede cambiar las propiedades de un rango de datos externos para controlar los datos que se han importado en una hoja de cálculo a través de una conexión a un origen de datos externo, como una base de datos, una consulta Web o un archivo de texto.

Un rango de datos externos (también denominado tabla de consulta) es un nombre definido o un nombre de tabla que define la ubicación de los datos que se introducen en una hoja de cálculo. Cuando se conecta a datos externos, Excel crea automáticamente un rango de datos externos. La única excepción es un informe de tabla dinámica que está conectado a un origen de datos: un informe de tabla dinámica no usa un rango de datos externos. En Excel, puede dar formato y diseñar un rango de datos externos o usarlo en cálculos, como con cualquier otro tipo de datos.

Propiedades del rango de datos externos

Un rango de datos externo tiene propiedades adicionales (no se deben confundir con las propiedades de conexión) que puede usar para controlar los datos, como la preservación del formato de las celdas y el ancho de las columnas. Para cambiar estas propiedades del rango de datos externos, haga clic en propiedades en el grupo conexiones de la pestaña datos . A continuación, realice los cambios siguiendo este procedimiento:

  • Para los rangos de datos externos que se crean desde Microsoft Query y el Asistente para la conexión de datos, use el cuadro de diálogo propiedades de datos externos .

  • Para los rangos de datos externos creados a partir de un archivo de texto importado o una consulta Web que recupere datos HTML, use el cuadro de diálogo propiedades del rango de datos externos .

  • Para los rangos de datos externos creados a partir de una consulta Web que recupere datos XML, use el cuadro de diálogo propiedades de asignación XML .

Usar plantillas con rangos de datos externos

Si desea compartir un resumen o un informe basado en datos externos, puede dar a otros usuarios un libro que contenga un rango de datos externos o puede crear una plantilla de informe. Un plantilla de informe le permite guardar el resumen o informe sin guardar los datos externos para que el archivo sea más pequeño. Los datos externos se recuperan cuando un usuario abre la plantilla de informe.

  1. Seleccione la hoja de cálculo en la que desea buscar un rango de datos externos.

  2. En la barra de fórmulas, haga clic en la flecha situada junto al cuadro Nombrey, a continuación, haga clic en el nombre del rango de datos externos que desee.

  1. En el grupo Nombres definidos de la pestaña Fórmulas, haga clic en Administrador de nombres.

    Imagen de la cinta de Excel

  2. En el cuadro de diálogo Administrador de nombres , haga clic en el nombre del rango de datos externos y, a continuación, haga clic en Editar. También puede hacer doble clic en el nombre.

  3. En el cuadro de diálogo Editar nombre , escriba el nuevo nombre de la referencia en el cuadro nombre .

Nota: El botón cerrar cierra solo el cuadro de diálogo Administrador de nombres . No es necesario que haga clic en cerrar para confirmar los cambios que ya haya realizado.

Puede cambiar la consulta subyacente para un rango de datos externos creado desde Microsoft Query, un archivo de texto importado, una consulta Web o el Asistente para la conexión de datos.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  3. En el cuadro de diálogo propiedades de datos externos , haga clic en propiedades de conexión Imagen del botón .

  4. En el cuadro de diálogo propiedades de conexión , haga clic en la pestaña definición y, después, haga clic en Editar consulta.

Para obtener más información sobre cómo crear y editar consultas en Microsoft Query, consulte la ayuda de Microsoft Query.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña Datos, en el grupo Conexiones, haga clic en Conexiones.

    Imagen de la cinta de Excel

  3. En el cuadro de diálogo conexiones del libro , haga clic en propiedades.

  4. En el cuadro de diálogo propiedades de conexión , haga clic en la pestaña definición y, después, haga clic en Editar consulta.

  5. En el cuadro de diálogo Importar archivo de texto , haga clic en importar.

  6. Realice cambios en el archivo de texto importado en el Asistente para importar texto y, a continuación, haga clic en Finalizar.

    Para obtener más información sobre cómo importar archivos de texto, vea importar o exportar archivos de texto.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  3. En el cuadro de diálogo conexiones del libro , haga clic en propiedades.

  4. En el cuadro de diálogo propiedades de conexión , haga clic en la pestaña definición y, después, haga clic en Editar consulta.

  5. Realice los cambios en la consulta Web en el cuadro de diálogo Editar consulta Web y, a continuación, haga clic en Finalizar.

Para obtener más información sobre cómo crear y editar consultas Web, vea conectarse a una página web.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  3. En el cuadro de diálogo propiedades de datos externos , haga clic en propiedades de conexión Imagen del botón .

  4. En el cuadro de diálogo propiedades de conexión , haga clic en la pestaña definición .

  5. Siga uno de estos procedimientos:

    • En el cuadro tipo de comando , haga clic en tabla y, a continuación, en el cuadro de texto comando , cambie el valor por el nombre de una tabla, vista o consulta adecuada.

    • En el cuadro tipo de comando , haga clic en SQL o predeterminado y, a continuación, en el cuadro de texto comando , edite la instrucción SQL.

      Nota: Según el modo en que se haya definido la conexión, es posible que el cuadro tipo de comando no esté disponible (aparece atenuado).

  1. En la barra de fórmulas, haga clic en la flecha situada junto al cuadro Nombrey seleccione el nombre del rango de datos externos que desea copiar.

    Para una tabla de Excel, seleccione el nombre del rango y, a continuación, presione CTRL + A para seleccionar los encabezados de tabla.

    Si desea incluir etiquetas de columna o fórmulas que no formen parte del rango de datos externos, seleccione las celdas que contienen los rótulos de columna o las fórmulas que desea copiar. Haga clic en la flecha situada junto al cuadro Nombre en la barra de fórmulas y haga clic en el nombre del rango de datos externos que desea copiar.

  2. En el grupo Portapapeles de la pestaña Inicio, haga clic en Copiar.

    Imagen de la cinta de Excel

  3. Cambie al libro en el que desea pegar el rango de datos externos.

  4. Haga clic en la celda superior izquierda de la área de pegado.

    Para asegurarse de que la datos externos no reemplaza los datos existentes, asegúrese de que la hoja de cálculo no tiene datos en la parte derecha de la celda en la que haga clic.

  5. En el grupo Portapapeles de la pestaña Inicio, haga clic en Pegar.

Nota: Si copia solo parte de un rango de datos externos, el consulta subyacente no se copiará y los datos copiados no se podrán actualizar.

Puede controlar cómo tratar un conjunto de datos más pequeño o más grande que se devuelva a Excel cuando se actualicen los datos.

  1. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  2. En el cuadro de diálogo propiedades del rango de datos externos , en si el número de filas del rango de datos cambia después de la actualización, haga clic en una de las siguientes opciones:

    • Insertar celdas para nuevos datos, eliminar celdas que no se usan

      Notas: 

      • Cuando se agregan una o más filas al origen de datos, las celdas directamente debajo del rango de datos externos se mueven hacia abajo, pero las celdas a la derecha del rango de datos externos no se mueven.

      • Cuando se eliminan una o más filas en el origen de datos, las celdas directamente debajo del rango de datos externos se mueven hacia arriba, pero las celdas a la derecha del rango de datos externos no se mueven.

    • Insertar filas enteras para nuevos datos, borrar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas al origen de datos, las celdas directamente debajo y a la derecha del rango de datos externos se desplazan hacia abajo.

      • Cuando se eliminan una o más filas en el origen de datos, las celdas directamente debajo y a la derecha del rango de datos externos no se mueven.

    • Sobrescribir celdas existentes con datos nuevos, borrar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas al origen de datos, las celdas directamente bajo el rango de datos externos se sobrescriben, pero las celdas situadas a la derecha del rango de datos externos no se mueven.

      • Cuando se eliminan una o más filas en el origen de datos, las celdas directamente debajo y a la derecha del rango de datos externos no se mueven.

  1. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  2. En diseño y formato de datos, siga uno o varios de estos procedimientos:

    • Para incluir los nombres de campo como la primera fila, active la casilla incluir nombres de campo .

      Nota: Esta casilla de verificación solo está disponible para un rango de datos externos que se ha convertido desde un programa de Microsoft Office 2003 o para un rango de datos externos creado mediante programación.

    • Para agregar una columna de números de fila, active la casilla incluir números de fila .

      Nota: Esta casilla no está disponible para un archivo de texto importado, un archivo XML o una consulta Web.

    • Para conservar el formato de celda que aplica, active la casilla mantener el formato de celda .

      Nota: Para una consulta Web, esta opción se desactiva automáticamente cuando se selecciona formato HTML completo en el cuadro de diálogo Opciones . Puede acceder al cuadro de diálogo Opciones desde el cuadro de diálogo Editar consulta .

    • Para conservar los anchos de columna establecidos, active la casilla Ajustar ancho de columna .

    • Para conservar la ordenación, el filtrado y el diseño de las columnas que aplica, active la casilla de verificación mantener orden, filtro y diseño de columna .

      Nota: Esta casilla no está disponible para una consulta Web.

    • Para conservar el filtrado de columnas que aplica, active la casilla de verificación mantener el filtro de columna .

      Nota: Esta casilla solo está disponible para una consulta Web basada en datos XML.

Nota: El procedimiento siguiente se aplica solo a un rango de datos externos que se convierte desde un programa de Microsoft Office 2003 o a un rango de datos externos creado mediante programación. Todos los rangos de datos externos que se crean mediante la interfaz de usuario se crean como tablas de Excel. Estas tablas se expanden automáticamente cuando se agregan nuevas filas y se rellenan las columnas calculadas.

  1. Escriba una fórmula en una celda adyacente a la primera fila de datos en el rango de datos externos.

    Nota: La primera fila de datos puede ser la primera o la segunda fila del rango de datos externos, dependiendo de si la primera fila contiene encabezados.

  2. Seleccione la celda y haga doble clic en el controlador de relleno para copiar la fórmula en todas las filas del rango de datos externos.

  3. Haga clic en una celda en el rango de datos externos.

  4. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  5. En el cuadro de diálogo propiedades del rango de datos externos , active la casilla de verificación rellenar hacia abajo las fórmulas de las columnas adyacentes a los datos .

    Nota: Si el rango de datos externos se expande al actualizar, Excel copia solo las fórmulas que sean inmediatamente adyacentes o dentro del rango de datos externos.

Inmovilizar un rango de datos externos conserva los datos pero no su consulta subyacente, por lo que no se puede actualizar un rango de datos externos inmovilizado.

  1. Haga clic en la hoja de cálculo que contiene la tabla de Excel de la que desea quitar la conexión de datos.

  2. En la barra de fórmulas, haga clic en la flecha situada junto al cuadro Nombrey, a continuación, haga clic en el nombre del rango de datos externos a partir del cual desea quitar la conexión de datos. A continuación, se selecciona el rango de datos.

  3. En la pestaña herramientas , en el grupo datos externos de la tabla, haga clic en desvincular. El rango de datos sigue teniendo el mismo nombre, pero se elimina la conexión.

Nota: El procedimiento siguiente se aplica solo a un rango de datos externos que se convierte desde un programa de Microsoft Office 2003 o a un rango de datos externos creado mediante programación. Todos los rangos de datos externos que se crean mediante la interfaz de usuario se crean como tablas de Excel. Estas tablas se expanden automáticamente cuando se agregan nuevas filas y se rellenan las columnas calculadas.

  1. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  2. En definiciónde la consulta, desactive la casilla Guardar definición de consulta .

Nota:  Al guardar el libro, la consulta subyacente se elimina del libro. Sin embargo, los archivos de consulta de base de datos guardados (archivos. dqy o. odc) no se eliminan. Si ha guardado la consulta al crearla en el Asistente para consultas o en Microsoft Query, el archivo de consulta se guarda en el equipo y puede volver a usar la consulta para recuperar datos externos. Para obtener más información, consulte la ayuda de Microsoft Query.

Nota: El procedimiento siguiente se aplica solo a un rango de datos externos creado mediante el Asistente para la conexión de datos o Microsoft Query (y no un archivo de texto importado o una consulta Web), un rango de datos externos convertido de un programa de Microsoft Office 2003 o un externo rango de datos creado mediante programación. Todos los rangos de datos externos que se crean mediante la interfaz de usuario se crean como tablas de Excel.

  1. Inserte una fila en blanco encima del rango de datos externos siguiendo uno de estos procedimientos:

    • Seleccione una celda encima de la cual desea insertar la nueva fila. A continuación, en la pestaña Inicio , en el grupo celdas , haga clic en la flecha junto a Insertary, a continuación, haga clic en Insertar filas de hoja. Como ha seleccionado una celda, se inserta una nueva fila encima de la fila actual.

    • Haga clic en el selector de filas de la fila encima de la cual desea insertar una nueva fila. A continuación, en la pestaña Inicio , en el grupo celdas , haga clic en Insertar. Como ha seleccionado una fila completa, se inserta una nueva fila encima de la fila seleccionada.

  2. Escriba las etiquetas que desee en las celdas de la fila en blanco.

  3. Haga clic en una celda en el rango de datos externos.

  4. En la pestaña datos , en el grupo conexiones , haga clic en propiedades.

    Imagen de la cinta de Excel

  5. En el cuadro de diálogo propiedades del rango de datos externos , en diseño y formato de datos, desactive la casilla incluir nombres de campo y, a continuación, haga clic en Aceptar.

  6. Para quitar los nombres de campo existentes y actualizar el rango de datos externos, haga clic en una celda del rango de datos externos y, a continuación, haga clic en actualizar Imagen del botón de datos .

Nota: Al recuperar datos de una base de datos, los cambios realizados en los nombres de columna en Microsoft Query se conservan en el rango de datos externos. Para obtener más información sobre cómo cambiar los nombres de columna en la consulta, consulte la ayuda de Microsoft Query.

  1. Haga clic en la hoja de cálculo que contiene el rango de datos externos que desea eliminar.

  2. En la barra de fórmulas, haga clic en la flecha situada junto al cuadro Nombrey, a continuación, haga clic en el nombre del rango de datos externos que desea eliminar.

    Si el rango de datos externos es una tabla de Excel, presione CTRL + A para seleccionar toda la tabla.

  3. Para eliminar el rango de datos externos, presione la tecla SUPRIMIr.

  4. Para eliminar la consulta subyacente, haga clic en cuando Excel le pida.

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×