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 a una hoja de cálculo mediante 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 externo es un nombre definido o un nombre de tabla que define la ubicación de los datos que se traen a 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 conectado a un origen de datos: un informe de tabla dinámica no usa un rango de datos externo. En Excel, puede aplicar formato y establecer un rango de datos externos o usarlo en cálculos, como con cualquier otro dato.

Propiedades del rango de datos externos

Un rango de datos externo tiene propiedades adicionales (que no debe confundirse con las propiedades de consulta o conexión) que puede usar para controlar los datos, como la conservación del formato de celda y el ancho de columna. Puede cambiar estas propiedades de rango de datos externos haciendo clic en Propiedades en el grupo Conexiones de la pestaña Datos. A continuación, realice los cambios haciendo lo siguiente:

  • 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 que se crean a partir de un archivo de texto importado o una consulta web que recupera datos HTML, use el cuadro de diálogo Propiedades del rango de datos externo.

  • Para los rangos de datos externos que se crean a partir de una consulta web que recupera datos XML, use el cuadro de diálogo Propiedades de mapa XML.

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

  2. En el 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. Seleccione Fórmulas >Administrador de nombres.

  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 para la referencia en el cuadro Nombre.

Nota: El botón Cerrar solo cierra el cuadro de diálogo Administrador de nombres. No tiene que hacer clic en Cerrar para confirmar los cambios que ya realizó.

Puede cambiar la consulta subyacente de un rango de datos externo creado a partir de 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 la que desea cambiar la consulta subyacente.

  2. Seleccione Propiedades > datos.

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

  4. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña Definición y, a continuación, haga clic en Editar consulta.

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

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

  2. Seleccione Data > Connections & Consultas > pestaña Connections, haga clic con el botón derecho en la conexión que desee y, a continuación, seleccione Propiedades.

  3. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña Definición y, a continuación, haga clic en Editar consulta.

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

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

    Para obtener más información sobre la importación de archivos de texto, vea Importar o exportar archivos de texto.

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

  2. Seleccione Propiedades > datos.

  3. Seleccione Data > Connections & Consultas > pestaña Connections, haga clic con el botón derecho en la conexión que desee y, a continuación, seleccione Propiedades.

  4. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña Definición y, a continuación, haga clic en Editar consulta.

  5. Realice 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 la que desea cambiar la consulta subyacente.   

  2. Seleccione Propiedades > datos.

  3. En el cuadro de diálogo Propiedades de datos externos, haga clic en Propiedades de 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 los siguientes 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 SQL o Predeterminado y, en el cuadro de texto Comando, edite la instrucción SQL comando.

      Nota: Según cómo se definió 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, después, presione CTRL+A para seleccionar los encabezados de tabla.

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

  2. Seleccione Inicio >Copiar.

  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 datos externos no reemplaza los datos existentes, asegúrese de que la hoja de cálculo no tiene datos a la derecha o debajo de la celda en la que hace clic.

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

Nota: Si copia solo una parte de un rango de datos externos, el consulta subyacente no se copia y los datos copiados no se pueden actualizar.

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

  1. Seleccione Propiedades > datos.

  2. En el cuadro de diálogo Propiedades del rango de datos externo, en Si el número de filas del rango de datos cambiaal actualizarse, haga clic en una de las siguientes opciones:

    • Insertar celdas para nuevos datos, eliminar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas en el origen de datos, las celdas directamente debajo del rango de datos externo 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 completas para nuevos datos, borrar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas en el origen de datos, las celdas directamente debajo y a la derecha del rango de datos externos se mueven 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 y borrar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas en el origen de datos, las celdas directamente debajo del rango de datos externos se sobrescriben, 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 y a la derecha del rango de datos externos no se mueven.

  1. Seleccione Propiedades > datos.

  2. En Formato y diseño de datos,realice una o varias de las siguientes acciones:

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

      Nota: Esta casilla solo está disponible para un rango de datos externo que se convierte desde un programa de Microsoft Office 2003 o en un rango de datos externo que se crea 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 aplique, active la casilla Conservar formato de celda.

      Nota: Para una consulta web, esta opción se borra automáticamente al seleccionar 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 que establezca, active la casilla Ajustar ancho de columna.

    • Para conservar la ordenación, filtrado y diseño de columna que se aplica, active la casilla Conservar ordenación de columna, filtro o diseño.

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

    • Para conservar el filtrado de columnas que aplique, active la casilla Conservar filtro de columna.

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

Nota: El procedimiento siguiente solo se aplica a un rango de datos externos que se convierte desde un programa Microsoft Office 2003 o a un rango de datos externo que se crea 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 del rango de datos externos.

    Nota: La primera fila de datos puede ser la primera o 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. Seleccione Propiedades > datos.

  5. En el cuadro de diálogo Propiedades del rango de datos externo, active la casilla Rellenar fórmulas en columnas adyacentes a los datos.

    Nota: Si el rango de datos externos se expande al actualizar, Excel copia solo aquellas fórmulas que están 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 Nombre y, a continuación, haga clic en el nombre del rango de datos externo del que 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 de tabla externa, haga clic en Desvincular. El rango de datos permanece y sigue teniendo el mismo nombre, pero se elimina la conexión.

Nota: El procedimiento siguiente solo se aplica a un rango de datos externos que se convierte desde un programa Microsoft Office 2003 o a un rango de datos externo que se crea 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. Seleccione Propiedades > datos.

  2. En Definición de 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 guardó la consulta al crearla en el Asistente para consultas o en Microsoft Query, el archivo de consulta se guardará en el equipo y podrá volver a usar la consulta para recuperar datos externos. Para obtener más información, vea Ayuda de Microsoft Query.

Nota: El procedimiento siguiente solo se aplica a un rango de datos externo que se crea mediante el Asistente para la conexión de datos o Microsoft Query (y no a un archivo de texto importado o consulta web), un rango de datos externo convertido desde un programa de Microsoft Office 2003 o un rango de datos externo 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 que desea insertar la nueva fila. A continuación, en la pestaña Inicio, en el grupo Celdas, haga clic en la flecha situada 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 fila de la fila encima de la que 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. Seleccione Propiedades > datos.

  5. En el cuadro de diálogo Propiedades del rango de datos externo, en Formato y diseño 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 externo y, a continuación, haga clic en Actualizar datos Imagen del botón .

Nota: Al recuperar datos de una base de datos, los cambios en los nombres de columna en Microsoft Query se conservan en el rango de datos externos. Para obtener información sobre cómo cambiar los nombres de columna en la consulta, vea 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 SUPRIMIR.

  4. Para eliminar la consulta subyacente, haga clic en Sí cuando Excel le solicite.

Vea también

Ayuda de Power Query para Excel

¿Necesita más ayuda?

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.

×