How to Make Negative Numbers Red in Excel
To make negative numbers red in Excel, select your cells, press Ctrl+1 to open Format Cells, go to the Number tab, and pick a red option under “Negative numbers.” For full control, choose Custom and use a code such as #,##0;[Red]-#,##0 or #,##0;[Red](#,##0), where the part after the semicolon styles negatives. You can also use conditional formatting if you want the rule to update automatically as values change.
If you work with financial data or any set of numerical information in Excel, you know that negative numbers often indicate losses, expenses or other unfavorable results. In order to make those numbers stand out and draw attention to them, you can set them to display in red color. This is a simple formatting trick that can make reports, budgets, or other documents more visually effective.
Introduction
To make negative numbers red in Excel, you don’t need any advanced knowledge of the software. By setting up conditional formatting, Excel can automatically display numbers in red if they are below zero. This article will show you how to make that happen, step-by-step.
Step 1: Select the range you want to format
First, you need to select the cells or range that you want to format. You can do this by highlighting the cells with your mouse or pressing Ctrl+A to select the entire worksheet. For this example, we’ll format cells A1 to A10.
Step 2: Open the Conditional Formatting dialog box
On the Home tab of the Excel ribbon, click on the Conditional Formatting button and select “New Rule…”.
Step 2a: Choose the rule type
In the New Formatting Rule dialog box, click on “Format only cells that contain”.
Step 2b: Set the rule parameters
In the next box, change “Cell Value” to “Less than” and type “0” in the value field. Then, click on the “Format” button to open the Format Cells dialog box.
Step 3: Set the font color to red
With the Format Cells dialog box open, select the “Font” tab and choose the color red. Click “OK” to return to the New Formatting Rule dialog box. You can preview how your selection will look by clicking the “Preview” button.
Step 4: Finalize the rule
Click “OK” to close the Format Cells dialog box, then “OK” again to apply the formatting rule. Your selected range will now display numbers in red if they are negative.
Conditional formatting is one of the most useful features in Excel, allowing you to automatically apply formatting based on certain conditions. Now that you know how to make negative numbers red, you can add visual impact to your spreadsheets and draw attention to important data. Try it out and see how it works for you!
Alternative: use the Format Cells dialog (Number tab)
Conditional formatting is great, but if you just want negatives to always show in red, the fastest method is the built-in number format. Select your range and press Ctrl+1 to open the Format Cells dialog, then click the Number tab. Choose Number (or Currency) in the Category list, and under Negative numbers pick one of the red options — Excel offers plain red, red with a minus sign, and red in parentheses. Click OK and any negative value in the range turns red automatically.
Build your own custom red format
For complete control, select Custom at the bottom of the Category list and type your own code. Excel number formats use up to four sections separated by semicolons in the order positive;negative;zero;text, and the [Red] color code styles whatever section it sits in. Some reliable examples:
#,##0;[Red]-#,##0— positives plain, negatives red with a leading minus sign.#,##0;[Red](#,##0)— negatives shown in red parentheses with no minus sign, the common accounting style.$#,##0.00;[Red]-$#,##0.00— currency with two decimals and red negatives.
A few syntax notes: the [Red] tag must come at the very start of its section, the parentheses in (#,##0) are literal characters that display around the number, and you only need to define the second (negative) section to color negatives — but adding a positive section first keeps positives looking the way you want. This is the same engine behind the comma style number format, so the codes are interchangeable. Note that a custom format only changes how a number looks in red; it does not make the value itself negative or change the underlying data.
When you want the rule to react to other conditions — say, coloring whole rows or highlighting a band of values — reach for conditional formatting instead, or change the cell color for fills rather than font color.
Useful Excel tips
Now that you know how to make negative numbers red in Excel, it’s worth exploring other ways to format your numerical data. Here are some useful tips you can apply to your spreadsheets:
Apply formatting to whole columns or rows
If you want to apply conditional formatting to an entire column or row instead of selected cells, simply click on the column or row letter/number to select it, then follow the same steps described in this article.
Copy and paste formatting rules
If you need to apply the same formatting rule to multiple ranges in your worksheet, save time by copying and pasting the formatting rule. Simply click on the cell that includes the rule you want to copy, then press “Ctrl + C”. Highlight the range you want to apply the rule to, right-click and select “Paste Special”, then choose the “Format” option.
Create custom formatting rules
Excel includes an extensive range of pre-formatted rules for conditional formatting, but you can also create custom rules to suit your specific needs. For example, you could create a rule that highlights any cell with a value between -10 and 10. To create a custom rule, select the “Use a formula to determine which cells to format” option in the New Formatting Rule dialog box, then enter your desired formula.
Formatting data in Excel can go a long way toward making your documents more readable, informative, and visually appealing. Now that you know how to make negative numbers red in Excel, you’re on your way to creating more effective spreadsheets. Make the most of Excel’s powerful formatting tools and see how much easier it can be to work with complex data.
FAQ
Here are some frequently asked questions about formatting negative numbers in Excel:
Can I edit the default color of negative numbers in Excel?
Yes, you can. By default, negative numbers in Excel display in red color, but you can change this to another color or even a custom format. Go to “File” > “Options” > “Advanced”, then scroll down to “When calculating this workbook”. Here, you can change the default format for negative numbers.
How do I format positive numbers in a different color?
You can apply the same conditional formatting to positive numbers by selecting “Greater than” instead of “Less than” in the New Formatting Rule dialog box. Then, follow the same steps for selecting the formatting style you want.
Can I apply this formatting rule to text or dates?
Yes, you can. The conditional formatting in Excel works with any type of data, not just numerical values. Simply select the cells you want to format, then choose a formatting rule that applies to your specific data type.
Can I preview the formatting rule before applying it?
Yes, you can. In the New Formatting Rule dialog box, there is a Preview box that shows you how your formatting will look based on the parameters you set. Use this preview to test out different formatting styles and see which one works best for your data.
How do I remove a formatting rule from a cell or range?
To remove a formatting rule from a cell or range, select the cell or range, then go to “Home” > “Conditional Formatting” > “Clear Rules” > “Clear Rules from Selected Cells”. This will remove all conditional formatting rules applied to the selected range.
Frequently Asked Questions
What is the custom number format to make negatives red?
Use #,##0;[Red]-#,##0 to show negatives in red with a minus sign, or #,##0;[Red](#,##0) to show them in red parentheses. The semicolon separates the positive section from the negative section, and [Red] colors the section it appears in. Enter it under Format Cells (Ctrl+1) > Number tab > Custom. It works the same as the comma style number format.
Should I use Format Cells or conditional formatting for red negatives?
Use the Format Cells Number tab (or a custom format) when you simply want every negative value to display in red — it is faster and travels with the cell when copied. Use conditional formatting when the rule needs to depend on other conditions, color entire rows, or change the cell fill color rather than the font.
How do I make negatives show in red parentheses instead of with a minus sign?
Pick the red-parentheses option under Format Cells > Number > Negative numbers, or use the custom code #,##0;[Red](#,##0). The parentheses are literal display characters and do not change the stored value — the number remains negative under the hood.
Can I use a color other than red in a custom format?
Yes. Excel’s named color codes include [Red], [Blue], [Green], [Magenta], [Cyan], [Yellow], [White], and [Black], plus [ColorN] (1–56) for the legacy palette — for example #,##0;[Blue]-#,##0. For richer choices than the eight named colors, set up a color-coding rule with conditional formatting.
Does making a number red change its value?
No. Coloring negatives red is purely cosmetic — the cell still holds a negative number and behaves normally in formulas. If you actually want to convert signs, that is a separate task covered in make Excel numbers negative and change negative numbers to positive.
How do I remove the red negative formatting later?
If you applied a custom number format, select the cells, press Ctrl+1, and switch the Category back to General or another format. If you used a rule, follow remove conditional formatting to clear it from the selected cells.
