Welcome to Excel Magic Trick number 1,393.
Hey, if you want to download the Excel workbook Excelt Magic
1,393 to 94 so you can follow along, click on the link
below the video.
Hey, in the next two videos--
1393 and 1394-- we're going to see
how to make the calculations Total Revenue,
Percent of Column Total, Percent of Parent Total,
and Percent of Parent Row Total.
Now, these calculations are normally done in a pivot table,
and sometimes these three calculations
are a little bit confusing.
We'll do it in this video on a pivot table,
and next video we'll see how to do it with a formula.
Now I'm going to go over to the sheet 1393.
We have a data set with Date, Product, Manufacturer,
Customer, and Revenue.
This is a proper data set-- field names
at the top, records in rows, empty cells all the way around.
So in order to create a pivot table,
we click in a single cell on the proper data set,
go up to Insert, Pivot Table, or we
can use the keyboard Alt-N-V.
The Create Pivot Table dialog box pops up.
We had a proper data set, so it guessed the correct data set.
I want to put it on this existing.
I click in Location.
I think I'm going to click in G3.
Click OK.
Now, here's our field list.
And we're going to take Manufacturer field
and drag it down to Rows.
Instantly the pivot table went through the entire Manufacturer
column and gave us a unique list.
And this unique list is the parent.
There is one of each company.
But for each company, there can be many products or children.
So I'm going to go over to Pivot Table Field list and drag
Product down below Manufacturer in the Row area.
Now, I'm immediately going to format this.
Row labels is not a good label at the top.
So I'm going to say Design, Report Layout, Show in Tabular.
Now we have our field names at the top,
and we can clearly see what it means to be parent and child.
This is called a parent-child relationship.
There can be only one company, but the company of course
can have many products or children.
Gel Boomerang is the parent.
We have four children or products or boomerangs.
Now, creating the total revenue is easy.
We drag our Revenue field down to the Value area.
I come over, I click in one cell in the Value area.
Right click.
Not Format Cells.
I don't want to format the cells.
I want to format the actual field.
So I click Number Formatting.
I can tell that this is the Number Formatting dialog
box for the actual field, not the cells,
because it only has one tab--
the Number tab.
I'm going to click on Currency.
Two decimals is fine.
Click OK.
So that's easy.
It just adds, given each one of the conditions or criteria,
including when it gets down to the Total row.
It knows to add up all of the Channel Craft product revenue.
So there's our totals.
Now, our next calculation is going
to be Percent of Column Total.
And this is pretty easy.
We're going to take each individual product amount
and manufacturer amount and compare it using division
to the overall total at the bottom of the column.
Now, we're going to drag Revenue down below Sum of Revenues.
Come up to one of the cells in the Values area.
Right click.
And here we have Show Values As.
Now, there's all sorts of amazing calculations here.
The ones we're going to talk about
are Percent of Column Total, Percent of Parent Total,
and Percent of Parent Row Total.
So I'm going to select Percent of Column Total first.
And instantly internally, it took
each one of these individual items
and compared them to the Column Total at the bottom.
This one is also pretty conceptually easy
to understand.
Now I'm going to click in the top cell and type a new title--
Percent of Column Total, and Enter.
Now let's talk about Percent of Parent Total.
Really all this is going to do is as the formula copies down,
it will compare each one of the items for Channel Craft
to the Channel Craft overall total.
So we'll get some percentage, 40% something,
some percentage, 50%, and this'll be 100%.
When it gets down to the next parent,
it'll take each one of the items and compare it
against that parent total.
All right, so I'm going to click inside the pivot table,
come over, grab Revenue and drag it
to Values at the very bottom.
Right click the value area.
Show Values As.
And this is Percent of Parent Total.
Now, it's polite.
It knows that we could have multiple items over here
in the Row area.
It happened to get the right one, Manufacturer.
That's the one, this is the many.
That's the parent, these are the children.
Click OK.
And look at that.
It took each one of the amounts--
1, 2, 3-- and compare them against that total.
Each one of the parent totals, of course, are 100%.
And it does not show anything down at the bottom.
Now I'm going to come up to the top and type Percent of Parent
Total and Enter.
Now let's go over and drag Revenue down one more time.
So there's four different revenues, each
with a different calculation.
Come over and right click, Show Values As, and down
to Percent of Parent Row Total.
And there it is, a bunch of percentages.
But what do they mean?
Well, it took each one of the products, and look at that.
They're exactly like Percent of Parent Total.
And then when it got to the Manufacturer Total,
it looks like it's exactly like the Percentage of Column Total.
So really, when you select it--
I'm going to type the name up here and then change
the column width.
When you select Percent Parent Row Total,
it's a combo of these two.
But why does it say Row instead of Column Total?
Well, because the parent is coming from the row.
And what it is doing is each one of the individual products'
amount is being compared with division to the manufacturer
total.
But when it gets to the total, there is no parent here.
So it's going to jump down and compare it to the actual column
total at the bottom.
So that's why each one of these percentages
are exactly like Percent of Parent Total.
All of these three product totals
are being compared to the manufacturer total.
But when it gets down here, uh-uh.
It's taking the manufacturer total, dividing it
by the column total.
All right, that's a little bit about Percent of Column Total,
Percent of Parent Total, and Percent of Row Total.
Now, when we come back in our next video,
we'll see how to do the same thing with formulas.
All right, we'll see you next video.