Excel: How to change the background color based on the cell value

Especially for huge MS Excel tables, it is helpful to mark cells with colors, if they contain a certain value. This is possible by using the “If-function”. In this article we will show you how.

Changing the background color of Excel cells using the “If – function”

The easiest way to explain how changing the colour of Excel cells via the if-function works is by using an example. So, let's imagine you're recording accounting transactions. To immediately spot negative values, you can highlight these cells using the if-function.

• As a starting point, prepare a list of all your accounting transactions. Mark the cells you want to highlight with colors according to their value.
• After that, click on the tab named “Home” and find the button “Conditional formatting”.

• This function allows you to change colors of cells based on their value: You can choose between “Less than...”, “Greater than...”, “Between...” and many more options.
• For our example, we want to mark cells with a value less than zero with a red background color. Therefore, click on “Highlight cell rules” and “Less than”.

• Chose “0” in the field “Format cells that are less than” and decide for a formatting option. For our example “Light red fill with dark red text” is most appropriate.

• Click on “OK”. MS Excel will format the cells you marked in the beginning immediately.
• Now you are able to see days with negative revenues quickly.

• You can repeat this procedure for other formatting options. For example, use the button “Between than” to highlight days with earnings high enough to meet your long-term business goals.