Database Setup for Pivot Tables

Database Setup for Pivot Tables

Before you can create a Pivot Table in Excel you need to understand the format that Excel likes the data to come in. The rules for database setup for Pivot Tables are very specific.

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.