Now we jump over to the Dashboard worksheet and set up a couple of dropdowns — one is the report period selector, and the other is the report range (how many months to include in the chart) selector.
Start by setting up some labels with dropdowns (I normally put these off to the side and outside the print range…but that doesn’t sit nice with the screen resolution I like to work with on this blog): Then, set up the dropdowns using Excel data validation: First, the report period.
That leaves us with a Data tab that looks like this: While we’re on this tab, we should go ahead and defined some named cells and some named ranges.
It’s important to have consistent naming conventions, so we’ll go with _Current for this (it works out to have the metric identified first, with the qualifier/type after — just trust me! The screen capture below shows this being done for the cell where the current value for Orders will go, but this needs to be done for Revenue and Web Traffic as well (I just remove the space for Web Traffic — Web Traffic_Current).
And, we’re definitely going to want to have the whole range of data on the tab available to us.
See for a look at my thoughts on dashboard visualization.) This is a slightly iterative process that starts with the setup of the Data tab.
On that worksheet, we’ll use the first column to list our dates — these could be days, weeks, months, whatever (they can be changed at any time and the whole approach still works).