Reporting lots of large values in Excel is probably a good thing, but if you want them to be readable, try this easy technique.
The article, Use a custom format in Excel to display easier to read millions uses a custom format to make large values more readable. For instance, 1,200,000 displays as 1.2 M instead—the format also rounds in Excel. If a value is less than a million, Excel’s custom format still displays the value with the M for million, but it drops in a decimal point. For example, 669227 displays as .67 M. Realistically, .67 M isn’t that much more readable than 669227. In fact, .67 M could be easily misread. That’s why in this article, I’ll show you two Excel conditional formatting rules that short large values: one for millions and one for thousands.
SEE: Software Installation Policy (TechRepublic Premium)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version of Excel. I recommend that you hold off on upgrading to Windows 11 until all the kinks are worked out. For your convenience, you can download the demonstration Excel .xlsx and .xls files. Excel for the web will display existing the conditional formatting correctly, but it doesn’t yet support custom value formats.
Excel’s custom millions format
First, let’s examine the millions format used in the linked article above
Each formatting code has four sections, separated by semi-colons (;). The above custom format uses only the first section, which specifies the formatting for positive values. To be comprehensive, an explanation of the four sections follows, even though we’ll use only the first section:
- The first section applies to positive values
- The second section applies to negative values
- The third section applies to 0
- The fourth section applies to text.
Now let’s break down the custom format we’ll use for values larger than or equal to one million. This format displays the dollar symbol, $ to the left. Next, the #.##,, handles the actual digits. First, the two commas represent the thousands separator, and ,, omits values in the thousand and hundred places. For example, 1780379 displays as $1.78 M. The decimal is necessary for conversion; 1.78 M is not the same thing as 178 M.
To add this custom format to your Excel workbook, do the following:
- On the Home tab, click the dialog launcher for the Value group.
- Click the Value tab if necessary.
- In the Category list, select Custom.
- In the Type control to the right, enter the custom format $#.##,,” M”; (Figure A).
- Click OK.
Figure B shows the custom format applied.
As you can see in the Formula bar, this format doesn’t change the underlying value—only the way Excel displays it. You might not like the way this format displays values less than one million. They’re not as readable as you might like; in fact, they might be misread. That’s why you need a custom thousands format.
Excel’s custom thousands format
At this point, you might want to take a stab at writing the custom thousands format yourself. If you came up with
you’re correct! The important thing to notices is the ###, component. This represents the possible three digits in the thousands position and omits digits in the hundreds position. In addition, K is universally known to represent thousands, so we’ll use that instead of T. Using the instructions above, add this custom format. With both custom formats added, it’s time to create the conditional formatting rules to apply them.
How to create conditional formatting rules in Excel
Once the custom value formats exist, you can apply them using conditional formatting rules. Right now, the format is set to General. First, select the data set shown in Figure C.
To create an Excel conditional format rule that handles values that are greater than or equal to one million, do the following:
- Select the data set (B3:E8).
- On the Home tab, click Conditional Formatting in the Styles group.
- Choose New Rule from the dropdown list.
- In the resulting dialog, choose Format Only Cells That Contain in the top pane.
- In the lower pane, choose Greater Than or Equal to from the second dropdown. The first dropdown is Cells, and that’s what we need.
- In the third control, enter 1000000.
- Click Format.
- Click the Number tab.
- From the Category list, choose Custom.
- To the right, select the custom format, $#.##,,” M”; (Figure D) and click OK.
- Figure C shows both the rule and the format. Click OK to apply it. As you can see in Figure E, only the values greater than or equal to one million are formatted.
Now, repeat the instructions to add a rule for the custom format
using Figure F as a guide for steps 6 and 10.
Figure G shows the results. Both custom formats take care of all the values. If your data set contains values into the trillions or less than a thousand, create new custom formats for those positions and apply them using conditional formatting. Excel can handle them all.