Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.
Practice Video: Construct Dashboard
Loading...
Do curso por Macquarie University
Excel Skills for Business: Advanced
136 ratings
Na lição
Building Professional Dashboards
To bring it home, this module guides you through the steps of building a professional dashboard. Data is meaningless unless it tells a story and can be used as business intelligence. This module will help you set up a dashboard – learn how to visualise data through graphs and charts, create data models, and add interactivity.
Conheça os instrutores
Dr Yvonne BreyerDirector, Learning and Teaching
Faculty of Business and Economics
Alex has now completed
the calculations and pull through the additional values that he needs.
He's now ready to start constructing the dashboard in earnest.
And in this video, we're going to look at some tips and
tricks for working with graphical elements in our dashboards.
To begin with, we have the values we need in these shapes,
but when we add a value to a shape,
if we pick up the formatting from the cell we select and
rather than going and manually formatting each of these cells,
we are going to use our format painter.
So, I'm going to click on the correctly formatted cell,
doubleclick my format painter and this will allow me to apply multiple paints.
And when I'm finished, I just click it off. So nice and quick.
Now, as we said in the previous video,
you very often will need to see some sort of comparison in your dashboard.
So, this value is fine but is not above or below my target.
So, as well as the actual values,
we've also pulled through the targets and now we want
some graphical way of illustrating if we are above or below that target.
And a very good way of doing this, is conditional formatting.
In the speech bubbles over here,
you can see we have added a little star rating where if they have achieved that target,
they get a full yellow star,
if they are not quite on target but above the sub par,
they get half a star,
and if they're below,
they get no star.
And this was done with conditional formatting.
Now we want to do something similar here,
but we want to see a little green up arrow if they are
on or above target and we want to see a little red down arrow if they're not.
Now of course, we want our conditional formatting to be highly responsive to change.
So, we're not going to hard code the values, instead,
we're going to do some little calculations over here and
then use those to base our conditional formatting on.
So, let's start with our time to answer.
We need a little calculation that will check if we have met the target or not.
And I'm just going to use a simple if for this.
So, I'm going to say, if
our actual answer time is less than or equal to the target answer time,
then I have achieved the target.
Otherwise, I haven't and I'm just going to use a one and a zero.
Now, the next one is our resolve rate and actually
we want that to be on or above the target value.
So, this is slightly the opposite.
We're going to say, if this value is greater than or equal
to our 65 percent then we get a one otherwise a zero.
Now, service level is much like resolve rate.
So, we can just copy that down.
And our abandon rate is like our answer rate.
So, we can just copy that formula and paste it over here.
And from just a quick glance,
you can see that we are on target for two of them but below par for the others.
Now we're going to go and put those values that we've calculated into these cells.
I'm just going to type equals and click on
my first value and then I'm going to do the same over here.
So we've got the calculations,
but now we need to replace those with conditional formats.
So, I'm going to click on to
the first one and I'm going to come up to conditional formatting,
but I'm going to come directly to new row,
so, I get complete control of how this works.
And I'm not looking for two color scales,
I'm actually looking for an icon.
Now, the icons that's available are pretty good,
but if you want total control you can actually create your own custom icon.
So, that's where we're going to do.
Instead of the green circle,
we going to have a green up arrow and instead of the yellow circle,
we going to have a red down arrow,
and in the unlikely event of it being negative,
we're going to have a yellow negative.
Now, I'm going to adjust these to match the actual values I'm expecting to see.
And I'm not working with percentages in this case,
I'm actually working with numbers.
And I want to say, if it's greater than or equal to one,
they're going to get the green arrow.
Otherwise, they're going to get the red and they'll only get yellow if it's negative.
Now very important, remember to tick the show icon only,
which means you won't actually see the calculated value.
And then we're going to say, okay,
just centerline that, job done.
And now, we can actually copy that formatting using our format painter again.
Double click my format painter and then just go and apply
it to the other cells that require a metric.
And there you go, a nice clear visual indicator using conditional formatting.
Now one of the main tools we're going to use in constructing our dashboards, is charts.
Charts are great, because they present a lot of information in a clear visual way.
So coming back to our calculation sheet,
Alex has already created a chart to show customer satisfaction ratings by agent.
But again, we want to see some measure of whether they are achieving what they need to.
And so, we want to show their targets in this as well
but we want to show it as a target line.
So the first thing we're going to do,
is scrolling down slightly.
You'll see here's the data for my chart.
I'm just going to grab that bottom left corner and drag across to include the target.
But I don't want the target to show is a bar.
So I'm going to select that series,
come up to my design,
click Change chart type and then I'm going to make this a line chart.
If you're working with an older version,
this might look slightly different but it will do the same things.
So just select line chart if you don't get exactly this dialog.
And now, we're going to click okay.
The solid line however looks a little weird,
so we're actually like to make this a dash line.
So I'm going to write to click on my series and I'm going to click Format data series.
I'm then going to come to the formatting options and if necessary, expand line.
If you come down a little bit,
you see there's an option to add a dash type and I'm just going to go
for the little dashes and that's looking a little bit more like a target line now.
Now, a few of the things we'll probably want to do.
We're actually going to remove those grid lines.
So I'll just click on any one of them press delete as a nice quicker way.
And if you have a legend,
you'll probably want to remove that as well.
This axis also needs to go from zero to five and I want it going up in units of one.
So, I'm going to come across to my format axis options.
And although this does say zero,
it may change if the values change.
So, we can actually fix that at zero and we're going to change
our maximum to five and we're going to change all major units to one.
And by doing this, even if our value is changed,
we have locked those values in place.
And then, so that our title is not so far above the chart,
we're also going to come to chart title and we're going to go for a centered overlay.
And this just reduces the amount of space that the title takes up.
We're now ready to copy that chart and paste it into our dashboard.
So Control-C come back to our dashboard,
click somewhere near where you wanted to go and control V. Now we can
immediately see there are a couple of problems but what fill is not working.
So, we're going to come up to our format tool and we're going to
choose no outline and no fail.
And now, we going to change our font to be white.
So just back on the home tab, change the font to white and that's looking pretty good.
And now you can resize it,
if you need to, you just need to squish it up a tiny bit.
Excellent!
One last little feature we're going to look at and this is Excel's camera tool.
It's not available in the ribbon and so you may not have encountered it before.
But it's really handy for creating dashboards.
Now, I have added it to my Quick Access Toolbar.
Just to remind it to do this,
you need to click the dropdown,
click more commands, you won't find it in popular commands.
So therefore commands not in the ribbon,
locate the camera tool and add it to your quick access.
Now, coming back to our calculations sheet,
another thing that Alex has done is to give an idea of the trend in satisfaction reviews,
he's created a spark line for each of our agents.
Now, we want to be careful putting spark lines in
our dashboards because they can make it look a little bit busy.
But, spark lines are great for representing a lot of data in a small space,
and we really need this information.
The problem is, spark line sit in a cell,
which means I won't get the flexibility to position it where I want it.
And that's where the camera tool comes in.
So to use the camera tool,
select the cells that you actually want to get a picture of,
then come up to your camera tool and click on it.
You'll see that dash line appears.
It kind of indicates that something's being copied.
The next step, is to go to where we want to paste it.
So am going to come back to my dash,
and click somewhere near where I want it to go.
Now if I click away for a moment,
you see the camera tool puts this border around it that can easily be removed.
Just come up to picture tools and click, reset picture.
And now if I'm tuck the listen here,
maybe move down a little bit,
you can resize it or do whatever you need to do with it.
But unfortunately because of the width of my cell,
it is now covering part of my chart.
So one more little adjustment,
I'm going to come to the send backwards,
and I'm just going to send that to the back.
Let me just move my chart up a tiny little bit,
and our dashboard is now really starting to take shape.
So, we've seen a quick introduction to working with charts,
but in the next video,
we're going to get really creative. So, keep watching.
Explore nosso catálogo
Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.
O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.
© 2018 Coursera Inc. Todos os direitos reservados.
