Share this content

Conditionally Displaying Decimal Places in Excel: Part 3

Apr 5th 2017
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

Over the years, I’ve been asked how to conditionally use decimal places with numbers. In Part 1 of this series, I used a custom number format to accomplish this. Although the technique is simple, it also limits the tests you can apply.

Part 2 of this series uses the MOD function with conditional formatting to display decimal places only when a number includes a fractional amount. This time I’ll explain how to use the TRUNC function in Excel to conditionally format decimal values for percentages.

The TRUNC function removes any fractional parts of a number without rounding. Conversely the INT function also removes fractional parts of a number, but in addition rounds down the smaller number.

You won’t notice this with numbers greater than zero, TRUNC and INT will both return the same value. However, TRUNC and INT will return different values for numbers that are less than zero, as shown in Figure 1.

TRUNC and INT only work with greater than 1 or less than -1, so the formulas in Figure 1 multiply and divide by 100 to allow the function to perform the calculation, and then restore the amounts back to decimal values.

Trunc 1

Figure 1: TRUNC and INT are similar but return different results for negative numbers.

Our goal will be to display a number such as 0.87 as 87%, while showing a number like 0.8325 as 83.25%. If we use Number section on the Home menu, we get an either/or choice of presenting both numbers as 87% and 83%, respectively, or 87.00% and 83.25%. As with part 2 of this series, the answer lies within Excel’s Conditional Formatting feature.

To test this technique, create a blank worksheet in Excel and then as shown in Figure 2:

  1. Enter a range of decimal values in cells A1:A4, such as 0.87, 0.8325, 0.7252, and 0.91.
  2. Select the cell or cells you would like to format conditionally, in this case cell A1:A4.
  3. Choose Conditional Formatting from the Home menu.
  4. Choose New Rule.

Trunc 2

Figure 2: Conditional Formatting allows you to apply variable formatting to worksheet cells.

The steps continue in Figure 3:

  1. Choose Use a Formula to Determine Which Cells to Format.
  2. Enter the following formula: 

=TRUNC(A1*100)=A1*100

Note: If you click on cell A1 while writing this formula Excel will add dollar signs so that the cell reference becomes $A$1. If so, press the F4 key three times to remove the absolute reference. If you do not, all the cells you selected will derive their formatting based on cell A1.

  1. Click the Format button.
  2. Choose Percentage.
  3. Set the number of decimal places to 0 (zero).
  4. Click OK to close the Format Cells dialog box.
  5. Click OK to close the New Formatting Rule dialog box.

Trunc 3

Figure 3:  The formula shown will prevent trailing zeros from displaying within a percentage, so .87 will appear as 87% instead of 87.00%.

Next add a second conditional formatting rule to show two decimal places for all other numbers. Select cells A1:A4 if necessary, then then carry out the above steps with two exceptions:

  1. Use this formula when you get to step 5
  2. Set the number of decimal places in step 8 to the desired level, typically 1 or 2.

At this point, assuming you specified two decimal places for the second rule, cells A1 through A4 should display 87%, 83.25%, 72.52%, and 91%, respectively, as shown in Figure 4. Note that Conditional Formatting supersedes formatting that you may apply to cells by way of the Number section of Excel’s Home menu or the Format Cells dialog box.

To remove conditional formatting, you can use the Clear Formats command on Excel’s Home menu, or utilize the steps shown in Figure 4:

  1. Choose Conditional Formatting from the Home menu.
  2. Choose Clear Rules.
  3. Choose Clear Rules from Entire Sheet, or if you wish to be more selective, Clear Rules from Selected Cells.

Trunc 4

Figure 4: Conditional Formatting can easily be removed from an entire worksheet or just selected cells.

Related articles:

Conditionally Displaying Decimal Places in Excel: Part 1
Conditionally Displaying Decimal Places in Excel: Part 2

Replies (2)

Please login or register to join the discussion.

avatar
By Gogs206
Nov 3rd 2017 11:28

After figure 3, point 1 when entering 2nd rule, states ‘use this formula when you get to step 5’ then doesn’t list a formula. Could you tell me whic formula to enter here please?

Thanks (0)
Replying to Gogs206:
avatar
By LONE WOLF
Jan 31st 2018 13:33

Gordon Taylor wrote:

After figure 3, point 1 when entering 2nd rule, states ‘use this formula when you get to step 5’ then doesn’t list a formula. Could you tell me whic formula to enter here please?

yes please do tell that second formula..many thanks

Thanks (0)