Ctrl + G, Literally a Go-To Excel Shortcut

Subscribe for more Excel tips and tricks

My recent  “Spreadsheet Blessing” ended with the wish that you “find no hard-coded cells.”

One of the downsides of Excel is that because it is a static form rather than a stop-end process a computer program, it is hard to diagnose errors.

Good spreadsheets are consistent, with defined inputs, outputs, and calculations. If you’re performing one function in a cell of an array, it should be done to them all — no exceptions! ‘

Hard coding cells is an easy workaround but will make things harder on the person inheriting your workbook.

Here’s a neat trick if you’ve received a new workbook and are suspicious that there may be hard coded cells. To diagnose, select an array of cells you want to check.

CtrlG

By keying Ctrl + G, a “Go To” box opens. Clicking “Special,” you see a selection of different cell types which Excel will identify. You can check for cells with formulas, comments, and more.

We will find cells with “constants,” that is, hard coded values. 

Selecting this, Excel will only leave highlighted the cells meeting that criteria. Now you know which cells are hard coded and can adjust.

 

Subscribe to my mailing list.

Comments

  1. “Go To” is awesome. Didn’t know about Ctrl + G, as I always used F5 to open the Go To dialog. One thing I used it for frequently is when a have a long list of constants (numbers and/or text) but there is blank rows scattered throughout the list. This might happen when copying in data from another source other than Excel. A quick way to eliminate the spaces in the list: Select the entire list, open the Go To dialog/Special and select the “Blanks” option. All the blank cells will become highlighted. Then right click on one of the blank cells and click “Delete” and “Shift cells up” (if working with a vertical list). Wallah! All blanks have been eliminated, leaving you with one continuous list.

    • George Mount says:

      Yes, I just learned it this week. I’ll often use it to find all cells which contain a formula, and once those are all highlighted will fill that in a certain color. Definitely a good way to diagnose what’s going on with your workbook.

Leave a Reply

%d bloggers like this: