How to Create Custom Formatting in Excel

Sometimes when I’m working with an Excel worksheet and I want a cell to look a certain way, I run into what appears to be a limitation in the program. It might be trying to enter product numbers with leading zeros or trying to make numbers show their unit of measure in the cell along side them.

Luckily, in most cases there is a simple solution to the problem. In this article I’ll step you through some of the typical formatting issues you’re likely to encounter in the business world and how to work around them.

Units of Measure

Try this for size: type “20 lbs” in a cell, press Enter, and then try and multiply that cell’s value by, say, two. It can’t be done. You cannot add a unit of measure to a number and have it still behave like a number. As soon as you add “lbs” to the cell entry, Excel turns your number into a piece of text. Multiplying a cell containing text simply doesn’t work.

There are two possible solutions to this ‑‑ one simple and one smart. The simplest solution is to place the text “lbs” entry in the cell to the right of your value. This works but it’s not appropriate in all situations, and there is a smarter solution using Excel’s Custom Formats feature.

To see this at work, type the number 20 into a cell and press Enter. Click on the cell to select it, and choose Format > Cells > Number tab. At the bottom of the Category list, click Custom and, on the left of the dialog, a list of custom formats will appear. In the box marked “Type,” enter the following information:

0.00 “lbs”

Check the Sample area to make sure the number looks the way you want, and click OK. The number is padded out to show two decimal places with the letters lbs appearing after it. Because the text “lbs” is part of the format and not the cell contents, the cell still contains a number so you can perform math on it.

Adding text to a cell using a custom format instead of typing it clearly labels the data and still lets Excel calculate it. (Click for larger image).

Padding Numbers

In the previous example you saw that we can format numbers and force then to display a fixed number of decimal places. The 0.00 part of the custom format makes this happen. For example, type the following into a cell:

0 “lbs”

The number appears as an integer – so 20.1 would appear as 20 lbs and 20.5 lbs will show as 21 lbs having been rounded up. The cell contents are still 20.5 so that value will be used in calculations, but the cell will show as 21 lbs.

When you are creating a custom format you need to decide how you want your numbers to display. Use:

• 0 for an integer
• 0.0 for a single decimal place
• 0.00 for two decimal places, and so on.

If you use 00.00 then you will get two numbers to the left of the decimal place so 1.5 will display as 01.50 and 20 will display as 20.00.

A custom format lets you position more digits either in front of or after the original number. (Click for larger image).

Leading Zeros

This leads us neatly into the next problem. If you’re in a business that sells products with product numbers, you will often have cause to type various product numbers into Excel cells. If you have product numbers that start with one or more zeros, Excel will knock off the leading zeros from your product numbers. So, 00123 will be displayed as 123.

The solution is to use a custom format to reinstate the zeros. So, if you have five digit product numbers, select the cells containing the product numbers, choose Format > Cells > Number tab, and click on Custom and, in the Type box type:

00000

When you click OK, all the zeros in the product number will redisplay.

Product numbers with leading zeros pose another problem that custom formats can help solve. (Click for larger image).

Managing Large Numbers

If you’re working with very large numbers, for example in the tens or hundreds of thousands or millions, you will encounter issues when you chart this data. Very large numbers will expand the area taken up by the Y-axis of the chart and they aren’t easy to read when they get too big.

The solution is to reduce the look of the numbers to a more manageable value. To do this, right-click the chart’s Y-axis and choose Format Axis > Number tab. Click on Custom – as you can see, you can create custom formats for chart axes too. In the Type box, type the following format to reduce numbers that are in the millions:

#,, “M”

This reduces 1,000,000 so it displays as 1M.

If you want to reduce numbers in the thousands, type this format into the box:

#,, “K”

This reduces 15,000 to display as 15K.

These custom formats simplify your numbers and make them easier to read and understand. The same formats work just fine on worksheet data too.

You can use a custom format to reduce large numbers on chart axes and make them easier to read. (Click for larger image).

Built-in Smart Formats

Now let’s move to the situation where you want to include phone numbers in a worksheet. In the U.S. that means you will be entering a nine-digit number into a cell. This is a very long number and, to ensure consistent number formatting, it would be nice if Excel could do the work for you.

The solution is to use a built-in format. To see it at work, type a phone number into a cell and select that cell. Choose Format > Cells > Number tab, and this time click the Special option. Make sure that the Local value is set to English (United States) and you will see four special formats. Select Phone Number, and the data will be formatted more neatly as a regular phone number.

Lining Up Numbers

There are other solutions that you can use where you want to display up to a certain number of digits after the decimal point. So, if you want to enter 2.5 and 2.45 and have both appear as typed but with the decimal points lined up, you can use this custom format:

?.??

In this case each number will appear as typed but they will be positioned so their decimal points are directly under each other for easier reading. 

It is also possible to add colors to your formats. To see this at work, type a series of numbers including some positive and negative ones and some zeros. Now format them with this custom format:

[blue] 0.0; [red] 0.00;;

This shows positive numbers as blue, negative as red and hides zero values. The format code to the left of the first semi colon manages positive numbers, the one next to it handles negative values and the one to the far right handles zero values; because there is nothing listed there, nothing shows.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com