Customer format millions and thousands

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
Hi All,


I have cells, where the value can can vary from the millions to the tens of thousands.

Do you know of any custome format options, if the value is say 5,100,000 we can show this as 5.1M and if the value is say 320,500 we can show it as 320.5T.

Values are in a dynamic chart, so a user can select a region and the values update, for that particular region. The issue is where some regions can go into the millions, where others will only go into the hundreds of thousand and possibly tens of thousands

These values go into a dynamic chart and showing the value as 5,000,000 is taking up too much real estate. If all the values were in the million, this would not be a problem and I could use s aimpel customer format, however when the values vary as mentioned above, I'm stumped.

Any help/advice appreciated.

Cheers
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
You could use Conditional Formatting with the Custom formats of [#,##0 ] for numbers less than 1,000 (note spaces after the last zero to maintain alignment. [#, "k"] for Thousands and [#,, "m"] for Millions excluding the square brackets.

HOLD on that - need to check something
 
Last edited:

hippiehacker

Well-known Member
Joined
Aug 2, 2011
Messages
1,911
this should display 5.100.000 as 5.1M
#,#.."M"

this should display 320.500 as 320,5K
#.##0,0."K"
 

hippiehacker

Well-known Member
Joined
Aug 2, 2011
Messages
1,911
this should display 5.100.000 as 5.1M
#,#.."M"

this should display 320.500 as 320,5K
#.##0,0."K"
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210

ADVERTISEMENT

Try again as my last formats did not allow for decimal places eg 5.1 m

Use Conditional Formatting with the Custom formats of [#,##0 ] for numbers less than 1,000 (note you can use spaces after the last zero to maintain alignment if deemed necessary. [#.00, "k"] for Thousands and [#.00,, "m"] for Millions excluding the square brackets.

These options round the reduced number to 2 decimal places. You could use #.0,, "m" if only 1 decimal place required or #.###,, "m" to allow flexible decimals.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
Give this Custom Cell Format a try...

[<1000000]0.0,"T";0.0,,"M"
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210

ADVERTISEMENT

Give this Custom Cell Format a try...

[<1000000]0.0,"T";0.0,,"M"

I like that better - didn't know you could include conditions in formats

Could add a further one for <1,000

[<1000]#;[<1000000]#.##,"T";#.##,,"M"
 

ErinB007

New Member
Joined
Mar 5, 2016
Messages
1
Is there a way to do the opposite? For instance, if I have data as 1.5K, but I want it to read 1,500.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,392
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Unfortunately, I don't think custom cell formatting can convert text to numbers, so, for your example of 1.5K, you can use:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">1.5K</td><td style="text-align: right;;">1,500.00</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,LEN(<font color="Red">A1</font>)-1</font>)*1000</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,701
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top