How to Fix “Formula Omits Adjacent Cells” Excel Error (2024)

The ‘Formula Omits Adjacent Cells’ is Excel’s way of saying you better recheck your formula for any missing or extra cells 🤚

You’d often see this error while running the SUM, AVERAGE, COUNT, and other mathematical or statistical functions in Excel.

What does it mean, and how can you fix it – we will see that in the guide below 👇

So continue reading and download our free sample workbook here.

Why this error appears

What is the ‘Formula Omits Adjacent Cells’ error, and why does it occur? Let me show this to you through the example below 🙈

In the image below we are adding up two lists of numbers.

Each of these rows has six numbers 6️⃣

For the first list, we are summing up all the numbers.

For the second list, we are summing up only the first five numbers 5️⃣

Did you see that yellow error icon and a green flag to the top left of the cell containing the sum for List 2 👀

Also, note that there’s no such sign or flag on the SUM for List 1.

Hover your cursor around the yellow error icon to see which error is this.

The “Formula omits adjacent cells” error 💀

What causes this error? When apparently, everything seems fine 🤔

This error is caused because we summed only five cells (B2:B6) for list 2.

So, we have omitted Cell B7 from the SUM formula for List 2. Whereas Cell B7 also contains a number. As all the numbers in List (A1:A7) are summed up – we have no such error there.

With that said, Excel thinks you have mistakenly left out Cell B7. That’s when Excel tells you that your formula SUM(B2:B6) omits adjacent cells (B7) 💁‍♀️

There are several ways how you can fix this error in Excel. We will cover them all now.

Fix #1: Change formulas to include adjacent cells

The first and easiest way to get rid of this error is to adjust your formulas:

You can change the SUM for List 2 from SUM (B2:B6) to SUM (B2:B7).

With this, technically, you have removed the error Excel thought you made 😎

So the Error signs (the green flag and yellow icon) are also removed.

Fix #2: Remove unused values

If you do not want to include Cell B7 in your calculation, remove its value from List 2.

We have only deleted the extra value causing the error (10 in Cell B7). And see, no more error signs or green flags 🚩

However, these are not some very practical ways to get rid of this error. You might not want to include B7 in your calculation. Maybe you genuinely want to sum the cells B2:B6 only.

Alternatively, you might want to retain the value in Cell B7, and deleting it might cause you to lose some data you never wanted to lose 😵

Don’t worry – here are other ways how you can remove this error in Excel without having to change your formulas or dataset.

Fix #3: Ignore the Error to remove the green triangle

If your formulas are all set and the error has no relevance to you, you can choose to ignore it. To ignore this error and get rid of the error icons surrounding the relevant cell:

1. Select the cell where the error occurs (Cell B9).

The yellow error sign will appear.

2. Click on the drop-down menu icon next to this error sign.

The following list will appear 📝

3. Click on Ignore Error from this list as shown above.

And the error icons will go away.

This is your way of telling Excel, “Thanks for your input, but I know my job!” 😝

If you have multiple such errors in other cells of your sheet, you’d have to ignore them for each cell showing this error.

Fix #4: Permanently remove the green triangle for this error

If you are done ticking off the error for each cell, root it out once and for all ❌

To permanently remove the green triangle caused by the ‘Formula omits Adjacent cells error’ follow these steps:

1. Go to the File tab > Options.
2. Under the dialog box for Excel Options, go to Formulas from the left pane.

3. Under the section Error checking rules, uncheck the box for Formulas that omit cells in a region.

Excel will now no longer pose the Formula omits adjacent cells error 💪

That’s it – Now what?

This tutorial is a complete guide on what is the ‘Formula omits adjacent cells’ error and how can you fix it in Excel.

Though a subtle one, this error can disturb your Excel jobs with nasty green flags topping the relevant cells of your sheet. If you liked reading about this error and ways to get rid of it – you’d love to learn more about Excel 🏹

To start, I suggest you go with the key functions of Excel. Like the VLOOKUP, SUMIF, and IF functions.

How to learn them? Click here and enroll in my 30-minute free email course to get your hands on the best Excel training on the internet.

Other resources

The list of Excel errors is just too long.

Learn about them all (what causes them and how can you fix them) here and go spread-sheeting unstoppably.