Show Decimal Points if needed [Quick Tip]

Sometimes you want to turnoff decimal points if the value after point is 0. Mireya, Chandoo.org member had one such situation. She writes:

I am a complete beginner in excel, how can I keep the zeros when I am working with decimals and remove them when are not required, ie

Thanks for your kind help.

Easy way: Use General Formatting

The default cell formatting in Excel is General. When you set a cell’s formatting to General, you are telling Excel,

Don’t bother me. Just figure it out.

And being a good Samaritan, Excel shows decimal point if there is something after it, else omits it.

See the demo aside to understand this.

What if your numbers are results of a calculation?

It doesn’t matter. General formatting takes good care of the cells. It shows and hides decimal point depending on the result of your formulas.

What if you want something fancy like accounting format, but turn off decimal values

Now you are talking. The General Formatting option shows numbers as typed (or calculated). So 124578395 would look like 124578395 instead of $ 12,45,78,395.

So how do you show $1,245 and $1,246.34?

Aside: You should always show decimal points if some values have them and others don’t. The below technique is useful when data is a result of calculation. For example: In a dynamic KPI report, for certain KPIs you may want to show decimal points, and omit for others.

To show decimal point if there is something after it

Just follow below steps:

1. Select the cell(s) where you want this formatting.
2. Go to Conditional Formatting > New rule from home ribbon.
3. Select rule type as “Use a formula…”
4. Check if there is a value after decimal point using a formula like =Mod(A1,1)>0
5. Click the format button
6. Go to “Number” tab and Apply formatting with 2 decimal places.
7. Click OK & You are done!

Now, if the cell has a decimal value, it shows, otherwise the decimal point is omitted.

Related: Conditional formatting Basics

Do you deal with such situations when formatting numbers?

Often when making reports (or dashboards), I have a cell where any data can go, based on user selection. In such cases, I use conditional formatting to define how it looks based on the data. Sometimes, I also use TEXT formula to format the data. This is more suitable when data is displayed in a text box rather than a cell.

What about you? Do you face situations like this? How often you rely on General formatting? Please share your experience and tips using comments.

More on Number formatting in Excel

Understanding how Excel formats numbers (and other values) can save you lots of time when you are designing dashboards, reports or workbooks that need to presented. Check out below articles to get few more tips.

• Introduction to number formatting in Excel and 10 tips
• Preserving leading zeros in a number using formatting
• Display decimals only if the value is less than 1
• How to hide “0″ in chart axis labels?
• How to hide cell’s contents using format codes?
• Adding colors to your chart labels using custom format
• Showing Indian Currency Format in Excel [and more on this]
• Develop & understand custom number formats
• Chart Axis formatting – Part 1 & Part 2

Excel Links – Going to Togo Edition Formula Forensics No. 028 – It’s Just a Jump to the Left

Your email address is safe with us. Our policies

Spread some love, It makes you awesome! Tweet Written by Chandoo Tags: custom cell formatting, Custom Number Format, Learn Excel, Microsoft Excel Conditional Formatting, number formatting, quick tip, screencasts Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question