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: Dashboard Design
Loading...
From the course by Macquarie University
Excel Skills for Business: Advanced
144 оценки
From the lesson
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.
Meet the Instructors
Dr Yvonne BreyerDirector, Learning and Teaching
Faculty of Business and Economics
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.
So it's starting to take shape,
but I now need to segment this bottom section.
And while I could obviously use a column,
the problem is once I start having different width columns,
if I then needed to put some calculations or something in it,
that could cause problems.
So my personal experience,
I'd suggest keep your columns even,
and rather use shapes to create any additional segmentation.
How would we do this? Well, we come to our Insert tab,
we come to Shapes, and we just need a line.
I'm going to click on Lines, and I wanted a segment about here,
just on the edge of J.
But when you draw a line,
it's sometimes tricky to get a completely straight line.
So, we're going to hold our Shift key down as we draw it,
and that will force it to be a completely straight horizontal line.
Once drawn, you can add some formatting to it.
So I'm going to make it black,
and I'm going to come to shape outline and change the weight to four and a half points.
And then, to ensure that these separations match as well,
I'm going to highlight row 15 and row five,
come to my Format,
Row Height, and make that 4.5 as well.
So that is now starting to take shape.
I would like to also include some placeholders to
show where my main metrics are going to go.
And, again, shapes are ideal for this.
So I'm going to click Insert, Shapes.
This time, I'm going to go for a rounded rectangle,
and I'm going to make sure I position that nicely
around the middle of column C.
If you want to be quite precise, and I recommend this,
you can also use the height and width dimensions in
the drawing tools to get exactly the correct dimension for your shape.
And I'd like mine to be 2.5 by four centimeters,
and I've just tabbed between them.
Now, I have my shape. I can add a little formatting to it if I want to.
So if I wanted a little bit of a drop shadow or a fade,
you can do that quite easily.
And rather than going and drawing the other four separately,
I can now just copy it.
And Excel allows you to duplicate a shape,
so making sure it's selected,
just hold your Control key down and press D four times.
And now, I have five identical shapes.
Rather than spending ages organizing them though,
I'm going to grab the last one I created and make sure
it's positioned fairly centrally over column O,
and then I want to select all of them.
Now, you could use your Control or your Shift key to do this.
But an even quicker way if you're working with lots of shapes,
come to Find and Edit,
choose Select Objects, and draw a nice big marquee around all the shapes.
Now, I'm going to use Excel's drawing tools to align these shapes and distribute them.
So I'm going to choose Align, and I'm going to Align Middle,
and then I'm going to choose Distribute Horizontally.
And those shapes are now completely aligned and nicely, evenly spaced.
And those aren't the only shapes you can use.
There's actually a fantastic selection.
So coming back to shapes,
for example, you can use little flashes or speech bubbles.
And you don't want it to be too silly,
but let's have a look at a speech bubble, these are quite good.
So I can draw in my speech bubble and then,
you may need to turn off the Select Shapes.
Go and choose what size I need it to be.
And, again, you have some great formatting options.
So if I just wanted an outside edge, I could do that.
In this case, I don't want a Shape Fill,
so I'm going to choose No Fill,
and I'm going to change my outline to be a little weightier.
Here we go, six points, make it nice and big.
And, of course, I would now duplicate and align those shapes accordingly.
You can also add text labels.
So, I could go and label in the cells,
but the problem with doing that is,
it doesn't give me much flexibility in terms of where I put those labels.
So another idea is to use text boxes.
And again, this can be found behind the Shapes.
So it's the little A in the text box,
and by popping in a text box over here,
so this is going to be my Average Calls,
and then again, come and change it so that there's no fill,
no outline, and my text is going to be white and size 12.
So that is how you can go about laying out your dashboard.
And that's a good idea even before you start getting into the calculations.
But in the next video,
we're going to look at how we can now prepare the data to populate our dashboard.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
