excel-vector.jpg
Image: 200dgr/Shutterstock

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

$#.##,,” M”;

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:

  1. On the Home tab, click the dialog launcher for the Value group.
  2. Click the Value tab if necessary.
  3. In the Category list, select Custom.
  4. In the Type control to the right, enter the custom format $#.##,,” M”; (Figure A).

Figure A

Create the custom format $#.##,," M".
Create the custom format $#.##,,” M”.
  1. Click OK.

Figure B shows the custom format applied.

Figure B

Millions are changed to decimal amounts for easier reading.
Millions are changed to decimal amounts for easier reading.

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

$###,” K”;

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.

Figure C

Set Excel’s custom millions format to this data set.
Set Excel’s custom millions format to this data set.

To create an Excel conditional format rule that handles values that are greater than or equal to one million, do the following:

  1. Select the data set (B3:E8).
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. Choose New Rule from the dropdown list.
  4. In the resulting dialog, choose Format Only Cells That Contain in the top pane.
  5. 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.
  6. In the third control, enter 1000000.
  7. Click Format.
  8. Click the Number tab.
  9. From the Category list, choose Custom.
  10. To the right, select the custom format, $#.##,,” M”; (Figure D) and click OK.

Figure D

Select the custom millions format.
Select the custom millions format.
  1. 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.

Figure E

The custom millions format works only on values greater than or equal to a million.
The custom millions format works only on values greater than or equal to a million.

Now, repeat the instructions to add a rule for the custom format

$###,” K”;

using Figure F as a guide for steps 6 and 10.

Figure F

Add the second conditional formatting rule.
Add the second conditional formatting rule.

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.

Figure G

The final product. Millions and thousands are in decimal format in your Excel spreadsheet.
The final product. Millions and thousands are in decimal format in your Excel spreadsheet.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays