- [Instructor] In this video, we're going to talk
about two different ways to visualize progress.
The first way is going to be
using a conditional format icon set
and the next way is going to be using a pivot chart.
Well, let's go ahead and get started.
The first thing we want to do is actually figure out
how we are doing progress-wise
between our budget and our expenses.
So the first thing we want to do
is just take a quick note of the data.
You'll see there's a column in here called Progress,
so this tells us how far we are along in running our project
with regard to the milestones we've set,
but there's also an additional metric we want to track.
So you'll note we have Progress right here,
but we also have how much of our budget we've set aside
and how much we've already spent,
so that's another form of progress, right?
So we want to create a new column in here.
You can see I right clicked on column H
and I hit Insert New Column.
In this case, I'm going to call this Percent Spent like that
and this is just going to be a really easy equation.
We'll type in equals, I'll select Expenses,
and divide it by the budget.
And so if it comes up as dollar signs for you, that's okay.
Highlight the whole thing and click Percentage.
Okay, so what we have here is the idea
that 51% of the project is done
and 50% of the budget has been spent,
so that's actually pretty good, right?
I think that we would be happy with that.
So one of the ways we can actually visualize progress,
if we're just thinking about progress here,
is we can apply a conditional format to this column.
So I'm going to highlight all the data in column D
and of course, you can use a keyboard for this as well.
I just use my mouse.
And then from on the Home tab,
I'm going to click conditional formatting
and under Icon Sets,
I'm going to use these little pie charts here.
I think actually this is technically
called the Harvey Balls font from,
I think it originated at Booz Allen Hamilton
which I used to work at,
so I think it's very cool that they have this in here.
That was one of the original fonts
to actually visualize this type of stuff.
Okay, so this is a good way to do it.
I mean, one thing that you might consider doing is,
I'm going to right click on this and hit Cut
and then I'm going to right click here
and I'll do Insert Cut Sales.
So I'm just moving this to the front
and then of course, you could decide to yourself
well, I don't really need those percentages.
So you'll go from on the Home tab,
you'll see there's
this little number pop out, click that here.
Under Custom, you can actually just remove the numbers,
replace this general with a semicolon,
and that's just going to remove the numbers.
It doesn't actually remove the values.
It just masks the numbers.
So in a certain sense,
if you really just wanted a quick visual
of how everything was doing,
you could just push that over here,
and then you can actually click on this down arrow
and hit Sort Largest to Smallest
and that's going to actually sort them all.
So you could see the ones that have
the most dunk first and vice versa
and remember these are still numbers behind here.
They're just being masked by this conditional format.
All right, so that's one way to measure progress
if you wanted to do it within the dataset itself.
Another way to do it is with a pivot table.
So having my cursor anywhere inside of here,
I'm going to click on Insert.
I'll click Pivot Table here
and we're going to do a New Worksheet.
So I'm going to zoom out here
and what I'm going to do is I'll drop Project Name into rows,
that's going to gimme the big list of project names,
and then what I'll do is I'm going to drop Progress
into the values over here
and let's see here.
We also want to drop Percent Spent over here.
Okay, so what we want to do is visualize
what the progress was versus the amount spent.
So I'll start in A4, I'll hold Shift.
I'll hit right, right, Control + Shift + Down.
After you've done that Control + Shift + Down
and you're holding Control + Shift,
hit that up key so that you highlight the whole region
but don't include the grand total.
So from here, I'll click Insert.
I'm going to insert a new 2D column chart like this.
So what we want is one set of data
is going to show us our progress
and the other set of data is going to show us our amount spent.
So the way I like to do this
is I'm going to right click on the orange.
That's the amount spent on mine.
I'll click on Format Data Series here.
We're going to make this series overlap 100%
so there are one on top of the other
and then I'm going to go to Design.
And with that percentage spent selected,
I'm going to go click Change Chart Type
and where it says Sum of Percent Spent,
I'm going to change that to a regular line chart like this.
So line chart with markers.
I'll hit Okay and then I'm going to select it one more time.
I'll go to Format.
I'm going to remove the shape outline.
I don't care about that,
but I would like these markers
on here to be a little bit more interesting.
So from here I will click on the paint bucket
and you'll see there's a little marker button here.
I'll click marker options, I'll go to Built-in,
and on this dropdown I'm going to select the line like that
and then I'm just going to make them bigger.
So this is going to give us a sense of comparison.
So we know that something that looks like this here,
where the progress is less than spent, that's good.
But if we spent more than the progress,
well maybe we should look into that project.
So let's see if we can organize this by the amount spent.
I have clicked cell C4.
I'm going to click Sort over here.
Let's do largest to smallest
so we can actually see this on our chart
and I don't really need this,
so I'm going to delete that.
I just clicked the legend, hit Delete.
Now this is a lot of data at once.
Let's see if we can actually do a little bit more with it.
I'm going to click into my pivot table
and I'll go to Insert
and I'll click Slicer,
and in this case, let's slice on some people
so let's do Assigned To like that,
and now from here we can actually drill down
and see how everyone's doing.
So we see over here under Nail It
that we've spent more than the progress.
That's probably not good.
In this case, we've spent a little
bit more than the progress in some of these cases.
Let's see if anyone's winning here.
So Mohammad's not doing so bad.
Looks like Omari is right on schedule with their projects.
Well, now that we've created progress charts
to visualize how our projects are doing,
in the next video,
we're going to talk about in-cell bar charts
which is going to actually help us understand
data at the project record level.