This does not compute!: Excel error messages

Q. I just opened my workbook and saw a bunch of #REF errors. What happened?

Edit linksA. #REF means that a Formula refers to a worksheet, column, row or workbook that Excel can’t find. Moved, renamed? The most likely explanation for your situation is that a workbook to which this one refers has been moved to a different location. Something to try is to click on the Edit Links button in the Data tab. It might be easier to recognize what changed by seeing them all listed. You can fix the broken links from this dialogue box.

If your workbook is not linked anywhere outside itself, see if you can tell if any worksheets have been deleted. These are the most common reasons to see a #REF.

Q. I occasionally get a #NAME error in my formulas. What does that mean?

A. When Excel gives you a #NAME error, it is telling you that you have specified a named cell or named range of cells to use in a formula. If you don’t know what that is or haven’t defined any named ranges, then you probably have a typo or missing punctuation mark in your formula. For example, if you intended to write SUM(A1:A3), but dropped the Typocolon, so it actually read SUM(A1A3), Excel is looking for a named range called A1A3. SUMA1A3 would also give you the same error.

When you see a #NAME error, first look for typos. If you have actually defined a named range, then click the dropdown arrow in the name box to see if Excel can identify it.

Q. I keep typing in a formula, but it doesn’t calculate anything. It just shows the formula. What am I doing wrong?

A. There are two explanations. First, the column you’re typing in may have been formatted to be text. Just look in the Number Format field in the Number group on the Change textHome tab. If it says Text, change it to General or Numbers.

The other reason might be that you have toggled Show Formulas to on. The shortcut to turn Show Formulas on is Ctrl+`. The grave or backward quote mark is found on the same key as ~ or tilde. If you were trying to use Ctrl+1, or thought you had the Shift key down when you pressed 1 to get the exclamation point, but instead had Ctrl pressed, you could have accidentally pressed the key just to the left of that 1 key. To toggle it off, press Ctrl+` or click on the Show Formulas button on the Formulas tab in the Formula Auditing group.