Fix an inconsistent formula

Troubleshoot why it's inconsistent

1. Show Formulas
   Select Formulas > Show Formulas.
2. Doing that lets you see the formulas in all cells, instead of the calculated results.
3. Compare the inconsistent formula with ones before and after it, and correct any unintentional inconsistencies.
4. Hide Formulas when you're done.
   When you're done, select Formulas > Show Formulas to show the calculated results of all cells.
5. If that doesn't work, select a cell nearby that doesn't have the problem.
6. Trace Precedents
   Select Formulas > Trace Precedents.
7. Select the cell that does have the problem.
8. Trace Precedents
   Select Formulas > Trace Precedents.
9. Compare the blue arrows or ranges in blue, and then correct any problems with the inconsistent formula.
10. Remove Arrows when you're done.
    Select Formulas > Remove Arrows.

More solutions

Make the inconsistent formula the consistent one

Select the cell with the inconsistent formula, and then hold down the SHIFT key while pressing one of your arrow keys. This selects the inconsistent cell along with others. Then do one of the following:

• If you selected cells below, press CTRL+D to fill the formula down.
• If you selected cells above, select Home > Fill > Up to fill the formula up.
• If you selected cells to the right, press CTRL+R to fill the formula to the right.
• If you selected cells to the left, select Home > Fill > Left to fill the formula to the left.

If you have more cells that need the formula, repeat the above process going in the other direction.

Fill the cell with a nearby formula to correct it

1. Select the cell with the problem.
2. Select this button:   and then select either Copy Formula from Above or Copy Formula from Left.
3. If that doesn't work and you need the formula from the cell below, select Home > Fill > Up.
4. If you need the formula from the cell to the right, select Home > Fill > Left.

Not an error?

If the formula is not an error, you can choose to ignore it:

1. Check for errors
   Select Formulas > Error Checking
2. Select Ignore Error.
3. Select OK or Next to go to the next error.

An error indicator appears when the formula does not match the pattern of other formulas near it. This does not always mean that the formula is wrong. If the formula is wrong, making the cell reference consistent often solves the problem.

For example, to multiply column A by column B, the formulas are A1*B1, A2*B2, A3*B3, and so on. If the next formula after A3*B3 is A4*B2, Excel identifies it as an inconsistent formula, because to continue the pattern, the formula should be A4*B4.

1. Select the cell that contains the error indicator and look at the Formula Bar to verify that cell references are correct.
2. Select the arrow next to the button that appears.
   
   The shortcut menu shows the options available to resolve this warning.
3. Do any of the following: