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.
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.