Number formats in Microsoft Excel define how data is displayed in your spreadsheet without changing the underlying values. As well as using number formats to present data as dates, times, percentages, and currencies, you’re able to build a more specific number format using custom number formatting.

Before I show you some examples, here’s how custom number formats work.

An Excel sheet showing a percentage in cell L1, and the corresponding number format in the Number group of the Home tab reflecting this data type.

In Excel,every cell has its own number format, which you may review by selecting a cell and looking at the Number group in the Home tab on the ribbon.

One of the format options you can apply when you click the down arrow is “Custom.” Custom number formats are defined in the Type field in the Custom section of the Format Cells dialog box, which you can launch by pressing Ctrl+1.

The Type field in the Custom section of Excel’s Format Cells dialog box is empty and highlighted.

The code you need to insert here follows a strict order:

So, the first argument dictates how positive numbers are formatted, the second how negative numbers are formatted, the third how zeros are formatted, and the fourth how text is formatted. Note how each value type is separated by a semicolon.

A soccer pitch heat map in Excel that shows the position from which goals have been scored by a team.

Now that you know how the custom number format works, here are some cool things you can do with it.

1Hide Cell Values

If you want to hide a cell’s contents while preserving its value, the best way is to use custom number formatting.

For example, thisheat mapillustrates the positions from which a team has scored this season’s goals. The different color tones—created throughconditional formatting color scales—make this clear without the need to display the corresponding numbers.

A table in Excel culminating in an overall score in the rightmost column, with data bars higlighting the differences between each row.

Similarly, the scores in this table are calculated using numbers that don’t hold any numerical value, and theconditional formatting data barstell the whole story. So, the data would be less confusing if the numbers were hidden altogether.

Here, when I print this employee info sheet, I want to hide the salary without deleting the figure.

An employee information sheet in Excel, with the employee’s salary highlighted.

If you’re in a similar position, first, select the cells whose values you want to hide, and click the “Number Format” dialog box launcher in the bottom-right corner of the Number group in the Home tab on the Excel ribbon. Alternatively, press Ctrl+1.

At this point, remember that the code you enter into the Type field of the Custom category follows this order:

Some data in an Excel sheet is highlighted, and the Number Format dialog box launcher icon is selected.

Since you wantalldata in the selected cells to be hidden, you don’t need to enter formatting rules for any of the four types of values—just the three semicolons that separate them:

When you click “OK,” the values in the selected cells will magically disappear, even though the data is still there.

Three semicolons are typed into the Type box in the Custom area of the Format Cells dialog box.

Since the data is still attributed to that cell, even though it’s hidden, you can stillreference it in formulasas you would when the data is visible.

Here’s how the heat map I showed you earlier looks with the numbers hidden.

Numbers hidden from view in a heat map in an Excel sheet by adusting the number format.

2Replace Zeros With a Dash

Imagine you’ve created this Excel spreadsheet that tracks the number of appearances each player has made this season.

However, you’re not happy with how untidy the table looks, so one thing you want to do to fix this is turn all the zeros into dashes.

A list of soccer players in Microsoft Excel, with their appearance data in subsequent columns.

First, select all the cells containing numerical values, and press Ctrl+1 to launch the Format Cells dialog box. Then, in the Type field of the Custom category, type:

where

The Custom category is selected in Excel’s Format Cells dialog box, and a code that displays zeros as dashes is typed into the Type field.

Now, the zeros are replaced by dashes, making the table look tidier without giving the impression that you’ve forgotten to populate those cells.

What’s more, any formulas that reference the affected cells still work as expected.

3Turn Values to Text Based on a Condition

Another way to use custom number formatting in Excel is to turn numbers into representative text based on conditions you set.

Let’s say you want to turn the students' overall scores in the Outcome column of this table into the word “Pass” if the values are 165 or above, or the word “Fail” if the values are below 165.

First, select the cells in the column you want to format, and press Ctrl+1 to open the Number Format dialog box.

Before you type the relevant code in the Type field of the Custom category, bear in mind that conditions must be placed in square brackets. If you fail to do this, the conditional characters you insert will show in the result in the cell. So, type:

In these cases, you only need to input two arguments, separated by a semicolon. The first is the formatting of values that meet the condition, and the second is the formatting of those that don’t. What’s more, you don’t need to repeat the condition in the second argument.

Here’s what you get when you click “OK.”

To format cells based on more than two conditions, useconditional formatting.

4Color Numbers Based on Their Values

Custom number format in Excel lets you define the color of the text in selected cells according to their values. What’s more, for the main eight colors (black, white, red, green, blue, yellow, magenta, and cyan), you don’t have to remember any complex code—you can simply type the color’s name.

There are only two things to note when using custom number formatting for this purpose—colors must be the first argument for each data type, and they must be placed in square brackets.

This example worksheet shows whether the employees have met the target of 400 sales. You want all the positive numbers in column C to be green, negative numbers red, and zeros blue.

Select all the cells in the Result column, press Ctrl+1 to open the Format Cells dialog box, and in the Type field of the Custom category, type:

Since negative values will be formatted red, I’ve not added the minus symbol to the negative argument.

When you click “OK,” the formatting is applied to the selected cells.

If you want access to more colors than the standard eight, instead of typing the color name, type the color code according to this chart:

5Repeat Leading and Trailing Characters

A final custom number format trick in Excel involves filling a cell with a certain character before or after the cell value.

Whether you want to insert a signature line, a date line, or a dotted line that links a chapter number to a page number in a table of contents, custom number formatting has got your back!

In this example, let’s say you want to repeat an underscore after the word “Signature” to create a signature line.

To do this, select cell A1, and press Ctrl+1 to launch the Format Cells dialog box. Then, in the Type field of the Custom category, type:

where the @ symbol represents the text value in the cell (in this case, “Signature”), and the asterisk tells Excel to repeat the character that follows (in this case, an underscore).

If the leading or trailing character is before or after a numerical value (rather than text), use the hash sign (#) instead of the @ sign.

This is what you get when you press Enter.

On the other hand, you can use the same method to repeat a leading character. Here, in cell B2, you want a dotted line before “Page 1.”

So, this time, in the Type field of the Custom category, type:

with the asterisk-character combination placedbeforethe text placeholder (@).

Here’s the result when you press Enter.

The benefit of using this method is that if youchange the column width, the instances of the repeated character adjust to fill the cell—regardless of whether it’s leading or trailing.

If you want to go one step further, you canuse custom number formatting to display a symbol—like ▲ or ▼—either with or instead of a numerical value. What’s more, you’re able to color these symbols so that your data is even clearer.

Microsoft 365 Personal

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.