|

Excel: Pivot Tables and Custom Numbers

By Bill Jelen
December 1, 2015
5 comments
Excel_web_image

I was conducting seminars for the IMA® Lincoln Trail Council in Nashville and Louisville and was asked the same question each time: “Why won’t custom number formatting applied to a pivot table stay after the pivot table is changed?” This is a common question that has plagued Excel for years. Any formatting that is applied outside of the Field Settings dialog is temporary.

 

In both sessions, I set out to demonstrate the problem. I created a pivot table. I used the Home tab to apply number formatting. I changed the pivot table…and the formatting stayed! This was completely unexpected.

 

In both instances, the person asking the question knew that this didn’t work on their office computer. Even I was convinced that it never worked. A lot of the IMA members in the audience knew that this didn’t work. People shouted out suggestions: “Change the underlying data and refresh the pivot table—certainly that will destroy the formatting.” But no matter what I tried, the number formatting wasn’t changing like we were expecting. I added fields, reshaped the pivot table, and tried other changes, but the number formatting stayed put in every case.

 

Since I was live in front of an audience, I theorized that perhaps Microsoft had pushed out a monthly update that fixed the problem. But later, back at the office, the problem started happening again.

 

NUMBER FORMATTING IN PIVOT TABLES

 

I reached out to the Excel team in Redmond, Wash., for an explanation. It turns out that the difference is incredibly subtle. But if you understand the difference, you can easily apply number formatting to your pivot tables that won’t disappear when you make a change.

 

The classic solution is to select one cell in the value field, go to Field Settings, then Number Format. This will create formatting that always sticks. But it’s painful to do this when you have many value fields in the pivot table and have to apply the format to each one individually.

 

But there’s a way that Excel will automatically apply permanent number formatting to a field. Take a look at the three value columns in Figure 1. In each field, I’ve manually applied Currency format by selecting some cells, pressing Ctrl+1, and selecting Currency, No Decimals. For the Revenue field, I chose all of the customer cells but not the Grand Total. For the COGS column, I chose only one cell. For the Profit column, I chose all customers and the Grand Total cell.

 

Figure 1
Figure 1

 

Next, I added the Year field as an inner row field. The results are shown in Figure 2. The currency format didn’t extend to the whole column in Revenue and COGS, but every cell in the Profit column is formatted correctly as Currency.

 

Figure 2
Figure 2

 

So here’s the quick solution when you have a pivot table with many value fields: Rather than visiting Field Settings for each individual field, you can carefully choose the entire Value Field section of the pivot table, including any Grand Total rows and Grand Total columns. Access the Format Cells dialog through the icons on the Home tab or by pressing Ctrl+1. Apply the formatting. Because you included the Grand Totals in your selection, the formatting will persist after future refreshes of the pivot table.

 

COMMUNICATE YOUR IMPROVEMENT IDEAS

 

I recently spent a few days with the Excel team and learned that they have started monitoring suggestions posted at Excel.UserVoice.com. If you have a great idea for future versions of Excel, post your idea on the site and then ask your coworkers to vote for your idea. Any idea that gets 20 votes will get a personal reply from the Excel team. The more votes an idea gets, the more likely it will be added to a future update to Excel. For an example, check out my post suggesting that you should be allowed to choose that future pivot tables start with Tabular Form instead of Compact Form: http://mrx.cl/pivotidea.

 

THIRD-PARTY SOLUTIONS

 

The custom number format issue is just one of the many annoyances in pivot tables. Who really wants to send a report to a manager with headings that read “Sum of Revenue”? Realistically, even if thousands of UserVoice votes cause a fix in Excel, you still will have to wait a few years for your IT department to upgrade to the new version. That means at least three more annoying years. If you want to solve these problems today, there are third-party add-ins that are free or inexpensive. Check out Pivot Pal at http://mrx.cl/pivotpaljon or Pivot Power Premium at http://mrx.cl/pppdebra.

 

SF SAYS

 

Excel.UserVoice.com is a great way for you to send your requests for Excel features directly to the team that builds Excel.

Bill Jelen is the author of MrExcel XL: 40 Greatest Excel Tips. Send questions for future articles to IMA@MrExcel.com.
5 + Show Comments

5 comments
    John Moore December 4, 2015 AT 5:24 pm

    thank you

    leon rosario December 30, 2015 AT 12:32 am

    THANK YOU IN ADVANCE.

    Excel Roundup 20160111 « Contextures Blog January 11, 2016 AT 12:01 am

    […] Strategic Finance magazine, Bill Jelen shows a simple trick that you can use when applying number formats in a pivot table. When you use Bill's technique, that number format stick, even after you refresh the pivot table. […]

    MF January 11, 2016 AT 10:23 pm

    To make the selection easier (when we have a large pivot table), we may hover the mouse on the label “Sum of profit”, when the cursor changes to a solid black arrow, click on it then the whole value will be selected. To make it work, make sure the “enable selection” is checked under pivot table options

    Michael Fagin August 22, 2017 AT 5:44 pm

    thank you

You may also like