Annoyances With Pivot Tables
Dealing with these annoyances
Are you fairly new to Pivot Tables in Excel? If you are, you may have had difficulties to make them look clear and professional looking. This post will list 7 ‘annoyances’ and a few important tips to ensure your Pivot Table look clean quickly.
Objective
This post discuss the advantages of using Excel Tables as the basis for all your Pivot Tables. Also we will discuss a few Pivot Table options you can tweak to make them clearer for your audience. To fully appreciate this post you should be familiar with creating basic Pivot Tables.
Excel Tables
To create a new Pivot Table, you need to have a list (some will call this a database) of data. One problem you may face with a list is that if someone adds new rows below, the Pivot Table will not know that. You need to verify this yourself using the Change Data Source under Analyze tab (Option in 2010) then scroll down to see if new rows were added. If yes, the you need to adjust the selection manually (Under Analyze > Change Data Source).
However, if you convert your simple list into a Table then you’ll never have to worry about new rows being added to your data because your Excel Table will automatically grow as new rows are added. To convert a list into an Excel Table click anywhere in your list and hit Ctrl+T then hit Enter. Excel will add the Table Tools contextual tab containing options.
Another critical option is to fill the Table Name at the far left. Always type a name for each table (no spaces and cannot start with a number but underscore is great).
When you move to the bottom of an Excel Table you notice a small blue marker at the bottom right corner. This marker indicates that this row in the last row in your table. See screenshot below.
To add a new row start typing in the first column immediately below the the last row and hit Tab (not Enter) to move to the next cell. As long as you hit Tab the active cell will never leave the boundary of the table and you can add as many rows as needed. The table will grow automatically.
If you notice that the marker is not immediately below the last row of the table, you need to drag this blue marker using the mouse below the last row of data. This would happen if a user selects only the data in the last row and hits Delete.
Pivot Tables
In this section we discuss some annoyances (not bugs) that you should know regarding the appearance of newly created Pivot Tables.
- Blank cells – When your pivot table uses various combinations of fields with numeric data you will invariably note a number of blank cells. These blank cells are there because the is no combination of two of three fields. It would be preferable to see a zero in such cells. To have all blank cells display a 0 follow these steps:
- Right-click in any cell in the numeric area of the pivot table and select Pivot Table Options. In the second half of the dialog box find the option For empty cells show and type a 0 in the text box. Hit Enter.
- Format all numbers – When you right-click a numeric value you have the following two choices: Format Cells and Number Format. Selecting Format Cells will allow you to format the selected cells only. Select Number Format to format all numbers in your pivot table.
- An interesting use of Format Cells is if you want most numbers to use a standard number format but you want a currency format only on the subtotals on grand total only.
- Subtotal above – As soon as you have more than one field in the Rows area, the pivot table will create a subtotal for every value of the first field in the Rows area.
- To have the Subtotals below the group, click the Design ribbon tab and at the far left select the the Subtotals drop down list and choose Show all Subtotals at Bottom of Group. If you want, you can also disable the subtotal all together.
Note that by moving the Subtotals below, will add a new row for every group. In a very large Pivot Table you could end up with a much longer report.
- To have the Subtotals below the group, click the Design ribbon tab and at the far left select the the Subtotals drop down list and choose Show all Subtotals at Bottom of Group. If you want, you can also disable the subtotal all together.
- Row and Column Labels – At the upper left of a pivot table you will see the entries Row Labels and Column Labels.The drop down arrow next to each allow you to filter your pivot table. This works exactly like the the Filter command.
- You can rename these cells by typing your own title in these two cells. Do not hit Delete since this will display an error message. Simply type the text you want instead of Row & Column Labels.
- You cannot type the name of an existing field. For example if your pivot table has a Region field, you cannot use the name Region. However you can type the name of an existing field followed by a space. This will make your label different because of the space.
- Finding a field in a very large list – Once I saw a user creating a report based on a list having over 60 columns. Since finding a data field in the Pivot Table Field list was tedious, I suggested that he simply type the first few characters in the Search box near the top of the Pivot Table Field List. Simply typing the first 2 or 3 characters will instantly filter the Field List.
- Missing data with some combinations – If you use more than one field in the Row Labels area then it is possible that not all combinations would appear. In the example below, we used the Salesman and the Region field. In the case of Lamarche there is no East region. This is because in the original list there is no row with both Lamarche and East.
- This one is slightly tricky. Although Lamarche did not sell in the East region, it would be nice, for sake of consistency, to have the combination Lamarche – East in with 0 across the row.
- To achieve this, right-click on any value for the field with missing data. In our case, right-click on any region item (for any Sales Reps) and choose Field Settings.
- In the Layout & Print tab enable the option Show items with no data then OK.
- Stop this AutoFit – After you set the width of each column in your pivot table, if you click on Refresh to ensure you have the most updated data (or any command that modifies the Pivot Table, like sorting.) then all columns width change to best fit! The rationale behind this feature is a mystery!
- To stop the pivot table from doing ever again, right-click in the pivot-table and select Pivot Table Options.
- In the Options dialog box look at the bottom for the option Autofit columns width on update and disable it. This Pivot Table will never have its columns automatically adjusted again.
- Report Layout – Not everyone like how Pivot Tables arrange the fields in a report. For example in the report above the Regions are indented under the sale-reps. I regularly hear of users preferring to have the Regions in column B.
- At the left of the Design contextual tab you will find the Layout group. Under Report Layout try the layouts Outline or Tabular. Not everyone prefer these layouts but, as I said, I regularly see users preferring one of these layouts.
- Refreshing the pivot table on opening the workbook – This one is useful. Since we have now decided to use Excel Tables as the basis for our pivot tables, we know that if someone adds/removes rows in the database, the pivot table must be refreshed. Wouldn’t be nice if it could refresh automatically upon opening opening the workbook?
- Right-click any cell inside a Pivot Table then choose Pivot Table Options.
- Select the Data tab. Near the top, enable the option Refresh data when opening the file.
- In the future, when opening your workbook, that Pivot Table will display the most recent data.
- Display other calculations for a field – Many users ignore that you can use a numeric field more than once in the Values field area. Why would one do this anyway, you may ask. Actually, you may want to add the same field four of five times in the Values field area! When you do this, you can configure each instances to display other calculations for that field.
For instance you may want to have the sum of the sales in your pivot table but also the highest and smallest figure for each group (like months for example). You can also have the percentage of each subtotal over the Grand Total and a few others. Below we have the sum of sales per month (the group) and the percentage of each figure over the Grand Total.
- To do something similar drag two instances of a numeric figure in the Value field box. Doing this will display the sum of the figures twice.
- Right-click on the second column of total and from the shortcut menu move over Show Value As then select % of Column Total. There you go!
- Another popular one is to display a running sum of the total sales. From the same menu as above select Running Total In… then select the field on which the cumulative is to be done. In the table above I would choose the Date field.
- If you want to see the count, average, maximum or minimum value, right-click one instance of the field in the Pivot Table and move over the sub-menu Summarize Values By and choose the option you need for your report.
- To re-order the columns in the summary, drag the fields in the Values area up or down.
- Remember to change the names of your column headings.
- Viewing the details for a particular value – This is not an annoyance but you may have asked yourself that question. In the Pivot Table above, suppose you’re curious as to which rows in the original list contribute to the Jun Sales figure.
- Simply double-click on that value and Excel will extract all rows from the Pivot cache into another worksheet. You can safely delete that worksheet once you’re done with your investigation.
- Splitting your Pivot Table into multiple smaller ones – This is one of the best kept secret of Pivot Tables. Let’s go back to our Sales Reps by Region report for a second. The underlying list for that report also has a Year field which is
not used in this report. Wouldn’t is be nice if we could split this Pivot Table in smaller ones based on the Year? So, since the underlying data contains 3 years of data, it would be amazing if we could extract from this report three yearly reports each in their respective sheet. Follow the steps below to do just that.
- Since we want to have a yearly breakdown, drag the Year field in the Filter area of the Pivot Table. See screenshot across.
- In cell A1 you see the name of the field in the Filter area. At the far left of the Analyze tab (Options in previous version of Excel) you will find the Options command.
Click the drop down arrow (not the actual button) of the Options button then in the Show Report Filter Pages list, ensure that the field you want to use to create the report in selected then click OK.
- Done! You now have one worksheet for each year. Each smaller report is a Pivot Table in its own right based on the same data list. If new rows are added in the original list, the main Pivot Table will refresh as well as the yearly reports. All you need to do is select Refresh All in the Analyze tab.
Conclusion
It looks like a lot of steps but if you apply each step to all your pivot tables, you will see that it is pretty straightforward and it becomes easier after a while. It is amazing that Excel 2016 cannot save the options discussed here for all other pivot tables you will create!
Good news!
Starting with MS Office 365 (and MS Office 2019) you can use the Excel options to control the default layout for future Pivot Tables. You will never have to spend 10 minutes or more to change the look of your Pivot Tables.
Go to File > Options then click on Data then click the Edit Default Layout button. Adjust the options for all future Pivot Tables to your liking.
Hope that you appreciated this post. Happy pivoting!
Daniel from ComboProjects