Format percentage with optional decimal places in Excel

Question

I have some cells in Excel with decimal places in, which I want to format as percentages. However, I only want decimal places to appear if the decimals are non-zero. For example, I have many cells which contain numbers such as:

0.09

0.195

0.876

0.4

I would like these to be displayed as percentages in the form:

9%

19.5%

87.6%

40%

Any solutions would be greatly appreciated.

Answer 1

Format your column as percentage with your desired number of decimal places. Then, to eliminate decimal places for those percentages that have only two significant numbers after the decimal point, use conditional format:

Rule Formula: (substitute your range to format for C1, or copy/paste the formatting from the cell you initially format.

=ABS(MOD(C1,0.01)) < 0.0000000001

And select Format ► Number ► Percent with zero decimal places

The comparison in the MOD formula above is to account for the occasional rounding errors in double precision math used by Excel. You may want to make that number smaller depending on the range of your data.