Reverse engineering Excel workbooks

There are many good reasons to reverse engineer an Excel workbook. Let’s take a look at how to decode the structure of a workbook. Then, we’ll see how to demystify tricky formulas. Finally, we’ll take a peek at how to reveal workbook linkage to other workbooks.

Decoding the structure of a workbook

You should take two actions to make sure you see all the worksheets in a workbook. First, right-click any worksheet tab at the bottom, and choose Un­­hide … if it is active. If it is not active (grayed out), there are no hidden worksheets. Another possible explanation is that workbook structure protection has been turned on. Click on the Review tab and, in the Changes group, check to see if the Protect Workbook button is highlighted. If it is, try clicking it to turn it off. If a password was set when protection was originally turned on, you’ll need it to unhide worksheets. With all the worksheets unhidden, right-click on the VCR buttons in the lower-left corner of the Excel window. All of the worksheets will appear in a list, from which you can navigate. In versions of Excel earlier than 2013, you will need to click on More Sheets, if there are more than 16 sheets in the workbook, to see the whole list.

Another piece of workbook structure is Named Ranges. Named Ranges are used in VLookUp functions, as data sources for Pivot Tables, Data Validation selection lists and in formulas in place of relative cell references. For ex­­ample, if cell C1 has a tax rate, which is used to compute invoice totals stored in D3:D10, the formula could read D3*TaxRate, rather than D3*C1. Click­­ing on the dropdown arrow in the Name Box is an easy way to see all the Named Ranges in a workbook. To take action on Named Ranges, access the Name Manager. You will find this on the Formulas tab, Defined Names group. This dialog box is very useful for troubleshooting duplicate Named Ranges and editing Named Ranges where data has been added but the rows have not been extended.

Demystifying how formulas work

Try these three techniques to understand how formulas work in your workbook. First, with any one cell selected, use the shortcut Ctrl+` (also the ~ key, usually to the left of the 1 key on the QWERTY keyboard). This will reveal all the formulas on the worksheet. Some­­times, that’s all you need to solve the mystery. On the Formulas tab, Formula Audit­­ing group, click the Trace Precedents button. Excel will take you on a journey to all the cells used in the formula. Keep clicking it to trace further back. An­­other clever tool is the Evaluate Formulas button. This allows you to step through your complex or compound formulas and watch as each component is calculated. It’s also a great place to spot where an error is occurring.

Workbook linkage: uncovered

Are you wondering how many formulas and tables are linked to data sources outside this workbook? If your workbook is linked externally, on the Data tab, Connections group, you’ll see the Edit Links button active. You can also use this dialog box to break or change links that aren’t working properly.