Cómo hacer un tablero de control o dashboard en Excel

No han sido pocas las veces que me he encontrado con empresas que si bien han abandonado el papel, todavía no explotan por completo las posibilidades que les entregan las herramientas en su computador.

Precisamente esta semana me encontré con dos gerentes que analizaban los ingresos de su negocio haciendo sumas con =suma() tras aplicar filtros en una base de datos gigante de excel. ¿Te imaginas?

No hablamos de herramientas complejas como ERP o CRM, hablamos de cosas simples como word o excel. Herramientas que la mayoría de las empresas tienen en sus equipos.

De ahí el post de hoy. Quiero mostrarte como en equipo, construimos un tablero de control (dashboard) en excel a partir de la información que tenían en bases de datos.

Hay muchos suscriptores de Ingenio Empresa que son expertos en excel y puede que lo que hoy muestro en este post ya se lo sepan al derecho y al revés.

Pero si nunca has hecho un tablero de control o manejas grandes volúmenes de datos en excel o si crees que puedes analizar la información de bases de datos de forma adecuada, quédate con nosotros.

Hoy hablaremos de:

  • Cómo definir la estructura de un tablero de control.
  • Cómo elaborar un tablero de control.
  • Cómo aplicar formato gráfico para que el tablero se vea bien.

¿Te quedas?

Qué es un tablero de control o Dashboard y para qué sirve

Tablero de control, conocido en inglés como Dashboard no es más que una pantalla donde se expone información de valor para el negocio. Este se construye a través de la manipulación, transformación y análisis de datos, de tal forma que en esa pantalla se exponga de forma muy visual, indicadores o kpi’s críticos de la organización.

Dicho esto, ¿para qué sirve un dashboard? Con la información pertinente presentado de una forma visual, el resultado es uno: Toma de decisiones.

El dashboard debe presentar la información de forma tal que las decisiones salten a la vista.

¿Cómo hacemos eso? Es lo que veremos a continuación.

Definiendo la estructura del tablero de control

Antes de elaborar el tablero debes revisar los datos y pensar,

  • ¿Qué información se puede obtener?
  • ¿Qué información me gustaría obtener?

Siempre recomiendo hacer este ejercicio en grupo, pues cada miembro puede tener unas necesidades concretas y si el dashboard va a ser para varios, pues mejor que a todos les sea útil.

Por ejemplo si estas viendo las ventas obtenidas por las diferentes líneas de galletas Noel en las ciudades de Colombia, el análisis de la estructura del dashboard puede resultar en:

  • Se requiere conocer las ventas por ciudad.
  • Mejor si se conoce las ventas por ciudad por línea de galleta.
  • Sería apropiado observar correlación entre ventas y ciudad.
  • Evolución de las ventas a través del tiempo.

Cómo hacer un tablero de control en Excel (parte 1): Definiendo la estructura

De esta forma ya partes con unos objetivos para tu dashboard.

Ajustando los datos del dashboard

Es probable que esa base de datos en excel haya sido contruida por diferentes personas en el tiempo o sea un reporte generado por un software.

Por lo genera las bases de datos no suelen ser uniformes, es decir que los datos no presentan el mismo formato.
Es decir que si en la columna ciudad dice Bogota en una celda y Bogotá en otra celda (fíjate que una tiene tilde y la otra no), Excel toma eso como dos palabras diferentes. Por lo tanto, cuando hagamos un análisis de frecuencia contando el número de ventas generadas por cada ciudad, se contaran de forma separada para Bogota y Bogotá. ¿Nos sirve esto? Por supuesto que no.

Por lo tanto, en este paso lo que hacemos es ajustar la base de datos.

Te adelanto que este suele ser el paso más díficil. Tenemos que aplicar filtros y mirar lo que nos muestra excel e ir reemplazando datos.

Para esto, suelo usar las siguientes herramientas:

  • Función Espacios: Elimina los espacios en en el texto. Por ejemplo si tengo Bogotá                 (espacios). Esto ocurre porque a veces escribimos una palabra y presionamos más de una vez la barra espaciadora para hacer el espacio entre palabras.
  • Función BuscarV: Trae un valor a partir de un comando en una base de datos.
  • Buscar y reemplazar: Lo activas presionando Control+B. Lo que haces es indicar la palabra que deseas reemplazar y colocar su reemplazo. Por ejemplo en buscar coloco Bogota y en reemplazar coloco Bogotá. Todas las palabras que digan Bogota serán reemplazadas por Bogotá.
  • Función Extrae: Extrae caracteres de una cadena de texto. Si una palabra dice COLOMBIANOS y quiero obtener los primeros 8 caracteres para que me quede en COLOMBIA esta función me lo permite.

Esas son las que más uso. Pero hay muchas, muchas más. Por ejemplo si el software te genera las fechas en formato texto, tendrás que ingeniartelas para pasarlas a formato fecha. La buena noticia es que en internet hay mucha información para cada problema que tengas al ajustar una base de datos en excel.

Cómo hacer un tablero de control en Excel (parte 2): Ajustando la base de datos

¡Listo! Seguimos.

Elaborando los gráficos, tablas y tarjetas del tablero de control

El resto ya es un paseo.

Nuestro archivo de excel debe tener una hoja o pestaña donde haya:

  • La base de datos ajustada
  • Las tablas dinámicas con las que se contruye el tablero de control
  • El tablero de control

Tener esta organización te permite realizar los ajustes necesarios cuando lo necesites. Si  lo tienes todo en una hoja de excel, además de no verse muy bien, los ajustes que vayas a hacer podrían tener errores.

En este paso lo que hacemos es insertar la tabla dinámica, crear los gráficos dinámicas a partir de las tablas ya creadas y construir tarjetas.

Cómo hacer un tablero de control en Excel (parte 3): Creando tablas y gráficos dinámicos

Aplicando segmentaciones y conexiones de informe

Las segmentaciones son cuadritos que al darle click harán que nuestros gráficos y tablas cambien. Puede que apliques una segmentación de tiempo (es decir que relacione los años o meses en un cuadro) pero quieras que esa segmentación no te afecte todos los cuadros y gráficos que tienes, ahí es cuando entran las conexiones de informe, con esto lo que logras es definir los cuadros, tablas y gráficos que son accionados por las segmentaciones.

Cómo hacer un tablero de control en Excel (parte 4): Segmentaciones de datos y conexiones de informe

Revisar y ajustar

Por último, debemos probar nuestro dashboard. Si lo elaboramos para otra persona, que sea este quien lo pruebe. Los ajustes serán realizados según el resultado de las pruebas.

En este paso aplicamos formatos gráficos y revisamos que todo se encuentre conforme lo planificado.

Cómo hacer un tablero de control en Excel (parte 5): Aplicando formato gráfico

Modelo en excel

¿Quieres elaborar tus propias herramientas a partir de modelos de ejemplo?
Todos queremos hacer más rápido nuestro trabajo. En modelos en excel encuentras archivos que puedes tomar de ejemplo para elaborar tus propias herramientas, incluyendo nuestro ejemplo de Dashboard en excel. Haz click aquí para descargar el ejemplo de tablero de control de este post.

Fuente de imagen: La imagen de encabezado del post fue descargada en: Freepik

Si esto te ha sido útil...
Únete a la comunidad. ¡Es gratis! Vas a recibir en tu correo recursos, herramientas y novedades exclusivas para miembros de Ingenio Empresa.

Responsable de tus datos: Diego Betancourt Finalidad: Envío de publicaciones y productos creados por Ingenio Empresa. Legitimación: Tu consentimiento otorgado en este formulario. Destinatario: MailRelay (ubicados en España). Es mi herramienta de email marketing. Derechos: Puedes darte de baja cuando desees y ejercer tus derechos de acceso, rectificación, cancelación y oposición.

5 comentarios en “Cómo hacer un tablero de control o dashboard en Excel”

  1. Buenas tardes.
    Excelente modelo y su explicación, Muchas gracias por aportarnos tantas herramientas y conocimientos, espero en un futuro ser muy útil para otras personas que requieran de mi experiencia, como en estos momentos lo son para mi.

    • Gracias por tu comentario Yehison.

      Pienso mucho que con internet el que no aprende es porque no quiere. Lo que puedes aprender aquí y en otros sitios lo podrás aplicar y enseñar a otros en otros entornos, solo si tu quieres y lo haces con disciplina. De ahí que internet suponga una democratización del conocimiento.

      Dicho de otra forma Yehison, el futuro es hoy.

      Un saludo.

  2. Hola, te consulto.
    si cambia algún dato de la matriz inicial de datos cambian las tablas dinámicas, y por lo tanto los gráficos asociados a cada tabla?
    He intentado hacer cambios en los datos y ni las tablas ni los gráficos cambian. No se si es un problema del excel de mi Pc, o es que es isa y no cambian en las tablas dinámicas.

    • Hola Cintia.

      Si los datos que modificas están dentro del rango de la tabla dinámica, debería comunicarse.

      Si agregaste datos por debajo del rango que toma la tabla dinámica, ahí si no se te va a actualizar. Para revisar el rango, dale click a la tabla dinámica, se te habilitará en la parte superior, “herramientas de tabla dinámica”, allí dale a la pestaña analizar y busca la opción “cambiar origen de datos”. Si no incluye los nuevos datos agregados, desde allí puedes cambiar el rango y la tabla debería actualizarse.

      Para evitar esta actividad de actualizar el rango de datos, puedes usar desref. En Exceltotal lo explican muy bien, aquí. Si te cuesta entenderlo, en youtube también hay ejemplos del uso de desref para tener “rangos dinámicos en tabla dinámica”

      Otra cosa que puedes hacer es usar power pivot y power query. A través de consultas y tablas, podrás actualizar automáticamente las tablas y tener toda la estructura del dashboard de una manera mucho más limpia, aunque esto tieme una curva de aprendizaje algo más empinada, pero te servirá mucho. En youtube encuentras tutoriales de power query y power pivot, y pronto estaremos sacando otro post de dashboard con estas herramientas de excel.

Deja un comentario

Responsable de tus datos: Diego Betancourt. Finalidad: Moderación de los comentarios por el tiempo que dure este post publicado o hasta que decidas borrar tu comentario. Legitimación: Tu consentimiento otorgado en este formulario. Destinatario: Wordpress.Derechos: Tienes derechos de acceso, rectificación, cancelación y oposición de tus datos. Este sitio usa Akismet para reducir el spam. Conoce cómo se procesan los datos de tus comentarios.