Welcome to Excel Magic Trick Number 1396.
Hey, if you want to download this Excel workbook
Excel Magic Trick 1396 to 97 so you can follow along,
click on the link below the video.
Hey, we got a great video here.
We got to talk about running total and percent of running
total calculations.
Now, the next three videos--
1396-1398, we're going to talk about how
to do this with pivot tables in this video.
Next video, we'll see how to do it with Excel formulas.
And in the third installment, we'll
see how to do it with DAX formulas.
Now, in our pivot table video, we're
going to have two examples.
And in each, we'll calculate count, running total,
and percent running total.
Now, the synonyms for those are-- frequency means count,
cumulative frequency means running total,
and percent cumulative frequency means percent in running total.
We might see these in statistical reports.
These are the names they use in the pivot table, show
values as feature.
Now, our first example is for quantity,
and we're going to actually have to group
into categories 101 quantity up to 150, 151 up to 200,
and then count how many of the transactions
had a quantity that falls between this lower
and upper limit.
Count.
Then, calculate the cumulative frequency or running total.
That just means 27 is, of course, 27.
But when we get to the second category,
it's the sum of the first two, third, some of the first three.
And then finally, we'll calculate the percent
cumulative frequency.
And this is quite a useful statistic
because now we can say about 42% of the transactions sold
with a quantity of 150 or less.
When we get down to this category right here,
75% of the transactions had a quantity of 250 or less.
Now, each one of these calculations
is based on the quantity.
Not only did we group based on the quantity column,
but we also made our calculations
on the quantity column.
When we come down to our second example,
we're actually going to group all the individual dates.
And notice, the same thing applies for this category
right here as it did for this category.
Up here, we counted between a lower and upper limit.
Here, we're summarizing between April 1, 2017.
And then, the end of the month, April 2017.
So there's a lower and upper limit here.
But then we're going to add from the revenue column.
So here, we're going to group by date
and then summarize based on revenue.
So in both reports, we'll do running total,
percent of running total.
Do the same thing up here.
And in both, we will group using the pivot table grouping
feature.
Let's go over to the sheet 1396PT and see how to do this.
Here's our proper data set field names
at the top, records and rows, empty cells all the way around.
I click in a single cell.
Go up to Insert Pivot Table or I can use the keyboard, Alt, N,
V. Now, we're going to put this on the existing
sheet, the location.
I'll try and select cell G10.
Click OK.
Now, this is what is so amazing about the pivot table feature--
because we can simply take quantity and drag it--
Remember this is a number field.
Usually, we're dragging it down to values.
But as soon as we drag it to rows, of course,
that always gives us a unique list of items from that column.
We simply come over to the column.
Right click.
And use the grouping feature.
Now, it sees the min and the max,
and it suggests an increment.
I'm simply going to highlight that.
Type 50 as the increment.
And then, click OK.
And instantly, we get our lower and upper limit
for each category.
Now, we can come over and we're going to drag quantity.
And watch this.
We already grouped quantity.
But when we drag quantity down here, which is a number field,
normally, it defaults to the sum calculation.
But we've categorized it so it defaults it to count,
which is exactly what we want.
Now, I'm going to click up in this cell and type frequency
and enter.
Not only that, but I'm going to get rid of that row label
by going up to Design, Report Layout, Show in Tabular.
Now, notice the pivot table keeps
changing column width every time I change the pivot table.
So I'm going to right click.
Come down to Pivot Table Options.
And on Layout and Format, uncheck Autofit Column Widths
on Update.
Click OK.
Then, I'm going to highlight all three columns
and universally change the width of all three.
So we have our frequency.
Now we can drag quantity down below.
Frequency in our values area.
Right click.
Show Values As.
And sure enough, look at that, there's Running Total In.
Now, we only have one field on the row,
so quantity is the correct base field.
Click OK.
And there's our running total.
And then, click at the top.
Cumulative frequency and Enter.
I'm going to drag quantity down once again,
the third item down in values.
Right click.
Show Values As.
Percent Running Total In.
Quantity as the base field, click OK.
Click up at the top.
Percent cumulative frequency and Enter.
So we can see 75% there.
Cumulative frequency is 310.
We can say from our transaction set
that 75% of these transactions had quantity of 250 or less.
Now, actually, our next video, we'll
make a chart of this which beautifully
articulates visually this percentage calculation.
But you can make the same statistical statement
by looking at each row in the pivot table.
That was our first example.
Now, we want to create our second example and group
by date.
So I click in a single cell in our data set.
Alt, N, V. I'm going to be risky and put this location
a couple rows below.
Click OK.
Now let's drag Date down to rows.
Instantly, in 2016 Excel, it automatically groups for you.
Now, in earlier versions, you still
have to come over and right click Group.
Not only that, but if you really did
want like daily counts or something,
then, you have to use ungroup in Excel 2016.
I'm going to come over and drag quarters
off and come over and expand.
There's all of our months.
There's all of our months for 2018.
Don't like row labels?
Come up to Design, Report Layout, Show in Tabular.
Now, in this case, we grouped by date.
But now, when we want to add individual revenue,
we simply drag that number field down here
and it defaults to SUM.
Now, watch this.
I want to name this field Revenue.
So I type revenue, and I hit Enter.
It's not going to let me because I have a revenue field already.
So here's the trick-- escape, right at the end type a space
and enter.
Now, I need to add number formatting
to this entire field.
Right click and not format cells,
I want number formatting.
This dialog box is mistitled.
It should say Format Field, but it says Format Cells.
I'm going to click Currency, two decimals is fine.
Click OK.
You can always tell that this is the correct mislabeled Format
Cells dialog box because it has only one tab, the Number tab.
If it had all of them, it would actually
be formatting the cells.
Click OK.
Now, let's do cumulative.
I'm going to drag revenue down below.
Right click.
Show Values As.
Running Total In.
Now, we do have a choice here because we
have two items over in the row.
If I do date, it will give me 100%
when it gets down to the end.
And that's what we want.
So there's the total for the year 2017.
And then down here is the total for 2018.
Click at the top.
Running Total and Enter.
Right click, Number Formatting.
Something like Currency.
Click OK.
Now notice, this field didn't default to Count up here
where we dropped the actual field we grouped, so it assumed
we wanted to count.
Here, this was a different field,
it assumed we wanted to use the SUM calculation.
Now, let's drag revenue one more time down below running total.
Right click.
Show Values As.
Percent Running Total In.
Definitely want date.
We get an individual 100% for each one of the years.
Notice, we didn't have to number format that one either.
So this one's going to be Percent Running Total.
Now, we can say 72% of our total revenues
for the year were accumulated by the last day,
and it's September.
So looks like by the end of June, 47% of our revenue
had accumulated.
And of course, by December 31, 100% of all the years
had been accumulated.
So that's some pretty powerful calculating
with running total and percent running total.
In this report, we saw how to group by year,
and then, do these two calculations up here.
Notice, the columns continue to change because I
forgot to do it down here too.
Right click Pivot Table Options.
Autofit.
Uncheck.
Click OK.
And then, our first report, we grouped by quantity
and did Frequency Cumulative and Percent Cumulative Frequency.
Next video, we'll see how to do this top calculation
with formulas.
Two different types of formulas and the chart
that works for percent cumulative or percent running
total in.
We'll see you next video.