Pivot tables seem to be one of those features in Excel that people struggle with. It can be a number of factors that cause the problem but once a few key areas are understood really sophisticated analysis of data can be performed. Get these key things correct and It will enable you to go from this:
in a few easy steps.
Here are the 3 key things that often cause the problems.
1: Data is in the wrong format
If your data is not set out with headings in the top row of the data (not necessarily the top row of the spreadsheet) then a Pivot table will not work. There must be a heading for each column even if you are not planning to analyse that column of data. The information must be clean. By that I mean no inconsistent spellings or spaces after data that we cannot see but the computer can. This will result in data that should be grouped together being separated. The importance of understanding “Rubbish in, Rubbish out” cannot be emphasised enough here. Also, dates are often entered inconsistently with Excel sometimes viewing what we think is a date as a number or text. All of these can give strange results.
2: Not knowing what you want to get out of the data
Computers are amazing but if you don’t know what you want to get out of the data how will Excel? Well, that’s not quite true. In the 2016 version of Excel there is the option of suggested pivot tables which cleverly uses the repetition in certain fields to come up with some ideas. These are not the only options that may work for you with your data but can at times give a good starting point if you are not sure what to analyse. Think carefully about what information is going to be useful to you before starting to create the pivot table.
3: Not understanding the 4 boxes on the pivot table pane and how it affects the analysis depending on what you put where
The 4 boxes available to us are filters, rows, columns and values. Fields (based on the column headings in the original data) can be dragged from the top of the pane to the boxes below. Normally values, as it suggests, contains a numerical field which will result in a sum of the data being produced. If a non-numerical field is put in then it will do a count of the data. The rows and columns are used to group the data. Normally put the field with the most unique entries into the rows, purely because this is easier to read!! Finally the filter field filters out specific data, so rather than analysing everything we can analyse a subset of the data. Some of these boxes can be left blank (you always need a value and either a row or a column). All the boxes can have more than 1 field.
Getting these 3 areas right should help you produce something quickly and easily with huge number crunching capability with minimal effort that can quickly be updated if the data changes.