PROFESSOR: Welcome to Highline BI348, class video number 19.
And if you want to download, this workbook,
BI348 Chapter 2 start or the finished file,
click on the link below the video.
Hey, in the last two videos, we got
busy creating a frequency distribution,
cumulative percent frequency distribution, and a histogram
with a cumulative percent line.
And guess what.
We used the data analysis feature in the last video.
And two videos ago, we used formulas
to create our tabular summary and graphical summary.
Now in this video, we want to go back to our data set.
Here it is.
Here's our revenue column, the data
we've been using to create our frequency distribution
and histograms.
But this time, we want to use a pivot table.
And in fact, we want to see that the pivot table for data
analysis, especially on the fly data analysis,
rule over the other two methods.
Now, we will compare and contrast.
Because each method has their advantages and disadvantages.
So for a pivot table, we click in a single cell, Alt N, V.
And I'm putting it on a new sheet, so I hit Enter.
I'm immediately going to come to the sheet,
double-click and call it PT, for pivot table, revenue report.
All right.
So what we did in the last two videos,
and we can go back over to the sales sheet
and remind ourselves.
We went through some steps to figure out what the class
interval was for grouping.
We need an upper and lower limit for our revenue categories
to count.
We determined that the class size should be 200.
The lower end of the first class should be 0,
and our total range was 3,000.
So, if we know those numbers, grouping in a pivot table
and counting the frequency within each one of those
categories is amazingly easy.
We're going to start by dragging Revenue down to Rows.
Now remember, the row area is usually a criteria area.
And it will always create a unique list.
So that's a unique list of transaction dollar amounts.
But we need to group this, right?
So you simply come over, right-click Group.
Now this Group dialog box will always
give us the min, the max, and a suggested increment.
We're going to give it 0 as our start,
tab, tab, 3,000 as the max, tab, and 200 as our increment.
And when we click OK, instantly it's
rolled up into categories that will
allow us to count from our revenue column
all of the transactional amounts between 0 and 200.
Now remember, a couple videos ago we talked about the fact
that there's a 200 as an upper limit here,
and 200 as a lower limit.
The 200 can only be included in one of these categories.
It is included in the lower.
The upper limit, when you're grouping in a pivot table,
is not included.
Now, remember when we talked about the frequency function,
and the data analysis feature histogram, it's the opposite.
Because in those two methods for grouping and counting
between an upper and a lower, the upper limit is included.
So remember, pivot tables, the upper limit is not included.
Now, to count I simply drag my Revenue down to Values,
and boom, there it is.
Now we will get a slightly different count
if we have the number 200, the number 400, et cetera.
But in essence, we're looking at frequency distribution
histogram to see the shape of the data.
And in general, having a few extra transactions
counted in different categories would not matter.
And you can actually go look, here this is 16,431.
If you go over here, 1600-- oh, well there is a difference.
And you could actually off to the side if you're inquisitive,
you could even go back to the original data set
and do a COUNTIFS on the number 200.
And the difference between how the pivot table calculated
the frequency and the data analysis feature and frequency
function counted the frequency, the difference
will be exactly equal to a COUNTIFS
of this upper limit of 200.
All right.
So there we go.
Now I want to change the label up here.
Because we talked about the ambiguity of how
a pivot table creates labels.
Earlier we saw how we could do a Replace
and Find to find that dash, and put up to.
But I'm simply going to create a label at the top.
The label I'm going to create is Revenue,
because this is revenue.
And I'm going to let the viewer of this report
know that the upper limit is not included.
Now when I Control enter, I actually want
to come up to the Home ribbon tab, and do Wrap Text.
I could create a new label called Frequency.
Now, this is the frequency.
Those would be the heights of the columns.
But I also what cumulative frequency.
Now I want to show you two extra columns in the pivot table.
I'm going to drag the Revenue down to Values.
And of course it's going to default to counting.
But let's right-click, and the absolutely beautiful Show
Values as in a pivot table.
We have all sorts of amazing calculations.
Remember, in an earlier video we did
percent of column total, which is relative frequency.
But we want to come down here, running total
is the cumulative total.
And percent running total is the percent cumulative total.
I'm going to just start with running total.
And it's going to ask me running total, and what?
Well, we only have one condition or criterion in our row area.
So the revenue is automatic.
I want to cumulatively add up all
of the frequencies for the revenue categories, click OK.
And again, those numbers are going
to be a little bit off compared to our other methods for doing
this calculation.
All right, I'm going to type cumulative frequency, and wrap
text.
Now let's drag revenue down again.
Because our real goal is to get percent cumulative.
So right-click.
Oh, Show Values as, absolutely amazing,
and Percent Running Total in.
He wants to know revenue.
I'm going to click OK.
And look at that.
That is just so quick and easy for calculating
percent cumulative total.
I'm going to call it Percent Cumulative Frequency and wrap
text.
Now, I want to compare and contrast this pivot table
to not only the data analysis feature--
notice we have 1, 2, 3-- but also back over
on our sales data, I want to remind ourselves
what we did for formulas.
We used the frequency function.
Notice, here's our tabular summary.
We were allowed to go frequency, percent frequency, cumulative,
and percent cumulative.
But when we created our chart, we
were allowed to select just a few of the columns,
and graphically display it with our chart.
That's not going to be the case with a pivot table.
If you have this column here and make a chart,
it will be in the chart.
So just as a test, let's come over
and I want to do just column, just
to look at this for a second.
And I'm not so worried about what it looks like.
But I want to try and delete this cumulative frequency.
Because I don't want it.
If I select the columns and hit the Delete key,
nothing happens.
No problem.
I want to go right-click, right-click, select data.
And now I want to go cumulative frequency and remove,
but the pivot table will not let you do it.
Click OK.
Hit Delete key on the chart.
It's actually not a problem.
Because if you wanted really to create this,
you could create another pivot table.
But when you're creating a pivot table
as the source for a chart, all columns must be used.
So I'm going to drag this one off.
And now I can make my chart.
Click in a single cell, insert.
And we used the combo chart before.
But I do want to show you that it's
possible to create a line in a column
if you have a column chart to start with.
I'm going to get rid of the field list.
Come over to the Field buttons, right-click
Hide all Field Buttons.
Click on the Legend, Control 1 to open up my Task pane.
And I'm going to say, please show it at the top.
Click on the Columns, Control 1, and there we go.
I can change the gap width.
But watch what happens.
Huh?
What's that?
Well, there's a column when I created the column,
it assumed that I wanted the little teeny,
all of these numbers less than the number 1 as columns.
But of course that that's not going to work.
So we have to actually internally change
the chart type.
And I need to select those columns,
and it may be really hard, but I can use my arrow keys
internally inside the chart.
The arrow keys will move through the different elements.
Notice, now I have them selected.
If that doesn't work, you go up to Format, and the dropdown
and current selection, you can choose
any element in the chart.
We want Percent Cumulative.
Now I can go up to Design, Change Chart Type.
And this dialogue box in 2013 or later is absolutely amazing.
Because we have to do two things.
I need to change this series of numbers
to a different chart type.
And I'm going to select Line With Markers.
And I need to put it on the secondary axis.
So it actually will be considered percentages.
Because right now both of these series of numbers
are going off the same vertical axis here,
which are numbers up to like 18,000.
So now when I click Secondary Axis,
it totally knows to show it as a percentage.
Click OK.
I'm going to change the colors of the column a little bit.
Come over here to the bucket, solid fill,
and I think I'm going to choose that blue right there.
Scroll down for Borders.
I'm going to select Black.
This is the art side.
This is personal preference.
I kind of like to make sure that the actual columns are
emphasized with a dark black outline.
Let's come up and add an axis title.
I'm going to point to this little arrow.
And I want to add a label for our revenue,
primary horizontal is fine.
And I'm going to click an equal sign to shoot me up
to the Formula bar.
Click on our label for our revenue category, and Enter.
Revenue Upper not Included.
That's looking pretty amazing here.
But remember, I want to show you why the pivot table rules
over these other methods.
There are some drawbacks.
Right?
We have an upper limit that's not included,
which in general doesn't particularly matter
for the shape of the data.
We have the fact that all the columns in the pivot table
will show up in our chart.
But let me close this here.
And really what I want to do is go back over to the sales data,
and think about our data set.
The histogram and frequency distribution
is helping us to reveal the pattern.
But notice one of our columns is type of transaction.
Wholesale means we're selling to a retailer who's
going to resell them.
And in general, they're going to be buying higher quantities,
and the revenue amount is going to be a lot bigger.
And then retail, we're selling to individuals, usually
for very small numbers.
So, I actually would like to add this as a criteria
to our report.
And in a pivot table, the best way to do that
is the amazing Analyze, and up here Slicer.
So this is a filter that will filter the entire table
and chart.
So Slicer, I'm going to select the field type, and click OK.
I think I'm going to move the chart down a little bit,
and then on the Slicer, drag this up, scoot it over.
You can add some colors if you want.
I think I'm going to select that one right there.
And watch this.
Oh my heavens.
I just filtered the whole thing.
Look at that.
I can analyze the shape of the data, the patterns I
see by filtering the data set.
So it looks like both retail and wholesale tend
to follow a similar pattern.
Right?
Smaller amounts of transactions have a higher frequency.
Well, for the retail it's even more extreme, right?
It makes perfect sense.
When you're an individual you are not
buying a lot of boomerangs or spending tons of money.
But look at this wholesale.
It's a little bit different.
And it kind of makes sense, right?
The smaller category still has the biggest frequencies.
But there's a couple taller columns here.
Now one thing I'd like to see for retail
is it looks like there's maybe something out here.
I'm not quite sure, the count.
So this is a situation where I think
I want to put the frequencies at the top of each column.
Now I can unfilter this, right?
Click on the chart.
I'm going to come over to the plus and say, Data Labels.
Now for a second, it's going to give me
all of those percentages.
I don't want those.
Delete.
But now it's got these at the top.
Click on them.
I'm thinking they're too wide, Control 1
to open up our Task pane.
And it looks like it didn't get smart.
Remember, one of the things we're
going to have to do if we can't find something
is click through these icons.
But under Properties, there it is, Text Direction.
And I'm going to say something like Rotate 270.
Not only that, but I'm thinking I want a comma.
I'm actually going to come over to the pivot table.
Because if I change the number formatting for this tabular
summary, this will also change.
Right-click, not Format Cells, but Number Formatting.
I think I'm going to select Number.
I definitely want a comma.
And I don't want any decimals.
Because this is counting.
Click OK.
And look at that.
There's a comma there, and a comma over here.
Now that's chart junk.
And remember, if I delete this, it collapses everything.
Control Z. So I have to do our trick, Control 1.
I'm going to have to add a number formatting
to this access.
And the trick is I need the numbers there, but not shown.
So I come down to Number.
This is the fourth time we've seen this awesome trick,
semicolon, semicolon, semicolon, four sections
to custom number formatting.
But if I leave it all empty, when I click Add,
it'll show nothing.
Now, I'm going to close this.
And here is our report.
I can select retail, and sure enough, look at that.
131 individuals had transactions for 1,000 to 1,200,
and our wholesale.
Now notice another amazing thing about the pivot table, retail,
it looks like my categories only went up to 1,000 to 1,200.
And you could see the pivot table reflects that over here.
But when I filter the data for wholesale,
I have all of my categories.
And boom, that is what makes a pivot table so amazing
for data analysis.
This is on the fly.
That means we didn't want a static report.
We wanted to be able to click through, change the criteria,
change our filter, and instantly have our tabular
and graphical summary update.
All right.
So the last three videos we talked
about continuous quantitative data--
our variable was revenue; how to create a tabular
summary with frequency, cumulative frequency,
and we did a couple other calculations with our formulas;
and a histogram and a percent cumulative frequency
line to analyze our data.
All right.
Next video we'll start talking about numerical measures
like mean, median, mode, and standard deviation.
All right.
We'll see you next video.