Quick, clean, and to the point

How to create a custom number format in Excel

Excel allows you to create custom formats for dates, times, text, and numbers.  In this lesson we'll create a simple custom format for positive and negative numbers based on an existing format.

Let's take a look.

The easiest way to create a custom format in Excel is to first apply a format that is similar to the format you want to create.

For example, suppose we want to create a custom format that shows positive numbers with a positive sign and negative numbers with a negative sign. In addition, we want this format to have a single decimal place and to display negative numbers in red.

In this case, the currency format is a good start. We can apply the currency format, then customize the options to display negative numbers in red and set decimal places to one.

Now, when we click OK, we see we're getting close.

To actually create our custom format, we need to revisit the Format Cells dialog. This time, we start by selecting Custom. This will immediately create a new custom format, based on currency, that we can further customize.

The semicolon separates the format for positive numbers on the left from negative numbers on the right. To finish our custom format, we need to replace the currency symbol with a plus sign on the left and a minus sign on the right. When we click OK, we see the format we're after.

If we modify any values, we see our format displays the new values properly.

To use the Custom format again, select the cells you'd like to format then access the Format Cells dialog box. New custom formats will appear at the bottom of the Custom format list.

Every time you change your custom format, Excel will save a new version. Use the delete button to remove custom formats you no longer need.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.