Formatting Condition is a Calculated Value

Hello and welcome to this tutorial on how to use conditional formatting with a calculated value in Excel! Today, we’re going to dive into the following question: How do you conditionally format a cell when the condition is based on a calculated value? Let’s jump right in!

Video

Tutorial

Let’s work through the concepts sequentially in the following exercises.

Exercise 1: Calculating Values for Conditional Formatting

In this illustration, our goal is to highlight clearance items in a list, where clearance prices are based on a calculated value. If you’ve ever shopped at Costco, you might know that their prices have meaning. For instance:

• Prices ending in 99 cents are regular priced items.
• Prices ending in 97 cents are clearance items.

For example, consider the following items and prices:

We’d like to highlight the clearance items, which are those ending in 97 cents.

To highlight the clearance items, let’s start by writing some formulas to isolate the cents.

Step 1: Extracting Cents from Prices

Separate Dollars and Cents: Use the truncate (TRUNC) function to get the whole dollar amount.

For example, we can write the following formula into D12

TRUNC(C12)

Fill the formula down column D:

To get the cents, we can subtract the whole dollar amount from the original price, and round it to two decimals with the ROUND function.

For example, we can write the following formula into E12

=ROUND(C12-D12,2)

We can fill the formula down column E:

Check for Clearance Prices: Determine if the cents equal 97.

We can write the following formula into F12:

=E12=0.97

We can fill the formula down column F:

The TRUE values are clearance items.

With our formula columns complete, let’s add some conditional formatting.

Exercise 2: Applying Conditional Formatting

Now that we have our calculated values, let’s apply conditional formatting.

Step 1: Select the Cells

Highlight the Range:

Select the range of cells where you want to apply the formatting.

In this exercise, we’ll select the SKU column B.

Step 2: Conditional Formatting

Open Conditional Formatting:

• Home > Conditional Formatting > New Rule.

Choose Use a formula to determine which cells to format.

Enter a formula that points to the Clearance result cell in the same row as the active cell, ensuring it is a relative cell reference (without any dollar signs).

=F12

Choose your desired formatting options (e.g., fill color, font color) and click OK:

This applies conditional formatting to highlight clearance items based on our calculated values.

Exercise 3: Conditional Formatting Without Helper Columns

Can we apply conditional formatting directly, that is, without helper columns? Absolutely!

So, we have the SKU column and the Amount column, and want to apply the same conditional formatting on calculated values, but, we don’t want to include the intermediate helper columns. We want it to look more like this:

We can basically apply the same formula logic directly into conditional formatting.

Step 1: Select the Cells

Highlight the Range: Select the cells you want to format.

Step 2: Conditional Formatting

Open Conditional Formatting:

Home > Conditional Formatting > New Rule

Choose Use a formula to determine which cells to format.

Enter the formula directly:

=ROUND((C12-TRUNC(C12)),2) = 0.97

Adjust the cell references to ensure they are relative by removing the dollar signs.

Choose your formatting options and click OK.

Now, the cells will be highlighted based on the calculated value without needing helper columns.

Conclusion

And that’s it! We’ve successfully used conditional formatting with calculated values to highlight clearance items. By extracting and evaluating the cents from the prices, we can create dynamic, easy-to-read spreadsheets that automatically format based on your criteria.

I hope this helps you in your Excel journey. If you have any questions or comments, please leave them below. I’d love to hear from you!

Sample File

You can download the sample file.

FAQ

Q: Can I use this method for other types of price endings?

Yes, you can adjust the formula to check for different price endings by changing the value in the formula.

Q: Can I apply multiple conditional formats based on different calculated values?

Absolutely! You can add multiple rules in the Conditional Formatting menu, each with different formulas and formats.

Q: Can I apply this to other types of data, not just prices?

Yes, this method works for any data where conditional formatting is needed based on calculated values. Adjust the formulas as necessary.

Q: What if the conditional formatting isn’t applying correctly?

Double-check your formulas and cell references and make sure they are relative or absolute as needed for your specific situation. The reference should be relative to the active cell within the range.