EXCEL TIPS: Formatting

How to Create Custom Formatting in Excel

Sometimes when 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, trying to shorten value lengths to show in thousandths, or having to make numbers requiring unit of measure added in an additional cell alongside them.

Luckily, in most cases there is a simple solution to the problem. I’ll step you through some of the typical formatting issues you’re likely to encounter within Shell and how to solve 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 does not 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, right-click and choose Format Cells.. the under the Category: list, click Custom and, on the right of the dialogue, a list of custom formats will appear. In the box marked "Type," type 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.  The same principle can be applied at the beginning of the number set for any currency symbol, so “USD” rather than the usual “$”.

Padding Numbers

In the previous example you saw that we can format numbers and force them 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 display as 21 lbs. This factor is a key contributor to those ‘weird’ situations where you know you’ve written the formula correctly, but the result is not what you expect like the example below: 

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.

Leading Zeros

This leads us neatly into the next problem. If you’re in a business that uses data that refers to products, product numbers, or reference numbers, etc you may have found that when those that start with one or more zeros, Excel will knock off the leading zeros from your 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 and right-click, choose Format Cells.. then under Category: click on Custom and, in the Type: box type:

00000

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

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 axis too.
• In the Type: box, type the following format to reduce numbers that are in the millions:

#,, “M”

This reduces Apple’s value to display as 21 M. If you want to reduce in the thousands, type this into the box:

#,###, "K"

This reduces Apples value of 21196702 to display as 21,197 K. These custom formats simplify your numbers and make them easier to read and understand. The same formats also work on worksheet data and table data too.

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.

• Right-click the cell and choose Format Cells.. and this time click the Special category
• Make sure that the Locale value is set to English (United States) and you will see four special formats show up in the Type: box. 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.

Adding Colour to Numbers

It is also possible to add colours 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.

Parts of a Number Format String

A custom format string can have up to four sections, which enables you to specify different format codes for positive numbers, negative numbers, zero values, and text. You do so by separating the codes with a semicolon. The codes are arranged in the following order:

Positive format; Negative format; Zero format; Text format

If you don't use all four sections of a format string, Excel interprets the format string as follows:

• If you use only one section: The format string applies to all types of entries.
• If you use two sections: The first section applies to positive values and zeros, and the second section applies to negative values.
• If you use three sections: The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros.
• If you use all four sections: The last section applies to text stored in the cell.

The following is an example of a custom number format that specifies different formats for each of these types:

[Black]?/?;[Red] "[["€ -0.000"]]";[Green] 0 "lbs.";[Blue] General

Hiding Zeros

In the following format string, the third element of the string is empty, which causes zero-value cells to display as blank:

General;-General;

This format string uses the General format for positive and negative values. You can, of course, substitute any other format codes for the positive and negative parts of the format string.

Other handy one is:

+#,##;-#,##;0;      shows positive and negative symbols

The following will actually hide the contents of the cell from both view and from printing (but can still be used to calculate).

;;;

Other Handy Custom Formats

Examples of Displaying a Value with Extra Zeros

Examples of Displaying Values in Millions

Useful Custom Date and Time Formats

 

Enjoy!

Stephen Graham-King

Senior PMO Consultant & Solutions Developer