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 new rows were added below the Pivot Table original selection. 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.

However, if you convert your simple list into a Table then you 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. One important one is the Table Name at the far left.

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 the current 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 the 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 all numbers in your pivot table. Select Number Format to format the selected cells only.
    • An interesting use of Number Format 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 tab and at the far left select the the Subtotals drop down list and choose Show all Subtotals at Bottom of Group. You may also want to 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.
  • 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.
  • Missing data with some combination – If you use more than one field in the Row Labels area then it is possible that not all combination would appear. In the example below, we used the Salesman and the Region field. In the case of Lamarche there is no South region. This is because in the original list there is no row with 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 (even for another Sales Rep) 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 if each column in your pivot table, you click on Refresh to ensure you have the right data (or any command that modifies the Pivot Table. Like sorting.) then all the 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.
  • Refreshing the pivot table on opening the workbook – This one is useful. Since we 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 the pivot table then choose Pivot Table Options.
    • In the Data tab in the top-half 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 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 any 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 on the second 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.
  • Viewing the details for a particular value – This is not an annoyance but you may have asked yourself that question. In the Pivot Table shown in the previous step, suppose you’re curious as to which rows of the original list contribute to the Jun Sales figure. 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.

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 cannot save any of the options discussed here for all other pivot tables you will create!

Hope that you appreciated this post. Happy pivoting!

Daniel from ComboProjects

dlamarche

MS Office Trainer for 23 years. I am also an Access developer and I love teaching VBA in Excel.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *