Before you Begin
We recommend that before you jump straight into designing your dashboard
in Excel you take a bit of time thinking about what information you
want to show and how you want to show it. The following steps should
help you prepare your Excel dashboard. Also don't forget to take a
quick look at our basic introduction to dashboard reporting..
Step 1:
Make a list of the key metrics you want to show on your dashboard.
Remember the key dashboarding principle of ‘eloquence through simplicity’.
Try where possible to group the metrics into thematic areas. For example
a Human Resources dashboard might show the following metrics..
Recruitment Metrics Number of open positions Average cost per recruitment Average days per recruitment |
Workforce Metrics Workforce breakdown by age Workforce breakdown by sex Workforce breakdown by grade |
Retention Metrics Voluntary turnover rate Involuntary turnover rate Average length of service |
Step 2:
Decide the most meaningful increment of time each of the dashboard
metrics will display (current, hourly, daily, weekly, monthly quarterly
or annually). You may want to design your dashboard to give you an
instantaneous snapshot of the current situation. You may want to update
your dashboard once a month for the monthly management meeting. You
may even decide to display and compare the same metric over different
time increments (for example the current number of open positions
compared to the trend over the last year).
Step 3:
Decide which dashboard widgets you want to use to display each of
your metrics. The following widgets are available (feel free to let
us know if you would like us to design more). Try to stick to three
or four styles per dashboard.
- Linear Dial Widget - Non-Linear Dial Widget
- Thermometer Widget
- Equalizer Widget
- Traffic Light Widget
- Pie Widget
- Trend Widget
- Up/Down Widget
- Funnel Widget
- Dots Widget
Step 4:
Choose a layout for you widgets. Generally it is good practice that
a dashboard should fit on a single landscape screen or single printed
sheet of paper (either landscape, portrait). Users should not have
to scroll, turn pages, drill-down or recalculate to find the necessary
information. The following are example layouts that fit onto a single
sheet of paper.
Step 5:
Sketch your dashboard out on paper. It is significantly easier to
first design a dashboard on paper than to build it straight in Excel.
Step 6:
Now it’s time to create your dashboard in Excel. Open up a new Excel
Dashboard Widgets spreadsheet and set up three new tabs as follows..
Tab #1 – rename as ‘My Dashboard’
Tab #2 – rename as ‘My Configuration Page’
Tab #3 – rename as ‘My Calculations Page’
Step 7:
Copy and paste the widgets you want to use from the Excel Dashboard
Widgets ‘Widgets Showcase’ Tab onto Tab #1 and arrange them into your
required dashboard layout. Use the cell lines to help you line the
widgets up neatly.
Step 8:
Copy and paste the configuration tables from the ‘Widgets Showcase’
onto Tab #2 and arrange them in a similar layout to the dashboard
layout.
Step 9:
Copy and paste the relevant widget calculations tables from the ‘Widget
Showcase Calcs’ tab into Tab #3.
Step 10:
Now comes the difficult part – you need to link these three tabs together
in exactly the same way as the widgets are linked together on the
‘Widgets Showcase’. More information on duplicating widgets can be
found here in the forum..
..and also here on YouTube....
Step 11:
You may like to configure your own color scheme. More information
on duplicating widgets can be found here..
..and also here on YouTube....
Final Note
If any of this doesn’t make sense – post a message in the forum and
we would be delighted to help you with your dashboard project.
Grab a coffee, take a deep breath and good luck with your dashboard!
DashboardWidgets
www.ExcelDashboardWidgets.org Administrator