Whereas Microsoft Excel presents many instruments for formatting knowledge in numerous methods, typically, the built-in instruments do not fairly do precisely what I am searching for, or take up an excessive amount of time to execute. In these eventualities, I exploit customized quantity formatting to shortly create a quantity format that meets my wants.

Disclaimer: There are some eventualities the place utilizing conditional formatting is preferable over utilizing customized quantity formatting, akin to coloring whole rows based on a value. Nonetheless, in conditions the place I might use both to generate the identical or related outcomes, I go for the latter. On this article, I will clarify why.

What Is Customized Quantity Formatting, and How Does It Work?

In Excel, each cell has its personal quantity format, which you’ll be able to assessment by deciding on a cell and searching on the Quantity group within the Residence tab on the ribbon.

One of many quantity format choices you possibly can apply to a cell is a customized quantity format, which you’ll be able to entry by clicking the Quantity Format dialog field launcher, earlier than clicking “Customized” within the Format Cells dialog field.

In case you choose utilizing Excel keyboard shortcuts, press Ctrl+1 to launch the Format Cells dialog field, press Tab to activate the Class menu, and kind Cus to leap to Customized.

By the top of this information, you can use customized quantity formatting to create spreadsheets that look one thing like this:

In case you’ve by no means used customized formatting earlier than, it might sound complicated at first, and I can see why you may assume that utilizing conditional formatting as a substitute could be the higher possibility. Nonetheless, Excel processes customized quantity formatting far more shortly than conditional formatting, and—when you perceive its logic—you may probably discover it faster and simpler to make use of.

Personally, one of many most important causes I choose utilizing customized quantity formatting is that every little thing occurs in a single place—there is not any want to leap forwards and backwards between totally different dialog packing containers so as to add numerous guidelines, so the method is time-saving and simple.

Earlier than I’m going forward and information you thru some examples, let me clarify how customized quantity formatting works.

Customized quantity codecs are entered within the Sort discipline within the Customized part of the Format Cells dialog field.

The code it is advisable to insert right here follows a strict order:

POSITIVE;NEGATIVE;ZERO;TEXT

In different phrases, the primary argument within the customized quantity format discipline dictates how constructive numbers are formatted, the second argument dictates how damaging numbers are formatted, the third argument dictates how impartial numbers are formatted, and the fourth argument dictates how textual content is formatted. Discover, additionally, how semicolons are used to separate every argument.

For instance, typing:

#.00;(#.00);-;"TXT"

into the Sort discipline shows:

• A quantity with two decimal locations for constructive numbers (#.00),
• A quantity with two decimal locations in parentheses for damaging numbers (#.00),
• A splash for zeros (-),
• And the letter string “TXT” for all textual content values (“TXT”).

In case you omit any of the arguments by including the semicolon however not typing any code, the related cells in your spreadsheet will seem clean.

For instance, typing:

#.00;;;"TXT"

into the Sort discipline shows a quantity with two decimal locations for constructive numbers, produces a clean cell for damaging numbers and zeros, and shows “TXT” for all textual content values.

Regardless that the values displayed in every cell rely upon what you specify within the customized quantity format Sort discipline, the precise values of the cells stay unchanged. Within the instance above, despite the fact that cell C2 seems clean (as a result of I omitted the NEGATIVE argument within the Sort discipline), once I choose the cell, the components bar reveals its true worth.

Which means I might nonetheless use this cell in formulation if required.

In addition to utilizing customized quantity formatting to vary what worth seems in a cell, you may as well change the colour of the values and add symbols to visualise your knowledge. Let’s look into this in additional element.

Altering the Font Coloration Utilizing Customized Quantity Formatting

Microsoft Excel’s customized quantity format helps you to outline the colour of the values within the chosen cells. What’s extra, for the principle eight colours (black, white, crimson, inexperienced, blue, yellow, magenta, and cyan), you needn’t bear in mind any code—you possibly can merely sort the colour identify. Colours have to be the primary argument for every knowledge sort, and so they have to be positioned in sq. parentheses.

Let’s take this instance, which reveals whether or not ten staff have met their gross sales targets. You need all of the constructive numbers in column C to be inexperienced, damaging numbers crimson, and zeros blue.

To do that, choose cell C2, open the “Format Cells” dialog field (Ctrl+1), and head to the “Customized” part.

Within the Sort discipline, enter:

[Green]#;[Red]#;[Blue]0;

to inform Excel to maintain constructive and damaging numbers as they’re (#)—however formatted inexperienced and crimson, respectively—and zeros as “0”—however formatted blue. Since there is not any textual content on this vary, you needn’t specify a quantity format for textual content values, so solely the primary three arguments are required.

While you click on “OK,” you may see the textual content change coloration accordingly. You possibly can then click on and drag the fill deal with to the ultimate row of knowledge to use this practice quantity format to the remaining values. This may not change the values in column C, since they have been calculated utilizing a components.

If you wish to use colours aside from the usual eight on provide for the font, as a substitute of typing the colour identify in sq. parentheses, sort the colour code:

For instance, typing:

[Color10]#;[Color30]#;[Color16]0;

would use a darkish inexperienced font for constructive numbers, a crimson font for damaging numbers, and a grey font for zeros.

To revert the quantity formatting again to its authentic, choose the cells containing the formatted knowledge, and within the Quantity group of the Residence tab on the ribbon, choose “Normal” within the drop-down checklist.

At this level, take a second to understand how attaining the identical end result utilizing conditional formatting would require you to create three separate guidelines. However, utilizing customized formatting, you may make these modifications by including a number of characters to the Sort discipline.

Displaying a Image Utilizing Customized Quantity Formatting

Very like when utilizing conditional formatting, you possibly can show symbols in line with the corresponding values via customized quantity formatting.

Utilizing the identical instance as above, for example you now wish to convert the numbers within the Outcome column (column C) to up arrows for constructive numbers, down arrows for damaging numbers, and an equal (=) signal for zeros.

To do that, you first must find the symbols, or know their keyboard shortcuts.

Within the Insert tab, click on “Image,” and insert the symbols in one other space of the spreadsheet, so you possibly can copy and paste them into your customized code.

Alternatively, use the Windows shortcut Home windows+Interval (.) to launch the emoji keyboard, and insert these straight into the Sort discipline.

However, I do know that the Home windows keyboard shortcut for ▲ is Alt+30, and ▼ is Alt+31, and I can use these shortcuts when typing within the Sort discipline.

So, choose cell C2, and within the Customized part of the Format Dialog field (Ctrl+1 > Tab > Cus), sort this very quick line of code:

▲;▼;"=";

to inform Excel to interchange constructive numbers with “▲,” damaging numbers with “▼,” and zeros with “=.”

Any textual content strings or typed symbols (together with mathematical or any keyboard symbols) have to be positioned in double quotes.

Now, press Enter, and use the fill deal with to use the customized quantity formatting to the remaining cells within the column.

Subsequent, you may wish to coloration the arrows to visualise your knowledge extra clearly. That is the place you mix the colour code with the arrow symbols, remembering that the colour code should all the time be positioned initially of the argument.

To do that, sort:

[Green]▲;[Red]▼;"=";

into the Sort discipline. Here is what you may see:

Utilizing Customized Quantity Formatting for Font Colours, Numbers, and Symbols

Now, it is time to mix all of the steps above to provide an end result that reveals coloured symbols and arrows on the identical time.

Within the Sort discipline for cell C2, sort:

[Green]#▲;[Red]#▼;0" =";

the place:

• The primary argument ([Green]#▲) turns constructive values inexperienced, displaying each the quantity and an upward triangle,
• The second argument ([Red]#▼) turns damaging values crimson, displaying each the quantity and a downward triangle, and
• The third argument (0″ =”) does not have any coloration formatting (so adopts the handbook cell formatting), and shows the quantity “0” adopted by an area and the “=” signal.

To align the quantity to the left of the cell, and the image to the precise of the cell, sort an asterisk (*) and an area between the image and quantity in every argument (for instance, [Green]#* ▲;[Red]#* ▼;0* ” =”;). The asterisk tells Excel to repeat the next character infinitely (till the cell’s boundary is met), and for the reason that following character is an area, the hole between the quantity and the image will increase and reduces with the cell width.

In a remaining instance, for example you’ve got labored out the share change in factors totals for numerous sports activities groups, and also you wish to shortly format the constructive and damaging figures in order that they flip inexperienced or crimson, have an up or down arrow, embrace the quantity, and are thought-about percentages. You additionally need zeros to show as dashes.

To do that, within the Sort discipline for the cells in column C, sort:

[Green]▲#%;[Red]▼#%;"-"

Here is what you may get for the end result:

---

As soon as you’ve got practiced utilizing customized quantity codecs so as to add coloration and symbols to your cells, you may understand how fast and straightforward the method is—and, hopefully, you may use customized quantity formatting extra usually sooner or later!