Array Formulas Excel Basics & Conditional Formatting

Most Excel tutorials teach array formulas as a magic trick: press CTRL+SHIFT+ENTER, watch the curly braces appear, move on. That approach produces people who can copy a formula but have no idea why it works or what to do when it breaks. I've been building spreadsheets professionally for close to twenty years, and the damage done by recipe-based formula teaching is real, measurable, and fixable.

This guide covers array formulas and conditional formatting in Excel: how to write a working array formula, carry it into a formula-based conditional formatting rule, and avoid the failure modes that don't show up in most beginner walkthroughs. By the end, you'll have a CF rule that highlights cells dynamically based on array logic, and you'll understand what it's actually doing.

---

What You'll Build — and the One Array Formula Concept to Nail First

The finished result is a conditional formatting rule that doesn't just check one cell at a time. It evaluates an entire range using array logic and highlights the right cells automatically. Before you build that, there's one concept gap worth closing, because skipping it causes a specific kind of confusion later.

Legacy Array Formulas vs. Dynamic Array Formulas: What Changed in Microsoft 365

In older versions of Excel, an array formula had to be confirmed with CTRL+SHIFT+ENTER. Excel would wrap the result in curly braces — {=SUM(A2:A10*B2:B10)} — to signal that it was processing multiple values at once. These are called legacy array formulas, and they're still present in Excel 2019 and earlier.

Microsoft 365 changed this. Dynamic arrays mean Excel now handles multi-value calculations automatically with just the Enter key. The result can spill across adjacent cells into what's called a spill range. You don't type the curly braces, and you don't press CTRL+SHIFT+ENTER. The formula just works.

Teaching CTRL+SHIFT+ENTER to new users is only justified if they're on a pre-365 version of Excel. If you're on Microsoft 365, learn the dynamic approach first.

Why Conditional Formatting Treats Array Formulas Differently Than You'd Expect

Here's the part most tutorials skip. Conditional formatting doesn't evaluate a range of results — it evaluates a single Boolean outcome (TRUE or FALSE) for each cell being checked. That constraint matters enormously once you get to Step 3, so hold onto it.

---

Step 1: Write Your Array Formula and Confirm It Works Before Adding Any Rule

Don't skip this step to get to the formatting faster. A CF rule built on a broken formula fails silently, with no error message to guide you.

Say you're working with a simple sales table. Column A has rep names, Column B has units sold, Column C has unit price. You want total revenue without a helper column.

Entering a Legacy Array Formula with CTRL+SHIFT+ENTER

In a blank cell, type =SUM(B2:B10*C2:C10). Then, before pressing Enter, hold CTRL+SHIFT+ENTER together. Excel wraps the formula in curly braces: {=SUM(B2:B10*C2:C10)}. If you see those braces in the formula bar, it worked. If you typed them manually, it didn't. Excel ignores hand-typed curly braces entirely.

In Microsoft 365, skip all of that. Type the same formula, press Enter, done. The formula handles the rest. If results spill across multiple cells, that's the dynamic array behavior working as designed.

If you're newer to how formulas work generally, the Excel Formulas and Functions for Beginners guide covers the foundational mechanics before you go further.

---

Step 2: Build a Formula-Based Conditional Formatting Rule Using Array Logic

With a working formula confirmed, you can carry that logic into a conditional formatting rule. The key word is "carry." You're not inserting the formula as-is — you're rewriting its logic as a Boolean test that CF can evaluate one cell at a time.

Using COUNTIF to Highlight Duplicate Values Across a Range

1. Select the cell range you want to format — say, A2:A20.
2. Go to Home → Conditional Formatting → New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter: =COUNTIF($A$2:$A$20,A2)>1
5. Set your fill color and click OK.

For each cell in your range, Excel checks how many times that value appears. If it appears more than once, the result is TRUE and the cell highlights. Excel runs that check individually for every row in your selection.

Why Your CF Formula Must Reference the First Cell — Not the Whole Range

The formula references A2 (not $A$2) for the second argument. That's intentional. When you apply conditional formatting to a multi-cell range, CF adjusts that relative reference for each cell it evaluates. Locking it with a dollar sign means every cell checks against A2 only. The whole rule misfires, quietly, with no error message telling you why.

This is the mistake I made during a management presentation early in my career — a formula that looked correct and produced wrong results because of one misplaced anchor.

Understanding relative vs. absolute references in Excel before building CF rules will save you real time.

---

Step 3: Handle the SPILL Error and Other Failures When Array Formulas Meet Conditional Formatting

This is the step where most beginner walkthroughs stop being honest.

If you're on Microsoft 365 and you try to paste a dynamic array formula (one that spills) directly into a conditional formatting rule, it won't work. CF needs a single TRUE or FALSE result per cell. A formula that returns a spill range of multiple values can't satisfy that requirement. You'll either get a SPILL error or, worse, a rule that applies to no cells at all with no explanation.

Dynamic array formulas that produce a spill range cannot be used directly inside a conditional formatting rule. Rewrite them as single-cell Boolean tests first.

The COUNTIF example in Step 2 is exactly this kind of rewrite: it evaluates one cell at a time, returns TRUE or FALSE, and CF handles the rest.

One more thing worth flagging: IFERROR catches every error type, not just #N/A. Inside an array formula, it can silently mask a logic mistake you haven't caught yet. Get the formula returning the right results first. Use IFERROR as a finishing step, not a debugging shortcut.

---

Common Mistakes With Array Formulas and Conditional Formatting — and How to Catch Them Fast

Three failures show up constantly in Excel beginner forums like MrExcel and the Microsoft Community. All three are fixable in under a minute once you know what to look for.

If you take one thing from this article: build the formula first, confirm it returns the correct Boolean result in a regular cell, then carry that logic into the conditional formatting rule. In that order, every time.

---