How to use pivot tables in Microsoft Excel
What is a pivot table?
I have twenty years experience working within a finance or accounting department. I first started working with spreadsheets over twenty years ago and have used them constantly since. One of the more powerful functions of Microsoft Excel is the pivot table.
Pivot Tables in Excel are interactive tables that group and summarize large amounts of data in an easy to read, concise tabular format allowing for complex analysis. Microsoft Excel Pivot Tables are very powerful allowing you to sort, hide and manipulate data quickly and easily. As you amend the pivot table data you are able to quickly refresh the pivot table giving you an instant update to your summarized data.
Have you used Microft Excel Pivot Tables?
Why use pivot tables?
As someone who works in a busy finance department I am often called to analyze data and produce summary information and charts. Pivot Tables within Excel allow me to manipulate large amounts of data and produce very quick summarized tables.
With the functionality of the Excel pivot table I can intuitively manipulate the results, adding columns, sub-totals and performing other analysis without having to re-invent the wheel every time. The pivot table is a very powerful tool that can provide management with timely and accurate information.
How to use Pivot Tables
Using pivot tables in Microsoft Excel is far easier than most people realize. The step by step guide will take you through the process from setting up the initial data right through to formatting the final pivot table.
Creating the data for the pivot table.
The data for a pivot table will usually be a simple table of information with the headings in the top row and the data in the rows below the heading. There are no limits on how many rows can be within the pivot table data. More complex pivot tables can take information from multiple tables but for this article I will only use one simple table.
Scenario: summarize the salary data for a small company summarizing elements such as total salary per department and splitting by active and terminated employees.
- Collecting all the data including Function, Manager, Employee Status (ActiveTerm), Salary, Bonus and Severence I created a simple table of data listing each element by employee.
Creating the pivot table
- Highlight the data range. With the mouse click in the top left cell (including headings), hold down the left mouse button and stretch the range to the bottom right cell of the data - DO NOT INCLUDE THE TOTALS IN THE RANGE
- On the Microsoft Excel ribbon choose the Insert menu and click on Pivot Table
You will be presented with the pivot table dialog box. As you highighted the range prior to clicking on pivot table the Table/Range section is already filled in. You can click on the icon next to the selected range and change the range if you need to.
You can now choose where you want the pivot table to be located - this option will default to New Worksheet but you can choose Existing Worksheet and select a cell within this worksheet if you want to. For the purposes of this example I will choose New Worksheet.
- Press OK
This creates the blank pivot table and uses the data from the range you selected to populate the Pivot Table field list.
You will now see the template for the pivot table (PivotTable1); this is where you will build the report. You can pull fields into the report directly from the field list, or drag the files into the following areas:
- Report Filter: this allows you to filter what appears in the report – for example you might want to filter by Manager and show the data relating to Cook only.
- Column Labels : this allows you to set up the headings in the report – for example you could list each Manager and produce a total by manager.
- Row Labels: this allows you to set up the headings for the rows – for example you could list each Function and collate the date by the Function.
- Values: this controls what information is going to be summarized.
In the simple example below I have created a pivot table that summarizes the salary for each Manager and breaks the totals down by the Function. At this point I have not formatted the table. I did this by simply dragging Manager into the Column Labels box, Function into the Row Labels box and Sum of Salary into the Values box.
What I would actually like is a sub-heading for Salary, Bonus, Severance and Total under each Function.
- Drag Bonus, Severance and Total into the Values box.
- The pivot table defaults to Count of Bonus etc. so click on each added element, select Value Field Settings and choose Sum.
- The pivot table also automatically adds these as column sub-headings. Simply drag the element Values from the Column Labels box into the Row Labels box.
- I’ve decided that I only want to see data for active employees and therefore I will filter the pivot table. Drag the element ActiveTerm into the Report Filter box.
- In cell B1 there is now a drop down arrow – click on this and click on Active – the pivot table now shows only data relating to Active. (Note: if you had multiple entries in ActiveTerm you can use the select multiple items check box in the drop down to choose more than one element to report).
You now have a functioning pivot table. If you go back and change the figures within the pivot table data you can update the pivot table by right clicking anywhere within the table and selecting refresh.
Adding new data to the Pivot Table
Sometimes as you are preparing a pivot table you may find that the criteria change or different data is required. In this example it was decided that a new column Incentive Compensation should be added to the data.
- Update the pivot table data and recalculate accordingly.
- Right Click on the pivot table and press refresh – you will notice that the new field has appeared in the field list.
- Drag Incentive Comp down to the Values section just above the Sum of Total element.
- Click on Incentive Comp in the Values section.
- Select Value Field Settings.
- Select Sum.
You have now added the new field to the pivot table
Formatting the Pivot Table
Now that I have the pivot table with the correct data summarized I want to format it before presenting it:
- Updating headings: Some of the main headings need changing. Simply click in the cell as if it were any Microsoft Excel and change accordingly. I changed the column label to Manager and the Row Label to function.
- Updating sub-headings: I’d like to remove ‘Sum of’ from the front of each sub heading. In the values section, click on each element and select Value Fields Settings. Amend the Custom Name as required – I removed the ‘Sum of’ from all elements. Note – the pivot table will not allow you to amend these values to the same as ‘field names’ – I simply added a space to the end of each. You can also click on the cell to change the text and it would update the whole table.
- Formatting the numbers: select the range of numbers you would like to format by clicking on the left most cell, holding down the mouse button and clicking on the right most cell of the range. Use the standard formatting options of Microsoft Excel to change the format of the numbers in this range.
- Formatting the Pivot Table: click on the pivot table. You will see two additional menus on the main Excel Ribbon. Select Design and click on the bottom arrow next to Pivot Table styles. You can now hover over any of the styles and see a preview of the Pivot Table – when you decide which one you want to use simply click on it and the format of the Pivot Table will change.
You have now completed a simple pivot table. (See first picture in article for final results)