Dynamic Excel Dashboard Reporting
It’s not quite a full cell-by-cell account of the methods I’ve done it as it requires a post at least twice as long, but instead call several steps and thoughts as you go along. In this post, I’ll go into more detail about the methods I created this dashboard. I thought historical American political data provides a rich and fascinating dataset to visualize and animate.
This was a project I’ve had in mind for a while. This project started with paper and pen, simply outlining a few ideas of the items, info and charts to include. So I went looking for voting data, state population data, and presidential data from the past century.
After that, I thought it would even be interesting to add estimates of the US state’s population. That I knew it might be interesting to take a look at the amount of voting data from a century along with advice on the presidents who served during that period. The first part of the dashboard that I implemented is the grid map of states and presidents voting records, on the left side of the dashboard.
When I think about Staging the information, I’m talking about preparing the information and configuring it in aggregated tables, ready to get the Excel dashboard. Finally, I implemented a thick white border towards the entire grid to provide some separation and get a cleaner look. At the top of this table, we have five column headings with annual values, which change as soon as the user activates the control area of the dashboard.
The concept here would be to update the profile picture around the dashboard to show a picture of the president depending on the name displayed. Changing the profile picture display was a bit trickier. Now that I had to put most of my visuals in the Excel dashboard, it was time to add some interactivity.
What follows here is a description of the methods I applied this approach to my dashboard. The following code then handles the logic by simply setting the year variable to whatever is chosen from your drop-down menu. This is the last big piece of the puzzle and I’ve also used some basic VBA to apply an animation option along with a manual year selection.
I froze the very best three rows to lock the Excel dashboard title and controls on the top screen. I formatted the header row with a light gray fill and a dark gray bottom border. I have also hidden all other sheets with the information and the staging data.
I hid the column of variables lurking next to the Excel dashboard to keep users from discovering them. All these changes were relatively minor and didn’t take long to be made, but they have a disproportionate impact on the aesthetic quality and ultimately the effect of the dashboard, so it’s worth using this last step. There is absolutely no requirement for these to be shown to the consumer, and in reality they reveal the risks of users modifying your computer information.
But first let me share a few ideas that I tried that didn’t make it to the final dashboard. Of course, our work was never done and not much of a moment passed after the metaphorical ink dried before I started thinking about improvements and then moving on. Replacing it with a simple bar chart would have been a huge improvement.
I had to remove most of the state labels because they were so busy, leaving only the largest state names. The population graph shows states, but they do not match the vote record data by state. Moral of the story – beware of all the beautiful maps available as they are hardly ever a marked improvement on their simpler, more humble brethren.
If I needed extra time built for a client rather than for fun then your variables will be cleaned up next to the dashboard and placed somewhere around the staging sheet, so annoyingly lurking around the dashboard sheet not meeting the target is committed to displaying information. Also, it is probably unnecessary to get the population column there because of the information in the graph. In the same vein of tidying up, I personally would have tried more named ranges to make my formulas neater and much less error prone if I did this again.