Dashboards en Excel, Tablas dinámicas y gráficos

En este articulo y video veremos cómo hacer un Dashboard en Excel, o támbién llamado Panel de Control o Cuadro de mando. Aunque realmente lo de menos es el nombre. Lo importante es que en un Dashboard puedes plasmar indicadores de cualquier información que requieras. Para analizar estos indicadores usaremos Tablas dinámicas, Segmentación de datos y Gráficos dinámicos.

Vistazo general de nuestro dashboard

En la Figura 1 vemos el dashboard terminado, en el cual vemos algunos elementos interesantes que podrás utilizar para armar los tuyos. Lo importante de los dashboards es conocer la información con la cual vas a trabajar y tener imaginación.

Dashboard en Excel

Figura 1. Dashboard en Excel.

En base a buenas prácticas que los expertos recomiendan, puedo decirte que para hacer un dashboard bien diseñado y que además pueda ser modificado a futuro, lo primero que debes hacer es tener 3 horas en tu archivo:

  • Hoja Datos: En esta hoja tendrás tus datos. Recuerda entre más columnas tenga tu información, mejor análisis podrás hacer.
  • Hoja Tablas dinámicas: En esta hoja insertaras las Tablas dinámicas que serán la base para el diseño de tu dashboard.
  • Hoja Dashboard: Aquí irá el diseño principal del dashboard.

Y como vemos en la Figura 1, tenemos principalmente 3 elementos, los cuales podemos combinar a nuestro gusto:

  • Segmentación de datos: También llamados Slices. Nos ayudarán como filtros principales para el resto de los elementos.
  • Fórmulas y funciones: Extraeremos información desde Tablás dinámicas usando fórmulas.
  • Gráficos dinámicos: Los gráficos son la herramienta favorita para analizar información.

Ver video Dashboards en Excel

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Pasos generales para armar un Dashboard en Excel

Para armar un dashboard, principalmente debes seguir los siguientes pasos:

  • Insertar varias Tablas dinámicas, obteniendo diferentes indicadores. Dejarlas en la hoja Tablas dinámicas.
  • Por cada Tablas dinámica, insertar un gráfico dinámico. Moverlos a la hoja Dashboard.
  • Darles el diseño deseado.

Dashboards en Excel

Figura 2. Elementos de un dashboard.

Manos a la obra

Top 5 de vendedores, Ventas y Gráfica

Sigamos los siguientes pasos para armar este indicador:

1. Estando en la hoja Datos insertamos una Tabla dinámica. Nos vamos a la Pestaña Insertar > Tabla dinámica.

2. La Insertamos en la hoja Tablas dinámicas.

3. La columna Empleados la movemos a Filas y la columna Ventas a Valores.

Insertamos una Tabla dinámica para obtener los mejores vendedores

Figura 3. Insertamos una Tabla dinámica para obtener los mejores vendedores.

4. En la hoja Dashboard ponemos los encabezados Top 5 de vendedores, Ventas y Gráfica en el rango E6:G6.

5. Para traer el nombre de los vendedores desde la Tabla dinámica, sólo ingresamos la fórmula en la celda E7: =’Tablas dinámicas’!C2

6. Arrastramos la fórmula hasta la celda E11.

Devolvemos el nombre de los 5 mejores vendedores

Figura 4. Devolvemos el nombre de los 5 mejores vendedores.

7. En la celda F7 ingresamos una fórmula para devolver la ventas de los vendedores, y la modificamos para que haga referencia a los nombres de la celda E7: =IMPORTARDATOSDINAMICOS(“Ventas”,’Tablas dinámicas’!$C$1,”Empleado”,E7)

Devolvemos las ventas

Figura 5. Devolvemos las ventas.

8. Ahora para graficas las ventas, vamos a usar la siguiente fórmula en la celda G7: =REPETIR(“|”,F7/MAX($F$7:$F$11)*100)

9. Arrastramos hasta la celda G11.

10. Damos formato a las celdas con tipo de Fuente Playbill.

Graficamos las ventas con fórmulas y dando formato a la celda

Figura 6. Graficamos las ventas con fórmulas y dando formato a la celda.

Gráfica de Ventas por Ciudad

Para graficas las ventas por Ciudad sigamos los siguientes pasos:

1. Seleccionamos la hoja Datos e Insertamos una Tabla dinámica. Pestaña Insertar > Tabla dinámica.

2. La Tablas dinámica la insertamos en la hoja Tablas dinámicas.

3. En etiquetas de fila movemos la columna Estado o Provincia y en Valores movemos Ventas.

4. Elegimos cualquier celda de la Tabla creada y nos vamos a la pestaña Analizar > Gráfico dinámico.

5. Elegimos el tipo de Barra.

Insertamos un gráfico dinámico en la base a la Tabla dinámica

Figura 7. Insertamos un gráfico dinámico en la base a la Tabla dinámica.

6. Seleccionamos el gráfico dinámico y lo cortamso.

7. Seleccionamos la hoja Dashboard y pegamos el gráfico.

8. Una vez pegado el gráfico lo seleccionamos y damos el formato deseado en la pestaña Diseño.

Pegamos el gráfico en la hoja Dashaboar y le damos formato

Figura 8. Pegamos el gráfico en la hoja Dashaboard y le damos formato.

Hasta este momento hemos creado una tabla con el top 5 y una gráfica. Para no hacer más grande el artículo repitiendo los pasos, haz otra tabla para el top 5 de productos y otra gráfica de Ventas por compañía. Siguiendo los pasos anteriores.

Segmentación de datos o Slices

En la hoja Tablas dinámicas hemos insertado algunas para hacer nuestros análisis, y los indicadores que medimos nos funcionan bien, pero qué sucede si deseamos que estos indicadores se ajusten a un filtro global, por ejemplo, filtros por año y mes. Imaginemos que deseamos que todos los indicadores muestren sus valores por determinado año y mes. Para lo anterior usaremos la Segmentación de datos. Sigamos los siguientes pasos:

1. En la hoja Dashboard elijamos uno de los gráficos.

2. Nos vamos a la pestaña Analizar > Insertar segmentación de datos.

3. Se mostrarán los campos de la tabla origen donde elegiremos el campo Año y Mes.

Elegimos Año y Mes para que sean nuestros filtros globales

Figura 9. Elegimos Año y Mes para que sean nuestros filtros globales.

4. Se insertarán dos Slices, uno mostrará los años y otro los meses.

5. Ahora debemos vincular esos dos Slices a todas las Tablas dinámicas, para que al elegir un elemento de un Slice, todas las gráficas se adecúen al filtro seleccionado.

6. Seleccionamos el Slice de Año y nos vamos a la pestaña Opciones > Conexiones de informe.

7. Marcamos las Tablas dinámicas que deseemos afectar.

Marcamos las Tabla dinámicas que deseemos afectar.

Figura 10. Marcamos las Tabla dinámicas que deseemos afectar.

Descarga el archivo de ejemplo

Descargar el ejemplo Dashboards en Excel – EXCELEINFO.rar

You may also like...

  • Jose Villordo

    Gracias por tus ejemplos, lo que si en este en particular, si cambias 4 datos, pones unos años 2015 y 2017 con sus respectivas fechas, al filtrar no lo hace correctamente, pruebalo y vas a ver lo que te digo, siempre trae los valores totales, sin importar el año

  • Roberth Alayo Villegas

    Alejandro gracias por tus enseñanzas son muy buenas

  • Jose Villordo

    Como puedo hacer para capturar el año que uno elije en el filtro ? Necesito saber que eligio ya sea desde VB o por formula, se puede acceder a el ?

  • Luis Martin

    Que tal……me intereso mucho esta manera de presentar la información, pero al realizar los paso para mostra el valor total, la suma de los valores no coinciden, habra algo en mis datos?
    es decir, tengo datos lo cuales me suman 26,000,00 y cuando hago la tabla dinamica me marca solo 2,000,000

  • David Rijo

    Gracias por el el aporte Alejandro, me ayudará mucho para mejorar mis reportes.

RECIBE CONTENIDO EXCLUSIVO

Tips, trucos, videos para convertirte en un EXPERTO EN EXCEL y llegar al éxito. Suscríbete y recibe el mejor contenido en tu correo.