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