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:
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.
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.
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.
CUSTOM NUMBER FORMATTING
When applying a number format to the values, select the CUSTOM category and enter the following information:
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.
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.