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”
Time needed: 3 minutes.
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.
- Prepare a list
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.
- Choose “Conditional formatting”
After that, click on the tab named “Home” and find the button “Conditional formatting”.
- Select your formatting features
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”.
- Enter formatting conditions
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.
- Confirm your selection
Click on “OK”. MS Excel will format the cells you marked in the beginning immediately.
- Check the result
Now you are able to see days with negative revenues quickly.
- Repeat for other formatting options
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.