Contents
1. Introduction
Color coding of cells based on a condition is very useful in Excel for highlighting areas and data points. It is one of the top tools in the arsenal of an Excel expert in making the spreadsheet look snazzy and convey crucial information. In this article, we show a simple example of how to apply conditional formatting to cells based on values.
2. The Final Result
The sample spreadsheet we are using is the monthly sales figures and shown for each month. The screenshot below shows the cells highlighted in different colors depending on the threshold specified. Let us see how we can achieve this starting from a plain sheet.
3. The Plain Spreadsheet
Here is how the spreadsheet looks without any formatting applied. And without the threshold and summarizing cells too.
4. Use SUMIF for Summary
Let us enter a couple of summarizing cells using the SUMIF formula so we can verify the results. We use SUMIF to compute the total of sales figures which exceed the threshold (of $600). The formula for the cell labelled Sum of Greater is shown below.
=SUMIF(B2:B13,"> 600")
Instead of entering the threshold directly into the formula, wouldn’t it be nice if we can enter it in a cell so we can change it easily?
Let’s do it.
The updated formula is shown below with the threshold value coming from cell D3. We need to use “&” to tell Excel to append the value from cell D3 to the expression “>“.
=SUMIF(B2:B13,">" & D3)
Likewise, for computing the sum of values less than or equal to the threshold value, we use the following formula:
=SUMIF(B2:B13,"<="&D3)
5. Applying Conditional Formatting
Let us now apply conditional formatting to the Income column so we can highlight the values greater than the threshold.
Select the column of values for which you want to apply the formatting. Click over to the Home tab and select Conditional Formatting >> Highlight Cell Rules >> Greater Than as shown:
Once the Greater Than window pops up, select the cell that contains the threshold value. Your data should now be highlighted with the selected style. You can of course choose to configure the style to your taste. Once this process is completed, here is what the sheet looks like.
6. Less-Than-or-Equal-To condition
You could repeat the same process (with some caveats) to apply a different style to values that are less than the threshold value too. However the “less than or equal to condition” is not present on the Highlight Cell Rules menu. Here is how you can add the condition.
Select the column values as before and click Conditional Formatting >> Manage Rules.
In the Rules Manager dialog, click New Rule.
Select Format only cells that contain and adjust the other parameters in the dialog to match your condition and select the cell containing the threshold value (=$D$3 in the pic below). Note that the format is not yet selected for the rule.
Now click Format to set up the format for the rule. Configure the style to whatever you desire and click OK twice to add the rule and the style.
Here is what the Rules Manager window looks like now. Click Apply (or OK) to apply your rules to the sheet.
Now our spreadsheet now looks like this. Note that we have added a cell containing the sum of both less than and greater than values as a form of double-checking the calculation.
Summary
Conditional formatting is a very useful tool in Excel to highlight important data and making it standout. Using SUMIF formula it is possible to add further verification to the spreadsheet.