How to Change Number Format in Excel
(+Custom Formats)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In Microsoft Excel, there are several formats available when dealing with numeric data.

Yet, the application also offers the ability to create custom number formats. That is, if any of the built-in offerings don’t meet your needs.

In this article, we will take a quick look at the idea of number formats in Excel.

Then we will turn our attention on how to customize these to your specific needs.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Number formatting isn’t just for looks

There are many number formats to choose from in Microsoft Excel.

number-formats

It always makes a difference to have your data formatted for viewing.

Kasper Langmann, Co-founder of Spreadsheeto

But this isn’t the primary reason for number formatting. Not all number formats the same in form or function.

If you are working with percentages, integers or currency format will not work well.

Formatting your data using number formats is quite simple.

The first thing to do is highlight the data. Then go to the Number group on the Home tab.

number-group

By default, number data that you enter is in General format until you change it.

The next thing to do is click on the small down arrow next to “General.”

general-options

If you click on Number, Excel changes your values to that format. You should now see your data with two decimal places added.

decimal-data

To remove those decimal places, select the data and go to the Number section of the Home tab again.

You can then click Decrease Decimal a couple of times.

decrease-decimal

You could also change the number format of your data to currency. Select Currency from the drop-down list.

currency-format

This will now change your data accordingly.

currency-data

You can get even more detailed with your number formatting. Click the small angled arrow in the lower right hand corner of the Number group.

This will expand the Number Format group.

number-format-group

This will open the Format Cells dialog box. You will find many options available here.

format-cells-dialog

This is where you can do things like set decimal places and how you want negative numbers to appear.

You can even change the symbol of the currency based on your needs.

currency-symbols

Let’s look at an example about date data. Say you have a date in the following format.

date-data

Select the cell containing the date.

Open the Format Cells dialog box and select Date from the Categories list.

date-category

Notice the many format variations in the Type list. Another thing to note is the sample shown above Type.

It gives you a quick glance at your actual data in the selected format type.

This will change as you cycle through the various types giving you a quick preview. Let’s select the long date format or the second type selection from the top.

long-date-format

We can see a preview in the Sample window. We must click OK to reformat our data.

formatted-date

Beyond the built-in options: custom formatting

The Format Cells dialog offers so many options available “out-of-the-box.” The available format category and type options go far beyond the basics.

But, if what is available still doesn’t meet your requirements, you can always create a custom format.

Let’s consider a set of numbers.

example-numbers

We start by opening the Format Cells dialog box as we did before. This time we will select Custom from the Category list.

custom-format

Then we select from the Type list.

type-list

We will start with “#,##0” and then build our own custom format from there.

Kasper Langmann, Co-founder of Spreadsheeto
new-number-set

If we want to build our own custom currency format, one of the first things we can do is add a dollar sign.

We can type that into the Type text box.

add-dollar-sign

One of the things you can customize is the color of your type font.

You do this by preceding your number format with the color name enclosed in square brackets.

We add blue type font formatting to our current format.

color-type

The preview in the Sample box does not show color changes.

We must click OK to see the change.

color-effect

So far, we have only worked with number formats that consist of one section of code.

Using semicolons to separate, we can have up to four of these code sections.

This means that we can do something like format both positive and negative numbers.

For instance, we may want our negative numbers to show up red font. This will be a good contrast to the blue we have set for positive numbers.

We could also have negative numbers shown in parentheses.

We can even go a step further and set a third code section for zero values.

multiple-formats

This will now change our list of numeric values to something more meaningful.

multicolor-data

This is a great demonstration of the flexibility custom number formatting offers. But let’s go even a step further.

Custom formatting even allows you to add text to your formats.

You could be even more descriptive based on whether your values are positive or negative.

You can do this by adding text by enclosing the custom string in double quotes.

custom-string

We have added the text “ Profit” to the end of our positive values. We have also added the text “ Loss” to the end of our negative values.

Pay attention to the spaces after the opening double quote and the first letter of each string.

Without these leading spaces, there would be no space between the numeric value and the text.

Now our updated custom format codes yield the following results.

data-custom-text

Conclusion

Much like all the tools Microsoft Excel offers, number formatting is a deep well of features. This article merely scratches the surface of the possibilities.

It is our hope that it has at least exposed you to a new set of ideas for your own development.

You could already well acquainted with number formatting. This might have been the first time you’ve seen this.

Either way, we hope you learned something that has inspired you to dig further. Further into what number formatting can do for your spreadsheets.

2019-10-16T12:58:27+00:00