How to use Pivot tables in MS Excel
Data in a MS Excel spreadsheet is usually presented in headed columns, i.e. in two dimensions: You read across to find the column you are interested in, and then you read down for the data. You might sort and filter this to find data more quickly.
Sometimes your query might be more complex. For example, you want the information in column B, for a particular range of values within column A, over a particular time period. Using filtering and sorting, this might take quite a while to figure out.
What a pivot table does
When you make a pivot table, you are in effect creating another table using the headings from your original data. This allows for much more complex analysis, while remaining fairly straightforward to view and understand.
Data must be formatted before you can put it into a pivot table. Ensure that there are no gaps, and no empty cells (run “find and replace”, filling all empty cells with “NULL” or something similar). The data must also be “flattened”, meaning all formulae should be removed. This can be achieved by selecting the whole dataset (ctrl + a), copy (ctrl +c), and then right-click and use the paste special function. Paste the data as “values only”.
You should also check for any obvious outliers. Use the A-Z and Z-A sorting functions to check for any obvious errors.
Building the table
In a separate worksheet, go to the toolbar and click “data”, then “pivot table and pivot chart report”. Follow the instructions to create a pivot table. Essentially, you just need to select the whole dataset and create the pivot table in your new worksheet.
Using the table
All the column headers from your dataset should appear in a control box on the right of the screen. You can drag-and-drop these into fields on your table.
The table has four parts. At the top of the page, you can list broad groups, which can be filtered; for example, if you were interested in data for a particular month, you might put the year at the top of the page, and select only the current year. You can do this by clicking the arrow to the right of the heading, and checking the appropriate boxes.
The column and row fields function as you would expect, and with a bit of practice you will find that the data can be combined and presented in a variety of ways.
You can not drag just anything into the data field; for example suppose you had put months in the left column, and revenue across the top. You can’t drag “year” into the data field. “Transactions”, or “Sales”, would make more sense.
If you right-click row or column header in the pivot table, and click “field settings”, you can select how you would like the data presented: As an average, sum, count, etc.Of course there is much more to learn, but the advice in this article should be enough to get you started creating pivot tables of your own, and saving the time you currently spend trawling through spreadsheets!