Imagino que, si estás leyendo esto, es porque has oído hablar de las tablas dinámicas en Excel y la cosa todavía te suena un poco a klingon.
Si es así, has llegado al lugar perfecto.
A partir de hoy, ya no solo te dejarán de sonar a cosa rara, sino que se van a convertir en uno de tus mejores aliados para conseguir hacer análisis de datos súper chulos y de una forma rápida, ágil y súper fácil.
Una cosa tengo que decirte antes de empezar…
… no inicies la lectura pensando que esto va a ser complicado.
A veces, parece que Excel es más difícil de lo que realmente es.
Solo hay que dar el primer paso para darse cuenta de que, entendiendo lo que se hace, las cosas suelen ser bastante más sencillas de lo que parece…
Contenidos
Tablas dinámicas en Excel. Pero… ¿Qué es eso?
Supongo que, si te digo que las tablas dinámicas en Excel no son, ni más ni menos, que un simple resumen de datos, me dirás que vale, que muy bien, pero que con eso no te basta.
Y tendrás toda la razón del mundo.
Con eso no tienes ni para empezar…
Pero es que, realmente, las tablas dinámica son eso, una herramienta que tiene Excel para resumir y analizar datos.
Crear tablas dinámicas en Excel con tus datos tiene muchas ventajas y las irás descubriendo a medida que vayamos avanzando.
Lo veremos con un ejemplo y así entenderás por qué te hablo de resúmenes de datos.
Pero antes, quiero sentar algunas bases.
Antes de meternos al lío
Antes de entrar a ponernos a crear tablas dinámicas en Excel, tenemos que saber que no siempre vamos a poder crear una con los datos que tenemos.
¿Por qué te digo esto?
Porque para que Excel nos haga una tabla dinámica, éstos tienen que tener una determinada estructura. No se vale cualquier cosa.
Por ejemplo, si tenemos los datos de esta manera:
Aunque nos parezca que la tabla queda muy bonita, Excel será incapaz de crear una tabla dinámica con esta estructura de datos.
Así no vamos a poder hacer un análisis de los mismos.
Para poder crear una tabla dinámica en Excel, tenemos que tener los datos de esta forma:
Es decir, debe estar cada tipología de datos en una única columna.
Esto es así porque Excel va a usar cada una de las columnas de forma individual.
Si tenemos el mismo tipo de datos distribuidos en distintas columnas, no va a poder recoger los datos de manera conjunta.
Como ya he comentado en otras ocasiones, las celdas combinadas en Excel, nos pueden ofrecer una presentación muy estética, pero no nos sirven si necesitamos hacer un análisis de datos.
Además, el tener dobles filas y columnas de encabezados, va a imposibilitar la selección de datos para hacer una tabla dinámica en Excel con ellos.
Y respecto a las fechas.
Para Excel, las fechas son números (aquí puedes ver la explicación de cómo Excel trabaja con ellas), por tanto, con los números puede operar.
Sin embargo, si solo le indicamos los nombres de los meses, eso para Excel es texto y, al texto, tiene poco jugo que sacarle.
Por otro lado, también te quería comentar que en este post voy a hablar de rangos de datos y de tablas oficiales de Excel.
Si no tienes clara la diferencia entre estos dos conceptos, te aconsejo que, antes de seguir leyendo, le eches una ojeada a este otro post. Algunas de las cosas de las que voy a hablar ya están explicadas ahí y no voy a explicarlas de nuevo.
Estructura de datos para crear tablas dinámicas en Excel
Una vez tenemos los datos bien estructurados en un rango de datos o en una tabla, podemos empezar a crear nuestra primera tabla dinámica en Excel.
Para este ejemplo, he creado un rango de datos aleatorios entre el 1 de enero de 2023 y el 14 de septiembre de 2023.
En total, son 190 filas de datos.
De entrada, te diría que siempre es preferible crear nuestra tabla dinámica en Excel partiendo de tablas oficiales en lugar de partir con rangos de datos.
Esto es así porque, de esta manera, nos aseguramos que si en un futuro se añaden datos al origen, la tabla dinámica que vayamos a crear, tendrá en cuenta esos nuevos datos.
En caso de que estemos absolutamente seguros que los datos no van a sufrir ningún tipo de modificación, podemos crear la tabla dinámica en Excel a partir de un rango de datos.
El ejemplo que voy a utilizar es sobre unas ventas ficticias. En teoría, se van a seguir añadiendo ventas con el paso del tiempo, por tanto, convertiré el rango de datos en tabla oficial de Excel y le asignaré un nombre a esa tabla.
Mi tabla se llamará “Ventas”.
Opciones para crear tablas dinámicas en Excel
Si nos vamos a la pestaña “Insertar”, a la izquierda de todo, podemos ver la ficha “Tablas”.
Esa ficha tiene un botón con un desplegable “Tabla dinámica” y dos botones más “Tablas dinámicas recomendadas” y “Tabla”.
El botón “Tabla”, ya lo sabemos, nos servirá para convertir un rango de datos en tabla, por tanto, nos vamos a olvidar de él porque no es lo que queremos hacer en este momento.
Vamos a centrarnos en las otras dos opciones.
Tablas dinámicas recomendadas
Como seguro que imaginas, este botón es para que Excel te recomiende el tipo de tabla dinámica que puedes realizar dependiendo de los datos que tengas en el origen.
En mi opinión, este botón solo se debe usar si ya sabes qué son las tablas dinámicas y si ya has hecho algunas previamente.
Te puede ahorrar algo de trabajo, pero sinceramente, no conozco a nadie que esté acostumbrado a hacer tablas dinámicas que lo utilice.
Yo no lo uso, porque siempre parto con una idea clara de lo que quiero hacer y prefiero empezar de cero.
Al final, hacer una tabla dinámica es muy sencillo y, en mi opinión, no merece la pena.
Ahora, una cosa sí te digo.
Si no tienes ni idea de qué son las tablas dinámicas y de cómo funcionan, olvídate de ese botón.
Te lo digo por propia experiencia, te va a liar más de lo que te va a ayudar…
Tabla dinámica
Como se ve en la imagen anterior, el desplegable “Tabla dinámica” tiene cuatro opciones, la que accedemos directamente clicando sobre el botón y tres más en el desplegable.
Botón “Tabla dinámica”
Tanto si tenemos los datos en una tabla como si los tenemos en un rango de datos bien estructurado, al clicar sobre el botón, veremos que Excel realiza una selección automática de todas las celdas que contienen datos.
Cuando lo hacemos, nos aparece una ventana emergente.
“Tabla dinámica desde la tabla o el rango”
Esta ventana tiene diversas opciones.
La primera es en la que tenemos que indicar cuáles son los datos que se deben analizar en la tabla dinámica.
En ella se nos dice que seleccionemos una tabla o rango, pero como Excel es un programa inteligente, realiza él solo la selección.
Si los datos están bien estructurados, si no existen filas o celdas vacías, si no se encuentra con datos que, de alguna manera, rompan con la estructura de los mismos, Excel no se va a equivocar y no va a hacer selecciones erróneas.
En caso que se encuentre con cualquier celda que rompa con esa estructura, la selección que realizará se detendrá en la fila inmediatamente anterior a la que contiene el cambio.
Esto es muy útil para detectar posibles errores en los datos que, de otra manera, serían más difíciles de detectar.
Si partimos de un rango de datos
Si los datos están en un rango, la selección que realiza Excel contendrá todas las filas, incluyendo los encabezados.
En la ventana emergente veremos esa selección en la que, en la opción “Tabla o rango” nos mostrará el nombre de la hoja que contiene el rango, entre comillas simples, y posteriormente todas las celdas que ha seleccionado:
En mi caso, como te he mostrado antes, hay datos hasta la fila 190, por tanto, la selección irá desde la celda A1 que contiene el primer dato del rango, hasta la celda E190 que contiene el último.
Si partimos de una tabla
En caso que previamente hayamos convertido el rango de datos en tabla oficial de Excel y le hayamos adjudicado un nombre a dicha tabla, la selección automática que realiza Excel es un poco diferente.
En este caso, Excel entiende que queremos realizar la tabla dinámica partiendo de la tabla.
Como ves, en lugar de mostrarnos el rango completo de datos, nos muestra el nombre que le hemos adjudicado a nuestra tabla.
Además, como los encabezados forman parte de la tabla y Excel lo sabe, no los seleccionará porque da por hecho que esas celdas son los encabezados de las columnas.
Modificar el rango de datos para realizar la tabla dinámica
En cualquiera de los dos casos, a través de la flecha situada a la derecha, podemos modificar el rango de datos que Excel ha seleccionado de forma automática.
De todas formas, si se trata de una tabla oficial, Excel nos volverá a mostrar el nombre de la hoja y las celdas que seleccionemos en lugar del nombre de la tabla.
Los datos para el análisis, en este caso, no serán los de la tabla sino los de un rango de la misma.
En la segunda opción de esta ventana podemos ver que Excel nos dice “Elija donde desea colocar la tabla dinámica”
Por defecto, en esta opción viene seleccionada en una “Nueva hoja de cálculo”. Sin embargo, podemos cambiarlo a “Hoja de cálculo existente” y, a través de “Ubicación”, seleccionar la celda en la que queramos situar la tabla dinámica.
La última opción “Elija si quiere analizar varias tablas”, es una opción bastante más avanzada, de la que hablaré en otro post más adelante.
El modelo de datos, como puedes ver, sirve para analizar los datos de más de una tabla. Es algo complejo que requiere otro nivel de conocimientos.
Aquí estamos aprendiendo las bases de las tablas dinámicas y no te quiero liar. Tenemos mucho trabajo por delante antes de entrar en este tipo de funcionalidades.
Botón «De una tabla o rango«
De hecho, esta opción es la misma que si clicamos directamente en el botón “Tabla dinámica”.
Excel va a tener el mismo comportamiento tanto si clicas una opción como si clicas la otra.
Botón «Desde Datos externos de origen«
Esta opción nos va a servir cuando, las tablas que contienen los datos, no están ubicadas en el mismo documento sobre el que estamos trabajando.
Además, también nos serviría si tenemos los datos, por ejemplo, en una base de datos en Access, en un archivo CSV, etc., pero en este caso, nos volvemos a encontrar con opciones avanzadas…
Si clicamos, nos aparecerá una ventana muy similar a la anterior.
“Tabla dinámica desde un origen externo”
En esta ventana, la única diferencia que tenemos respecto a la anterior, es que tenemos que decirle a Excel dónde tiene que ir a buscar los datos a través del botón “Elegir conexión”.
Al clicarlo aparecerá una nueva ventana “Conexiones existentes” que tiene dos pestañas.
Nos tenemos que olvidar de la pestaña “Conexiones” (volvemos a estar con funcionalidades avanzadas) y seleccionar la pestaña “Tablas”.
Ahí nos aparecerán los libros que tenemos abiertos y las tablas que contienen esos libros.
Como puedes ver, mi libro se llama «Tablas dinámicas en Excel» y en este libro, solo tengo la tabla “Ventas”.
Si tuviera más libros con tablas creadas, aparecerían aquí y podría seleccionar la tabla que me interesara.
Desde Modelo de datos
Esta opción solo la encontraremos disponible en casos muy concretos.
Tal como te he dicho antes, el modelo de datos es algo avanzado en lo que no voy a entrar hoy.
Pero lo que sí te puedo decir es que, al escoger esta opción, accederemos a una herramienta que tiene Excel que se llama Power Pivot.
Power Pivot sirve, entre muchas otras cosas, para relacionar diferentes tablas de diferentes orígenes de datos.
Creación de una tabla dinámica con Excel
Una vez tenemos seleccionada la tabla a partir de la que queremos crear la tabla dinámica y hemos escogido su ubicación (yo he dejado la ubicación por defecto), veremos cómo Excel crea una hoja nueva y en ella aparece lo que podemos ver en esta imagen:
En primer lugar, en la cinta de opciones nos han aparecido dos pestañas nuevas “Analizar tabla dinámica” y “Diseño”.
Sobre las celdas, aparece una sección en la que va a estar situada la tabla dinámica y, a la derecha, aparecen los campos de la misma.
Esta lista de campos la puedes ver de la misma forma que la tengo yo, o con otro formato distinto, pero lo que importa es que veas los campos y las áreas a las que arrastrar esos campos.
En caso que no te aparezcan los campos de la tabla dinámica, deberías ir a la pestaña “Analizar tabla dinámica” y, a la izquierda, desplegando el botón “Mostrar”, clicar sobre “Lista de campos”.
Cosas a tener en cuenta a la hora de crear tablas dinámicas en Excel
A partir de aquí, es muy fácil empezar a mover campos de un lado a otro y hacer pruebas.
Me parece perfecto.
Eso está muy bien para entender el funcionamiento de las tablas dinámicas, pero frenemos un poco antes de lanzarnos a la piscina.
Las tablas dinámicas en Excel tienen la inmensa ventaja de que son muy fácilmente moldeables y que podemos obtener todo tipo de resultados.
Pero hay algunas cosillas que vale la pena tener en cuenta.
En primer lugar, tenemos que tener muy claros los resúmenes que queremos obtener.
Empezar a poner campos a las áreas sin ton ni son, puede provocar que obtengamos resultados que podrían llegar a ser muy caóticos y nada útiles.
Primero pensemos bien qué queremos hacer y después hagámoslo.
En segundo lugar, y esto no es exclusivo de las tablas dinámicas sino de cualquier tabla que creemos en Excel, los datos siempre deberían crecer hacia abajo, no hacia la derecha.
¿Qué quiere decir esto?
Que los campos con más cantidad de datos deberían estar en las filas y los que tienen menos datos en las columnas.
De esta forma, las tablas dinámicas crecerán en el sentido correcto y podremos obtener una visualización más clara de los resultados.
Por último, las tablas dinámicas nos ofrecen la posibilidad de crear filtros, segmentaciones de datos y escalas de tiempo.
Es decir, podemos utilizar algunas de sus funcionalidades para poder filtrar los resultados de la tabla, sin necesidad de que esta crezca innecesariamente.
Esto, lo veremos más adelante.
Crear la primera tabla dinámica en Excel
Bien, para empezar, vamos a crear una tabla dinámica en la que vamos a ver el importe que se ha vendido de cada producto, por tanto, en el área de las filas colocaremos los productos.
Podemos hacerlo seleccionando la palabra “Producto” y arrastrándola al área “Filas” o, simplemente, marcando la casilla de selección.
Generalmente se suele arrastrar porque, en ocasiones, Excel no nos coloca los campos en el área que queremos, pero si pruebas marcando la casilla de selección y funciona, ya lo tendrás.
En caso que no funcione, solo arrástrala al área de filas.
En el momento en que tengamos los productos en el área de filas, veremos en la tabla dinámica todos los productos que tiene la tabla de origen de datos ordenados de la A a la Z.
Como verás, Excel le ha puesto un nombre genérico a la columna, pero lo puedes cambiar a través de la barra de fórmulas.
Ahora, haciendo clic sobre el campo “Ventas”, verás que Excel lo coloca directamente en el área de valores y realiza la suma.
Esto es así porque este campo contiene datos numéricos y Excel entiende que los tiene que sumar.
Luego veremos que podemos hacer otras operaciones con el mismo campo.
Al colocar datos en el área de valores, la columna adquiere el nombre “Suma de” y el nombre del campo, en este caso, “Suma de Ventas”.
De la misma manera, podemos modificar el nombre a través de la barra de fórmulas, sin embargo en este caso, veremos que, si tenemos la intención de dejar únicamente el nombre “Ventas” Excel no nos va a dejar y nos saltará un aviso diciéndonos que “El nombre de tabla dinámica ya existe”.
Un truquillo para saltarse esta restricción, es poner un espacio delante o detrás de la palabra, así: “ Ventas“.
Configuraciones básicas de los campos de una tabla dinámica
Como has visto en la imagen de la tabla dinámica, los valores numéricos no tienen el formato moneda que tenían los datos.
Esta y muchas otras configuraciones se pueden modificar.
Para ello tendremos que acceder a la configuración de las mismas a través de la opción que encontramos clicando en la flechita que tiene cada uno de los campos y que nos abrirá la ventana correspondiente.
Como ves, esta ventana tiene dos pestañas y diversas opciones:
Por ejemplo, en “Formato de número” accederemos a la clásica ventana que tiene Excel para dar formato a cualquier celda.
También podemos cambiar la operación que realiza Excel con los números y cambiar de suma a recuento, promedio, máximo, mínimo, etc. y mostrar los valores de distinta forma.
Evidentemente, no puedo explicarte cada una de las opciones, pero es cuestión de que vayas probando para ver el comportamiento que tiene la tabla con cada una de ellas y escojas la que se adapte a lo que necesitas.
Algunas cosas más que deberías saber sobre las tablas dinámicas en Excel
En principio, ya tenemos creada nuestra tabla dinámica.
Pero una tabla dinámica tiene muchas otras cosas que le podemos hacer.
Algunas básicas y otras mucho más avanzadas que no podré explicar aquí (dominar las tablas dinámicas no da para un post, da para un curso de bastantes horas).
Vamos a ver algunas.
Repetir el mismo campo con diferente tipo de cálculo
Hasta ahora, lo que hemos hecho es poner un campo en la lista de valores para que nos muestre el importe total vendido.
Podemos volver a introducir el mismo campo a la zona de valores tantas veces como necesitemos y cambiar el tipo de cálculo que queremos obtener.
Así, sobre un único dato de origen, podemos obtener, por ejemplo, un porcentaje de ventas sobre el total, la cantidad de veces que se ha vendido (en mi caso se repite mucho el 9 por los datos de origen), o crear un ranking de ventas (al que también podemos añadir formato condicional).
Filtros, segmentaciones de datos y líneas de tiempo
Una de las cosas más cómodas que nos ofrecen las tablas dinámicas, de cara a la visualización de los datos, son los filtros.
Con ellos podemos obtener una visión parcial de los resultados en base a esos filtros.
Existen tres tipos de filtros en las tablas dinámicas:
Filtros
Los filtros admiten cualquier tipo de dato y se activan mediante el área “Filtros” que nos encontramos en los campos de la tabla dinámica.
Así, si colocamos cualquier campo en esa área, podremos filtrar (¡cuántas veces la palabra filtro, por favor!) la tabla por el campo que hemos puesto.
Como ves, se puede seleccionar un único valor o se pueden seleccionar varios clicando en la casilla “Seleccionar varios elementos”.
Segmentaciones de datos
Las segmentaciones de datos son otro tipo de filtro que también admite cualquier tipo de campo, pero en este caso, el filtro queda situado fuera de la tabla dinámica.
Tenemos dos maneras de añadir un campo a una segmentación de datos.
A través del botón secundario
Para hacerlo, nos tenemos que situar encima del campo que queramos añadir como segmentación de datos, clicar con el botón secundario y seleccionar la opción “Agregar como segmentación de datos”.
A través de la cinta de opciones
Para hacerlo a través de la cinta de opciones, tenemos que tener el ratón colocado sobre cualquier celda de la tabla dinámica para que esta esté activa y acceder a la pestaña “Analizar tabla dinámica”.
En la parte central, veremos la ficha “Filtros” y en ella el botón “Insertar segmentación de datos”.
Cuando cliquemos aparecerá una ventana en la que podemos escoger el campo que queremos añadir a la segmentación.
Líneas de tiempo
Las líneas de tiempo, como puedes imaginar, solo admiten campos de tipo fecha. Sirven para filtrar por diferentes periodos.
Se activan de la misma manera que las segmentaciones de datos, pero utilizando solo los campos de tipo fecha.
Una vez activadas, podemos escoger el tipo de periodo por el que queremos filtrar la tabla.
Poner nombre a la tabla dinámica
De la misma manera que ocurre con las tablas oficiales de Excel, a las tablas dinámicas también es conveniente ponerles nombre.
Esto nos servirá si más adelante queremos crear gráficos o realizar cualquier otra cosa con ellas.
Para adjudicar nombres a las tablas dinámicas, lo haremos a través de la cinta de opciones a la izquierda de la pestaña “Analizar tabla dinámica” y clicando sobre el desplegable “Tabla dinámica”.
Diseños, estilos, colores, totales y subtotales
Podemos cambiar el diseño, tanto a nivel de colores como a nivel de los datos que visualizamos a través de la pestaña “Diseño” de la cinta de opciones.
Esta pestaña tiene tres grupos “Diseño”, “Opciones de estilo de tabla dinámica” y “Estilos de tabla dinámica”.
Te animo a que juegues y pruebes todos los botones para ver el comportamiento que tiene tu tabla dinámica cuando lo hagas.
Verás que puedes cambiar la forma en la que se muestran las filas, las columnas, los totales y subtotales y, además, cambiar los colores y formatos de tu tabla dinámica.
Como has podido ver, sin demasiada complicación, hemos conseguido una tabla dinámica que nos ofrece un resumen de los datos de origen a nuestro gusto y que podemos ir adaptando a las necesidades del momento.
Ahora, anímate a hacer lo mismo con tus datos y me cuentas…
