make negative numbers red in Excel
How to make negative numbers red in Excel

There are times that you want to differentiate positive numbers from negative numbers in an Excel spreadsheet. For instance, you may want to change all negative numbers into red to prompt you about their state.

Also, you may want to do this just for the negative numbers to stand out from the positive numbers for easier identification.

A practical use case of this will be a situation where you are using a spreadsheet to track the progress of something. You can therefore decide that any of the quantities of interest that goes into the negative following a transaction should change color to red so as to alert you to probably restock.

You can equally represent the negative numbers with other colors or any color that you want but we are going to work with the color red for the examples in this post.

There are quite a number of ways this can be achieved and you will learn about all the possible ways in this basic excel tutorial. Some of the methods you will learn are:

  • Using conditional formatting to change color of negative numbers
  • How to use number formatting to make negative numbers red in Excel

How to use conditional formatting to make negative numbers red in excel

In order to use conditional formatting to change the color of negative numbers to red color, follow the steps below:

  • Press CTRL + A to select everything on your Excel worksheet
  • If you are interested in applying it to just a single row or column then go ahead and select just that part
  • Make sure the home tab is active or click on the home tab if you are not sure about that
  • Look for conditional formatting around the middle in the styles area
  • Click on Conditional formatting to bring up the dropdown
  • On the dropdown, click Highlight cells rules
  • Then click on Less than on the next dropdown
  • A small popup comes up 
  • Type the number zero (0) into the “format cells that are less than” textbox. This means that the rule that you are going to set will be applied to all numbers less than zero and since every number less than zero is a negative number, you get the outcome that you are looking for
  • On the next textbox, click the dropdown arrow
  • Then choose red text and click OK
  • This will automatically convert all negative numbers within the selected region of your spreadsheet into red text

Instead of making the text just red color, you can also choose to highlight the cells containing the negative numbers as well. There is an option to highlight these cells red.

To achieve this, follow the steps above until you get to the part where you chose red text. This time around, choose Light red fill with dark red text. You can then go ahead and press OK.

techpady virtual classroom

Digital School Academy

Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials

How to remove conditional formatting in Excel

There may be parts of your spreadsheet that you wouldn’t want the conditional formatting you set to be applied to. You may also want to just remove conditional formatting from some blank cells in your document. So, let’s take a look at how you can achieve either of these.

  • If you want to remove conditional formatting from just some parts like the blank cells, then go ahead to select all of those cells
  • Click on the home tab
  • Click on conditional formatting
  • Look for clear rules on the dropdown that comes up
  • Then chose either:
    • Clear Rules from selected cells if you have selected some cells
    • Clear rules from entire sheet if you want to get rid of all the conditional formatting on the entire page

Make negative numbers red using Excel Number format

Excel by default comes with a numbering format. One of these formats presents all negative numbers in red. Every Excel spreadsheet by default have data presented in it set to the general format in order to accommodate any data type.

So, if you have a specific column or rows that should accept only numbers then you can set the format or data type to numbers only.

This is where you can choose the special red color presentation for all negative numbers. So, if you have a column or row for which you want all negative numbers to be red in color, then follow the steps below:

  • Select the column or row of interest
  • Click on the home tab
  • Look for the numbers section on the ribbon
  • The is a small icon at the button right of the numbers section
  • Click on that icon to expand the numbers section
  • A popup shows with title Format cells
  • Under the number tab, click on Number under category
  • You are presented with 5 options under negative numbers
  • The last option has a negative number in red color so that is what you are interested in.
  • Click on the last option
  • Then click on OK
  • This immediately turns all negative numbers in your spreadsheet to red color
Spread the love

Similar Posts

Leave a Reply

Your email address will not be published.