Reporting large values ​​in Excel is probably a good thing, but if you want to make them readable, try this simple technique.

Articles, use a custom format in Excel to make reading easier to display. Millions of people use a custom format to make larger values ​​more readable. For example, instead of 1,200,000 it displays as 1.2 M — Also the format in Excel is round. If a value is less than one million, Excel’s custom layout still displays the value with M for million, but it drops to the decimal point. For example, 669227 displays as .67M. In fact, the .67 M is not more readable than the 669227. In fact, .67 M can easily be misread. That is why in this article, I will show you two Excel conditional formatting rules which are small to large values: one for millions and one for thousands.

Views: Software Installation Policy (TekriPublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. I recommend that you stop upgrading to Windows 11 until all issues are resolved. For your convenience, you can download a display of Excel .xlsx and .xls files. Excel for the web will display the existing conditional layout correctly, but it still does not support custom standard layouts.

Excel’s custom million format

First, let’s examine the million formats used in the article linked above

#. ##, “M”;

Each formatting code consists of four sections, separated by semi-colon (;). The custom layout above uses only the first category, which specifies the layout for positive quality. To be broad, an explanation of the four sections is followed, although we will use only the first section:

• The first section applies to positive values
• The second category applies to negative values
• The third section applies to 0
• The fourth section applies to the text.

Now let’s break down the custom format that we will use for values ​​greater than or equal to one million. This format displays the $ sign on the left. Next, #. ##, manages the actual number. First, two commas represent the divisor of thousands, and subtract values ​​in thousands and hundreds of places. For example, 1780379 displays as 1.78 M. Decimal is required for conversion; Is 1.78 M No. The same thing 178 m.

To add this custom format to your Excel workbook, do the following:

1. On the Home tab, click the Value Group dialog launcher.
2. If necessary, click on the Values ​​tab.
3. In the Category list, select Custom.
4. In the Type control on the right, type custom format #. ## ,, “M”; (Figure A)

Figure A

5. Click OK.

Figure B Shows the custom layout applied.

Figure B

As you can see in the formula bar, this format does not change the underlying value – only the way Excel displays it. You may not like the way this format displays values ​​less than one million. These are not as readable as you would like; In fact, they may be misread. For this you need a custom thousand format.

Excel’s custom thousand formats

At this point, you may want to take a stab to write a custom thousand format yourself. If you bring

###, “K”;

You’re right! The important thing to note is the ###, material. It represents three possible digits in a thousand positions and excludes digits in a hundred positions. In addition, K is universally known to represent thousands, so we will use it instead of T. Using the instructions above, add this custom layout. With the addition of both custom formats, it’s time to create conditional formatting rules for their application.

How to create conditional formatting rules in Excel

Once custom value formats exist, you can apply them using conditional formatting rules. At the moment, the format is set as normal. First, select the data set shown Figure C.

Figure C

To create an Excel conditional layout rule that handles more than one million or equal values, do the following:

1. Select the data set (B3: E8).
2. On the Home tab, click Conditional Format in the Style group.
3. Select the new rules from the dropdown list.
4. In the resulting dialog, select only the cell layout in the top pane.
5. In the pane below, choose greater or equal to the second dropdown. The first dropdown is the cell, and that’s what we need.
6. In the third control, enter 1000000.
7. Click Format.
8. Click on the Numbers tab.
9. From the Category list, select Custom.
10. On the right, select Custom Format, #. ##, “M”; (Figure D) And click OK.

Figure D

11. Figure C Shows both rules and format. Click OK to apply it. As you can see Figure eOnly values ​​greater than or equal to one million have been formatted

Figure e

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

###, “K”;

Use Figure F As a guide for steps 6 and 10.

Figure F

Figure G. Shows results. Both custom layouts take care of all the standards. If your data set has values ​​of trillions or less, create new custom formats for those locations and apply them using conditional formatting. Excel can handle them all.

Figure G.