Excel: Pivot Tables and Custom Numbers

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
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
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