Number formatting is a core part of Excel for anyone who uses it often.

For that, you’ll have to create your own custom format in Excel.

As a general note, you’re free to customize nearly everything in Excel.

you might evencreate a button with a custom function in Excel.

To get started, check out the table below.

This table is a summary of some important symbols in custom formatting.

Symbol

Function

Optional number.

If the custom format is#, then the cell will display any number in it.

The decimal points are determined by the number of zeroes you put in after the period.

\

Displays the character following it.

If the custom format is#\Mand the cell value is 500, the cell will display 500M. "

Displays custom text.

%

Multiplies the number by 100 and shows it as a percentage.

Another important factor to consider when creating a custom format is the order.

For negative values it will displayNegative, and for zero it will displayZero.

The custom format doesn’t have to include all three of these conditions.

If you put in only the first one, it will be used for all three.

Now let’s turn this knowledge into skill with some examples.

Let’s say that you want to input the duration of some operations into a range of cells.

Here’s how you’re free to do that:

Now your custom format is ready.

Notice how the cell values are unchanged despite theHourssuffix being displayed in the cells.

In fact, the numbers are all rounded up.

Let’s build on the previous example.

Here we have the same spreadsheet, but want to change the displayed values for negatives and zeros.

Custom formats with text strings allow you to create new things in Excel.

For instance, you’re free to use custom formatting tocreate a bulleted list in Excel.

Excel Custom Formatting Example: Colors and Conditions

Now, let’s try something new with custom formatting.

In this example, we have the grades of some students.

If not, then they have unfortunately failed.

Let’s get down to it.

With that in mind, let’s create a custom format with conditions.

Again, note that the cell values haven’t changed from the original numbers.

With custom formatting in Excel, you might create a unique cell format that exactly suits your needs.