All articles with 'number formatting' Tag

Show Decimal Points if needed [Quick Tip]

Published on Sep 5, 2012 in Excel Howtos
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.

This is where we can use General formatting. Read on to learn how General formatting works and what to do in extreme cases.

Continue »

Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]

Published on Feb 15, 2012 in Excel Howtos
Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]

Here is a quick tip to add awesome to your Wednesday.

If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?

Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.

Continue »

How to show Indian Currency Format in Excel?

Published on Jul 26, 2010 in Excel Howtos
How to show Indian Currency Format in Excel?

This is a guest post by our excel ninja – Hui. Today we will learn how to format numbers and amounts in Indian currency format. Indian numbers are grouped differently than standard English numbers. English Grouping              123,456,789.01 Indian Grouping               12,34,56,789.01 Quick and easy fix to show numbers in Indian format: A custom number format of: […]

Continue »

The 1000th Post

Published on Dec 21, 2009 in blogging, Excel Howtos
The 1000th Post

It is not everyday that a blog boasts of 1000th post. After blogging for 1999 days ie 5 years 5 months 24 days, finally, this is my thousandth post.

While this is not a real mile stone or anything, I want to use this post to say thanks. Without you, this mile stone means nothing to me. 🙂

Continue »

How to hide “0” in chart axis [quick tip]

Published on Dec 14, 2009 in Charts and Graphs, Excel Howtos
How to hide “0” in chart axis [quick tip]

Have you ever wondered how you can hide that 0 (zero) at axis bottom? Like shown beside. You can use custom cell formatting codes (or custom number formatting codes) to do just that. Just go to axis formatting and from the number tab set the axis number format to something like #,##0;-#,##0;; Read the rest of the post to learn more.

Continue »

How to get tick marks in Excel? [custom cell formatting]

Published on Sep 15, 2009 in Excel Howtos, Learn Excel
How to get tick marks in Excel? [custom cell formatting]

Here is tricky scenario, faced by Basil, our forum member, “I want to have Excel display a wing ding check mark when a user types “y” in a cell. I have been trying to do a substitute formula but putting the symbol in an unused portion of the spreadsheet and calling it to the selected cell but I can’t get it to work. Any thoughts?”

We can use either cell formatting or conditional formatting to achieve this.

Continue »

How to Hide a Cell’s Contents in Excel? [Quick Tip]

Published on Jun 5, 2009 in Excel Howtos, Featured, Learn Excel

Here is a very quick tip if you ever want to hide a cell’s contents in excel

Just use the custom cell formatting (more here) code ;;;

Continue »

Using Credit Card Numbers in Excel

Published on Apr 7, 2009 in Excel Howtos, Learn Excel
Using Credit Card Numbers in Excel

Do you know that it is not so straight forward to use credit card numbers in excel. Yes, excel uses a precision of 15 digits and thus when you enter a credit card number (16 digits) it is converted in to scientific format and the details are lost. Read on to learn the work around.

Continue »

Colors in Chart Labels [Quick Tip]

Published on Jan 29, 2009 in Charts and Graphs, Learn Excel
Colors in Chart Labels [Quick Tip]

A simple label formatting hack can improve the effectiveness of YoY change charts by adding color to differentiate positive vs. negative growth (or mediocre vs. sky rocketing growth rates). See this example:

Continue »

Display decimals only when the number is less than 1 [Excel number formatting tip]

Published on Oct 2, 2008 in Learn Excel
Display decimals only when the number is less than 1 [Excel number formatting tip]

Here is a quick excel number formatting tip. If you ever want to format numbers in such a way that it shows decimal values only if the number is less than 1 you can use conditional custom cell formatting (do not confuse with conditional formatting). Here is an example: In such cases you can use […]

Continue »