Shopping

Highlight a price below your target with conditional formatting

Highlight a price below your target with conditional formatting in Excel and Google Sheets, using one custom formula and a made-up weekly price list.

Editorial illustration for Highlight a price below your target with conditional formatting

To highlight a price at or below your target, put the target in one cell, then add a conditional formatting rule with a custom formula such as =$B2<=$E$1, applied to the price column. In both Excel and Google Sheets the cell turns green whenever the price falls to or under the target. The dollar signs matter: they lock the target cell while the row reference moves down.

What is the setup?

Say column A holds the week, column B holds the price of an electric kettle, and cell E1 holds your target, ₹1,200, with a label in D1. The price list is below, with invented numbers.

WeekPriceAt or under ₹1,200?
Week 1₹1,349No
Week 2₹1,299No
Week 3₹1,249No
Week 4₹1,199Yes
Week 5₹1,229No
Week 6₹1,149Yes
Week 7₹1,199Yes
Week 8₹1,259No

Three weeks are at or below the target: ₹1,199, ₹1,149 and ₹1,199. A cell at exactly ₹1,200 would also count, because the rule uses less than or equal to, not less than.

How do you do it in Excel?

  1. Select the price cells, B2 to B9.
  2. On the Home tab, click Conditional Formatting, then New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. In the formula box, type =$B2<=$E$1. Excel treats the formula as written for the first selected cell, so start from B2.
  5. Click Format, choose a green fill on the Fill tab, and press OK twice.

Menu names can differ slightly between versions, but the idea is the same: a rule based on a formula that returns true or false for each cell.

How do you do it in Google Sheets?

  1. Select B2 to B9.
  2. Open the Format menu and choose Conditional formatting.
  3. Under Format cells if, choose Custom formula is.
  4. Enter =$B2<=$E$1.
  5. Pick a green fill under Formatting style, then click Done.

If you prefer not to write a formula, Sheets also offers Less than or equal to in the same list, and you can type =$E$1 as the value. The custom formula is more flexible, because it can look at other columns too.

Why do the dollar signs matter?

In =$B2<=$E$1, the $B locks the column, the 2 is free to move down the rows, and $E$1 pins the target to one cell. If you drop the dollar signs from E1 and write =B2<=E1, the rule will compare B3 with E2, B4 with E3 and so on, and the colours will be wrong. If the highlight looks random, check this first.

Can you highlight only the lowest price?

Yes. Use =B2=MIN($B$2:$B$9) as the rule instead. In this list, only Week 6 at ₹1,149 turns green. You can have both rules at once, with different colours, such as green for at or under target and bold for the lowest so far.

What if you track several products?

Give each product its own target in a column and refer to it by row. If column B holds prices and column C holds targets, the rule becomes =$B2<=$C2, applied to B2 to B30. Each row then compares itself with its own target.

When the product is bought, move the actual price into your expense records, and if you use SpendixAI, a quick hand entry with the paid amount keeps your shopping total accurate. Then clear the highlight rule or archive the sheet so you are not watching a product you already own.

  • Target in one cell, locked with dollar signs.
  • Rule uses less than or equal to.
  • Prices are numbers.
  • Review the colour once a week.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports