Blogger and freelance writer from the northeast coast of England.
Setting Up the Spreadsheet
For those of us without a disposition to tech, the term conditional formatting may sound daunting. In reality, conditional formatting is an approachable and useful function. Getting a grip on how to use it is a great way to take your Microsoft Excel skills up a notch.
Using an imaginary scenario to demonstrate conditional formatting, let us take the case of Barney, who runs a small business delivering sandwiches to local offices. Barney’s customers square up with him at the end of the week, and he enters the takings into an Excel sheet every Friday night. His customers are sometimes out at meetings, or otherwise absent, and this is reflected in fluctuations in his takings. Sometimes Barney does not bring in enough money to cover his outgoings.
To make sure he always knows when this happens, Barney uses conditional formatting to give him a ‘red alert’ if his outgoings exceed his income. What this means is that the cell displaying his total income after outgoings have been deducted will turn red if the value slips below zero.
The graphic above shows Barney’s Excel sheet from one office before he has entered any values. Column C is a list of his customers, and column D shows what each customer spent that week. Column E lists his weekly expenses, and column F shows those amounts. A solitary cell in column G will show Barney's balance after he has entered details of his income and outgoings, and this is the cell that will have a condition applied to show the red alert.
If you would like to follow this process, which breaks down the formula and conditions into easy-to-understand paragraphs, set up your Excel sheet as I have done in the graphic (you needn’t bother with colouring the columns).
Next we see that Barney has entered values for the week; how much his customers spent, and what stock he bought. With the data entry part of the project completed, it's time to let automation do its stuff. If you are following this operation, here's what to do.
The first step is to apply AutoSum to columns D and F so that income and outgoings are totted up. Type in the relevant values for income and outgoings, and select the group of cells from D3 to D18. Click on the AutoSum icon, which lies in the Home tab, and is in the form of the Greek capital letter Sigma (Σ.). AutoSum automatically adds up the values you entered, and the total appears in cell D18. Repeat the process with the values in column F.
Adding a Formula
The next step is to add a formula to cell G18, that will deduct the total of column F from the total of column D. To do this, select cell G18, and enter the following formula, either directly into the cell, or in the formula bar:
If we break it down, this formula is quite simple. It tells us that for that particular cell, G18 in this case, the value is equivalent to (=) the value of cell D18 minus (-) the value of cell F18.
If you are using the values above, then after you add the formula to cell G18, it should display 20, and you are ready to apply conditional formatting.
To apply a condition to a cell, in this case, G18, select the Conditional Formatting option on the Home tab. In the column of Rule Type panes, select the following: Highlight Cells With, then Cell Value and Less Than. Type 0 into the bottom pane of that group. This formatting will now be applied every time cell G28 shows a value below zero.
To select the type of formatting you wish to apply, go to the Format With pane, which sits alone below the Rule Type panes. To adhere to what is indicated in the title of this article, choose a red fill with white text, and click Done. With the condition in place, tinker with the values on your sheet to see the cell change from green to red and back again to make sure the condition is applied correctly. Job done.
Into the Red
Just as Barney seemed to be doing well, Covid 19 struck, and, due to someone in the office testing positive for the virus, everyone except Mork had to isolate at home (there's something odd about that guy's metabolism). This blow came just after Barney had bought stock, so it had a huge impact on his takings for that week, and his outgoings exceeded his income. To make him aware of this, the red alert condition he had set up was triggered, drawing his attention to the cell showing a defecit of 68.
Add a Second Condition
So Barney was alerted to a negative value thanks to conditional formatting. Apropos of exploring this useful function further, and gaining a better understanding of it, let us say Barney added a second condition to cell G18, this time to show positive values.
To do this, Barney highlighted the appropriate cell and went to Home/Conditional Formatting. As there is already a condition attached to that cell, the aforementioned red alert, the menu appeared showing the existing condition for that cell. Barney clicked on Add, and a replica of the original menu opened. This time Barney chose:
- cell value
- is greater than
So this condition will apply every time that cell contains a positive value.
As this new condition applies when the cell value is positive, or in the black as you might say, Barney chose a black fill with white text for the new condition.
So a single cell has had a formula and two conditions applied to it; an automatic deduction of expenditure from income, a red display for negative values, and a black fill for positive values. There's a lot going on over at cell G18!
With conditions applied for positive and negative values, the only value that retains its original fill colour is zero, which stays green, just like on a roulette wheel. If you want to check this out, sting Barney with a fuel bill to the value of 22. That should bring up the green zero.
This is a very basic example of conditional formatting, but it does, I hope, demonstrate how it works and when it can be used.