Modelado de datos en Excel: qué es y cómo aplicarlo paso a paso

En este artículo entiendes qué es el modelado de datos dentro de Excel y por qué decide si tus hojas te dan totales fiables o números que no cuadran. Te llevo de la mano por los tres modelos que puedes montar dentro de un libro —plano, relacional y dimensional— y los construimos uno a uno sobre el mismo caso: una tienda que registra ventas y necesita cruzarlas con sus clientes y sus productos. Lo que vas a saber hacer al terminar es elegir el modelo adecuado según el problema, montar uno relacional con Power Query y uno dimensional con Power Pivot, y arreglar los errores típicos. Funciona en Excel 2016, 2019, 2021, 2024 y Microsoft 365 en Windows; Power Query también está en Mac y en la web con menos conectores, y Power Pivot solo en Windows. Datos comprobados en octubre de 2026.

Qué es el modelado de datos y por qué importa en una hoja de Excel

Imagina una hoja de ventas con clientes repetidos fila a fila: «Marta» aparece veinte veces, «Marta García» otras tantas, y la dirección cambia sin criterio. Cuando pides el total por cliente, salen números distintos según cómo esté escrito el nombre, y el informe mensual nunca cierra. Eso pasa porque la hoja guarda todo junto —cliente, producto, importe, fecha— sin pensar en cómo se va a consultar después.

Modelar datos es decidir cómo se organizan las tablas, qué columnas lleva cada una y cómo se relacionan entre sí. No es un paso decorativo:decide actualización que puedas cruzar información sin duplicarla, que los totales sean únicos para cada cliente y que añadir un producto nuevo no rompa las fórmulas. En una hoja pequeña no se nota; cuando la hoja pasa de unos miles de filas, el modelo es lo que separa un libro útil de uno que tarda en abrirse y devuelve resultados equivocados.

Los tres modelos que puedes armar dentro de Excel: plano, relacional y dimensional

Los tres modelos se construyen con las mismas filas y columnas, pero cambian las reglas de cómo se relacionan. El modelo plano apila todo en una sola tabla y es lo que sale por defecto al abrir Excel. El relacional divide la información en tablas conectadas por una clave común —un identificador que aparece en las dos—, de modo que cada dato se escribe una sola vez. El dimensional separa una tabla de hechos —los números que se quieren analizar, como ventas o importes— de varias tablas de dimensión que la describen —tiempo, producto, cliente—.

Modelo plano: una sola tabla con todo

La hoja Ventas que ves arriba es un modelo plano: cada fila trae todo lo necesario para entender esa venta. Es el formato que da Excel al escribir directamente y el más rápido para pocos registros. Se rompe al crecer porque el nombre del cliente se repite en cada venta: si cambias un dato del cliente, tienes que buscarlo en todas las filas; los informes por cliente dependen de que el nombre esté escrito exactamente igual, y una lista de 50.000 filas empieza a ir lenta.

Modelo relacional: varias tablas unidas por una clave

La hoja Pedidos solo guarda los identificadores (ID Cliente, ID Producto); los nombres y categorías viven en Clientes y Productos. Así, el nombre «Marta» aparece una vez. Para cruzar la información Excel usa funciones como BUSCARV o BUSCARX, o une las tablas con Power Query. Este modelo es el que ya emplea cualquier base de datos (Access, SQL Server) y el que mejor aguanta el crecimiento.

Modelo dimensional: hechos y dimensiones para analizar

La tabla Hechos guarda los números; DimTiempo y DimProducto (que tendría el mismo aspecto que Productos pero con ID como clave) describen cada venta. El modelo dimensional está pensado para analizar: una tabla dinámica puede poner meses en filas, categorías en columnas e importe en valores y resuelve el informe en un arrastre. Es el modelo que construyen Power Pivot, Power BI y casi cualquier herramienta de Business Intelligence.

Las herramientas de Excel que construyen cada modelo

Las tablas de Excel estructuran los datos y añaden bandas de color, autofiltro y nombres válidos para fórmulas. Power Query importa datos, los limpia y los une sin tocar el origen. Power Pivot crea el modelo en memoria y permite tablas dinámicas que cruzan varias tablas a la vez. Si quieres ampliar, la documentación oficial de Power Query y de Power Pivot está en support.microsoft.com.

Pasar de una tabla plana a un modelo relacional con Power Query

Partimos de la hoja Ventas del primer apartado: cuatro filas con cliente, producto, unidades e importe. Vamos a dividirla en tres tablas —Ventas, Clientes y Productos— y unirlas por sus identificadores, todo con Power Query, para conseguir el mismo informe limpio sin escribir BUSCARV.

Convertir los datos en una tabla de Excel

1. Selecciona A1:F5 con el ratón.
2. Pulsa Ctrl+T para abrir Crear tabla.
3. Marca La tabla tiene encabezados y pulsa Aceptar.
4. En la esquina izquierda, donde pone «Tabla1», escribe el nombre Ventas y pulsa Entrar.

Ahora los datos cambian de aspecto: aparecen bandas de color, una fila de totales abajo y filtros en cada encabezado. Al pulsar Tab desde la última celda, la fila siguiente se añade a la tabla automáticamente, listo para escribir un pedido nuevo.

Traer las tablas de clientes y productos con Power Query

Crea en el mismo libro dos hojas nuevas: Clientes (con los datos del apartado «Modelo relacional») y Productos (con los suyos). Conviértelas también en tablas con Ctrl+T y nómbralas Clientes y Productos.

1. Sitúate en la hoja Clientes y haz clic dentro de la tabla.
2. Ve a Datos > Obtener y transformar datos > Obtener datos > Desde otras fuentes > Desde tabla o rango.
3. En el editor de Power Query, pulsa Cerrar y cargar en… y elige Solo crear conexión.
4. Repite lo mismo con la hoja Productos.

Al volver a Excel, en la derecha aparece el panel Consultas y conexiones con Clientes y Productos listadas, pero sin nuevas hojas en el libro. Eso significa que Power Query está listo para combinarlas cuando se lo pidamos.

Unir las tres por ID de cliente y de producto

1. En Consultas y conexiones, doble clic sobre Ventas para abrir el editor.
2. En la cinta del editor: Inicio > Combinar > Combinar consultas.
3. Elige la tabla Ventas arriba y Clientes abajo; arrastra ID Cliente de arriba sobre ID Cliente de abajo. Pulsa Aceptar.
4. En la columna nueva, pulsa el icono de expandir (las dos flechas) y deja solo Nombre y Ciudad. Confirma con Aceptar.
5. Repite los pasos 2 a 4 para unir con Productos por ID Producto, quedándote con Producto y Categoría.
6. Pulsa Cerrar y cargar en… y marca Tabla y Hoja de cálculo existente en la celda A1 de una hoja nueva llamada VentasUnidas.

Construir un modelo dimensional con Power Pivot y tablas dinámicas

Ahora sube el listón: queremos cruzar tiempo, producto e importe en un solo informe. El modelo dimensional es el camino y Power Pivot es la herramienta que lo monta sin escribir código.

Activar Power Pivot y añadir las tablas al modelo de datos

1. Ve a Archivo > Opciones > Complementos.
2. En la parte inferior, en Administrar: Complementos de Excel, pulsa Ir….
3. Marca Microsoft Power Pivot para Excel y pulsa Aceptar`. La pestaña Power Pivot* aparece en la cinta.
4. Haz clic dentro de la tabla VentasUnidas, ve a Power Pivot > Tablas > Agregar a modelo de datos.
5. Repite con las tablas Clientes y Productos (o con la tabla dimensión que prefieras).

Crear la relación entre hechos y dimensiones

1. Pulsa Power Pivot > Administrar para abrir la ventana de Power Pivot.
2. En la pestaña *Inicio > Ver > Vista de diagrama`.
3. Arrastra el campo ID Cliente de la tabla Clientes sobre el campo ID Cliente de VentasUnidas.
4. En el cuadro Crear relación, deja el tipo Uno a varios y acepta.
5. Repite el arrastre entre ID Producto de Productos y ID Producto de VentasUnidas.

Las dos relaciones se dibujan como líneas continuas sin flecha, lo que indica que el motor las entiende y las usará en los informes.

Armar la tabla dinámica sobre el modelo de datos

1. Haz clic en una celda fuera de las tablas y ve a Insertar > Tablas > Tabla dinámica.
2. Marca Usar el modelo de datos de este libro y pulsa Aceptar.
3. En Campos de tabla dinámica, arrastra Año a *Filas*, Producto a *Columnas* e Importe` a Valores (queda como Suma de Importe*).

El total cruzado (18,20) coincide con lo que suma VentasUnidas. Esa coincidencia demuestra que el modelo dimensional está bien montado: las dimensiones Año y Producto llegan a los importes a través de las relaciones, no porque estuvieran duplicadas en la tabla de hechos.

Comparar el resultado con la misma tabla en Power Query

En Microsoft 365 puedes obtener el mismo total con una sola fórmula, sin tabla dinámica. En una celda vacía escribe:

=AGRUPARPOR(VentasUnidas[Producto];VentasUnidas[Importe];SUMA)

Modelos no relacionales: trabajar con JSON o texto dentro de Excel

No todo lo que entra en Excel viene en una tabla ordenada. A veces cada fila trae un bloque JSON con datos del cliente o el pedido, o una columna guarda varios valores separados por comas. Estos casos no son modelos relacionales, pero Excel tiene herramientas para tratarlos sin salir del libro.

Sacar datos de un JSON con Power Query

1. Guarda el archivo pedidos.json con este contenido.

[{"id":1,"cliente":"Marta","producto":"Libreta","importe":5},
{"id":2,"cliente":"Pedro","producto":"Boli","importe":2.4}]

1. Ve a Datos > Obtener y transformar datos > Obtener datos > Desde archivo > Desde JSON y elige el archivo.
2. En el editor, pulsa Listar y luego Convertir en tabla. Pulsa Aceptar.
3. Pulsa el botón de expandir y deja todas las columnas. Cerrar y cargar en… en una hoja nueva.

Excel ha convertido el JSON en una tabla normal, lista para unirla con otras tablas con los mismos pasos del modelo relacional.

Partir un texto largo en columnas con Texto en columnas

1. En la columna A tienes «García, Marta» y «López, Pedro».
2. Selecciona la columna y ve a Datos > Herramientas de datos > Texto en columnas.

1. Elige Delimitado > Coma y pulsa Finalizar. Los nombres y apellidos quedan en B y C.

Errores típicos al montar un modelo de datos en Excel

La tabla dinámica repite el cliente en cada fila

Sucede cuando el origen es plano y arrastras Cliente a filas sin agregar primero; cada fila es un pedido, no un cliente. La solución es agrupar: haz clic derecho sobre cualquier nombre en la tabla dinámica y elige Agrupar, o crea el modelo relacional para que Excel no tenga que repetir nombres.

La relación entre tablas sale con flecha y los totales dan vacío

En la Vista de diagrama de Power Pivot, si la línea que une dos tablas aparece con una flecha en vez de con un ∞, la relación está al revés. Bórrala (clic derecho > Eliminar relación) y vuelve a crearla arrastrando desde la tabla dimensión hacia la tabla de hechos. Con la flecha desapareciendo y el ∞ presente, los totales dejarán de salir vacíos.

#¡DESBORDAMIENTO! al usar FILTRAR sobre una tabla mal dimensionada

=FILTRAR(A2:D5;C2:C5>5)

Si la celda justo debajo de la fórmula ya tiene datos, Excel muestra #¡DESBORDAMIENTO!. Borra esas celdas o pega la fórmula en una zona vacía. La función FILTRAR (en inglés FILTER) existe desde Excel 2021 y Microsoft 365; en versiones anteriores usa tablas dinámicas o la herramienta Filtro avanzado en Datos > Ordenar y filtrar.

El modelo de datos no aparece al crear la tabla dinámica

Si al abrir Power Pivot la ventana está vacía, significa que las tablas no se añadieron. Cierra la ventana, vuelve a la hoja, haz clic dentro de la tabla que quieres incluir y ejecuta Power Pivot > Tablas > Agregar a modelo de datos. Repite con cada tabla antes de insertar la tabla dinámica.

Lo que ya sabes hacer con los tipos de modelado en Excel

• Tres modelos en un mismo libro: plano, relacional y dimensional, cada uno con su caso de uso.
• Tablas de Excel con Ctrl+T para dar estructura a cualquier lista.
• Power Query para unir Clientes, Productos y Pedidos por sus claves y dejar el resultado listo para refrescar.
• Power Pivot para montar hechos y dimensiones y analizarlos con una tabla dinámica que cruza tiempo y producto.
• Análisis no relacionales con JSON y Texto en columnas sin salir de Excel.

El siguiente paso natural es cargar varios archivos de una carpeta con Power Query (Datos > Obtener datos > Desde archivo > Desde carpeta) para escalar este modelo relacional a decenas de libros mensuales sin tocar nada a mano.

Dudas al usar los modelos de datos en Excel

Dudas al usar los modelos de datos en Excel