Software

Debugging Excel formulas one cell at a time

If a formula refers to cells throughout your spreadsheet, double-checking each cell can be a slow, tedious process—unless you know this trick.

In his article “Use these handy tips to debug your Excel formulas,” Bob Johnson presents some invaluable lessons on using [F9] to troubleshoot spreadsheet problems, in addition to some keyboard shortcuts that every Excel user should learn and use. Today, we’ll show you yet another keyboard trick you can use to ferret out the bad data when one of your formulas returns incorrect or unexpected results: [Ctrl][. (That’s [Ctrl] plus the open square bracket key.)
Get valuable tips for using Excel, Word, and Access, all delivered straight to your inbox every Wednesday and Friday. Best of all, they’re absolutely free! Sign up for Microsoft Office Suite—one TechMail that will bring you power tips for working with documents, databases, worksheet functions, VBA, and much more.
Cycle through each cell referenced by your function
So how does the [Ctrl][ keyboard shortcut save you time and trouble? If you’re trying to confirm (or troubleshoot) the results returned by a given formula, [Ctrl][ puts you in “formula review mode” and saves you the trouble of navigating to each cell (or range of cells) referred to by your formula.

Instead, [Ctrl][ lets you “cycle” through each cell one a time so you can inspect the entries in those cells. Here’s how it works:
  1. Click the cell that contains the formula you want to debug.
  2. Press [Ctrl][.
  3. At this point, Excel will highlight every cell that’s referred to by your formula.
  4. The “current” cell will be the first cell referred to in your formula. You can inspect its contents in the Formula Bar and make any changes that are required.
  5. Press [Enter] to “cycle” to the next location (cell) in the batch of cells that Excel selected.
  6. Keep pressing [Enter] until you’ve inspected all the cells mentioned by your formula. When you reach the last cell, pressing [Enter] cycles you back to the top of the list of selected cells.
  7. To return to Excel’s normal editing mode, press one of the navigation keys (such as [Home], [End], or any of the arrow keys).

Let’s demonstrate how this feature works. Suppose you’ve entered an Excel formula in cell B1 that sums the values in two separate ranges, something like =SUM(A5:A10)+SUM(B5:B10). Click cell B1 once and press [Ctrl][. When you do, Excel will highlight A5:B10 and make cell A5 the “current” cell, as shown in Figure A.

Figure A
We entered =SUM(A5:A10)+SUM(B5:B10) in cell B1 and then pressed [Ctrl][ to highlight all the cells referred to by that formula. The circled cell reference in the Name Box reminds us that the “current” cell is A5. Pressing [Enter] cycles through each of the highlighted cells.


Press [Enter] again, and Excel will make A6 the current cell; press [Enter] again and A7 becomes the current cell, and so on. Your job is to review the contents of each cell as you cycle through, making sure that each entry is correct. (By the way, you can press [Shift][Enter] to move backward through the highlighted cells.)

When you’re satisfied, press any of the navigational keys to return to Excel’s normal mode. If your formula is still returning an incorrect value, even after you’ve double-checked all the cells to which your function refers, the next step in the troubleshooting process is to make sure you’re not using the wrong function!
To comment on this tip, please post a comment below or drop us a note.
4 comments
r.cicca
r.cicca

doesn't work on excel 2010 italian

La Mancha
La Mancha

I am working in Excel and when I try to connect two cells with (=)to duplicate text it is showing the formula not the contents of the cell. It is not happening in all cells...what did I do wrong?

cruffing
cruffing

I'm having the same issue and just wondering if you resolved it, can you share with me? Thanks!

Editor's Picks