Apply Two Different Number Formats to a Single Field (Excel Pivot Tables)

There comes a time in every Excel Power User’s life when they must deal with mixed content in a single field.  Examine the following table:

image

The Value field (column E) contains a mixture of sale amounts (values as decimals) and discount rates (percentages.)  If a pivot table is constructed using this data, it can take the form of the image below.

image

Because we wish to have a pivot table with data that is easy to read, we apply a currency style to the values.  Unfortunately, Excel applies the currency style to both the sale amount and the discount rate.

image

This is because Excel will only allow you to apply a single number style to a single pivot table field.

If we try to apply the percent style to the discount rate we produce the same issue but with a different style.

image

The solution…

CUSTOM NUMBER FORMATTING

When applying a number format to the values, select the CUSTOM category and enter the following information:

[>=2]$#,##0.00;[<2]0%

image

This will format any number greater than or equal to 2 with a currency style, and any number less than 2 with a percent style.

image

This custom number format relies on the fact that your smallest sale will not be less than or equal to 2, and your largest rate will not be equal to or greater than 2.  You may have to fine tune these values in the custom number format if your data does fall within this defined range of 2.