Finding and fixing problems in Microsoft Excel is essential for a fully functioning workbook, but it can take forever. So, I made a note of the keyboard shortcuts that let me trace broken logic, see the dependencies, and fix complex calculations in just a few seconds—and I haven’t looked back since.
The keyboard shortcuts in this article apply to Windows computers. Mac keyboard shortcuts often follow the same principles, though the Alt key on a Windows keyboard is often replaced by the Options key on a Mac, and the Windows Ctrl key can be substituted by the Mac’s Cmd key.
Locating cells containing errors
Finding the cells containing the errors is the first step to making my worksheets tick. These shortcuts help me do just that by toggling views and prioritizing repair lists.
Display all formulas
Pressing Ctrl+` (grave accent) toggles Excel to display all underlying formulas in the current worksheet instead of their calculated results. Also, when I navigate to a cell after using this shortcut, all the cell’s precedents are color-coded.
This gives a blueprint view of the spreadsheet, so I can scan the formulas for any glaring issues. This shortcut is particularly useful for catching errors related to inconsistent logic that Excel’s automatic error checkers often miss.
Here are the things I look for after pressing Ctrl+`:
Area to check
What to look for
Potential problem
Consistency in columns
Scroll down a column and check if the cell references change according to the row they’re on (A1 becomes A2, A3, and so on).
If a formula suddenly breaks this flow, such as skipping a row or using a different function, it signals a manual copying error or formula break.
Absolute vs. relative references
Look for dollar ($) signs. Check if a formula is mixing absolute ($A$1) and relative (A1) references where it shouldn’t.
Incorrectly using absolute references when copying a formula across rows or columns can break large calculations.
Hard-coded values
Look for cells displaying a constant, such as 1,500,000, rather than a formula, like =D2*E2.
A hard-coded number could mean that a calculated value was manually overridden, breaking the automated calculation chain.
When I’ve spotted a cell containing inconsistent formula logic, I select it and press F2 to edit the formula directly.
Press Ctrl+` again to toggle the spreadsheet back to the results-based view.
Credit: Lucas Gouveia/How-To Geek
Related
Microsoft Excel Keyboard Shortcuts: Printable Cheat Sheet
Excel’s keyboard shortcuts are a real time-saver.
Locate the issues
The Go To Special dialog box, activated by pressing F5 > Alt+S, is one of my biggest allies in Excel, as it helps me isolate cells containing errors in large datasets.
Once the dialog box opens, I need to tell Excel I’m only looking for errors. Pressing F activates the Formulas option, then U > X > G unchecks Numbers, Text, and Logicals, leaving only Errors checked. This (F5 > Alt+S > F > U > X > G) might seem lengthy, but now that it’s part of my muscle memory, it’s much quicker than using my mouse to get to the same outcome.
When I press Enter, only the cells currently containing a calculation error are selected. What’s more, the error-holding cell that is highest up and furthest left becomes active, prioritizing my repair list.
Then, once I’ve fixed the first cell, I press Enter to move to the next. If I want to replace all the errors with a placeholder like “Check,” after typing the relevant string into the first cell, I press Ctrl+Enter.
Instead of selecting all errors, press F5 > Alt+S > C > Enter to highlight all constants—numbers, text, and symbols that aren’t derived from a formula. This is crucial for verifying that all manual inputs are grouped together and haven’t been mixed into sections containing formulas.
Finding broken relationships between cells
While the shortcuts above help me find errors and inconsistencies, they don’t help me find out why they happened. So, I use these shortcuts to understand the flow of data and pinpoint exactly where data or logic broke down.
Trace precedents
Pressing Alt > M > P forces Excel to draw blue arrows to the current cell (in this case, J2) from all cells referenced in its formula (its precedents—in this case, cells H2 and I2).
This is the best and quickest way to confirm that the formula is pulling data from the correct cells. If the formula is wrong, tracing precedents using this keyboard shortcut immediately shows if the error is caused by bad input data or the formula itself. If the arrows point to a precedent cell that is blank, contains text instead of a number, or holds an incorrect value, I select it and update its contents. The error in the final formula then usually resolves straight away.
Trace dependents
On the other hand, pressing Alt > M > D triggers blue arrows from the current cell (in this example, cell H2) to all other cells whose formulas use its value (its dependents—in this example, cell J2).
This helps determine the downstream damage of a potentially bad input cell. As a result, as soon as I make a fix, I can verify the impact. If the dependent cells still show errors or produce unexpected results, I know the dependent formulas might also need fixing.
Arrows drawn by tracing precedents or dependents remain on the screen until you specifically remove them by pressing Alt > M > A > A.
Select all precedents
Although precedent arrows are useful, sometimes it’s handy to select all the precedent cells (Ctrl+[), so I can act on them instantly. In this example, after I select cell J2 and press Ctrl+[, as well as getting an instant visual confirmation of the input range (H2 and I2) without arrows clogging my screen, I can make changes to the resultant cells, such as applying a conditional formatting rule, in just a few seconds.
Select all dependents
Selecting a cell and pressing Ctrl+] is a quick way to select all its dependent cells, meaning I can verify their results simultaneously without bringing up and later removing the Trace Dependents arrows. Here, I selected cell G2 and pressed Ctrl+] to reveal that cells H2 and I2 depend on its value.
For example, after fixing the core input, I can press Ctrl+] > Enter to cycle through the selected dependent cells and check their results individually. Also, if I realize a group of dependent formulas requires a slight change, such as updating a broken cell reference, selecting them all at once is the cleanest and fastest way to perform the targeted repair.
Related
6 Excel Keyboard Shortcuts I Wish I Knew Earlier
These shortcuts would have saved me so much time!
Evaluating calculations step by step
After locating the error and tracing its source, the final step is understanding which intermediate step in complex formulas is returning an incorrect value.
When I select a cell containing a formula and press Alt > M > V, the Evaluate Formula dialog box appears. Notice that the first part of the formula is underlined.
When I press E (Evaluate), this turns this underlined expression into its result (or the relevant error code if that’s what that expression returns) and underlines the next expression.
Then, I can keep pressing E until the whole formula is evaluated. This shortcut eliminates any guesswork when dealing with long, multi-function formulas and tells me exactly which part of the formula I need to correct. Previously, I might have deleted the whole formula and started again, but this shortcut lets me avoid that.
Summary: The error-fixing shortcuts
Here’s a summary table of all the shortcuts in this guide, which you can print and pin to your notice board:
Shortcut
What it does
Primary benefit
Ctrl+`
Toggles between displaying calculated results and underlying formulas.
Instantly reveals the sheet’s blueprint, allowing quick visual scanning for inconsistent logic or hard-coded inputs.
F5 > Alt+S > F > E
Uses the Go To Special dialog to select only cells containing errors.
Prioritizes your repair list by navigating directly to every broken formula in the sheet.
Alt > M > P
Draws visual arrows to show all input cells (precedents) used by the active formula.
Visually confirms the source of the formula’s data, helping diagnose if the error is due to bad input or bad structure.
Alt > M > D
Draws visual arrows to show all cells that rely on the active cell’s result (dependents).
Maps the downstream impact of a fix or change before you commit to it.
Ctrl+[
Selects and navigates to all precedent (input) cells.
Allows for immediate bulk action or verification of all precedent cells without visual clutter.
Ctrl+]
Selects and navigates to all dependent (output) cells.
Allows for immediate bulk action or verification of all downstream results without visual clutter.
Alt > M > V, then E
Opens the Evaluate Formula dialog box and runs through the formula’s expressions.
Pinpoints the exact segment of a nested formula that is failing by calculating it one step at a time.
As well as knowing all the shortcuts for finding and fixing errors, it’s just as useful to learn how to quickly navigate Microsoft Excel. Indeed, the program is so jam-packed with tabs, buttons, and dialog boxes that using the mouse to move around the interface can take far too long, so keyboard shortcuts are the ideal way to speed up this process.
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
