Calculated Conditional Formatting


The Excel HelpDesk Support team received the following request for help:

Problem – Display values for calculated values in a Spreadsheet

I am trying to come up with a formula where if the sum comes to 0 in the next cell I want it to show Low, but if it comes to 1 then it should show Med or 2 or higher then it should show High

The following sample table of calculations was received to demonstrate the request


Use IF statement to create the formula result

First step we will create an IF statement formula to automatically populate the column “STEP 5” with the value “Low”, “Medium” or “High”. Using the values provided by the request we have the following rules for the IF statement

0 – Low
1 – Medium
2+ – High

We will also include in this formula a case for any value other than those specified in the request, in these cases we will display nothing for the result. So in a sample spreadsheet we create the formula.

if statement example

Include Conditional Formatting to highlight the entries

Now to highlight the entries as they are made we will include specific conditional formats for each of these values. Start by selecting the entire column “STEP 5”, then select Conditional Formatting. See the screen display below for the specific format options to select.




If you would like a copy of a sample spreadsheet that includes this formula and conditional formatting click on the following download link

Download Sample File

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.