Ready to impress your boss with the new MS Excel dashboard‒ just download the SphInX Dashboard and you’re set to go.
In this post, I’m going to give a detailed description of the structure of the dashboard and an overview of the components involved. I’ll describe the working and creation part in detail in the posts that will follow.
As always, we have divided the have followed a three sheet structure:
- Display sheet (User Interface)
- Processing sheet
The section below describes the three sheets individually.
This sheet forms the most important part of the dashboard‒ it is the sole point of interaction between the user and the underlying dataset. It doesn’t matter if you have analyzed your data to derive an extraordinary insight‒ it won’t sell if you’re unable to present it well.
Since SphInX is a KPI reporting dashboard across countries, I’ve created the selectors for only Country and currency selection to keep it simple but as we go on, we will keep on increasing the number of visualization options available to the user. The analyses have been restricted to 1) brand-wise sales 2) market share 3) molecule profiles.
This sheet forms the link between the display sheet and the data sheet. This is where all the calculations for the dashboard are performed. While making this sheet, we ensured that all the calculations are dynamic i.e. they change on changing the selections on the display sheet.
The database can be one sheet or multiple sheets depending upon the nature of the data being represented. We have used two sheets with different types of data:
1) Sheet 1 – IMS sales data sheet: This sheet has the sales data for different products across three countries (to know more about IMS data, visit their website)
2) Sheet 2- Molecule profile sheet: This sheet has a mini-profile(Molecule name, Indication, Price, etc.) for the different molecules covered in the sales sheet
Working of the tool:
We’ll be using a number of formulas, objects and tricks in making the dashboard. They have been listed below and their detailed explanation can be found in Dashboard 101 section:
- Formulas: OFFSET, MATCH, COUNTIF, INDEX, ISNA, ISERROR (We’ll be using these formulas in combination with each other and will also be develop array formulas to perform tasks that are usually done programmatically using VBA.)
- Objects: Combo Box (Drop down), Option buttons
- Tricks: Named range, Dynamic chart, Conditional formatting, Array formulas
We’ll detail on all of these in our next post. Till then you can play with the dashboard and explore its various components.
Keep Excelling! :)