How to Use Pivot Tables in Microsoft Excel
What Is a Pivot Table?
I have twenty years of experience working within either 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.
These are interactive tables in Excel that group and summarize large amounts of data in an easy-to-read and concise tabular format that allows for complex analysis. This tool is very powerful as it allows you to sort, hide, and manipulate data quickly and easily. You can also get an instant update to your summarized data with a quick refresh as you amend your information.
How to Use Pivot Tables
Using pivot tables in Excel is far easier than most people realize. This step-by-step guide will take you through the process from setting up the initial data right through to formatting the final table.
Creating the Data
The data will usually be a simple table of information with the headings in the top row and the data in the rows below. There are no limits on how many rows there can be.
Imagine a scenario where you have to summarize the salary data for a small company by organizing elements such as total salary per department and splitting by active and terminated employees.
By collecting all the data and labeling them as Function, Manager, Employee Status (ActiveTerm), Salary, Bonus and Severance, I created a simple table of data listing each element by employee.
Creating the Pivot Table
- Highlight the data range.
- Click in the top left cell (including headings) and hold down the left mouse button. Stretch the range to the bottom right cell of the data. Do not include the totals in the range.
- On the Excel ribbon, choose the Insert menu and click on Pivot Table.
You will be presented with a 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 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 to create a blank pivot table and use the data from the range you selected to populate the field.
You will now see the template (titled 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 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 it. 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 subheading 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 so click on each added element, select Value Field Settings and choose Sum.
- The table also automatically adds these as column subheadings. 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. 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 table now shows only data relating to active. 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 data you can update the table by right clicking anywhere within and selecting Refresh.
Adding New Data
Sometimes, as you are preparing a pivot table, you may find that your information changes or different data is required. In the example below, it has been decided that a new column, Incentive Compensation, should be added to the data.
- Update the data and recalculate accordingly.
- Right click on the 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.
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 document and change accordingly. I changed the column label to manager and the row label to function.
- Updating Subheadings: I’d like to remove "Sum of" from the front of each subheading. 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 that 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 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 Styles. You can now hover over any of the styles and see a preview. When you decide which one you want to use, simply click on it and the format will change.
You have now completed a simple pivot table.