In preparation for teaching a workshop on Excel Dashboards & Reports, I’m thinking about what needs to be covered. Several months ago, Mynda Treacy of My Online Training Hub provided an excellent guest post on Excel reporting.
(Going forward, I use ‘Dashboard’ to include both Dashboards and Reports. Dashboards use charts and other visuals while Reports are tables of numbers and totals. In everyday language ‘Dashboard’ and ‘Report’ are used interchangeably.)
♦
Dashboards are data summaries used for different purposes. They display trends if we’re monitoring things like expenses, customer complaints, employee training progress. A Dashboard can give at-a-glance of where we are vs. where want to be with budget, hiring or product launch.
Those are benefits of the Dashboard itself. However, there are 2 side benefits of Dashboards. When a spreadsheet is developed with a Dashboard in mind,
- Your document layout can be cleaner and data entry simpler
- Source data can be stored in a way that allows for ad hoc investigation
Dashboards Simplify Your Data Entry & Organization
One issue that novice spreadsheet developers face is layout & organization. If it’s an annual budget, they might make 12 worksheets for each month. And then the realization:
“Ah! I would like to know my medical expenses over the year.”
Now there is a 13th worksheet for Medical summary, then a 14th for Auto-related expenses. The image below shows 17 worksheets!
It makes perfect sense. We’re talking basic sums and linking cells. Dividing a document into month-sized pieces makes sense, and there ya’ go … the creation of a frustrating document with too many moving parts.
If we think ahead of time and plan to use a Dashboard, it is often possible to place all data in one place, and rely on the Dashboard to give the summaries. Now we have TWO worksheets, not 17:
- Data Input/Storage
- Dashboard
Note in the image below:
- 3 months of activity in one list
- The dates aren’t in order (the Excel Dashboard will organize the dates)
- There are categories:
- Meetings
- Parking: Business
- Transaction amounts
- There are just 2 main tabs. The Categories tab is simply the list of categories that populate the dropdown boxes.
The image below is a small piece of the 2008 Summary. We get a broad view of what’s what in the 2008 expenses. That’s all we need at-a-glance. We don’t need to see transactions dates, the names of the charities or, the cost of renting a projector.
For more advanced documents, we can add a VBA-driven form that takes new data and compiles it in a storage area.
See the post and video on the Insurance Agent Career Tracker. In the video, you get a glimpse of the dashboard at 3:08 to 3:20.
All of the information is stored in just one place. The Career Tracker Dashboard extracts the policy types, monthly activity, revenue, commission totals, birthdays, etc.
Dashboards Simplify Data Analysis & Investigation
What if we do want to dig out the detail on renting a projector? Maybe it’ll save money to buy one? Having data all in one place makes it easier to dig out information that you didn’t expect you’d need. Enter: pivot tables!
Because all the transaction details are is in one place, we can use a pivot table to see granular information. In contrast, using the sheet-per-month method, we would have to hop from sheet to sheet looking for the projector rentals.
We won’t go into pivot table details here. The point to get is this:
[blockquote]Using a Dashboard keeps vital stats front and center while granular details are easily accessible. Without a dashboard, layout gets complicated, inflexible and unforgiving.[/blockquote]This is Only the Start
Dashboard development is its own artform that I won’t detail here. Some elements in Excel Dashboard & Report development are:
There are many layers in an effective Dashboard, and the clarity is worth it. Stay tuned for more! Meanwhile, please keep your data clean.
Dashboard 70 photo credit: nima; hopographer via photopin cc
