Modelado de datos en Excel: qué es y cómo empezar

Cuando los datos de ventas, clientes y productos viven en una sola hoja repetida hasta el infinito, cualquier cambio de precio se vuelve una pesadilla: hay que buscar la cadena de texto, reescribirla y rezar para no dejarse ninguna copia. Un modelo de datos es el plano que evita ese desastre: decides qué tablas existen, qué columnas tiene cada una y cómo se conectan antes de escribir un solo número. Al terminar este tutorial tendrás un libro de Excel con un esquema dibujado, tres tablas relacionadas y una tabla dinámica que cruza clientes con productos, listo para crecer sin romperse. Funciona en Microsoft 365, Excel 2024 y 2021; en Excel 2019 y 2016 la herramienta Relaciones existe pero Power Query cambia de ubicación según la instalación.

Qué es un modelo de datos y por qué te conviene dibujarlo antes de empezar

Imagina la hoja «Ventas» típica: una columna para el cliente, otra para el nombre del producto, otra para la ciudad, otra para la categoría, y los importes repetidos fila tras fila. El día que un cliente cambia de ciudad hay que buscarla en cada fila; el día que se renombra un producto, el doble. Cuando aparecen dos clientes que se llaman igual, ya no sabes cuál es cuál.

El modelo de datos es el plano que se diseña antes de tocar el teclado para que eso no pase. Tiene tres niveles:

• Conceptual: qué entidades existen (Cliente, Producto, Venta) y cómo se relacionan en lenguaje humano.
• Lógico: cada entidad con sus atributos (Cliente: IdCliente, Nombre, Ciudad) y el tipo de cada uno (texto, número, fecha).
• Físico: las tablas tal como quedan en Excel, con sus columnas, sus tipos y las hojas en que viven.

Nivel          | Qué se decide                  | Ejemplo
-------------- | ------------------------------ | -----------------------------------------
Conceptual     | Entidades y relaciones         | Cliente vende Productos
Lógico         | Atributos y claves             | Cliente(IdCliente, Nombre, Ciudad)
Físico         | Tablas, columnas y tipos       | Hoja "Clientes" con A1 como texto

Excel no es una herramienta de modelado como Erwin Data Modeler o IBM InfoSphere Data Architect, pero para modelos pequeños y medianos basta y sobra: una hoja con el esquema, tres tablas y la herramienta Relaciones.

Las piezas del modelo que vas a usar una y otra vez

Estas son las piezas que se repiten en cualquier modelo, da igual el tamaño:

• Entidad: lo que se convierte en tabla (Clientes, Productos, Ventas).
• Atributo: cada columna de la tabla (Nombre, Ciudad, Importe).
• Clave primaria: la columna que identifica la fila sin ambigüedad (IdCliente, IdProducto, IdVenta). No se repite nunca.
• Clave externa: la columna en una tabla que apunta a la clave primaria de otra (IdCliente dentro de Ventas apunta a la fila de ese cliente en Clientes).
• Relación: el vínculo entre dos tablas. Casi siempre es uno a varios (un cliente tiene muchas ventas; un producto aparece en muchas ventas). El caso varios a varios (un cliente compra varios productos y un producto lo compran varios clientes) se resuelve con una tabla intermedia, que en este tutorial es la propia tabla Ventas.
• Esquema: la forma final del modelo. En Excel aguantas bien tres:

– Plano: todo en una hoja, útil para datos pequeños o de importación. – Relacional en hojas separadas: cada entidad en su hoja, conectadas por claves. El más limpio. – Estrella: una tabla de hechos (Ventas) rodeada de tablas de dimensión (Clientes, Productos, Fechas). Es el que usan las herramientas de análisis.

Dibuja el esquema en una hoja con solo cuadro y texto

Antes de crear las tablas, dibuja el plano en una hoja aparte. Así ves de un vistazo qué falta y qué sobra.

1. Añade una hoja nueva y llámala Esquema (clic derecho en la pestaña > Cambiar nombre).
2. En Insertar > Ilustraciones > Formas elige el rectángulo.
3. Dibuja tres rectángulos separados: «Clientes», «Productos» y «Ventas».
4. Haz clic dentro de cada uno y escribe su nombre.
5. En Insertar > Ilustraciones > Formas elige la flecha recta.
6. Une la clave primaria con su clave externa: una flecha desde el rectángulo «Clientes» hasta «Ventas» y otra desde «Productos» hasta «Ventas».
7. Mete los atributos como líneas de texto dentro de cada rectángulo, con la clave primaria subrayada o en negrita para distinguirla.

Tablas, columnas y tipos de dato

Antes de pasar el esquema a Excel, conviene dejar escritos los atributos y su tipo. Esto se llama diccionario de datos y es lo que más cuesta rehacer si te lo saltas.

Tabla     | Columna      | Tipo         | Ejemplo
--------- | ------------ | ------------ | -----------
Clientes  | IdCliente    | Número entero| 1
Clientes  | Nombre       | Texto        | Ana Ruiz
Clientes  | Ciudad       | Texto        | Lima
Productos | IdProducto   | Número entero| 10
Productos | Nombre       | Texto        | Cuaderno
Productos | Categoría    | Texto        | Papelería
Ventas    | IdVenta      | Número entero| 1001
Ventas    | Fecha        | Fecha        | 15/03/2026
Ventas    | IdCliente    | Número entero| 1
Ventas    | IdProducto   | Número entero| 10
Ventas    | Unidades     | Número entero| 3
Ventas    | Importe      | Moneda       | 12,50

La columna «Importe» se guarda como número con formato moneda; las claves, como número entero sin decimales.

Las relaciones entre tablas, una a una

Dos relaciones en este modelo:

• Cliente (1) → Ventas (varios) por IdCliente: un cliente puede tener muchas ventas, pero cada venta pertenece a un solo cliente.
• Producto (1) → Ventas (varios) por IdProducto: un producto puede aparecer en muchas ventas, pero cada venta tiene un solo producto.

Para reflejarlas en el esquema:

1. Dibuja una flecha desde el rectángulo «Clientes» hasta el rectángulo «Ventas» y etiqueta el extremo con IdCliente.
2. Dibuja otra flecha desde «Productos» hasta «Ventas» y etiquétala con IdProducto.

Crea las tablas en hojas separadas y dale forma de tabla de Excel

Ahora que el esquema está en papel (o en una hoja), se traslada a Excel. Una tabla por hoja, con sus datos, y cada bloque se convierte en tabla de Excel para que los nombres y los rangos se actualicen al crecer.

Hoja Clientes:

Hoja Productos:

Hoja Ventas:

Pasos para cada hoja:

1. Escribe los datos como en las tablas de arriba.
2. Selecciona todo el bloque con sus encabezados (por ejemplo, A1:C5 en Clientes).
3. Pulsa Ctrl+T para crear la tabla de Excel. Marca la casilla «La tabla tiene encabezados» y pulsa Aceptar.
4. En la pestaña Diseño de tabla que aparece, cambia el nombre a Clientes, Productos o Ventas según corresponda (campo «Nombre de la tabla», arriba a la izquierda).

Relaciona las tablas sin escribir una fórmula

Excel trae una herramienta pensada justo para esto: Relaciones. Une las tablas por sus claves y permite que las tablas dinámicas las crucen sin BUSCARV.

1. Ve a la hoja Ventas y haz clic en cualquier celda de la tabla.
2. Abre Datos > Herramientas de datos > Relaciones.
3. Pulsa Nueva y rellena:

1. Repite para IdProducto, eligiendo Productos como tabla relacionada.
2. Cierra el cuadro y vuelve a la hoja.

La tabla dinámica que cruza Cliente con Productos

Las relaciones no se ven hasta que las usas en una tabla dinámica.

1. Haz clic en la tabla Ventas y ve a Insertar > Tablas > Tabla dinámica.
2. Acepta el destino en una hoja nueva.
3. En el panel de la derecha:

– Arrastra Nombre (de Clientes) a Filas. – Arrastra Nombre (de Productos) a Columnas. – Arrastra Importe a Valores.

1. La tabla dinámica muestra cada cliente como fila y cada producto como columna, con la suma de importes en el cruce.

Trae un valor concreto con BUSCARV desde otra tabla

La herramienta Relaciones solo sirve para tablas dinámicas. Si necesitas el dato de otra tabla en una celda concreta, usa BUSCARV (en inglés VLOOKUP).

En la hoja Ventas, añade una columna Ciudad a la derecha de IdCliente y escribe en D2:

=BUSCARV(C2;Clientes!$A:$C;3;FALSO)

Cuando los datos viven en una sola hoja, modelar también cuenta

No siempre se empieza de cero. Muchas veces llega una hoja plana exportada de otro sistema, con columnas repetidas para cada cliente o cada mes: Enero, Febrero, Marzo…, o Cliente1, Cliente2, Cliente3… Esas hojas son una pesadilla: los meses deberían ser filas, no columnas, y los clientes repetidos deberían ser una sola fila en una tabla aparte.

Detectar columnas que deberían ser una tabla aparte es fácil si te haces estas preguntas:

• ¿Esta columna repite el mismo valor muchas veces? Entonces es candidata a tabla de dimensión.
• ¿Cambia su valor a menudo? Si cambia el nombre de un cliente en una fila y no en las demás, tienes un problema.
• ¿Podría tener atributos propios? Si los clientes tienen ciudad, teléfono o correo, no deberían ir en cada fila de venta.

Separa una tabla plana con Power Query

Power Query (en Excel 2016 y posteriores, en la pestaña Datos) deja grabado el proceso: lo haces una vez y se aplica con Actualizar cada vez que cambian los datos originales.

Supón esta hoja Plano:

Pasos para pasarlo a modelo:

1. Selecciona la tabla y ve a Datos > Obtener y transformar datos > Desde tabla o rango.
2. En el editor de Power Query, selecciona las columnas Ana, Pedro, Lucía y Diego.
3. Ve a Transformar > Anular dinamización de columnas > Anular dinamización solo de las columnas seleccionadas.
4. Aparecen dos columnas: Atributo (con los nombres de los clientes) y Valor (con las unidades). Renómbralas a Cliente y Unidades.
5. Quita las filas con Unidades en blanco (clic en la flecha del filtro de la columna Unidades y desmarca (En blanco)).
6. Cierra y carga en Cerrar y cargar > Cerrar y cargar en... > Tabla > Hoja de cálculo existente, en la celda A1 de una hoja nueva llamada VentasPowerQuery.

Los errores que de verdad pasan cuando el modelo está mal

Cómo encontrar los duplicados antes de que exploten

La tabla dinámica suma de más porque hay dos clientes con el mismo nombre y la misma ciudad, pero distinto IdCliente. La hoja no avisa; el modelo se rompe en silencio.

Pasos para detectarlos:

1. Selecciona la columna IdCliente de la tabla Clientes.
2. Ve a Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Valores duplicados.
3. Elige el formato (relleno rojo claro) y pulsa Aceptar. Las celdas duplicadas se pintan.
4. Revisa las pintadas: si son errores, bórralas; si son correctas (dos clientes con el mismo nombre por casualidad), asigna un nuevo IdCliente.

Cuando la relación no se deja crear

El cuadro de Relaciones devuelve el mensaje «No se puede crear la relación porque las columnas contienen tipos de datos diferentes». La causa: IdCliente en Clientes es número, pero en Ventas alguien lo escribió como texto.

Arreglo paso a paso:

1. En la hoja Ventas, selecciona la columna IdCliente (la columna C de los datos, sin el encabezado).
2. Ve a Datos > Herramientas de datos > Texto en columnas. Pulsa Aceptar dos veces (sin cambiar opciones).
3. Vuelve a seleccionar la misma columna y, con Ctrl+1, abre Formato de celdas > Número > Número con cero decimales. Aceptar.
4. Repite en Clientes para asegurar el mismo formato.
5. Vuelve a Datos > Herramientas de datos > Relaciones > Nueva y crea la relación: ya no dará error.

Lo que Excel no hace y qué herramienta usar en su lugar

Excel sirve para diseñar y mantener modelos pequeños y medianos, pero hay cosas que no hace:

• No genera documentación automática del modelo a partir de las tablas. La documentación la escribes tú (la hoja Esquema de este tutorial).
• No valida reglas UML. Si necesitas que el modelo cumpla notación estándar, usa Erwin, IBM InfoSphere o Lucidchart.
• No hace ingeniería inversa desde una base de datos real (lee los nombres de tablas, columnas y tipos de un servidor y los dibuja). Para eso están Erwin Data Modeler y dbdiagram.io.
• Las relaciones de la herramienta Relaciones solo sirven para tablas dinámicas. Para cruzar tablas en celdas, necesitas BUSCARV, INDICE y COINCIDIR, o BUSCARX en Microsoft 365 y Excel 2021 en adelante.
• No escala a millones de filas en una sola hoja sin pasar por Power Query o por un modelo de datos en memoria (Power Pivot).

Para modelos grandes, las opciones más usadas son Erwin Data Modeler (pago, estándar de la industria), IBM InfoSphere Data Architect (pago, orientado a empresa), Lucidchart y dbdiagram.io (gratuitos, dibujo) y dbt (gratis, transformaciones versionadas).

Lo que ya sabes hacer con un modelo en Excel y el siguiente paso

Cinco claves para llevarte de este tutorial:

• Dibujar el esquema en una hoja antes de tocar datos, con rectángulos y flechas.
• Separar cada entidad en su hoja y darle formato de tabla de Excel con Ctrl+T.
• Crear las relaciones desde Datos > Herramientas de datos > Relaciones, eligiendo bien clave externa y clave principal.
• Cruzarlas con una tabla dinámica que combine campos de varias tablas, sin escribir BUSCARV.
• Limpiar duplicados con formato condicional antes de unir, para que los totales cuadren.

El siguiente paso es alimentar el modelo desde fuera: usa Power Query para leer un CSV, una base de datos Access o una carpeta entera, y carga cada tabla en su hoja. Así, el día que cambie el origen, basta con pulsar Actualizar y todo se regenera sin tocar el modelo.

Datos comprobados en octubre de 2026.