Practice Video: Prepare Data

Loading...

En provenance du cours de Macquarie University

Excel Skills for Business: Advanced

248 notes

Macquarie University

Excel Skills for Business: Advanced

248 notes

Cours 4 sur 4 dans Specialization Excel Skills for Business

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.

À partir de la leçon

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.

Practice Video: Dashboard Design9:49

Practice Video: Prepare Data8:42

Practice Video: Construct Dashboard11:05

Practice Video: Creative Charting11:07

Practice Video: Interactive Dashboard9:04

Rencontrer les enseignants

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

Now that Alex has a good idea of the layout and structure of his dashboard,

he's ready to start preparing the data and performing any necessary calculations.

So let's come across and have a look at our data.

This data will be imported every six months.

The first step would generally be to go about cleaning the data,

but we are lucky and then this data is already very

clean and doesn't require further retention.

While it is usually good practice to put calculations on a separate sheet,

sometimes it may be more pragmatic to put a few helper functions in with the data.

And that's what we're going to start with doing first.

We're going to be doing a lot of calculations with the dates particularly,

trying to get metrics for different months.

And to make this a little easier,

we're going to make sure that we've got a little function that returns just the month.

So, we're just going to quickly type in heading month,

and then using the MONTH function,

I'm going to click on call date,

and that will return the month of the call.

And then, I'm going to do the same thing for the year,

except this time, I'm going to use the YEAR function.

Those are both complete, so I'm just going to use my fill handle to copy down.

Because I'm going to be working with a lot of these columns on a regular basis

to make my calculations a lot easier and quicker to create,

I'm going to use named ranges.

So I'm going to press control A, and then,

control shift F3, and I do want to use the top row, so, okay.

What I've done now is name each of these columns.

And you'll see this makes the calculations much simpler.

And finally, to ensure that the data range will automatically adjust

and my calculations still pick up any additional values,

I'm going to put this in a table.

So this is just control T and then, okay.

Now, I don't want such bold formatting so I'm going to simplify that.

And then very importantly,

I'm going to come up to the table name and I'm going to give it

a more user-friendly name and I'm going to call it tbl_calls.

So now, my raw data is prepared.

I want to start doing some calculations.

And I'm going to do these in a new sheet called calcs.

Many dashboards and certainly,

in the case of call centers,

we'll need some benchmarks or target data to compare the results to.

So the first thing Alex has done is entered

the target values established by the business.

For example, they're looking to get a resolution rate of 65 percent.

If the business themselves are going to enter the targets,

we would probably put this on a separate sheet.

But as Alex is putting them in,

we're not going to worry to do that.

Now, coming down to our summary data,

these are some of the main things we need to calculate before we can even get started.

And the first one we're looking at is the start date.

Because this dashboard will be re-used in six months time with completely different data,

we're going to need to calculate this.

And this is very simple, we're just going to use our MIN function and it's our call data.

We've named the column so nice and easy.

We also want to get our total calls.

And this is just going to be the number of rows in our newly created table calls.

Now, average monthly calls is a little trickier,

because we're going to need to get the average of the number of calls per month.

Now, when you're confronted with the more complex calculation like this,

it might be easier to break it down into a few simple calculations,

and that's exactly what we're going to do.

When we get into our monthly section,

we're going to need a breakdown of the metrics by month anyway.

So, we're going to start those calculations here and then we're going to

use our monthly core calculations to get our average monthly calls.

So, first thing we're going to need is our start date.

So I'm going to click in A33, type equals,

and click on our start date,

and then click enter.

We're then going to use our EDATE function so I'm going to type equals

EDATE and we're going to add one month to our start date.

And then, we can copy that down for the following few months till we've got six months

worth of data and there are six months good to go.

It may be handy to have a month name for when we come to do our charts.

So, for this, we're going to use our TEXT function.

Click on our date and we want that in a month format.

So I'm just going to put four M's in there and then tab.

We're also going to need our actual month number,

which we will use to compare with the data in the table.

So I'm just going to click on that month,

and tab, and then, we're also going to need a year.

Just make sure you choose the function not the named range.

And finally, we need to calculate our monthly calls for that month.

Now, because we're only getting six months worth of data,

we won't get any duplicates in the month number

so we don't actually have to worry to check the year as well.

So we're just going to do an equals and we're doing a COUNTIFS,

and the criteria range we're checking is our month column.

And this time, I do want the named range,

and I'm checking to see if it's equal to the month.

So, for July, you can see those were our total calls.

Now, I'm just going to copy those all down,

and we're started to generate the metrics

that we're going to need to show our monthly breakdown.

Now, we can also complete

our average monthly calls so we're just going to come back up here,

type in equals average,

and select our monthly calls.

So we've started putting

some calculations in place so we can generate the values we need.

The next thing is to look at how we can actually get those values

to populate our graphical elements on our dashboard.

So coming back to our dashboard,

we're just going to expose some of these cells temporarily.

So I'm going to select those columns and choose no fill.

Now even though this is going to be hidden data,

for maintenance purposes, it is really important that you still label your cells.

So this first one is going to be calls per

month and the next one's going to be our average on speed.

Now, I don't actually want to put the calculations in here,

I just want to pull the values through so they're easily accessible to the dashboard.

So, I'm going to type in equals,

come back to my calcs,

and click average monthly calls.

You'll notice that I have the calculation for my ASA directly under that,

so I can just drag that down.

And then, I don't actually want any decimal places so I'm just going to remove those.

So I now have the values in the sheet,

but how do I get them into the actual graphics themselves?

Obviously, I could just have a cell and put it in,

but again, that limits our scope in terms of moving things around.

So, instead, I'm going to click on my shape,

come up to my formula bar,

type equals, and click on the cell that contains the value I want.

I can't put our calculation in here but I can put a cell reference.

And when I click enter,

it is now pull that value directly into my shape.

And all I need to do now is add a little bit of formatting.

So, I'm going to make the size 24,

and it's quite important with your sizes,

think about them, and once you've selected, be consistent.

And that is how you can pull your data into your dashboard using graphical elements.

So now our data is prepared.

The next step is to start constructing

our dashboard in earnest and that's what we'll look at in the next video.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play