20 cosas que debe saber sobre las tablas dinámicas de Excel

Muchos expertos de Excel creen que las tablas dinámicas son la herramienta más poderosa en Excel. Ninguna otra herramienta en Excel te brinda la capacidad de analizar datos como una tabla dinámica.

A continuación te voy a nombre los aspectos más improtantes que debes tener en cuenta en cualquier tabla dinamica.

Video: ¿Por qué tablas dinámicas? – fórmulas vs. tablas dinámicas

1. Puedes construir una tabla dinámica en aproximadamente un minuto

Mucha gente tiene la idea de que construir una tabla dinámica es complicado y requiere mucho tiempo, pero no es cierto. En comparación con el tiempo que llevaría crear un informe equivalente de forma manual, las tablas dinámicas son increíblemente rápidas. Si tiene los datos de origen bien organizados, puede crear una tabla dinámica de menos de un minuto. Así es cómo debes hacerlo:

• Selecciona cualquier celda en la fuente de los datos.
• En la pestaña Insertar de la cinta, haz clic en el botón de la tabla dinámica
• En el cuadro de diálogo Crear tabla dinámica, verifica los datos y haz clic en Aceptar
• Arrastra un campo «etiqueta» en el área Etiquetas de fila (por ejemplo, cliente)
  Arrastra un campo numérico al área Valores (por ejemplo, ventas)

Video: Cómo crear rápidamente una tabla dinámica

Datos brutos (ventas de chocolate), listos para una tabla dinámica

Selecciona cualquier celda en los datos para comenzar.

Una tabla dinámica rápida de Excel que muestra las ventas de chocolate

Una tabla dinamica muy simple en unos 30 segundos

2. Perfecciona tus datos de origen

Para minimizar los problemas en el futuro, siempre usa un origen de datos de buena calidad, organizados en un diseño tabular. Los datos de origen «perfecto» no tendrán filas o columnas en blanco ni subtotales. Cada columna tendrá un nombre único (en una sola fila), cada campo tendrá un valor en cada fila y las columnas no contendrán grupos repetidos de datos (es decir, nombres de meses, nombres de ubicaciones, nombres de regiones, etc.).

Video: cómo completar rápidamente los datos faltantes

¡Datos perfectos para una tabla dinamica!

3. Cuenta los datos primero

Cuando creas por primera vez una tabla dinámica, úsala para generar un recuento simple primero para asegurarte de que la tabla dinámica está procesando los datos como esperas. Para hacer esto, simplemente agrega cualquier campo de texto como un campo de Valor. Verá una tabla dinámica muy pequeña que muestra el recuento total de registros, es decir, el número total de filas en sus datos. Si este número tiene sentido para ti, ya estás listo. Si el número no tiene sentido para ti, es posible que la tabla dinámica no esté leyendo los datos correctamente o que los datos no se hayan definido correctamente.

300 nombres significa que tenemos 300 empleados. Comprobar.

4. Planifica antes de construir

Aunque es muy divertido arrastrar los campos alrededor de una tabla dinámica, y ver a Excel produce otra representación inusual de los datos. Antes de comenzar a construir, anota lo que estás tratando de medir o comprender, y esboza algunos informes simples en una libreta. Estas simples notas te ayudarán a guiarte a través de la gran cantidad de opciones que tienes a tu disposición. Manten las cosas simples, y concéntrate en las preguntas que necesitas responder.

5. Usa una tabla para tus datos para crear un «rango dinámico»

Si utilizas una tabla de Excel para los datos fuente de tu tabla dinámica, obtienes un beneficio muy bueno: tu rango de datos se vuelve «dinámico». Un rango dinámico se expandirá y contraerá automáticamente a medida que agregues o elimines datos, por lo que no tendrás que preocuparte de que a la tabla dinámica le falten los datos más recientes. Cuando utilizas una tabla para su tabla dinámica, la tabla dinámica siempre estará sincronizada con tus datos.

Para usar una tabla para tu tabla dinamica:

• Selecciona cualquier celda en los datos usa el atajo de teclado Ctrl-T para crear una Tabla
• Haz clic en el botón Resumir con tabla dinámica (TableTools> Diseño)
• Construye tu tabla dinámica normalmente
• Beneficio: los datos que agregues a tu Tabla aparecerán automáticamente en tu tabla dinamica al actualizar

Video: use una tabla para su próxima mesa pivote

Crear una tabla simple a partir de los datos usando (Ctrl-T)

Ahora que tenemos una tabla, podemos usar Resumir con tabla dinámica

6. Usa una tabla dinamica para contar cosas

Por defecto, una tabla dinámica contará cualquier campo de texto. Esta puede ser una característica muy útil en muchas situaciones comerciales generales. Por ejemplo, supongamos que tienes una lista de empleados y deseas obtener un recuento por departamento. Para obtener un desglose por departamento, sigue estos pasos:

• Crea una tabla dinámica de la forma habitual
• Agrega el departamento como una etiqueta de fila
• Agrega el campo Nombre del empleado como un valor
• La tabla dinámica mostrará un recuento de empleados por Departamento

Desglose de empleados por departamento

7. Mostrar totales como un porcentaje

En muchas tablas dinámicas, querrás mostrar un porcentaje en lugar de un conteo. Por ejemplo, quizás deseas mostrar un desglose de las ventas por producto. Pero, en lugar de mostrar las ventas totales de cada producto, deseas mostrar las ventas como un porcentaje de las ventas totales. Suponiendo que tienes un campo llamado Ventas en tus datos, simplemente sigue estos pasos:

• Agrega producto a la tabla dinámica como una etiqueta de fila
• Agrega ventas a la tabla dinámica como valor
• Haz clic con el botón derecho en el campo Ventas y configura «Mostrar valores como» en «% de gran total»

Consulta el consejo a continuación «Agregar un campo más de una vez a una tabla dinámica» para obtener información sobre cómo mostrar las ventas y ventas totales como un porcentaje del total al mismo tiempo.

Cambiar la visualización del valor a % del total

Suma de empleados mostrada como % del total

8. Usa una tabla dinámica para compilar una lista de valores únicos

Debido a que las tablas dinámicas resumen los datos, se pueden usar para buscar valores únicos en un campo. Esta es una buena forma de ver rápidamente todos los valores que aparecen en un campo y también encontrar errores tipográficos y otras incoherencias. Por ejemplo, supongamos que tiene datos de ventas y desea ver una lista de cada producto que se vendió. Para crear una lista de productos:

• Crea una tabla dinámica
• Agrega el producto como una etiqueta de fila
• Agrega cualquier otro campo de texto (categoría, cliente, etc.) como valor
• La tabla dinámica mostrará una lista de todos los productos que aparecen en los datos de ventas

Todos los productos que aparecen en los datos aparecen en la lista (incluido un error tipográfico)

9. Crea una tabla dinámica autocontenida

Cuando hayas creado una tabla dinámica a partir de datos en la misma hoja de cálculo, puedes eliminar los datos si lo deseas y la tabla dinámica continuará funcionando normalmente. Esto se debe a que una tabla dinámica tiene una memoria caché dinámica que contiene un duplicado exacto de los datos utilizados para crear la tabla dinámica.

• Actualiza la tabla dinámica para asegurarte de que la memoria caché esta actualizada (Herramientas de tabla dinámica> Actualizar)
• Elimina la hoja de trabajo que contiene los datos
• Usa tu tabla dinamica de la forma habitual

Ver: Cómo hacer una tabla dinámica autónoma

10. Agrupa una tabla dinámica manualmente

Aunque las tablas dinámicas agrupan datos automáticamente de muchas maneras, también puede agrupar elementos manualmente en sus propios grupos personalizados. Por ejemplo, supongamos que tienes una tabla dinámica que muestra un desglose de empleados por departamento. Supongamos que deseas agrupar los departamentos de Ingeniería, Cumplimiento y Soporte en el Grupo 1, y Ventas y Marketing en el Grupo 2. El Grupo 1 y el Grupo 2 no aparecen en los datos, son sus propios grupos personalizados. Para agrupar la tabla dinámica en los grupos ad hoc, Grupo 1 y Grupo 2:

• Control-clic para seleccionar cada elemento en el primer grupo
• Haz clic con el botón derecho en uno de los elementos y elije Agrupar en el menú
• Excel crea un nuevo grupo, «Grupo1»
• Selecciona Marketing y Ventas en la columna B, y agrupa como arriba
• Excel crea otro grupo, «Group2»

Comenzando a agrupar manualmente

A la mitad de la agrupación manual: el grupo 1 está listo

Grupo finalizado manualmente

11. Agrupar datos numéricos en rangos

Una de las características más interesantes y potentes que tiene cada tabla dinámica es la capacidad de agrupar datos numéricos en intervalos o categorías. Por ejemplo, supongamos que tienes una lista de resultados de votación que incluye la edad del votante y deseas resumir los resultados por grupo de edad:

• Crea tu tabla dinámica
• Agrega edad como una etiqueta de fila, votar como una etiqueta de columna y el nombre como un valor
• Haz clic con el botón derecho en cualquier valor en el campo Edad y elije Grupo
• Ingresa 10 como el intervalo en el área de entrada «Por:»
• Al hacer clic en Aceptar, verás los datos de votación agrupados por edad en intervalos de 10 años.

Video: Cómo agrupar una tabla dinámica por rango de edad

La fuente de datos para los resultados de votación

Agrupar el campo de edad en intervalos de 10 años

Listo agrupando los resultados de votación por rango de edad

12. Cambiar el nombre de los campos para una mejor legibilidad

Cuando agregas campos a una tabla dinámica, la tabla dinámica mostrará el nombre que aparece en los datos de origen. Los nombres de los campos de valor aparecerán con «Suma de» o «Recuento de» cuando se agreguen a una tabla dinámica. Por ejemplo, verás la suma de las ventas, el recuento de la región, etc. Sin embargo, simplemente puedes sobrescribir este nombre con el suyo. Simplemente selecciona la celda que contiene el campo que deseas cambiar el nombre y escribe un nuevo nombre.

Cambiar el nombre de un campo escribiendo el nombre original

13. Agregar un espacio a los nombres de los campos cuando Excel protesta

Cuando intentes cambiar el nombre de los campos, podrías encontrarse con un problema si tratas de usar exactamente el mismo nombre de campo que aparece en los datos. Por ejemplo, supongamos que tienes un campo llamado Ventas en tus datos de origen. Como campo de valor, aparece como Suma de ventas, pero (sensatamente) deseas que diga Ventas. Sin embargo, cuando intentas utilizar Ventas, Excel se queja de que el campo ya existe y arroja un mensaje de error «El nombre de campo de tabla dinámica ya existe».

A Excel no le gusta tu nuevo nombre de campo

Como solución, simplemente agrega un espacio al final de tu nuevo nombre de campo. No puedes ver la diferencia, y Excel no se quejará.

Agregar un espacio al nombre evita el problema

14. Agrega un campo más de una vez a una tabla dinámica

Hay muchas situaciones en las que tiene sentido agregar el mismo campo a una tabla dinámica más de una vez. Puede parecer extraño, pero puedes agregar el mismo campo a una tabla dinámica más de una vez. Por ejemplo, supongamos que tienes una tabla dinámica que muestra un recuento de empleados por departamento.

El recuento funciona bien, pero también deseas mostrar el conteo como un porcentaje del total de empleados. En este caso, la solución más simple es agregar el mismo campo dos veces como un campo Valor:

• Agrega un campo de texto al área de Valor (por ejemplo, Nombre, Nombre, etc.)
• Por defecto, obtendrás un recuento de campos de texto
• Agrega el mismo campo nuevamente al área de Valor
• Haz clic con el botón derecho en la segunda instancia y cambia Mostrar valores como «% de gran total»
• Cambia el nombre de ambos campos como desees

Establecer un campo para mostrar el porcentaje del total

El campo Nombre se ha agregado dos veces

15. Formatear automáticamente todos los campos de valor

Cada vez que agregas un campo numérico como valor en una tabla dinámica, debes establecer el formato numérico directamente en el campo. Es posible que sientas la tentación de formatear los valores que ve en la tabla dinámica directamente, pero esta no es una buena idea, ya que no es confiable a medida que cambia la tabla dinámica. Establecer el formato directamente en el campo garantizará que el campo se muestre con el formato que desee, sin importar cuán grande o pequeña sea la tabla dinámica.

Por ejemplo, supon una tabla dinámica que muestra un desglose de las ventas por región. Cuando agregues por primera vez el campo Ventas a la tabla dinámica, se mostrará en formato de número general, ya que es un campo numérico. Para aplicar el formato de número de contabilidad al campo en sí:

• Haz clic con el botón derecho en el campo Ventas y selecciona Configuración de campo de valor en el menú
• Haz clic en el botón Formato de número en el cuadro de diálogo Configuración de campo de valor que aparece
• Establece el formato en Contabilidad y haz clic en Aceptar para salir

Configuración de formato directamente en un campo de valor

16. Bajar para ver (o extraer) los datos detrás de un total

Cada vez que veas un total mostrado en una tabla dinámica, puedes ver y extraer fácilmente los datos que componen el total al «profundizar». Por ejemplo, supon que estás mirando una tabla dinámica que muestra el recuento de empleados por departamento. Puedes ver que hay 50 empleados en el departamento de ingeniería, pero deseas ver los nombres reales. Para ver las 50 personas que componen este número, haz doble clic directamente en el número 50 y Excel agregará una nueva hoja a tu libro de trabajo que contiene los datos exactos utilizados para calcular 50 ingenieros. Puedes usar este mismo enfoque para ver y extraer datos detrás de los totales dondequiera que los vea en una tabla dinámica.

Haz doble clic en un total para «profundizar»

Los 50 ingenieros, extraídos en una nueva hoja de forma automática

17. Clona tus tablas dinámicas cuando necesites otra vista

Una vez que tengas configurada una tabla dinámica, es posible que desees ver una vista diferente de los mismos datos. Por supuesto, puedes reorganizar su tabla dinámica existente para crear la nueva vista. Pero si estás creando un informe que planea usar y actualizar de forma continua, lo más fácil es clonar una tabla dinámica existente, de modo que ambas vistas de los datos estén siempre disponibles.

Hay dos formas sencillas de clonar una tabla dinámica. La primera forma consiste en duplicar la hoja de cálculo que contiene la tabla dinámica. Si tienes una tabla dinámica configurada en una hoja de cálculo con un título, etc., puede hacer clic con el botón derecho en la pestaña de la hoja de trabajo para copiar la hoja de trabajo en el mismo libro de trabajo. Otra forma de clonar una tabla dinámica es copiar la tabla dinámica y pegarla en otro lugar. Usando estos enfoques, puede hacer tantas copias como desee.

Cuando clona una tabla dinámica de esta manera, ambas tablas dinámicas comparten el mismo caché dinámico. Esto significa que cuando actualices cualquiera de los clones (o el original) se actualizarán todas las tablas pivote relacionadas.

Video: Cómo clonar una tabla dinámica

18. Desconexión de una tabla dinámica para actualizar de forma independiente

Después de haber clonado una tabla dinámica, es posible que te encuentres con una situación en la que realmente no deseas que el clon esté vinculado al mismo caché dinámico que el original. Un ejemplo común es después de haber agrupado un campo de fecha en una tabla dinámica, actualizar y descubrir que también has agrupado accidentalmente el mismo campo de fecha en otra tabla dinámica que no tenía la intención de cambiar. Cuando las tablas dinámicas comparten la misma memoria caché dinámica, también comparten la agrupación de campos.

Esta es una forma de eliminar el clics de una tabla dinámica, es decir, desvincularla de la memoria caché dinámica que comparte con otras tablas dinámicas en la misma hoja de cálculo:

• Cortar toda la tabla dinámica al portapapeles
• Pegue la tabla dinámica en un nuevo libro de trabajo
• Actualiza la tabla dinámica
• Copia de nuevo en el portapapeles
• Pegar de nuevo en el libro de trabajo original
• Descartar el libro de trabajo temporal

Tu tabla dinámica ahora usará su propio caché dinámico y no se actualizará con las otras tablas pivote en el libro de trabajo, o compartirá la misma agrupación de campo.

19. Deshazte de los títulos inútiles

El diseño predeterminado para nuevas tablas dinámicas es el diseño compacto. Este diseño mostrará «Etiquetas de fila» y «Etiquetas de columna» como encabezados en la tabla dinámica. Estos no son los títulos más intuitivos, especialmente para las personas que no suelen utilizar tablas dinámicas. Una manera fácil de deshacerse de estos encabezados impares es cambiar el diseño de la tabla dinámica de Compacto a Esquema o Diseño tabular. Esto hará que la tabla dinámica muestre los nombres reales de los campos como encabezados en la tabla dinámica, lo cual es mucho más sensato. Para deshacerse por completo de estas etiquetas, busque un botón llamado Encabezados de campo en la pestaña Analizar de la cinta Herramientas de tabla dinámica. Al hacer clic en este botón, se desactivarán los títulos por completo.

Ten en cuenta los títulos de campo inútiles y confusos

Cambiar el diseño de compacto a contorno

Los encabezados de campo en el diseño del esquema son mucho más sensibles

20. Agrega un poco de espacio en blanco alrededor de sus tablas dinámicas

Este es solo un consejo de diseño simple. Todos los buenos diseñadores saben que un diseño agradable requiere un poco de espacio en blanco. Espacio en blanco solo significa espacio vacío reservado para dar espacio a la disposición. Después de crear una tabla dinámica, inserta una columna adicional a la izquierda y una o dos filas adicionales en la parte superior. Esto le dará a tu tabla dinamica algo de espacio para respirar y creará un mejor diseño. En la mayoría de los casos, también recomiendo que desactives las líneas de la cuadrícula en la hoja de trabajo. La tabla dinámica presentará una fuerte cuadrícula visual, por lo que las líneas de la cuadrícula fuera de la tabla dinámica son innecesarias y simplemente crearán un ruido visual.

Agrega un poco de espacio en blanco alrededor de las tablas dinámicas

Un pequeño espacio en blanco hace que tus tablas dinamica se vean más pulidas

21. Deshacerse de los totales generales de filas y columnas

De forma predeterminada, las tablas dinámicas muestran totales para filas y columnas, pero puede desactivar fácilmente uno o ambos de estos totales si no los quieres. En la pestaña Pivot Table de la cinta, solo haz clic en el botón Totals y elije las opciones que desees.

Habilitar y deshabilitar totales generales

Puede eliminar totales generales para filas y columnas

22. Formatea celdas vacías

Si tienes una tabla dinámica que tiene muchas celdas en blanco, puedes controlar el carácter que se muestra en cada celda en blanco. Por defecto, las celdas vacías no mostrarán nada en absoluto. Para configurar su propio personaje, haz clic en el botón derecho dentro de la tabla dinámica y selecciona las opciones de tabla dinámica. Luego, asegúrate de que «Empty cells as:» esté marcado e ingresa el carácter que deseas ver. Tenga en cuenta que esta configuración respeta el formato de número aplicado. Por ejemplo. si estás utilizando el formato de número de cuenta para un campo de valor numérico e ingresa un cero, verás un guión «-» en la tabla dinámica, ya que así es como se muestran los valores cero con el formato de Contabilidad.

La tabla dinámica muestra celdas vacías como 0 (cero) con formato de contabilidad.

Las celdas vacías configuradas para mostrar 0 (cero) y el formato de número de contabilidad le dan guiones.

23. Desactivar Auto ajuste cuando sea necesario

De forma predeterminada, cuando actualizas una tabla dinámica, las columnas que contienen datos se ajustan automáticamente para ajustarse mejor a los datos. Normalmente, esto es algo bueno, pero puede volverse loco si tiene otras cosas en la hoja de trabajo junto con la tabla dinámica, o si ha ajustado cuidadosamente los anchos de columna manualmente y no quiere que se modifiquen. Para desactivar esta característica, haz clic en el botón derecho dentro de la tabla dinámica y elije Opciones de tabla dinámica. En la primera pestaña de las opciones desmarque «Autoajustar anchos de columna en la actualización».

Opción de autoajuste de columna de tabla dinámica para Windows.

Puedes aprender Tablas Dinámicas por tu cuenta, leer blogs y ver videos de YouTube. Pero también puedes aprender Tablas Dinámicas de una forma más RÁPIDA, con un curso de video, donde te enseñaré paso a paso. Si estás interesado apuntate al curso que empezaremos el 2 de Mayo haciendo clic aquí.