Tablas en Excel: referencias estructuradas, limpieza de datos y Power Query

Las tablas en Excel son la función que más trabajo ahorra y la que menos gente usa. Convertir un rango en tabla toma tres segundos, y a partir de ahí las fórmulas se escriben solas al añadir filas, las referencias se leen en español y cualquier tabla dinámica que cuelgue de ella crece sin que haya que redefinir nada. Esta guía explica cómo crear una tabla de Excel, cómo usar sus referencias estructuradas y cómo dejar los datos limpios antes de analizarlos.

Está escrita en dos niveles. Si usted trabaja con hojas de cálculo sin ser de sistemas, encontrará el paso a paso, la validación de datos y los errores que más cuestan. Si administra la información de un área, encontrará la parte técnica: la sintaxis completa de las referencias estructuradas, cuándo conviene Power Query y cómo se buscan datos con XLOOKUP sin volver a escribir un BUSCARV frágil.

Una serie de siete guías con un mismo conjunto de datos

Esta es la primera de siete guías sobre Microsoft 365. Las siete usan el mismo conjunto de datos —un registro de tickets de soporte— y lo llevan de punta a punta: se limpia en Excel, se resume, se automatiza, se convierte en un informe de Word, se reparte por correo y termina en una presentación. Ese hilo es lo que convierte siete artículos sueltos en un método de trabajo.

1. Tablas en Excel — está leyéndola: preparar y limpiar los datos.
2. Tablas dinámicas — resumir esos datos en segundos.
3. Macros en Excel — automatizar lo que se repite cada mes.
4. Estilos en Word — construir el informe con índice y referencias cruzadas.
5. Combinar correspondencia — enviar a cada área su parte.
6. Patrón de diapositivas — la plantilla de la presentación.
7. Vincular Excel con PowerPoint — que el gráfico siga al dato.

El conjunto de datos que usan las siete guías

El conjunto de datos es un extracto de doce filas de un registro real de mesa de servicio. Cópielo en una hoja en blanco: es el punto de partida de las siete guías.

Son 36,5 horas en total, y ese será el contenido de la tabla de Excel. Ponga además, en una celda aparte de la misma hoja, la tarifa por hora: B2 = 75000. Con eso queda todo lo necesario para las siete guías.

Qué son las tablas en Excel y qué resuelven

Una tabla de Excel no es un rango con bordes bonitos. Es un objeto con nombre que Excel gestiona por dentro —Microsoft lo documenta en su información general sobre las tablas de Excel—: sabe dónde empieza, dónde termina, cómo se llama cada columna y qué fórmulas se aplican a toda la columna. Ese cambio de naturaleza es el que resuelve cuatro problemas de golpe.

Hay un quinto beneficio menos obvio y quizá el más valioso: una tabla obliga a tener los datos bien puestos. Una fila por registro, una columna por atributo, encabezados en la primera fila y ninguna celda combinada. Esa disciplina es la que permite que todo lo demás funcione.

Cómo crear tablas en Excel paso a paso

1. Sitúe el cursor en cualquier celda del rango. No hace falta seleccionarlo entero.
2. Pulse Ctrl + T (o Ctrl + Q en algunos teclados en español) o vaya a Insertar > Tabla.
3. Confirme que la casilla «La tabla tiene encabezados» está marcada y que el rango detectado es el correcto.
4. Acepte. El rango se convierte en tabla, con filtros y bandas de color.
5. En Diseño de tabla, cambie el nombre del cuadro de la izquierda por uno propio.

El nombre de la tabla de Excel importa más de lo que parece

Excel bautiza la primera tabla como Tabla1, y ese nombre no dice nada. Póngale uno descriptivo desde el principio, porque aparecerá en cada fórmula, en el origen de cada tabla dinámica y en cualquier consulta de Power Query. En esta serie la tabla se llama tblTickets.

Tres reglas para el nombre: sin espacios, que empiece por letra y que sea reconocible a los seis meses. El prefijo tbl es una convención cómoda, porque al escribir tbl en una fórmula Excel ofrece todas las tablas del libro.

Lo que cambia en el momento en que acepta

Aparecen los desplegables de filtro en cada encabezado, las filas se colorean de forma alterna para leerlas mejor, y los encabezados se quedan fijos arriba al desplazarse dentro de la tabla, sin necesidad de inmovilizar paneles. Además se activa la pestaña Diseño de tabla, con la fila de totales, los estilos y el botón para convertirla de nuevo en rango si hiciera falta.

Referencias estructuradas: el fin de A1:F1241

Esta es la parte que de verdad cambia la forma de trabajar. Dentro de una tabla, las fórmulas dejan de apuntar a coordenadas y pasan a apuntar a nombres de columna. Escriba en una columna nueva llamada Costo:

=[@Horas]*$B$2

Al pulsar Intro, la fórmula se copia sola a las 1.240 filas. Y cuando llegue la fila 1.241, bajará sin que nadie haga nada. El símbolo @ significa «esta fila»; sin él, la referencia abarca la columna entera.

La sintaxis completa de las referencias estructuradas

La documentación de Microsoft sobre referencias estructuradas recoge la lista completa. Un detalle que ahorra disgustos: si el nombre de la columna lleva un espacio o un signo, hay que encerrarlo en corchetes dobles, como en tblTickets[[Horas facturables]]. Por eso conviene que los encabezados sean palabras simples.

Por qué la fórmula deja de romperse

Con un rango suelto, insertar una columna en medio desplaza todo y las fórmulas que apuntaban a E ahora apuntan a otra cosa. Con referencias estructuradas eso no ocurre: la fórmula sigue diciendo [Horas] y Excel sabe dónde está esa columna, esté donde esté. Es la diferencia entre una hoja que aguanta dos años y una que hay que rehacer cada trimestre.

La fila de totales de las tablas en Excel

En Diseño de tabla, marque Fila de totales (o pulse Ctrl + Mayús + T). Aparece una fila al final con un desplegable por columna que permite elegir la operación: suma, promedio, cuenta, máximo, mínimo, desviación. En nuestro conjunto de datos, la suma de Horas da 36,5 y la de Costo, 2.737.500.

Lo que Excel escribe ahí es =SUBTOTALES(109;[Horas]), y el 109 es la clave: significa «suma ignorando las filas ocultas por un filtro». Por eso, al filtrar por Bogotá, el total baja a 15,5 solo. Con un =SUMA() normal seguiría mostrando 36,5 y estaría mintiendo.

Validación de datos: que no entre lo que no debe

La mitad de los problemas de una hoja compartida vienen de la entrada. «Bogotá», «bogota», «Bogotá D.C.» y «BOG» son cuatro sedes distintas para Excel, y cualquier resumen las contará por separado. La validación de datos lo evita en el origen.

Lista desplegable a partir de otra tabla

1. Cree una tabla aparte, tblSedes, con las cuatro sedes válidas en una sola columna.
2. Seleccione la columna Sede de tblTickets.
3. Vaya a Datos > Validación de datos > Permitir: Lista.
4. En Origen escriba =tblSedes[Sede].

Al hacerlo así, añadir una sede nueva a tblSedes la hace aparecer en el desplegable al instante, sin tocar la validación. Con un rango fijo habría que reeditarla cada vez.

Marcar lo que ya está mal

La validación solo actúa sobre lo que se escribe después. Para lo que ya está dentro, use Validación de datos > Rodear con un círculo datos no válidos: Excel marca en rojo cada celda que incumple la regla. Es la forma más rápida de auditar una hoja heredada.

Limpiar los datos de las tablas en Excel

Ningún resumen es mejor que los datos que lo alimentan, y las tablas en Excel no arreglan un dato mal escrito. Estas cuatro operaciones resuelven la mayoría de los casos y se hacen en minutos.

Sobre Quitar duplicados conviene una advertencia: borra filas sin preguntar cuáles. Antes de usarlo, duplique la hoja o marque los repetidos con formato condicional para ver qué se va a perder.

Power Query: la limpieza que se repite sola

Si el archivo llega cada mes con la misma suciedad, limpiarlo a mano cada mes es trabajo perdido. Power Query —en la pestaña Datos > Obtener datos— graba los pasos de limpieza y los vuelve a aplicar sobre el archivo nuevo con un botón.

El flujo típico para nuestro caso tiene cinco pasos: conectar al archivo de origen, promover la primera fila a encabezados, dar el tipo correcto a cada columna, quitar las filas vacías y cargar el resultado como tabla en la hoja. Queda registrado en el panel Pasos aplicados, donde se puede editar o deshacer cualquiera de ellos.

Cuándo conviene Power Query y cuándo no

Power Query tiene además una virtud poco citada: no toca el archivo de origen. Trabaja sobre una copia de los datos, así que nunca se corrompe el original por un paso mal dado.

Buscar en tablas de Excel: XLOOKUP frente a BUSCARV

Para traer un dato de otra tabla de Excel, BUSCARV fue durante veinte años la respuesta y hoy ya no lo es. XLOOKUP —BUSCARX en la interfaz en español— resuelve sus tres limitaciones históricas.

Antes (frágil):
=BUSCARV([@Sede]; A2:D50; 3; FALSO)

Ahora (robusto):
=BUSCARX([@Sede]; tblSedes[Sede]; tblSedes[Responsable]; "sin dato")

⚠️ BUSCARX existe en Microsoft 365 y en Excel 2021 en adelante. Si el archivo se comparte con alguien que usa Excel 2016 o 2019, la fórmula le llegará como #¿NOMBRE?. En ese caso, la alternativa compatible es ÍNDICE con COINCIDIR, que también admite referencias estructuradas.

Formato condicional sobre tablas en Excel

El formato condicional aplicado a una columna de una tabla de Excel se extiende solo a las filas nuevas, cosa que no ocurre con un rango. Tres reglas útiles para nuestro conjunto de datos:

Una advertencia de rendimiento: el formato condicional con fórmula se recalcula constantemente. En una tabla de miles de filas con varias reglas encima, el archivo se vuelve lento. Si nota demoras, empiece por ahí.

Errores frecuentes con tablas en Excel

Atajos que compensan aprenderse

Preguntas frecuentes sobre tablas en Excel

¿Cuántas filas aguanta una tabla de Excel?

El límite de la hoja es de 1.048.576 filas, y la tabla puede llegar hasta ahí. En la práctica, mucho antes de ese número el archivo se vuelve pesado. A partir de unos cientos de miles de filas conviene cargar los datos al modelo de datos en lugar de a la hoja, cosa que se ve en la guía de tablas dinámicas.

¿Se puede volver atrás y dejarlo como rango?

Sí: Diseño de tabla > Convertir en rango. El formato se queda, pero se pierden las referencias estructuradas y las fórmulas se reescriben como coordenadas. Es una operación de ida y vuelta segura, aunque rara vez hay motivo para hacerla.

¿Las tablas en Excel funcionan en la versión web?

Sí. Crear tablas, filtrar, usar referencias estructuradas y la fila de totales funcionan en Excel para la web. Lo que allí no está disponible es Power Query completo ni las macros, que exigen la aplicación de escritorio.

¿Por qué mi fórmula no se copia a las filas nuevas?

Casi siempre porque la columna tiene fórmulas distintas en algunas filas y Excel ha desactivado la columna calculada. Corrija una celda, pulse el icono que aparece y elija Sobrescribir todas las celdas de esta columna con esta fórmula.

¿Conviene una tabla por hoja o varias?

Como norma, una tabla de datos por hoja y las tablas auxiliares —sedes, áreas, tarifas— en una hoja de catálogos aparte. Mezclar varias tablas grandes en una misma hoja complica los filtros y estorba al insertar filas.

El siguiente paso de la serie

Con tblTickets creada, nombrada, validada y limpia, los datos ya están listos para responder preguntas. La segunda guía de la serie los resume por sede, por área y por categoría en segundos y sin escribir una sola fórmula.

En KHARONTE administramos las licencias, los usuarios y los accesos de Microsoft 365 de nuestros clientes, con todo a nombre de la empresa. Si quiere saber qué cambió en el catálogo y en la factura, lo explicamos en el artículo sobre los precios de Microsoft 365, y el lado de la administración, en la guía del centro de administración.