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

Posted on January 31st, 2012 in Excel Howtos , Huis , Posts by Hui - 46 comments

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”.

Well thanks to a tip I picked up from Kyle who responded to a post here at Chandoo.org they are all wrong.

It is possible to Multiply or Divide any cell contents by any power of 10 using Custom Number Formats !

That is:

How does this work:

When using custom number format we have two possibilities to modify the display number

  1. Use a Comma to divide by 1000; or
  2. Use a % to Multiply by 100

So using a combination of these any power of 10 can be obtained.

So using the correct combination of , and % can result in any power of 10 multiplier we require.

The problem is that using a % adds a % to the number!

The trick which Kyle added is that adding a Ctrl J to the Custom Number format allows us to hide the % signs on a second row of text, then by adjusting the cell to have word wrap and adjusting the row height the second row is not visible.

The Ctrl J must be added after the ,’s and before the %’s

So using the examples above the table is:

The Ctrl J adds a Carriage Return, chr(10), to the Format String.

Finally after applying the Custom Number Format the Cell must be edited to enable Word Wrap.

Select the Cells with the custom Formats, Ctrl 1, Alignment

 

You can see the hidden % symbols if you increase the Row Height.

 

Combination with Regular Custom Formats

These Custom Number Formats can of course still be combined with regular Custom Number Formats, just make sure that the Ctrl J is inserted before the % signs:

No Loss of the Cells Value

It is also worth noting that the original number is still maintained internally in the cell and that cells dependent on the cells don’t have to adjust for the display value.

 

Multi Line Formats

By extension we can now use this technique to add multiple Lines of Text to a Custom Number Format

 

Downloads

You can download a file containing all the above example here: Download Here

 

Other Links to Custom Number Formats

Here:

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

http://chandoo.org/wp/2011/11/02/a-technique-to-quickly-develop-custom-number-formats/

http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/

http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/

http://chandoo.org/wp/tag/custom-cell-formatting/

Elsewhere

http://www.ozgrid.com/Excel/CustomFormats.htm

http://peltiertech.com/Excel/NumberFormats.html

 

Thanx

Just a quick final Thank You to Kyle for highlighting this Custom Number Format feature/trick last week

I look forward to your comments below:

 

 

 

Your email address is safe with us. Our policies

Written by Hui...
Tags:
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

46 Responses to “Custom Number Formats (Multiply & Divide by any Power of 10)”

  1. Luke M says:

    Okay, that is pretty awesome. Thanks for the tip Kyle & Hui!

  2. Steve Slaman says:

    This gets me closer to a solution for reporting financial in (000’s) but I am still left with the rounding problem when adding these numbers. As an example 1,234,567 using 0,###, I get 1,235 which is great but when I sum 1,234,567 and 1,234,567 in this format 1,235 + 1,235 I get 2,469 instead of 2,470. 2,469 is technically correct, but for those who cannot see the full number the sum appears incorrect. Is there a fix for this?

    • Cameron says:

      Steve, I often have to display numbers in a similar fashion. I have to ask though, is it wise or even necessary to force the display to ‘2,470’? As long as it is understood that some rounding is going on, it should be acceptable that the numbers won’t add up on screen. In fact displaying these incorrectly (outside of the realm of rounding) seems misleading?

      Often, I will include somewhere on the page, prominently enough to be seen but not obtrusive, “In Thou.” or “In Mils.”

      Alternatively you could make the custom format “#,K” which automatically suggests rounding.

      • Steve Slaman says:

        I hear you, but that all numbers tie is absolutely expected at my company. As a former CFO explained to me years ago, if the numbers don’t add up it brings into question the validity of the report (as he chucked my report into the trash). Experience has taught me that he was absolutely right. This can take Sr. Management and Board of Directors off the focus of what the report and presentation is about. So the hours it takes me to tick and tie the reports is well spent (and cheap) compared to the time spent and repercussions up the ladder. I wish we could show the complete numbers in these presentation reports (30 slides per quarter) but it is just too busy on the screen.

        I do put (000’s) at the top left of each report (we report in thousands).

        Thanks for your response!

        • Kyle McGhee says:

          Well, there really is no way to solve it other than rounding to the nearest X. (since that is what Excel is doing with the format, rounding to the nearest 1000)
          Here is a formula, for a total, that rounds each number in A1 to A100 to the nearest 1000 and then sums it up.
          =SUMPRODUCT(ROUND(A1:A100,-3))
          It has the same effect has rounding each individually but it leaves the individual items alone so you can keep accurate percentages.

          • Steve Slaman says:

            You are right, the fact is we are taking away three places in each number by rounding to thousands and holes are naturally created that must be filled or explained. But it is still great to have the discussion. I learn about how others think and put another tool or two in my Excel toolbox.

            I greatly appreciate this!!

    • Kyle McGhee says:

      Steve,

      I have to agree with Cameron. I made a similar post yesterday morning that was lost with the server migration. There is a certain level of displayed inaccuracy that is expected when reporting in 000s or 00s. It is best to leave everything as is, which will provide the most accurate results. As Cameron suggested, stating it is in 000s would be the best approach, and if desired, stating that there may be rounding differences as well. I work in finance/accounting as well and I never round except for check totals (due to the floating point issues).

      Kyle

      • Steve Slaman says:

        I really appreciate your response. You are both correct with expected inaccuracies. In my situation, the inaccuracies are best done in plugs. If I have to round one of the larger numbers (say advertising) up or down 1 (1,000) to get the number to balance it is much cleaner and reduces questions. Of course it also takes more time and makes the process manual.

  3. Kyle McGhee says:

    Try this:

    =(ROUND(A1,-3)+ROUND(A2,-3))

    • Steve Slaman says:

      Thanks for the post. I currently use the round commands (round, roundup and rounddown) but was hoping to get away from that. In a simple or single sum the round command works well but when I start using those rounded sums to get to a bottom line (Profit/Loss) or on a balance sheet, they can veer out of balance from actual final numbers more often than I like to see. It especially gets ugly in % change. I spend a lot of time verifying that everything tics and ties. I’m still going to try this format on my next financial presentation with the rounds to see how well it works.

      Thanks again!!

  4. Another handy technique is to use the text function. For example, to display a date in a format that you want, you can do something like:

    =text( A1, “yyyy-MM-dd” )

    Lots of potential variations there, but it lets you format A1 however you want it, without altering it.

    side note, in some languages, I’ve seen other choices such as “jjjj-MM-dd” so if you care about internationalization, try to make your formatting easy to change by making the format text refer to a single cell.

  5. Jonathan Cohen says:

    Try setting precision as diplayed in the advanced options.

    • Steve Slaman says:

      Thanks. This is a cool new feature to me, unfortunately in this instance the original number is lost to the rounded number (1,234,567 becomes 1,235,000). Great tip for future reference, I’m already thinking of ways I can use it.

      Thanks again!!

  6. Arti K says:

    Excellent tip! Thank you.

  7. SARAN KUMAR says:

    nice.. post

    Regards,
    Saran
    lostinexcel.blogspot.com

  8. Dhakkanz says:

    Steve, this is an excellent tip. But with the % sign used to Multiply, the symbol still shows up in the cells, even with usage. Am I missing anything else?

    • Hui... says:

      @Dhakkanz
      Did you put a Ctrl J in front of the % sign ?

      • Que says:

        The Ctrl J trick works but not very well if the space is limited.  For example, I want to show 9E-12 as 9 (units is in picoseconds).  If I limit the cell width to less than 10 characters then this trick does not work.

        Any other suggestions if the space is limited? 

  9. bill says:

    this is very clever. i like the concept. there is something about the concept that makes me uneasy… i think it is reading this article after having finished the great series of articles on auditing and spreadsheet risk….

  10. bill says:

    i recently stumbled on a software company that was addressing the rounding and or formatting creating incorrect sums (visible) issue. their solution seemed hard to implement and has costs but does work quite well. i think a well written vba program would be more flexible (for solving rounding/formatting issues). here is their link:
    http://www.think-cell.com/products/round/overview.shtml

  11. Sugandh says:

    I was so happy to see such a custom format. I had not imagined that there would be such a simple solution to the problem. Thanks a lot.
    This post will be very useful to Engineers like me who have to do a lot of electrical design calculations in excel. Very often we have to deal with *1000 or /1000 value of current or voltage For ex. millivolts mV, milliampere mA, milliwatt mW.
    With this formatting trick, I can now remove all the *1000 or /1000 I had to add in my formulas to take care of the multiplier factor.
    :-)
    However,
    I have one new problem using this custom format. My Excel uses German standard way of representing numbers i.e. a “comma” to denote decimal point and a “dot or punkt” to denote thousands (‘1000s) placeholder. Alos, eventually the sheets I make will be read on German Excel only.

    Can someone post a German equivalent to the same formatting?

    Thanks again.

  12. Nima says:

    Thanks very much.
    The trick was very usefull.
    Problems will be very simple when they are solved!
    Thanks again.  

  13. […] Microsoft – Custom Number Formatting  > Chandoo – Factor 10 number scaling  > ASAP Utilities – Create a bulleted list  > Daily Dose of Excel – Star Rating […]

  14. […] Microsoft – Custom Number Formatting  > Chandoo – Factor 10 number scaling  > ASAP Utilities – Create a bulleted list  > Daily Dose of Excel – Star Rating […]

  15. Ivan says:

    Hi Thank you for the tutorial. It is very useful and clear.
    And I have a question related to this format.
    Is it possible to multiply by a different number instead of 10?
    I don’t want to create a new column to do the multiplication.

    Let’s say it will automatically multiply 2 when I enter 19 and will show 38 in the cell.

    Thanks for reading and hope you could help me.

  16. Matt says:

    OK so I’m trying to use this to format a cell so that this:

    0.09
    0.07
    -0.14

    Will display as:

    9.0%
    7.0
    (14.0)

    Looking to have the decimal points align but not show the % symbol after the first row. The problem with the above technique is that formating the wrap text will eliminate any placeholders you put in place to help with the alignment formating. Is there any workaround to this?

  17. Matt says:

    Different cells, just gave 3 as an example. Effectively the cell would multiply by 100 but still give the spacing as if a % were there, ie putting a _% effect at the end of the cell

  18. […] Hi, You can do this with custom number formatting. There are limits to what you can do with it, but for your specific example, a format like this might work: 0000" / (4837)" To learn more about custom number formatting see: Create or delete a custom number format – Excel – Office.com Excel Custom Formats: Numbers/Text Formats in Excel Spreadsheets Custom Number Formats (Multiply & Divide by any Power of 10) | Chandoo.org – Learn Microsoft Exc… […]

  19. Suzie says:

    okay idk what i did wrong, maybe my excel is being stupid but its not working :(

    i originally have 52.6625 (it’s in thousands) so it’s really 52,662.50.

    if I put in the *1000 formatting above i get this result:

    52,662.5%%%

    if I try it without the carrots i get:

    52,662.5CtrlJ%%%

    Also if I go back into the custom formatting, it changed it to:
    #,###.#%%%

    I dont know why it put a “/” in between the Ctrl either. Any suggestions as to what I’m doing wrong??

    Thanks!

  20. Suzie says:

    nvm! im dumb and realized you don’t type the control j but actually hit control+j

  21. mathew varghese says:

    Dear Hui,
    I have been reading your posts with awe. Thanks to Chandoos’s work. I need your help. How do I custom format a number to display in lakhs as used in India…..123456789.00 as 12,34,56,789.00 (twelve crores thirty four lakhs fifty six thousand seven hundred eighty nine…..I understand excel will show numbers in thousands only….

    Thanks

  22. Caron says:

    Thanks for this tip. I have two questions:
    1. I need to divide numbers by 1000 but leave no decimals.
    At the moment the format I’m using from your site is # ##0.###.
    However, this is leaving me with a result with a . on the end.
    i.e 1000 becomes 1. instead of just 1 with no point on the end.

    2. My spreadsheets has a number of pivot tables representing a sheet that automatically updates from the accounting database.
    So lots of ways of analyzing sales.
    Is there a way I can set the format once for a value instead of having to format on every sheet?

    All help much appreciated

  23. Brian says:

    Unfortunately it seems this work-around has now been semi-disabled in Excel 2010.

    When you first enter the number format, the application accepts it and everything works fine. But if you save the file and re-open it: excel has modified the number format, and it breaks the functionality.

    Specifically: all the formats that make use of a comma AND percentage sign in the same (sub)-format, excel now DELETES all trailing commas in the saved custom-number-format string. Hence when you reopen the file all formats like “mA” or “mV” are now displayed 1000x their actual value.

    Example:
    Try to apply: 0.0,”mV”%%%
    Excel will strip the comma and save it as: 0.0″mV”%%%
    (which obviously won’t produce the desired effect)

    Funny enough, excel WILL apply the correct number format TEMPORARILY to the open document: but if you re-open the file, POOF! all number formats wrecked.

    (Perhaps older versions always saved the number format wrongly too, and they have changed the way number formats are refreshed upon opening the file. Or they could have purposely disabled this – who knows)

    So sad. This was an almost perfect work-around.
    I would be interested which versions of Excel this still works in.

  24. James says:

    Hi,

    Is there a way to automatically include the 2 digit year into a custom format for invoice numbering …?
    Is there a trick to combine functions (such as today() or year()..) in a given custom format ?
    Thanks

  25. Johnd239 says:

    You could certainly see your skills within the work you write. The world hopes for more passionate writers like you who arent afraid to say how they believe. Always follow your heart. cgaedfkafgde

  26. Johnb136 says:

    I think you have observed some very interesting details , appreciate it for the post. kabddabcdfag

  27. Roshan says:

    Hi,

    Is there a way to add 100 to only the number format? I have a graph which shows the diff from 100, e.g. 89 is -ve 11 from 100 and 139 is +ve 39. I want my graph to calculate based on -11 & 39 but shows 89 & 139. I can do this for the positive number using the format #,##100 but it shows -111 instead of 89 for the negative.

    Not sure if I was very clear in what I was looking for, but any help is appreciated.

    Thanks,
    Roshan

  28. Deepak vats (Chandigarh) says:

    dear Sir

    Thanks

  29. Deepak vats (Chandigarh) says:

    Dear Sir
    I read your article. I want to display absolute figures in lacs –
    /1000000

    Put 0.######,,,%% in Custom Number formatting

    want Rs. 1662052 as 16.62
    but getting as 1.6222052%%
    Trying Ctrl+J as madam kyle suggested but %% are reflecting on excel screen and not getting hided so cannot send report to Top Management. Kindly assist. I think I am making a mistake in Ctrl+J step.

    Thanks a lot in advance.
    warm regards,
    Deepak Vats

Leave a Reply