Formula Omits Adjacent Cells Error in Excel: How to Fix It

Ignore the Excel error or update your formula to include the cells

by Claire Moraa
Claire Moraa
Claire Moraa
Author
Claire likes to think she's got a knack for solving problems and improving the quality of life for those around her. Driven by the forces of rationality, curiosity,... read more
Reviewed by Alex Serban
Alex Serban
Alex Serban
Windows Server & Networking Expert
After moving away from the corporate work-style, Alex has found rewards in a lifestyle of constant analysis, team coordination and pestering his colleagues. Holding an MCSA Windows Server... read more
Affiliate Disclosure
  • The error formula omits adjacent cells means that Excel cannot calculate the formula because it is missing something. 
  • Excel tries to calculate a formula but only takes into account the values from one cell, ignoring other cells with values needed for the calculation.
  • If this happens, it means that you have left out a cell on one side of your formula and need to clean up your sheet.

XINSTALL BY CLICKING THE DOWNLOAD FILE
To fix various PC problems, we recommend Restoro PC Repair Tool:
This software will repair common computer errors, protect you from file loss, malware, hardware failure and optimize your PC for maximum performance. Fix PC issues and remove viruses now in 3 easy steps:

  1. Download Restoro PC Repair Tool that comes with Patented Technologies (patent available here).
  2. Click Start Scan to find Windows issues that could be causing PC problems.
  3. Click Repair All to fix issues affecting your computer's security and performance
  • Restoro has been downloaded by 0 readers this month.

If you get an error message saying that your formula omits adjacent cells when you try to enter it, your Excel spreadsheet has probably been modified.

The problem is that when such an error occurs, it can mess up your entire worksheet. You may have to redo all of your calculations from scratch or worse, start over, especially if Excel fails to respond when saving.

What does the error formula omits adjacent cells mean in excel?

The error formula omits adjacent cells means that there is an error in the formula in the cell. When you have an array of formulas, Excel assumes that the data for all are continuous and not separated by other data.

If your data is separated by other data or if some cells contain blank values, then Excel will not be able to evaluate the formula correctly. But why does this happen? Below are some possible reasons:

  • Cell has no data – The error function in Excel only calculates values for cells where there is data present. If a cell has no data, then it will not be included in the calculation process.
  • The cell no longer exists – It is possible that the formula you may be referencing belongs to a cell that has been deleted or renamed.
  • Range has been changed – The formula may be referencing a dynamic range that no longer exists due to changes in the spreadsheet.
  • Blank cells in between – This error occurs when there are empty cells between each cell that you want to include in your formula. 

What can I do if the Excel error formula omits adjacent cells?

Check off the following first before you proceed:

  • First, check to make sure that your formula includes all of the necessary parts.
  • If you are working with a small data set, try to identify any empty cells and delete them.

1. Uncheck formulas that omit cells

  1. Launch your Excel sheet and then click on File.
  2. Navigate to Options and then select Formulas.
  3. Look for Error checking rules and uncheck Formulas which omit cells in a region.
  4. Click OK.

2. Switch from absolute to relative reference

  1. Open the Excel sheet and select the cell that contains the formula.
  2. Navigate to the formula bar at the top and press F4 until you get the relative reference option.

Absolute and relative references in Excel formulas are two ways to refer to cells in your spreadsheet. They both have their purpose, and sometimes you may find yourself using them interchangeably.

Absolute cell references are the default in Excel. However, there are times when using an absolute reference is not the best option. This type of notation is not always intuitive because it does not take into account the position of the cell that contains the formula.

3. Update the formula to include the cells

When you update the formula to include the cells that you want to reference, Excel can properly evaluate it and display its result properly.

The caveat to this method is that if you have a very large spreadsheet with many formulas, then it can be difficult to track down which cell is causing the problem.

If nothing seems to work, you can opt to ignore the error or seek help from the help task pane in Excel. The built-in tool diagnoses some problems and may be able to recommend a solution.

Excel errors are in no shortage as you can expect to encounter a few hiccups when you’re working with sheets and formulas. We have crafted a dedicated article to help you in case you want to recover a corrupted Excel file so that you don’t have to lose all your work.

In other instances, you may be unable to type in Excel, but we know what you can do to keep things moving as you shall see in our expert guide.

Let us know of any other tricks you used if you were in a similar predicament in the comment section below.

Still having issues? Fix them with this tool:

SPONSORED

If the advices above haven't solved your issue, your PC may experience deeper Windows problems. We recommend downloading this PC Repair tool (rated Great on TrustPilot.com) to easily address them. After installation, simply click the Start Scan button and then press on Repair All.

This article covers:Topics: