Excel Dahsboard Basics
In the simplest terms, Microsoft Excel dashboards are condensed and mostly, visual representation of data sets, including but not limited to tables, charts and graphs. It is a visible display of the most crucial information needed to reach multiple objects, consolidated and arranged on an individual screen so the information may be monitored in a glimpse. Many organizations employ dash panels only for the management use. In fact, they are as useful for the operational levels as for the leadership because of simplicity and ease-of-use.
Let us suppose you have sales data for last 5 periods for your business from all sales people, territories and type of items. A dashboard can help you see the trends by product; sales by each employee; response from different regions and so on.
The idea is to show the most important performance indicators to be monitored. A single screen is desirable, but there might be tables with a lot of rows or charts with a lot of data points requiring some scrolling. Built-in interactive functions, such as filtering, drill, date ranges and the like add value to such analysis. However, the critical pieces of information, e.g., underperforming metrics and areas of variance should be highlighted on the default screen.
Best designed Excel dashboards update upon adding new items to the underlying datasets usually spread across various sheets in the same or a linked file. Continuing with our previous example, let us say a new sales territory is added. This should prompt an automatic update to the graphs on the summary panel. In the absence of this modularity, the analysis becomes more static in nature. Even if there is no immediate perceivable need, it is good to build this functionality in the beginning itself.
A lot of times, the data utilized is historical data. However, some dashboards rely on live data that can be sourced in different ways, such as an OBDC driver (Open Database Connectivity) that accesses data from a database or even a simple web query. There are more complex ways to obtain large amounts of live data, but that is typically used by technically savvy users, like data scientists and information analysts.