Conditionally Displaying Decimal Places in Excel: Part 3

Spreadsheets and graphs on a desk

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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.


Please login or register to join the discussion.

Jul 23rd 2017 06:34

Conditionally Displaying Decimal Places in Excel: Part 3 and now it will very helpful for Excel users.Thanks for sharing
friendship status

Thanks (0)
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)