Conditional Formatting Formula Generator

You know that sinking feeling when you sort a spreadsheet and all your careful colour-coding slides onto the wrong rows? One click on ‘Sort A–Z’ and the duplicates you flagged are suddenly pointing at innocent rows. The culprit is nearly always the formula: most conditional formatting rules track row positions instead of values. This conditional formatting formula generator writes rules that track values instead, so sorting, filtering or inserting rows can’t shake them loose. Paste in a few of your own rows first and watch the rule behave on your data before you commit to anything.

Pick one of three sort-proof templates, enter your range, and get a ready-to-paste conditional-formatting formula for Google Sheets — then preview it on your own sample grid before you open Sheets.

Your formula

Apply-to range:

  1. In Google Sheets, select the range .
  2. Click Format → Conditional formatting.
  3. Under "Format rules", choose Custom formula is and paste:
  4. Pick a fill color and click Done.

Live preview

Paste a sample grid above to see which cells would highlight.

The preview runs the same logic as the generated formula on your sample grid. It does not parse Sheets formulas — it only previews the logic this tool generated.

Privacy: everything runs in your browser — nothing is uploaded.

What this conditional formatting formula generator does

Pick one of three templates, type in your apply-to range and key columns, and the tool writes the whole formula with every dollar sign in the right place — $A2 where the column has to stay put, $A$2:$A$50 where the lookup area mustn’t budge. You also get the exact range to select and the numbered steps for adding the rule in Sheets, so there’s no guesswork at the end.

The live preview is the bit you’ll actually love. Paste a few rows from your own sheet — headers first, tab-separated — and the tool runs the same logic as the generated formula on your sample, lighting up exactly the cells that would light up in Sheets. If the preview looks off, tweak the settings until it looks right, and only then touch your real sheet.

The three sort-proof templates

Two-key match highlights rows where a two-column pair turns up more than once. It uses COUNTIFS with fully absolute lookup ranges and row-relative criteria, so every row is judged on its own values — sorting the sheet can’t push the verdict onto the wrong row. Handy for duplicate order IDs, repeated name-and-date pairs, or any composite key you can think of.

Reference-row compare measures every row against a baseline row the formula finds by key with FILTER — never by row number. You name the key column, the value column, the baseline key (say, Baseline) and an operator, and each row gets compared against whatever value that baseline row holds right now. Sort, insert or delete rows as you please; the reference follows its key.

Cross-column duplicates highlights a whole row when its value pops up in another column. A single COUNTIF with a mixed reference checks each row’s key cell against the absolute search range. It’s the quickest way to catch IDs that appear on both of two lists, or SKUs from one column leaking into another.

Format a cell based on another cell

How to use the conditional formatting formula generator

  1. Choose the template that fits your job — two-key match, reference-row compare, or cross-column duplicates.
  2. Type the apply-to range exactly as it appears in Sheets, for example A2:D50. The top-left cell anchors the formula.
  3. Enter the key column letters and make sure they sit inside your range; the tool warns you on the spot if one falls outside.
  4. For reference-row compare, add the baseline key value and pick an operator such as > or <>.
  5. Paste a sample grid with headers — or press Load example for a ready-made demo — and check the live preview.
  6. Copy the formula and the range, then follow the numbered steps under the formula to add the rule in Sheets.

Worked example: highlight duplicates in duplicate order–SKU pairs

Picture an orders table in A2:D7 with the headers Order, SKU, Qty and Status. You want every row highlighted where the Order-plus-SKU pair shows up more than once. Choose the two-key match template, set the range to A2:D7 and the key columns to A,B, then paste the six data rows into the sample grid.

The tool generates =COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2)>1. The lookup ranges are fully absolute ($A$2:$A$7) so they never shift; the criteria are mixed ($A2, $B2) so each row checks its own pair. Paste it as a custom formula applied to A2:D7.

In the preview, four rows light up: rows 2 and 4 share the pair 1001 / A-10, and rows 3 and 6 share 1002 / B-20. Rows 5 and 7 stay plain because 1003 / C-30 and 1004 / D-40 each appear once. Sort the sheet any way you like — the same four logical rows keep their highlight, because the rule follows values, not positions.

Tips for reliable results

Keep the key columns inside the apply-to range — the generator simply won’t build a formula otherwise, and it tells you exactly which column is out of bounds. If your data starts below row 1, set the first data row so your header rows never get scored in the preview.

Google Sheets’ COUNTIF and COUNTIFS already ignore letter case, so A-10 matches a-10 in the real rule. The case-insensitive toggle only changes the preview, letting you rehearse exact-case matching before you commit.

The preview mirrors the generated logic — it can’t parse formulas you wrote yourself. If you hand-edit the formula in Sheets, paste the same settings back into the tool to re-check what the generated version would do.

Conditional formatting in Excel and Google Sheets

The generator is built for Google Sheets, and the same sort-proof rules work in Excel too. Copy the formula the generator gives you into a conditional formatting rule in either app and it behaves the same way.

More Sheets tools

If you also juggle formulas across tabs, the bulk reference locker keeps them organised, Count Dropdown Values Across Sheets tallies dropdown answers without fragile references, and Messy Date Cleaner turns pasted date chaos into sortable dates.

FAQs

Why do my CF rules break when I sort?

It almost always comes down to references that point at row positions instead of values: a rule written as =$C5>100 still points at row 5 after a sort, even though your data has moved on. These templates anchor the criteria to each row’s own cells ($C2-style mixed references) and find baselines by key with FILTER, so the logic travels with your values and sorting can’t rip it loose.

Does it work in Excel too?

The generated formulas use COUNTIFS, COUNTIF and FILTER, which Excel 365 and Excel 2021 support as well — so the two-key match and cross-column templates paste straight into Excel’s conditional formatting with the same anchoring. The reference-row compare relies on FILTER returning a single value, which behaves the same in current Excel versions; in older Excel without FILTER, wrap the lookup in INDEX/MATCH instead.

Does anything get uploaded?

Nope. The generator builds the formula and runs the preview entirely in JavaScript in your browser — your ranges, keys and sample data never leave the page.

How do I highlight duplicate values with conditional formatting?

Use the duplicate template in the generator and copy the formula it gives you into a conditional formatting rule, and every repeated value in the range is highlighted. The worked example on this page uses duplicate order and SKU pairs, and because the rule is sort-proof it keeps highlighting the right rows after you sort the sheet.

Can I base a conditional formatting rule on another cell?

Yes. Choose the cross-column duplicates template when the value you are checking lives in another column, or reference-row compare when each row is measured against a baseline row found by key. The generator builds the formula from those other cells, so the rule follows values rather than fixed positions.

Note: everything runs in your browser — nothing you paste into this conditional formatting formula generator is uploaded or stored.

Leave a Comment