Format negative percentages to make them easy to find

 Important   The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

Negative percentages can be hard to spot as you scan a worksheet. But you can make them easy to find by applying special formatting to negative percentages, or by creating a conditional formatting rule.

Create a custom format

  1. Select the cell or cells with negative percentages. To select multiple cells, hold down the Ctrl key as you select each cell.
  2. On the Home tab, click Format > Format Cells.

On the Home tab, Format button, and the Format cells button on the menu

  1. In the Format Cells box, in the Category list, click Custom.
  2. In the Type box, enter the following format: 0.00%;[Red]-0.00%.

Custom format to display negative percentages in red

Now the negative percentage stands out, formatted in red.

Excel data with a negative percentage formatted in red in cell D3

 Tip    Custom formats are saved with the workbook. The next time you need to apply it, follow steps 2 and 3 above, and you’ll find it in the Type list. To use a custom format in other workbooks, save it as a template called Book1.xltx, or Book1.xltm (if the workbook contains macros), and then base future workbooks on that template..

Create a custom formatting rule

This rule will be available only in the worksheet in which you create it.

  1. In a worksheet, select the range of cells in which you’ll enter negative percentages.
  2. On the Home tab, click Conditional Formatting > New Rule.

Conditional Formatting button on the Home tab

  1. In the New Formatting Rule box, select Format only cells that contain.
  2. In the Edit the Rule Description box, under Format only cells with, select less than.

Edit conditional formatting rule box

  1. In the last box, type 0.
  2. Click Format.
  3. In the Format Cells box, select a font or cell color, and then click OK twice.

When you type a negative percentage within the selected range, it will automatically be formatted in red.

 
 
Applies to:
Excel 2013