All articles with 'Custom Number Format' Tag

Use NUMBERVALUE() to convert European Number format

Published on Oct 6, 2015 in Excel Howtos, Learn Excel

If you deal with customers or colleagues in Europe, often you may see numbers like this:

  • 1.433.502,50
  • 9.324,00
  • 3,141593

When these numbers are pasted in Excel, they become text, because Excel can’t understand them.

Here is a simple way to convert the European numbers to regular ones.

Use NUMBERVALUE() Function.

Continue »

Custom Number Formats – Colors

Published on Feb 3, 2015 in Excel Howtos, Huis, Posts by Hui, Quick Tip
Custom Number Formats – Colors

Did you know you can use Excel 95-03 Color Names or Color Numbers in your Custom Number Formats in all Excel versions up to an including Excel 2013?

This post will show you how.

Continue »

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 »

Displaying Text Values in Pivot Tables without VBA

Published on May 7, 2012 in Excel Howtos, Huis, Pivot Tables & Charts, Posts by Hui
Displaying Text Values in Pivot Tables without VBA

Pivot tables are a great way of summarising and consolidating data to produce summary reports.

One of the main limitations of Pivot tables is that they don’t natively return Text values.

This post looks at a method to work around this without the use of VBA.

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 »

Custom Number Formats (Multiply & Divide by any Power of 10)

Published on Jan 31, 2012 in Excel Howtos, Huis, Posts by Hui
Custom Number Formats (Multiply & Divide by any Power of 10)

In the past here at Chandoo.org and at many many other sites, people have asked the question
“How can I display a number multiplied or divided by 10, 100, 1000, 1000000 etc, but still have the cell maintain the original number for use in subsequent calculations”.

Typically the answer has been limited to “It can’t be done” or “it can only be done in multiples of 1000”.

This post will show you how you can display numbers whilst Dividing or Multiplying the cells value by any Power of 10 !

Continue »

Formula Forensics. 009 – Pradhishnair’s Chainage Problem

Published on Jan 17, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics. 009 – Pradhishnair’s Chainage Problem

A common Forum Post question and one that Chandoo has written about a few times is, Does my data overlap with another range?

This week Formula Forensics examines Pradhishnair’s Overlapping Chaninage Problem where he wants to know if two values overlap with a range of other values

Continue »

A Technique to Quickly Develop Custom Number Formats

Published on Nov 2, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
A Technique to Quickly Develop Custom Number Formats

This post examines a quick technique for developing Custom Number Formats for Cells, Charts or any other Number location in Excel.

Continue »

Custom Chart Axis Formating – Part 2.

Custom Chart Axis Formating – Part 2.

Last week I introduced the technique of using custom Number Formats for Chart Axis labels.

Today I present a few more alternative layouts including Chart Series Data Label Formats.

Continue »

Selective Chart Axis Formating

Selective Chart Axis Formating

A few weeks back, John asked a post question, “ How can I add a £ to the 80 on a Chart axis and leave the rest of the numbers plain numbers ? ”

Custom Number Formats, That’s How.

Continue »