Welcome back to our course on Excel 2013 Advanced.
In this section we’re going to start to look at pivot tables.
Now it’s a little bit tricky really when we start on pivot tables here because I’m
sure that some of you will have used pivot tables before, at least in a basic form.
On the other hand, understanding some of the basics of pivot tables if you haven’t used
them before is absolutely essential even if what we’re going to do is go into some of
the more advanced aspects of their use.
So what I’m going to do is to start with a relatively straightforward case study with
some fairly straightforward data and develop a pivot table and a pivot chart based on this
straightforward data before we move into some of the more advanced aspects and in particular
some of the aspects that have been introduced in this and other recent versions of Excel.
There are a few features of Microsoft Excel that really are considered to be its greatest
strengths and amongst these are pivot tables, pivot charts, and the associated tools and
techniques.
They’ve been in Excel for several versions now.
By no means they are new and they’ve continually been improved particularly in recent versions,
but the fundamental idea of pivot tables has been around for some time and has been one
of its great strengths for some time.
The first thing to explain to you is not what a pivot table is.
I’ll come back to that in a little while.
I’m going to start by explaining situations in which you would use pivot tables because
that will help to explain not only why you use them but what they are.
We use pivot tables for what we call transactional data.
So that’s records of things that have happened normally in some kind of sequence.
Now that doesn’t necessarily mean that there is some kind of dependency between these items,
but it does mean that they are similar events or similar measurements that have a certain
set of properties in common.
Now the case study we’re going to start with is a very straightforward one and it’s
to do with sales in convenient stores.
And in a number of convenient stores in the U.S.A. we’ve recorded individual sales over
a long period of time.
I’ve actually got a lot of data in the workbook that we’re using for this example, many
thousands of transactions and each transaction is a row on this sheet.
So taking that row, for example, we’ve got a date for the transaction.
We’ve got a branch.
It’s the Denver, Colorado branch.
We’ve got a value, $1.
And the department in which the sale was made.
It’s breads.
Now if I go through all of this data and as I say there are many thousands of lines of
data, you’ll see that I’m dealing with a few branches over a period of time, not
a very long period of time.
But there are hundreds of sales each day and they’re all categorized in exactly the same
way.
So first of all, what we’re dealing with is rows in a worksheet or some worksheets
which each represent some kind of transaction.
It’s not necessarily a sale but just something that happens; some event, something that was
measured.
Now one of the problems you always get with very large amounts of data is finding some
meaningful ways of analyzing that data or presenting that data.
So for example, given this straightforward convenient store sales data how might we want
to analyze it?
Supposing we wanted to show, for instance, how the sales in the stores that we’ve got
varied over a period of time.
Maybe we would plot the total sales for each store for each month in the time period where
we’ve made the measurements or maybe for each week.
Or would we split those sales by department and see if maybe one department in one store
has a much better growth of sales over a period of time than another?
There are various options for plotting what we’ve measured against other things that
we’ve measured.
So are we looking to see how the stores compare with each other?
Are we looking to see how the departments compare with each other?
Are we looking to see how things vary over time?
Now, in order to do that, we want to be able to look at various ways of plotting different
properties of this data against other properties of the data and we’ll want to be able to
switch what we’re plotting round.
We’ll want to be able to, if you think in terms of X and Y axes in a chart, we’ll
want to be able to sometimes put something on the X axis, maybe sometimes put it on the
Y axis, to plot something different against something else.
Now the name pivot table arises because you have the ability to pivot properties of the
data, individual attributes of the measured data, and to pivot them from one axis in a
plot to another axis in a plot.
And that’s originally how the name pivot table came into force for this kind of analysis
and presentation of data.
Now before we create this pivot table, I’ve got a few important points I need to cover.
Pivot tables have been around for some time and you may well have used them in an earlier
version of Excel.
If you have pivot tables created in Excel 2003 they’re really not compatible with
the newer version and if you try to use those in the newer version, the chances are you
won’t get too far.
If you have some data that you’ve created pivot tables from in Excel 2003, your best
bet is to go back to the original data, much like the data that you’ve seen here, transactional
data by row, and really start building a pivot table from scratch in Excel 2013 and usually
in the long run you’ll find that’s quicker than trying to do something with pivot tables
you’ve created in Excel 2003.
With more recent versions of Excel there is a high level of compatibility.
So if you’ve got pivot tables, pivot charts created in Excel 2010, then you should find
that everything is compatible.
Having said that there are a lot of new features, several new features that were introduced
in Excel 2010 and several introduced in Excel 2013 so even if you’ve used those more recent
versions I think it’s still worth going through the sections of this course because
I will be covering many of those new features.
Another thing I should point out now is that in general terms you can actually create pivot
tables and pivot charts using external data.
It doesn’t actually need to be data that’s in the workbook that you’re creating the
table and chart in so you could link to an external data source.
But in this specific first case study we’re just looking at the data on this store data
sheet in the current workbook.
Now unfortunately I also have to go through some of the rules about how this data is presented.
Each transaction needs to be a row, not a column.
So if your data is actually in column form, so for instance, column A is the first transaction,
column B the second transaction, and so on, you need to transpose all of that and get
the data so that a transaction is a row.
You also have situations where it may not be as simple as a row.
So for example, let’s suppose the first row her was year 2010 and then we had some
figures in column D for Q1 and then column E’s Q2, and F is Q3, and so on.
So we’ve got a sort of rectangle array.
That’s also no good.
You need to have transaction per row.
If you get data in the way I’ve just described, you need to split that up and get it into
row by row transaction sequence.
You should also have no blank rows, no blank columns.
Each column should have a heading with a title and the title should be unique.
You don’t want two of these columns to have the same title at the top.
Now as with many of the data sets that you’re going to use, pivot tables on our data set
has got a column here with numeric data.
If in a numeric data column there is no value, do not put a space.
Put in a zero because again if you put a space in that will cause trouble as well.
One other point, if you’re working in compatibility mode, so bear in mind that in compatibility
mode you’re able to deal with Excel workbooks from older versions of Excel and able to save
workbooks for older versions as well, then many of the newer features of pivot tables
in the last three versions, 2007, 2010, 2013 are going to be disabled which you probably
don’t want.
So avoid working in compatibility mode if you possibly can.
So the data that I’ve got here satisfies all of those rules and I can go ahead and
create a pivot table.
In Excel 2013 quite a few new options have been introduced for this.
So I’m going to start with one of the new options, and all you need to do in this case
is to select all of the data for the table.
There is a keyboard shortcut to do that.
Let me just click in there somewhere.
The keyboard shortcut I want is Control and asterisk, select all of the data, and then
you get this little icon here that appears out to the right.
Now the icon there is the Quick Analysis icon.
If I just hover over it, you can see the screen tip Quick Analysis.
Use the quick analysis tool to quickly and easily analyze your data with some of Excel’s
most useful tools such as charts, color coding, and formulas.
So click on the Quick Analysis icon and what it comes up with is this little sort of menu
bar here.
Do you want formatting?
Do you want chart?
Do you want totals, tables, or Sparklines?
Well, I’m going to go for tables on this occasion so click on tables and I’m given
the choice of just creating a table, creating one kind of pivot table.
Note that there’s a preview of that pivot table up above, second kind of pivot table,
and then I have a More option as well.
I’ll come back to More later.
Let’s look at that first pivot table.
If you look at that you can see what it’s actually done.
It’s taken one of the properties of each of the transactions which is the branch name
and what it’s done is to categorize all of the sales according to branch.
So we’ve got Boston and then a total value of the sales, $60,157.14; Chicago, $19,868.21;
Denver, Miami, so on.
Now that is my pivot table or should I say it is one version of a pivot table.
So let’s click on that option and see what happens.
What happens is that Excel 2013 creates a new sheet and the new sheet has this pivot
table on it.
In column A, it’s got the branch.
In column B, it’s got the sum of the values.
And there is a panel on the right where I can now set about controlling what’s actually
shown in that pivot table.
Now we’re going to look at this in much more detail in the next section, but let me
just show you now a little bit about what pivot table means.
Currently in the list of pivot table fields on the right, I’ve only got two fields checked.
I’ve got branch checked and I’ve got value checked and they’re the two that are shown
here.
In fact with the branch I’ve got the names of the individual branches.
In fact there’s a New York branch there which seems to have a very small number of
sales.
I’m not quite sure why that is.
But the value, I’ve got the sum of the values shown in the table.
Now the symbol down here, sum of value, tells you that it is the sum of value that’s shown.
Now let’s suppose I wanted to introduce another field into this table.
Let’s suppose that as well as branch and value I want department.
Well, I check this to include department and what I get now is branch and department both
shown in the same pivot table.
So for Boston, I’ve got a breakdown by department.
For Chicago, I’ve got a breakdown by department and so on.
Now let’s look at this little area in the bottom right here.
We’ve got four quadrants.
We’ve got a filters quadrant, a columns quadrant, a rows quadrant, and a values quadrant.
At the moment in the rows quadrant, we’ve got branch and department.
Let me move department up to columns and see what happens.
What I’ve now got is a breakdown of sales, still for each branch because the branches
are in the rows, but I’ve got a breakdown by department where the departments are represented
by columns.
So not only can I see the total sales, for instance here, for the bottom Boston branch.
Still the same total that I had before but I’ve got a breakdown by department.
In fact not only that, I can even go so far as to filter what I need.
So let’s suppose I disable them all and I just say I want a breakdown for gross sales,
hot teas, and juices, click on OK, and I just get those three columns.
Now in addition to that if I wanted to I could pivot the whole thing.
So I could take department, put it back down there, take branch, put it up there, and you
can see what happens.
I’ve got departments going down there, the three that I filtered on, and then I’ve
got the branches going across there.
So you can see even from that the amazing power and flexibility of pivot tables in Excel
2013.
So that’s the basic principle of pivot tables.
In the next section we’re going to look at this in much more detail and we’re also
going to look at some more of the new features.
I’ll see you then.