Ctrl + ~ : An X-ray of your spreadsheet

This is one of my favorite Excel keyboard functions, used in frequency only after Ctrl + S, but not many know about it.

Excel is great for easy data analysis, but it is hard to check for errors. Remember the scandals concerning spreadsheet errors in Thomas Piketty’s book? If Nobel Prize-winning economists have trouble making sure their formulas are right, you should be on the alert.

Below we have a simple inventory and sales model.  We want to validate our model’s assumptions.  2014-12-29_19-02-06

Right now the only way to check is one cell at a time.  It can be difficult to understand which cells are dependent on which.  Instead of trying a piecemeal approach, use this simple keyboard shortcut to see all of your spreadsheet’s inner workings: Ctrl + ~.

condensed ctrl ~

(I have condensed the columns to make this page easier to read.)

This is like an X-ray for your spreadsheet, allowing you to see which cells are formulas, which are values, and how they come together.

Subscribe to my mailing list.

Trackbacks

  1. […] Unwinding a formula in Excel can be difficult. Commas and clauses and parentheses…oh my! One way to map a spreadsheet’s relationships is through Ctrl + ~. […]

Leave a Reply

%d bloggers like this: