Tip:
Highlight text to annotate it
X
Before you can create a Pivot Table in Excel you need to understand the format that Excel
likes the data to come in.
So here we have a simple data base related to sales people and some products sold. A
couple of things you need to realize.
The first thing is that Pivot Tables require that every single column has a label in it.
So you’ll see for example that here we don’t have a label and if you try set up a Pivot
Table it will explain that there is a problem with this – you either need to delete these
columns or else what I like to do is just give it a label and in this case I am going
to call it “blank” and if you had another column you would call it Blank 1, Blank 2
etc, because at some stage you may actually need these extra columns.
The second thing to understand is that the column labels have to be unique. Excel is
going to use these to decide what type of reports it will create so having two columns
called Product like this one here and this one here will cause a problem and a Pivot
Table won’t be created. So we need to make sure that they are all unique so I am going
to go here and change the name to be Sales.
Another thing about the column headings is that the Pivot Table cannot have a merged
cell - so you can see here I have got the dates description but it is merged and applies
to those two columns. You cannot have that so you need to unmerge the cells and now we
have this other problem – a column without a header which we need to fix. In this case
and I am going to take these down because these two provide the correct label.
Also it is important that your labels are descriptive so you’ll see here just having
date 1 or date 2 would be problematic. Here we can see we have got an order date and a
delivery date.
With the nature of Pivot Tables and what they do which is take a whole chunk of information
and do various sorting and categorizing on them, it is unnecessary to have these sub-
totals built into a report. The pivot table will do all of this on its own so the data
base must come without the sub- totals. So in this case I am going to go and delete all
of these.
The pivot table also has some special functionality built in to handle dates. But before it can
use this functionality it needs to recognize the data as a date – so if you look here
you’ll see that the order date is represented as a number ( 2009 09 25) but Excel is not
recognizing that as a date. So you need to convert this somehow to be in a date format
that Excel recognizes.
Another requirement of Pivot Tables is that each line must contain all the information
relating to that line. So, for example here, as a human, I can assume that these two headers
apply all the way to here. However Excel does not assume it and it actually needs to be
able to include there, London and Ben (the city and salesperson) so that when it does
its sorting and categorization and summarization it knows what information applies to it. So
ideally we should not have blanks in any of the columns – they should have information
that describes the entire row of information.
So for our Pivot Table to work your data cannot really look like that - it should look like
this. You’ll notice that every column has got a header, the headers are all descriptive,
they are all unique, there are no blanks (all these numbers have been filled in), the dates
are now represented as a date (are recognized as a date), there are no merged cells and
all sub-totals have been removed
Once you are in this format you can get onto actually building the Pivot Table.