Modelo de Business Intelligence en Excel: guía para empezar

Un modelo de business intelligence en Excel es la forma de pasar de una hoja llena de cifras sueltas a un cuadro de mando que responde preguntas de negocio con unos clics: cuánto se ha vendido, en qué región, por qué producto, comparando meses. Sirve para una tienda, una pyme o un departamento que ya tiene datos en Excel y quiere verlos de otra manera, sin montar una herramienta nueva.

En este tutorial montarás uno con un libro realista: una tienda con tres tablas (Ventas, Productos y Clientes). Al terminar tendrás el modelo relacionado, dos medidas en DAX, dos tablas dinámicas y dos segmentadores que filtran todo a la vez. Lo que se explica funciona en Excel para Microsoft 365, Excel 2024 y Excel 2021, y en Excel 2019 y 2016 con la pestaña Power Pivot activada (más abajo se indica cómo). En Excel para la web no están Power Pivot ni VBA, pero el resto sí.

Qué es un modelo de BI y para qué sirve montarlo en Excel

Business intelligence (BI) es, en la práctica, convertir datos en bruto en indicadores visuales que responden preguntas de negocio. Una hoja con mil filas de ventas no es BI: es una lista. Un modelo de BI es la misma lista, pero cruzada con productos y clientes, con totales que se calculan al vuelo, con segmentadores para filtrar por región o por mes y con gráficos que cambian cuando filtras.

Imagina una tienda que registra cada venta en una fila: fecha, id de producto, id de cliente, importe y región. Esa hoja por sí sola no dice mucho. Cuando juntas Ventas con Productos (con su nombre y categoría) y con Clientes (con su ciudad y tipo), aparece algo nuevo: puedes ver las ventas por categoría en cada región, el ticket medio por tipo de cliente o el mejor mes por producto. Eso ya es BI: las mismas filas, respondiendo preguntas que antes no se podían hacer sin copiar y pegar.

De los datos en bruto a los indicadores que importan

La diferencia entre una lista y un modelo está en cuatro piezas que trabajan juntas: las tablas con un nombre propio (tablas del modelo), las relaciones entre ellas (para que un id de producto se conecte con su fila de Productos), las medidas (fórmulas en DAX que calculan totales, promedios o porcentajes) y los segmentadores (controles visuales que filtran a la vez todas las tablas dinámicas conectadas). Cuando están las cuatro, lo que el lector ve son indicadores, no filas sueltas.

Por qué Excel es un buen lugar para empezar

Excel trae de serie las piezas que cualquier BI usa: Power Query para traer y limpiar datos, Power Pivot para el modelo de datos y las medidas DAX, tablas dinámicas para resumir y segmentadores para filtrar. Cuando el modelo crece, ese mismo libro se conecta con Power BI sin reescribir nada: el modelo se publica y se mantiene en la nube. Empezar en Excel es, en muchos casos, el camino más corto al BI.

Las piezas del modelo: Power Query, Power Pivot, tablas dinámicas y segmentadores

Las cuatro piezas están en la cinta de opciones y se usan en este orden: primero Power Query limpia y carga los datos, después Power Pivot los relaciona y mide, por último las tablas dinámicas y los segmentadores presentan el resultado.

Power Query se abre desde Datos > Obtener y transformar datos. Sirve para traer datos de archivos, bases o páginas web, y para limpiarlos sin tocar la hoja original: cada cambio queda como un paso que se puede borrar, rehacer o reordenar.

Power Pivot es la pestaña donde vive el modelo de datos: las tablas cargadas se ven como fichas, se relacionan arrastrando campos y se escriben medidas en DAX. En Excel 2019 y 2016 viene como complemento y se activa aparte; en Excel 2021, 2024 y Microsoft 365 ya está integrada.

Las tablas dinámicas se insertan desde Insertar > Tablas > Tabla dinámica, y al elegir «Usar modelo de datos de este libro» pueden usar todas las tablas relacionadas. Los segmentadores se insertan desde Insertar > Filtros > Segmentación de datos, y al conectarlos a varias tablas dinámicas filtran todas a la vez.

Power Query: traer y limpiar los datos sin tocar la hoja

El editor de Power Query tiene tres paneles: Consultas (a la izquierda, las tablas cargadas), Vista previa (en el centro, los datos que se están transformando) y Pasos aplicados (a la derecha, la lista de cambios que has ido haciendo). Cada clic —quitar una columna, cambiar un tipo, filtrar— añade un paso. Si un paso sobra, se borra con la X a su derecha; si está en mal orden, se arrastra. La hoja de Excel no se toca: lo que se transforma es una copia lógica en el modelo.

Power Pivot: el modelo de datos donde se relacionan las tablas

Al abrir Power Pivot aparece la vista de datos, con cada tabla en una pestaña abajo. El botón Vista de diagrama (en la pestaña Inicio de Power Pivot) coloca las tablas como cajas y permite arrastrar un campo de una caja sobre el campo equivalente de otra para crear una relación. Las columnas calculadas se escriben en una celda de la tabla y se evalúan fila a fila; las medidas se escriben en el área de cálculo y se evalúan en el contexto del filtro (es decir, cambian cuando filtras con un segmentador).

Tablas dinámicas: la capa visual que cruza los datos

Insertar > Tablas > Tabla dinámica abre un cuadro con dos decisiones: de dónde sacar los datos y dónde poner la tabla. Si marcas «Usar modelo de datos de este libro», la tabla dinámica puede usar cualquier tabla del modelo y cualquier medida. La lista de campos, a la derecha, tiene cuatro cuadrantes: Filas, Columnas, Valores y Filtros. Arrastrar un campo a Filas lo convierte en el eje vertical; a Valores, en lo que se calcula; a Filtros, en un desplegable encima de la tabla.

Segmentadores y líneas de tiempo para filtrar a la vez

Un segmentador es un botón flotante con los valores únicos de un campo: clicar «Norte» filtra todas las tablas dinámicas conectadas. La línea de tiempo es lo mismo, pero con un eje de fechas y un control de meses o años. Ambos se insertan desde la pestaña Análisis de tabla dinámica, o con clic derecho sobre la tabla dinámica. Para que un segmentador afecte a varias tablas dinámicas se usa Segmentaciones de datos > Conexiones de informe y se marca cada tabla.

Cargar ventas, productos y clientes con Power Query

El libro de ejemplo tiene tres hojas: Ventas (12 filas de ventas de 2026), Productos (6 productos con su categoría) y Clientes (5 clientes con su región). Cada tabla se importa con Power Query desde su hoja y se carga solo como conexión al modelo, sin duplicar los datos.

Traer cada tabla desde su hoja y dejarla como tabla con nombre

1. En la hoja Ventas, selecciona cualquier celda del rango y ve a Datos > Obtener y transformar datos > Desde tabla o rango. Si pide crear una tabla, acepta: la primera fila son los encabezados.
2. En el editor de Power Query, en el panel Consultas, cambia el nombre de la consulta a tblVentas (clic derecho > Cambiar nombre).
3. Repite con la hoja Productos y renombra la consulta a tblProductos.
4. Repite con la hoja Clientes y renombra a tblClientes.

Quitar columnas que no pintan nada y cambiar tipos de dato

1. En tblVentas, si quedó una Columna1 vacía a la derecha, clic sobre su encabezado y pulsar Supr (o Inicio > Quitar columnas > Quitar columnas).
2. Clic sobre el encabezado Importe y elige Transformar > Tipo de dato: Número decimal fijo.
3. Clic sobre Fecha y elige Transformar > Tipo de dato: Fecha.

Cerrar y cargar al modelo de datos

1. En el editor de Power Query, ve a Inicio > Cerrar y cargar > Cerrar y cargar en…
2. En el cuadro, marca Solo crear conexión y, debajo, marca Agregar estos datos al modelo de datos.
3. Pulsa Aceptar.

Relacionar las tablas en Power Pivot y crear medidas DAX

Con las tres tablas en el modelo, llega el momento de conectarlas. La regla es arrastrar el campo clave de la tabla de dimensión (Productos, Clientes) sobre su equivalente en la tabla de hechos (Ventas). Después se crean dos medidas: una suma y un promedio.

Crear la relación arrastrando el campo clave

1. Abre la pestaña Power Pivot y pulsa Inicio > Vista de diagrama.
2. Arrastra Productos[IdProducto] sobre Ventas[IdProducto].
3. Repite con Clientes[IdCliente] sobre Ventas[IdCliente].
4. Comprueba que las dos líneas son continuas (relación activa).

Medida Importe total con SUM

1. En Power Pivot, abajo del todo, ve al área de cálculo (la celda vacía bajo las tablas).
2. Escribe esta fórmula en una celda vacía del área de cálculo:

=SUM(tblVentas[Importe])

1. Pulsa Entrar.
2. Selecciona la celda de la medida, ve a Inicio > Propiedades de medida y, enDescripción, escribe: Importe total de ventas.

Medida Ticket medio con AVERAGE

1. En otra celda del área de cálculo, escribe:

=AVERAGE(tblVentas[Importe])

1. Pulsa Entrar y guarda.

Montar el cuadro de mando con tablas dinámicas y segmentadores

La hoja Cuadro es donde se ven los indicadores. Tendrá dos tablas dinámicas (una por mes, otra por producto) y tres controles: segmentador de Región, segmentador de Año y línea de tiempo de Fecha.

Tabla dinámica de Ventas por Mes con la medida Importe total

1. Ve a Insertar > Tablas > Tabla dinámica y marca Usar modelo de datos de este libro y Hoja de cálculo nueva. Renombra la hoja a Cuadro.
2. En la lista de campos, arrastra tblVentas[Fecha] a Filas.
3. Clic derecho sobre una fecha de la tabla y elige Agrupar > Meses.
4. Arrastra la medida Importe total (la del área de cálculo de Power Pivot) a Valores.

Tabla dinámica de Ventas por Producto

1. En la misma hoja Cuadro, debajo de la primera, inserta otra tabla dinámica desde el modelo de datos.
2. Arrastra tblProductos[Nombre] a Filas.
3. Arrastra la medida Importe total a Valores.
4. Arrastra tblClientes[Región] a Columnas para ver el cruce.

Añadir segmentadores de Región y Año

1. Clic sobre la primera tabla dinámica (Mes).
2. Clic derecho > Insertar segmentación de datos > marca tblClientes[Región] y tblVentas[Fecha] (luego se cambia a Año).
3. Para que la segmentación de Región afecte a las dos tablas, ve a Segmentaciones de datos > Conexiones de informe y marca la segunda tabla dinámica.
4. Para el segmentador de Fecha, clic derecho > Agrupar > Años (o Meses/Años). Renómbralo a «Año».

Añadir una línea de tiempo sobre Fecha

1. Clic sobre la primera tabla dinámica.
2. Clic derecho > Insertar línea de tiempo > tblVentas[Fecha].
3. Selecciona los meses de enero a junio de 2026 con el control de meses.

Por qué el modelo falla: rutas, errores DAX y segmentadores que no filtran

Estos cuatro errores aparecen casi siempre al montar el primer modelo. Con el libro de ejemplo delante, se ven y se arreglan en pocos pasos.

La relación está inactiva y el total suma de más

Aparece un asterisco * sobre la línea de relación en la vista de diagrama de Power Pivot. Causa: la relación se marcó como inactiva o se duplicó. Arreglo: doble clic sobre la línea, marcar Activa y, si hay dos relaciones entre las mismas tablas, dejar solo una activa.

La medida devuelve el mismo número en todas las filas

La medida se escribió con SUM([Importe]) en lugar de SUM(tblVentas[Importe]). Causa: Excel no sabe a qué tabla pertenece Importe y suma la columna equivocada o devuelve un valor fijo. Arreglo: escribir la fórmula completa con la tabla:

=SUM(tblVentas[Importe])

El segmentador solo filtra una tabla

Causa: al insertar la segmentación no se conectó con la segunda tabla. Arreglo: clic sobre el segmentador > Segmentaciones de datos > Conexiones de informe y marcar la tabla que falta.

Power Pivot no aparece en la cinta

Causa: en Excel 2019 y 2016, la pestaña viene oculta o como complemento sin cargar. Arreglo: Archivo > Opciones > Complementos, abajo elegir Complementos de COM, pulsar Ir, marcar Microsoft Power Pivot for Excel y reiniciar Excel.

Lo que Excel para BI no puede hacer y cuándo toca subirlo a Power BI

Excel cubre bien un modelo de BI de tamaño pequeño o medio, pero tiene techo. Estos son los límites que más se notan en la práctica:

• Tamaño del modelo: el archivo .xlsx tiene un tope práctico que ronda 1 millón de filas por tabla cuando se usa el modelo de datos, y el modelo entero no debería pasar de unos pocos GB si el equipo tiene poca RAM.
• Trabajo en equipo: Excel no bloquea celdas a nivel de medida ni de modelo, así que dos personasEditando a la vez la misma medida se pisan.
• Actualización automática: las consultas de Power Query se actualizan a mano o con VBA, no a una hora programada ni cuando llega un dato nuevo.
• Datos en la nube: Excel no conecta de forma nativa con Snowflake, BigQuery, Databricks ni con la mayoría de las bases modernas sin un conector de pago.
• Distribución: compartir un libro grande con 50 personas es lento y pesado; un informe de Power BI se sirve desde la nube y se ve en el navegador.

Cuando el modelo crece más allá de unos cientos de miles de filas, cuando hay que actualizar a solas cada noche, o cuando el equipo quiere ver el cuadro en el móvil, conviene mover el modelo a Power BI Desktop: el archivo .xlsx se importa tal cual, las medidas DAX se llevan sin tocar y los segmentadores se convierten en los mismos botones, pero con actualización en la nube.

Lo que ya sabes montar y el siguiente paso

Con el libro de ejemplo ya tienes montado:

• Tres tablas (tblVentas, tblProductos, tblClientes) cargadas con Power Query y conectadas al modelo de datos.
• Dos relaciones activas en Power Pivot, una por cada clave.
• Dos medidas DAX: Importe total y Ticket medio.
• Dos tablas dinámicas en la hoja Cuadro: ventas por mes y ventas por producto y región.
• Tres controles: segmentador de Región, segmentador de Año y línea de tiempo sobre Fecha, conectados a las dos tablas.

El siguiente paso es publicar este libro en Power BI Desktop: Archivo > Publicar > Exportar a Power BI Desktop. Una vez allí, el modelo se conecta a Power BI Service y el cuadro de mando se actualiza solo cada noche, sin abrir Excel.

Datos comprobados en octubre de 2026. La pestaña Power Pivot está integrada en Excel 2021, 2024 y Microsoft 365; en Excel 2019 y 2016 se activa como complemento COM. Power BI Desktop es gratuito y se descarga desde la web de Microsoft.

Dudas al montar un modelo de BI en Excel