This week, we're going to be looking at how we can design dashboards in Excel.
So first of all, what is a dashboard?
The definitions do vary, but essentially,
a dashboard is a visual display on a single screen that shows
the key information that a business needs to achieve a certain process or outcome.
Dashboards can be static or interactive.
And while there is an argument that says dashboards shouldn't have interactive elements,
if it will support the business requirement,
then it may well be something you want to do.
And what we're going to look at is how we can create
a fairly simple dashboard with one or two interactive elements.
So this week, Alex has been asked to
create a dashboard for a company that has a small call center.
Six months ago, they realized that the call center was underperforming,
and they put some initiatives in place to try and address this.
To begin with, they did seem to be quite a positive response,
but they're concerned that standards are starting to slip again,
and they would like a way to quickly identify
how the call center has performed over the last six months.
And for this, Alex is going to provide them with a dashboard.
The first thing we need to do with our dashboard is to determine
what the key information is that the business needs to know.
A common mistake is to put too much information into a dashboard,
and this actually reduces the impact.
So step one, identify the key metrics the business needs to know.
And then, of course, ensure that the data will,
in fact, support those metrics.
The next step is to start designing the dashboard.
This would probably be done just with pencil and paper to begin with.
And once you have a reasonably good idea of how you want a dashboard to be laid out,
it's probably a good idea to just go and lay it out roughly in Excel.
And that's the step that we're going to jump into now.
So we're going to create a new sheet.
And the first thing we're going to do is select a background color.
And we're going to go for black because,
when viewed on screen,
bright colors against a black background are extremely visible.
While our dashboard is going to contain mostly visual elements,
we will occasionally need to put information into cells so that,
for example, we can use Conditional Formatting.
So, this will require a little bit of careful planning.
Now, we've determined that we need five key metrics across the top,
and we're going to work on three columns per
metric so that we can have some information centered and then,
potentially, information on either side.
So we're going to start by creating some space to pop a heading in.
So, I 'm going to select B2 to B4,
and then I'm going to come the whole way across to P4,
and I'm just going to identify that by applying a different color.
I'm going to go for a nice, dark grey.
Grey is very in vogue at the moment.
Alright, now the space where we're going to put our key metrics
is going to be the next section,
and this is really important.
You must use segmentation well in your dashboard.
What that means is,
group information that belongs together in close proximity.
And where you're going to have interactive elements,
make it very clear in your grouping which elements
are going to be impacted by any changes the user should make.
So across the top here,
we're going to have our main metrics across the six-month period.
And I'm going to select from B6 down to B14,
and again come across to column P,
and make that a dark grey.
We're then going to have another section at the bottom.
In fact, we're going to have two sections,
one to show agent metrics and one to show monthly metrics.
So once again, I'm going to select across to P,
but I'm going to go all the way down to row 36.