Required data format for Pivot tables
In order for a pivot table to work the data it receives MUST be in a certain format. It is important to understand the required data format for Pivot tables as it can be very frustrating if the Pivot Tables doesn’t recognise your data and refuses to switch on.
Ideal data sets should adhere to the following rules
Each column to be included in the pivot table must have a label
- Make sure all your columns are labelled.
- Give them descriptive names.
- Remove any merged cells in the header column as this can hide columns with no labels.
- If you genuinely have a blank column and for whatever reason you want to keep it, label is Blank1 and the next one Blank2. You can always go back and change it later.
Each label must be unique
- Each column labels must be unique (otherwise how does Excel know what you are referring to?
- Furthermore label should be descriptive enough that you will know what it means without having to look at the data.
- This is not important for Excel. If you want to call is Date1, Date2, Date3, Excel will work with it. It just will be hard for you to remember that Date1 is order date, Date2 is delivery date and Date3 is invoice date. It should also be in only one cell.
The dataset should not contain any subtotals or totals.
The pivot tables will be doing all the subtotalling you will ever need and will only get confused by the additional totals, often resulting in the duplication of values.
Remove all subtotals and totals from the dataset. Depending on the size of the data you may be able to work through it line by line deleting the unwanted rows.
If the data is bigger, consider using the Data Autofilter to quickly delete all unwanted rows of data. You can see how to do this in our Data Cleanup Course
Excel should be able to recognise the format of the data especially dates.
Occasionally you get a situation where you receive information that to the human eye is clearly a date but Excel doesn’t recognize it e.g. US format dates on a South African machine, or the date shown as 20091031.
This will result in sorting of the dates or numbers not being correct, or the Pivot Tables will not allow you to group the dates.
In this case you can use the CONVERT TEXT to COLUMNs tool to tell excel to read the cells as dates. To see how to do this look at the Data Cleanup Course
Each row should contain all the information that relates to that record.
You shouldn’t need to look at the row above or below to understand a row. Excel needs all the information relating to a row to be on that row. Occasionally you receive data in the following format.
This is not Pivot Table friendly and we need to ‘fill in’ the blanks with the relevant information e.g. the second data row should have London as the city and Ben as the salesperson as the sales relate to him. Ideally we need to get it to look like this
To see how to easily fill in the gaps look at our Data Cleanup Course
The data is captured in rows and not in columns.
What does this mean? A pivot table works better if each row contains only one record e.g. in the correct version below the sales for each month is recorded on a single row. In the incorrect version a row contains 2 months of sales data.
it would be preferable if it appears like this
Unfortunately this is a bit of a headache. Hopefully you can pull the information into your spreadsheet in a different format. If not you will need to manually go through the process of getting the information into columns. In this example you would need to:
- Create a new column and call it date
- Copy the data under sales person code and paste it below itself (to create an exact copy of itself). You will need to do this as many times as the number of columns you have.
- Label the first 4 cells in month as the first month and the second 4 cells as the second month
- Copy and paste the data under the months into the appropriate cells.
- Depending on the number of months of data you may want to consider a macro to do this for you.