Imagine a situation where you have built a dynamic report or dashboard that you a really proud of. The user can make selections from a drop-down box and the report updates automatically, it’s a beautiful thing. There is just one issue with these types of dynamic reports/dashboards – not all the information is of the same significance. For example, Product A has a profit of $30,000,000, but Product J only has a profit of $100,000. How should you format the numbers so they work with whichever product has been selected? How do you ensure large numbers are not overly detailed and small numbers are not masked by the size of the rounding? If product A changed from last period by $50,000 nobody cares, it’s too small, but if Product J change by that amount, that is a significant movement.
I have got two possible solutions for you. Depending on the circumstance you can pick the option which suits your requirements. Both circumstances rely on Custom Number Formatting, so let’s have a brief overview of that before looking at the two options.
Custom Number Formatting
As you’re probably aware, you can format numbers in Excel using the icons on the Home menu.
But you are not limited to these options. You can use Custom Number Formats to display numbers however you like. The Custom Number Format is a text string which tells Excel how you wish the number to be formatted.
Custom number formats are separated into 4 sections, all separated by semicolons.
[display of positive values] ; [display of negative values] ; [display of zero values] ; [display of text values]
- # (hash) represents an optional digit. If a number is there is will be displayed.
- 0 (zero) represents a forced digit. Even if there isn’t a number to display a zero will be displayed. Any zero following the decimal places represents the number of decimal places.
- , (comma) at the end indicates the value should be divided by 1,000, two commas mean it is divided by 1,000,000.
- , (comma) between # or 0 indicates the thousand separator character
- . (period) indicates that there should always be one decimal places displayed.
Any example number format is:
#,##0.0,,;-#,##0.0,,;0.0,,
Based on the basic rules above see if you can work out how this Custom Number Format would display a number. For a more detailed description of Custom Number formats try this tutorial.
Option 1 – Format with the Text Function
This option is useful where the value is used in a text string. Take a look at the screenshot below.
The formula in Cell C10 is
=TEXT(B10,VLOOKUP(B10,$B$4:$C$5,2,1))
The TEXT function has two arguments:
- Value – the number to be formatted
- Format_text – the number format to be applied
So, our formula is formatting the value in cell B10 based on the VLOOKUP value returned from cells B4-C5. We have used an approximate match in our VLOOKUP, which gives the closest value below the lookup value. In our circumstance, Cell B5, which has a value of 1,000,000 is the closest value below B10, as a result the value in Cell C5 is returned as the Format_text
C5 is displaying the following text:
#,##0.0,,;-#,##0.0,,;0.0,,
The result is Cell C10 display as 30.0 (30,000,000 divided by 1,000,000, and displayed with a single decimal point.
The formula in Cell C11 is:
=TEXT(B11,VLOOKUP(B12,$B$4:$C$5,2,1))
This VLOOKUP will return the number format from Cell C4. So, the result of Cell C11 is 100 (100,000 divided by 1,000 and displayed without any decimal places).
This option of using the TEXT function is very useful where the value is to be used within a text string. However, it is important to realize that the values in Cells C10 and C11 are no longer numbers, but text stored to look like numbers. Therefore, you are unable to apply any numeric functions on these cells.
Option 2 – Format with Conditional Formatting
If we want to undertake any calculations on the formatted number we need to use conditional formatting.
The values in cells D10 and D11 are equal to B10 and B11, but we will format the numbers based on their values. Select Cells D10 and D11, then from the Home menu select Conditional Formatting -> New Rule…
First, we will set-up the custom number format where the value is greater than or equal to 1,000,000. Click Use a format to determine which cells to format, then enter a rule for when the format should be applied. Then, click Format….
Format the number to be the same as the text in Cell C5.
Click OK, then OK again to get back to the Conditional Formatting Rules Manager.
Select New Rule and set up another custom number for values less than 1,000,000
This time create a number format the same as Cell C4.
Click OK, then OK again. Once back at the Conditional Formatting Rules Manager click Apply. The values in Cells D10 and D11 are formatted to look just like the result in Cells C10 and C11.
It is important to realize, the values in D10 and D11 are still 30,000,000 and 100,000. The number format applied simply formats the number to appear how we have requested it. Any calculations performed on these cells they will be treated as their original value.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
hello,
i need your help for the below format in excel
123,456,779.00
How to change into above format.
Hi Peter,
To give you a 100% correct answer I would need to know quite a bit more about your scenario. For example, should 6779 be formatted as:
6,779.00 or
000,006,779.00
Should a negative (a) have a minus sign (b) be shown in a different color (c) have parentheses around it?
In its simplest form, which is what I suspect you’re trying to achieve, you should use:
#,##0.00
Let me know if that doesn’t give you what you want.