Creación de un tablero de Excel (explicado con ejemplos y plantillas)

Un tablero de Excel puede ser una herramienta asombrosa cuando se trata de rastrear KPI, comparar puntos de datos, y obtener vistas respaldadas por datos que pueden ayudar a la administración a tomar decisiones.

En este tutorial, aprenderá cómo crear un tablero de Excel, las mejores prácticas a seguir al crear uno, las características y herramientas que puede usar en Excel , cosas que se deben evitar a toda costa y material de capacitación recomendado.

¿Qué es un panel de Excel y en qué se diferencia de un informe?

Primero comprendamos qué es un tablero de Excel.

Un tablero de Excel es de una página (principalmente, pero no siempre es necesario) que ayuda a los gerentes y líderes empresariales a rastrear indicadores clave de rendimiento o métricas y tomar una decisión basada en ello. Contiene gráficos / tablas / vistas respaldadas por datos.

Un panel de control a menudo se denomina informe, sin embargo, no todos los informes son paneles de control.

Aquí está la diferencia:

• Un informe solo recopilaría y mostraría datos en un solo lugar. Por ejemplo, si un gerente quiere saber cómo han crecido las ventas durante el último período y qué región fue la más rentable, un informe no podría responder. Simplemente informaría todos los datos de ventas relevantes. Luego, estos informes se utilizan para crear paneles (en Excel o PowerPoint) que ayudarán en la toma de decisiones.
• Un panel, por otro lado, respondería instantáneamente preguntas importantes como qué regiones tienen un mejor desempeño y qué productos. en caso de que la dirección se centre. Estos paneles pueden ser estáticos o interactivos (donde el usuario puede hacer selecciones y cambiar las vistas y los datos se actualizarán dinámicamente).

Ahora que sabemos qué es un panel, vamos a sumergirnos y aprenda a crear un panel en Excel.

¿Cómo crear un panel de Excel?

La creación de un panel de Excel es un proceso de varios pasos y hay algunas cosas clave que debe tener en cuenta al crearlo.

Incluso antes de iniciar Excel, debe estar tener claros los objetivos del panel.

Por ejemplo, si está creando un panel de KPI para realizar un seguimiento de los KPI financieros de una empresa, su objetivo sería mostrar la comparación del período actual con el período anterior (s).

De manera similar, si está creando un tablero para que el departamento de Recursos Humanos realice un seguimiento de la capacitación de los empleados, el objetivo sería mostrar cuántos empleados han sido capacitados y cuántos necesitan ser capacitados para alcanzar el objetivo.

Cosas que hacer antes de empezar a crear un panel de Excel

Mucha gente comienza a trabajar en el panel tan pronto como tiene en sus manos los datos.

Y, en la mayoría de los casos, les trae la miseria de volver a trabajar en el panel de control, ya que no se cumplen los objetivos del cliente / partes interesadas.

A continuación se muestran algunos de los Preguntas que debe haber respondido antes de comenzar a crear un tablero de Excel:

P: ¿Cuál es el propósito del tablero?

Lo primero que debe hacer tan pronto como obtenga los datos ( o incluso antes de obtener los datos), es para tener claridad sobre lo que quiere la parte interesada. Sea claro sobre el propósito que debe cumplir el panel.

¿Se trata de realizar un seguimiento de los KPI solo una vez o de forma regular? ¿Necesita realizar un seguimiento de los KPI de toda la empresa o de la división? Hacer las preguntas correctas lo ayudaría a comprender qué datos necesita y cómo diseñar el tablero.

P: ¿Cuáles son las fuentes de datos?

Siempre sepa de dónde provienen los datos y en qué formato. En uno de mis proyectos, los datos se proporcionaron como archivos PDF en español. Esto cambió por completo el alcance y la mayor parte de nuestro tiempo fue absorbido seleccionando manualmente los datos. Estas son las preguntas que debe hacer: ¿Quién es el propietario de los datos? ¿En qué formato obtendrás los datos? ¿Con qué frecuencia se actualizan los datos?

P: ¿Quién usará este tablero de Excel?

Un gerente probablemente solo estaría interesado en la información que brinda su tablero, sin embargo, algunos analistas de datos en su equipo puede necesitar una vista más detallada. Según quién usa su panel, debe estructurar los datos y el resultado final.

P: ¿Con qué frecuencia debe actualizarse el panel de Excel?

Si sus paneles deben actualizarse semanalmente o mensualmente, es mejor que cree un modelo plug-and-play (donde simplemente copie y pegue los datos y se actualizará automáticamente). Si es un ejercicio de una sola vez, puede omitir algo de automatización y hacerlo manualmente.

P: ¿Qué versión de Office usa el cliente / parte interesada?

Es mejor No asuma que el cliente / interesado tiene la última versión de MS Office. Una vez creé un tablero solo para saber que mi interesado estaba usando Excel 2003.Esto llevó a algunas modificaciones, ya que la función IFERROR no funciona en la versión de Excel 2003 (que había usado mucho al crear el tablero).

Obtener los datos en Excel

Una vez que tiene una buena idea de lo que necesita crear, los siguientes pasos son tener en sus manos los datos y obtenerlos en Excel.

Su vida es fácil cuando su cliente le brinda datos en Excel, sin embargo, si ese no es el caso, necesita encontrar una manera eficiente de obtenerlo en Excel.

Si tiene archivos CSV o archivos de texto, puede convertirlos fácilmente en Excel. Si tiene acceso a una base de datos que almacena los datos, puede crear una conexión y actualizarla indirectamente.

Una vez que tenga los datos, debe limpiarlos y estandarizarlos.

Por ejemplo, es posible que deba deshacerse de los espacios iniciales, finales o dobles, buscar y eliminar duplicados, eliminar espacios en blanco y errores, etc.

En algunos casos, es posible que incluso necesite reestructurar datos (por ejemplo, digamos que necesita crear una tabla dinámica).

Estos pasos dependerían del proyecto y del aspecto de sus datos en Excel.

Descripción de la estructura del panel

Una vez que tenga los datos en Excel, sabrá exactamente lo que puede y lo que no puede usar en su panel de Excel.

En esta etapa, es una buena idea volver con su partes interesadas con un esquema del tablero de Excel.

Como práctica recomendada, creo un esquema simple en PowerPoint junto con notas adicionales. El propósito de este paso es asegurarse de que la parte interesada comprenda qué tipo de tablero puede esperar con los datos disponibles.

También ayuda, ya que la parte interesada puede sugerir cambios que le agregarían más valor.

A continuación, se muestra un ejemplo de un esquema de muestra que creé para uno de los paneles de KPI:

Una vez que haya elaborado el esquema, es hora de comenzar a crear el panel de Excel.

Como práctica recomendada, divida su libro de Excel en tres partes (estas son las hojas de trabajo que creo con el mismo nombre):

• Datos: podría ser una o más de una hoja de trabajo que contenga los datos sin procesar.
• Cálculos: esto es donde haces todos los cálculos. Nuevamente, es posible que tenga una o más de una hoja para los cálculos.
• Tablero: esta es la hoja que tiene el tablero. En la mayoría de los casos, es una vista de una sola página que muestra análisis / conocimientos respaldados por datos.

Tabla de Excel: la salsa secreta de un tablero de Excel eficiente

Lo primero que hago con los datos sin procesar es convertirlos en una tabla de Excel.

La tabla de Excel ofrece muchas ventajas que son cruciales al crear un tablero de Excel.

Para convertir datos tabulares en una tabla de Excel, seleccione los datos y vaya a la pestaña Insertar y haga clic en el ícono de Tabla.

Estos son los beneficios de usar una tabla de Excel para su tablero:

• Cuando convierte un conjunto de datos tabulares en una tabla de Excel , no necesita preocuparse de que los datos cambien en una etapa posterior. Incluso si obtiene datos adicionales, simplemente puede agregarlos a la tabla sin preocuparse de que las fórmulas se estropeen. Esto es realmente útil cuando creo paneles de control plug-and-play.
• Con una tabla de Excel, puede usar los nombres de las columnas en lugar de la referencia. Por ejemplo, en su lugar es C2: C1000, puede usar Ventas.

Funciones importantes de Excel para paneles

Puede crear muchos buenos paneles interactivos de Excel si simplemente usando fórmulas de Excel.

Cuando hace una selección de una lista desplegable, o usa una barra de desplazamiento o selecciona una casilla de verificación, hay fórmulas que se actualizan en función de los resultados y le brindan los datos actualizados / ver en el tablero.

Aquí están mis cinco funciones principales de Excel para tableros de Excel:

• Función SUMPRODUCT: Es mi función favorita al crear un tablero de Excel interactivo. Me permite hacer cálculos complejos cuando hay muchas variables. Por ejemplo, suponga que tengo un panel de ventas y quiero saber cuáles fueron las ventas realizadas por el representante Bob en el tercer trimestre en la región Este. Simplemente puedo crear una fórmula SUMPRODUCT para esto.
• Función INDEX / MATCH: Soy un gran defensor del uso de la combinación de la fórmula INDEX y MATCH para buscar datos en paneles de Excel. También puede usar la función VLOOKUP, pero creo que INDEX / MATCH es una mejor opción.
• Función IFERROR: al hacer cálculos en los datos sin procesar, a menudo terminará con errores. Utilizo IFERROR ampliamente para ocultar errores en el tablero (y muchas veces también en los datos sin procesar).
• Función TEXT: si desea crear títulos o títulos dinámicos, debe usar la función TEXT para .
• Función FILAS / COLUMNAS: las uso a menudo cuando tengo que copiar una fórmula y uno de los argumentos debe incrementarse a medida que bajamos / derecha de la celda.

Cosas a tener en cuenta al trabajar con fórmulas en un panel de Excel:

• Evite el uso de fórmulas volátiles (como OFFSET, NOW, TODAY, etc. en..). Esto ralentizará su libro de trabajo.
• Elimine fórmulas innecesarias. Si tiene algunas fórmulas adicionales en la hoja de cálculo, elimínelas mientras finaliza el tablero.
• Use columnas auxiliares, ya que pueden ayudarlo a evitar fórmulas de matriz larga.

Interactivo Herramientas para hacer que su tablero de Excel sea impresionante

Hay muchas herramientas interactivas que puede utilizar para hacer que su tablero de Excel sea dinámico y fácil de usar.

A continuación, se muestran algunas de las que utilizo con regularidad:

• Barras de desplazamiento: use las barras de desplazamiento para guardar el espacio de su libro de trabajo. Por ejemplo, si tiene 100 filas de datos, puede usar una barra de desplazamiento con solo 10 filas en el tablero. Un usuario puede usar la barra de desplazamiento si desea ver el conjunto de datos completo.
• Casillas de verificación: las casillas de verificación le permiten hacer selecciones y actualizar el tablero. Por ejemplo, supongamos que tengo un panel de capacitación y soy el director ejecutivo de la empresa, me gustaría ver el panel general de la empresa. Pero si soy el jefe de ventas, solo querría ver el desempeño de mi departamento. En tal caso, puede crear un panel con casillas de verificación para las diferentes divisiones de la empresa.
• Lista desplegable: una lista desplegable permite al usuario hacer una selección exclusiva. Puede utilizar estas selecciones desplegables para actualizar el panel. Por ejemplo, si muestra datos por departamento, puede tener los nombres de los departamentos en el menú desplegable.

Uso de gráficos de Excel para visualizar datos en un tablero de Excel

Los gráficos no solo hacen que su tablero de Excel sea visualmente atractivo, sino que también facilitan su uso e interpretación.

A continuación, se ofrecen algunos consejos al usar gráficos en un tablero de Excel:

• Seleccione el gráfico correcto: Excel le ofrece muchas opciones de gráficos y debe utilizar el gráfico correcto. Por ejemplo, si tiene que mostrar una tendencia, debe usar un gráfico de líneas, pero si desea resaltar los valores reales, un gráfico de barras / columnas podría ser la opción correcta. Si bien muchos expertos desaconsejan el uso de un gráfico circular, le sugiero que utilice su criterio. Si su audiencia está acostumbrada a ver gráficos circulares, también puede usarlos.
• Use gráficos combinados: le recomiendo usar gráficos combinados, ya que permiten al usuario comparar valores y extraer información significativa. Por ejemplo, puede mostrar la cifra de ventas como un gráfico de columnas y el crecimiento como un gráfico de líneas.

• Utilice gráficos dinámicos: si desea permitir que el usuario realice selecciones y desea que el gráfico se actualice con él, utilice gráficos dinámicos. Ahora, un gráfico dinámico no es más que un gráfico regular cuyos datos se actualizan en el back-end cuando realiza selecciones.

• Utilice minigráficos para hacer que sus datos sean más significativos: si tiene muchos datos en su panel / informe, puede considerar usar minigráficos para que sean más visuales. Un minigráfico es un pequeño gráfico que reside en una celda y se puede crear utilizando un conjunto de datos. Estos son útiles cuando desea mostrar una tendencia a lo largo del tiempo y, al mismo tiempo, ahorrar espacio en su tablero. Puede obtener más información sobre los minigráficos de Excel aquí.
• Use colores contrastantes para resaltar los datos: esta es una sugerencia de gráficos genérica en la que debe resaltar los datos en un gráfico para que sea fácil de entender. Por ejemplo, si tiene datos de ventas, puede resaltar el año con un valor de ventas más bajo en rojo.

Puede navegar a través de algunos de mis tutoriales de gráficos aquí.

Además, si desea avanzar más en gráficos de Excel, le recomiendo que visite el blog del experto en gráficos de Excel Jon Peltier.

Qué hacer y qué no hacer en los paneles de Excel

¡Empecemos primero con lo que no se debe hacer!

Estas son algunas de las cosas que le recomiendo que evite al crear un panel de Excel. Nuevamente, estos variarían según su proyecto y las partes interesadas, pero son válidos en la mayoría de los casos.

• No desordene sus tableros: solo porque tenga datos y gráficos no significa que deba ir en su tablero. Recuerde que el objetivo del tablero es ayudar a identificar un problema o ayudar a tomar decisiones. Así que manténgalo relevante y elimine todo lo que no pertenezca allí. A menudo me pregunto si es bueno tener algo que es absolutamente imprescindible. Luego continúo y elimino todas las cosas buenas.
• No use fórmulas volátiles: ya que ralentizarán los cálculos.
• No guarde datos adicionales en su libro de trabajo: si necesita esos datos, cree una copia del panel y guárdela como copia de seguridad.

Ahora echemos un vistazo a algunas recomendaciones (o mejores prácticas)

• Numeración de sus gráficos / sección: su tablero no es solo un conjunto aleatorio de gráficos y puntos de datos. En cambio, es una historia en la que una cosa lleva a la otra.Debe asegurarse de que su audiencia siga los pasos en el orden correcto y, por lo tanto, es mejor numerarlos. Si bien es posible que pueda guiarlos cuando esté presentando en vivo, es de gran ayuda cuando alguien usa su tablero en una etapa posterior o saca una copia.
• Restrinja el movimiento en el área del tablero: Oculte todas las filas / columnas para asegurarse de que el usuario no se desplace accidentalmente.
• Congelar filas / columnas importantes: use congelar paneles cuando desee que algunas filas / columnas estén siempre visibles en el panel.
• Haga que las formas / gráficos se adhieran: asegúrese de que sus formas / gráficos o controles interactivos no se oculten o cambien de tamaño cuando alguien oculte / cambie el tamaño de las celdas. También puede usar la herramienta Cámara de Excel para tomar una instantánea de gráficos / tablas y colocarla en el tablero de Excel (estas imágenes son dinámicas y se actualizan cuando se actualiza el gráfico / tabla de fondo).
• Proporcione un Guía del usuario: si tiene un tablero complejo, es una buena idea crear una hoja de trabajo separada y resaltar los pasos. Ayudará a las personas a usar su panel incluso cuando no esté allí.
• Ahorre espacio con gráficos combinados: use gráficos combinados (como gráficos de viñetas, gráficos de termómetros y gráficos reales frente a objetivos) para guardar su hoja de trabajo espacio.
• Use símbolos & Formato condicional: use símbolos (como flechas arriba / abajo o marca de verificación / marca de cruz) y formato condicional para agregar un capa de análisis a su panel de control (pero tampoco se exceda).

¿Desea crear paneles de control profesionales en Excel? Consulte el curso de Tablero de Excel en línea donde le muestro todo sobre la creación de un Tablero de Excel de clase mundial.

Ejemplos de Tablero

Aquí hay algunos ejemplos interesantes de Tablero de Excel que puede descargar y jugar con.

Tablero de KPI de Excel

Puede usar este tablero para realizar un seguimiento de los KPI de varias empresas y luego use gráficos de viñetas para profundizar en el desempeño de la empresa individual.

Haga clic aquí para descargar este panel de KPI.

Si está interesado en aprender a crear este panel de KPI, haga clic en aquí.

Panel de rendimiento del centro de llamadas

Puede utilizar este panel para realizar un seguimiento de los KPI clave de un centro de llamadas. Desde este panel, puede aprender cómo crear gráficos de combinación, cómo resaltar puntos de datos específicos en gráficos, cómo ordenar usando botones de opción, etc.

Haga clic aquí para descargar el panel.

Puede leer más sobre este tablero aquí.

Temporada de EPL visualizada en un tablero de Excel

En este panel, aprenderá a usar VBA en paneles de Excel. En este tablero, los detalles de los juegos se actualizan cuando haces doble clic en las celdas de la izquierda. También usa VBA para mostrar un menú de ayuda para guiar al usuario en el uso de este panel.

Haga clic aquí para descargar el panel de EPL.

Puede leer más sobre este panel aquí.

Capacitación recomendada sobre paneles

Si desea aprender a crear paneles de control profesionales de clase mundial en Excel, consulte mi curso GRATUITO en línea sobre paneles de Excel.