Modelo de datos en Excel: qué es y cómo crear uno paso a paso

Antes de tocar una sola celda, piensa qué información quieres guardar y cómo se relaciona. Un modelo de datos lógico te obliga a separar los datos en entidades (clientes, pedidos, productos), poner a cada una sus atributos como columnas y dibujar las relaciones antes de escribir nada en el libro. Al terminar, sabrás crear tres hojas relacionadas en Excel, unirlas dentro del Modelo de datos que viene con Excel 2016 y posteriores (también en 2021, 2024 y Microsoft 365) y comprobar que una tabla dinámica cruza las tres tablas sin contar ningún dato dos veces.

Qué es un modelo de datos lógico y por qué se hace antes de tocar Excel

Imagina una hoja de "Ventas2025" en la que cada fila mezcla el nombre del cliente, su teléfono, el producto, su precio y el importe del pedido. Esa hoja funciona al principio, pero el día que un cliente cambia de teléfono hay que buscarlo en cientos de filas, y si dos vendedores escriben "Ana López" y "A. Lopez" el mismo cliente aparece dos veces en los totales.

Un modelo de datos lógico resuelve esto antes de crear la hoja. Divide la información en entidades separadas y conecta unas con otras mediante claves: una tabla para clientes, otra para pedidos, otra para productos. Cada dato vive en un único sitio, y las hojas se enlazan en lugar de duplicarse.

La gracia práctica es que después podrás construir una tabla dinámica que sume ventas por categoría de producto filtrando por ciudad del cliente, algo que en una hoja plana exige BUSCARV (VLOOKUP) encadenados o columnas auxiliares que se estropean en cuanto cambia un dato.

Hacer el modelo lógico antes de tocar Excel no es un capricho: es lo que permite que el archivo crezca sin romperse, que varios usuarios escriban a la vez y que los datos puedan irse mañana a Access, a Power Query o a una base de datos real.

Las piezas de un modelo lógico: entidades, atributos, claves y relaciones

Una entidad es cada "cosa" sobre la que guardas información, y en Excel se convierte en una hoja. En nuestro libro de tienda habrá tres: Clientes, Productos y Pedidos. Cada atributo es una columna dentro de la entidad: el cliente tiene Nombre, Ciudad y FechaAlta; el producto tiene Nombre, Categoría y Precio; el pedido tiene Fecha, IDCliente, IDProducto, Cantidad e Importe.

La clave primaria es el campo que identifica cada fila sin confusión: en Clientes será IDCliente (un número único por cliente), en Productos IDProducto, y en Pedidos IDPedido. La regla es que la clave primaria nunca se repite y nunca queda vacía, porque es el "DNI" de la fila.

La clave externa es el puente entre tablas: en Pedidos, IDCliente apunta al cliente que hizo el pedido, e IDProducto apunta al producto comprado. Ambas son claves externas que en otra tabla son la clave primaria.

Las relaciones son de tres tipos. Uno a uno (1:1), por ejemplo un cliente y su único DNI adjunto: una fila de cada tabla conecta con una fila de la otra. Uno a muchos (1:N), el caso normal: un cliente hace muchos pedidos, un producto aparece en muchas líneas de pedido. Muchos a muchos (N:M), que se resuelve con una tabla intermedia, como aquí Pedidos, donde cada cliente se cruza con muchos productos y cada producto aparece en muchos pedidos.

De la idea al esquema: dibujar el modelo lógico en una hoja de Excel

Antes de crear las tablas definitivas, dibuja el esquema en una hoja llamada "Esquema". Aquí no hay datos reales, solo la forma del modelo.

1. Crea una hoja nueva y llámala "Esquema".
2. En A1 escribe Clientes, en negrita y con fondo de color. En A2:A6 escribe los nombres de los atributos: IDCliente, Nombre, Ciudad, FechaAlta, Email.
3. En B1 escribe Tipo y en B2:B6 el tipo de cada uno: Número, Texto, Texto, Fecha, Texto.
4. En C1 escribe Clave y marca PK (clave primaria) en la fila de IDCliente con PK y FK (clave externa) cuando la columna aparezca en otra tabla.
5. Debajo, repite el bloque para Productos (IDProducto, Nombre, Categoría, Precio) y para Pedidos (IDPedido, Fecha, IDCliente, IDProducto, Cantidad, Importe).

Pasar del esquema a tablas en Excel: una hoja por entidad

Ahora traslada el esquema a tres hojas reales con datos pequeños. Una hoja por entidad evita duplicar información y prepara el terreno para las relaciones.

1. Crea las hojas Clientes, Productos y Pedidos y copia los datos de cada bloque en su sitio.
2. En la fila 1 de cada hoja pon los encabezados en negrita: IDCliente, Nombre, Ciudad, FechaAlta, Email; IDProducto, Nombre, Categoria, Precio; y IDPedido, Fecha, IDCliente, IDProducto, Cantidad, Importe.
3. Asegúrate de que las columnas C y D de Pedidos contienen los IDs ya escritos a mano: son las claves externas que conectarán con las otras dos hojas.

Crear las tablas con la herramienta Tabla de Excel

Convertir cada hoja en Tabla de Excel hace que el rango se expanda solo al añadir filas, lo que mantiene vivas las relaciones que crearás en el siguiente paso.

1. Sitúa el cursor en cualquier celda con datos de Clientes y pulsa Ctrl+A para seleccionar todo.
2. En Insertar > Tablas > Tabla, marca la casilla "La tabla tiene encabezados" y acepta. Llama a la tabla tblClientes en el cuadro Nombre de tabla (pestaña Diseño de tabla > extremo izquierdo).
3. Repite con Productos (llámala tblProductos) y con Pedidos (llámala tblPedidos).

Relacionar las tablas en el Modelo de datos de Excel

Con las tres tablas listas, llega el paso clave: decirle a Excel que la columna IDCliente de Pedidos es la misma información que la columna IDCliente de Clientes. Eso es una relación y vive dentro del Modelo de datos (Power Pivot), disponible desde Excel 2016 en Windows; en Mac hay menos opciones y en la web varía según el plan.

1. Ve a Datos > Herramientas de datos > Relaciones > Nueva relación.
2. Crea la primera relación: Tabla tblClientes, Columna externa (clave primaria) IDCliente; Tabla relacionada tblPedidos, Columna relacionada (clave externa) IDCliente. Acepta.
3. Repite para la segunda relación: tblProductos.IDProducto con tblPedidos.IDProducto. Acepta.

Una vez creadas, el cuadro Relaciones muestra dos entradas. Ya tienes un pequeño data warehouse dentro del propio libro.

Comprobar el modelo con una tabla dinámica que cruza las tablas

La validación más rápida del modelo es una tabla dinámica que cruce tablas que antes estaban separadas en hojas distintas.

1. Selecciona cualquier celda de Pedidos y abre Insertar > Tablas > Tabla dinámica.
2. Elige Nueva hoja de cálculo y marca la casilla "Agregar estos datos al Modelo de datos". Acepta.
3. En el panel de campos, arrastra Categoria (desde Productos) a Filas.
4. Arrastra Importe (desde Pedidos) a Valores. Por defecto aplica SUMA.
5. Arrastra Ciudad (desde Clientes) a Filas, encima o debajo de Categoría, para ver el cruce.

Sin escribir una sola fórmula, la tabla dinámica ha sumado los pedidos uniendo las tres tablas a través de sus claves. Eso solo es posible porque antes se definieron las relaciones.

Errores típicos al montar el modelo lógico en Excel

Repetir el nombre y la ciudad del cliente en cada fila de Pedidos

Suele verse en libros "planos" importados de otro sistema. La Pedidos termina con NombreCliente, CiudadCliente, IDCliente, IDProducto, Importe. El problema es que si un cliente cambia de ciudad hay que buscar todas sus filas, y cualquier divergencia rompe las SUMAR.SI (SUMIFS) posteriores.

La causa es no normalizar: el atributo Ciudad pertenece a Clientes, no a Pedidos. El arreglo es borrar NombreCliente y CiudadCliente de Pedidos, dejar solo IDCliente como clave externa y traer la ciudad a los informes con relaciones o con un BUSCARV sobre la tabla Clientes.

Dos tablas con una columna "ID" sin relación entre sí

Tras crear las relaciones, la tabla dinámica muestra sumas mayores de lo esperado, porque el mismo pedido se cuenta en cada tabla.

La causa es que se ha relacionado la columna equivocada (por ejemplo, IDProducto de Productos con IDPedido de Pedidos) o que las dos tablas tienen un campo llamado "ID" pero con datos diferentes. El arreglo es abrir Datos > Herramientas de datos > Relaciones, eliminar la que está mal y crearla de nuevo con las columnas correctas, asegurándose de que ambas contienen los mismos valores numéricos sin espacios ni ceros a la izquierda distintos.

Nombres de campo con tilde, espacio o mayúsculas distintas en cada tabla

Power Query cambia el nombre si encuentra "Categoría" y "categoria" y los reporta como dos columnas distintas; las relaciones fallan en silencio porque Excel no avisa.

La causa es no haber unificado los encabezados al crear las tablas. El arreglo es decidir una convención al inicio (por ejemplo, sin tildes y en singular: Categoria, Precio, Importe), aplicarla en las tres hojas con el mismo nombre exacto y, si el archivo viene de otro sistema, pasar antes por Datos > Obtener y transformar datos para normalizar.

Lo que Excel no hace como herramienta de modelado lógico

Excel es una hoja de cálculo, no una herramienta CASE de modelado. No genera el diagrama Entidad-Relación de cero ni exporta el esquema a SQL. No valida la cardinalidad: si escribes relación 1 a muchos y permites que dos clientes compartan el mismo ID, no te avisa. Tampoco aplica las formas normales ni te dice si una columna debería estar en otra tabla; eso lo decides tú en el papel.

Cuando necesitas generar el diagrama y validarlo están herramientas como Erwin Data Modeler, Visual Paradigm, IBM InfoSphere Data Architect y, para esquemas grandes a partir de datos existentes, SQL Developer Data Modeler de Oracle o dbt para modelado analítico. La documentación oficial de cada uno está en sus páginas de producto (erwin.com, visual-paradigm.com, oracle.com y docs.getdbt.com).

En resumen: Excel es un buen sitio para ejecutar el modelo lógico (consultas, informes, Power Query para cargar), no para diseñarlo y validarlo de forma gráfica.

Lo que ya sabes hacer con un modelo de datos lógico en Excel

• Separas la información en entidades (Clientes, Productos y Pedidos), cada una con sus atributos como columnas.
• Marcas la clave primaria de cada tabla y usas claves externas en Pedidos para conectar con las otras dos.
• Creas las tablas con Insertar > Tablas > Tabla para que el rango crezca solo al añadir filas.
• Defines las relaciones en Datos > Herramientas de datos > Relaciones dentro del Modelo de datos.
• Compruebas el modelo con una tabla dinámica que cruza tablas distintas sin escribir fórmulas.

El siguiente paso es cargar las mismas tres tablas desde archivos CSV distintos en Power Query (Datos > Obtener y transformar datos > Desde un archivo > Desde texto/CSV), aplicar los pasos de limpieza vistos aquí (sin tildes, tipos correctos) y conectarlas al Modelo de datos para que el esquema se actualice con un solo clic.

Datos comprobados en octubre de 2026.

Dudas al montar un modelo de datos lógico en Excel