Structure and navigate workbooks efficiently

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

The importance of building well-designed workbooks cannot be understated, and in my opinion the knowledge of how to design workbooks effectively is one of the most fundamental Excel skills that needs to be learnt. Here we will look at what kind of worksheets should generally exist within a workbook and what they should contain. Whilst it is worth considering some broad principles, of course the specifics of the workbook structure needs to be tailored to each individual case.

Even with an ideal workbook structure, navigating the workbook can still sometimes be difficult, especially when working across links to multiple files and when there are a large number of sheets. The second part of this topic looks at some tips for effective navigation including hyperlinks, save workspace and the watch window.

Even if your workbook doesn’t constitute a model in the traditional sense it is worth following a structure recommended for business models in Excel for any workbook .Broadly speaking this means it should consist of the following worksheets:

• Documentation/Instructions sheet: this should contain basic details about the workbook such as when it was built, by whom and for what specific purpose. The assumptions and limitations of the workbook should be clearly explained, as well as how the workbook is structured-I.e. what all the various sheets are and where the user is expected to input data/see outputs. It might be worth inserting hyperlinks to sheets to aid navigation-see below on how to do this. The formats of cells should be made clear to users.
• Control worksheet: this is where you have workbook level variables which can then be referenced throughout the file. For example if you prepare a certain workbook on a monthly basis then this workbook should contain the month name which can then be referenced in headings etc.-see here for an example using the TEXT function. It is also very important that the control sheet contains reconciliation checks including [validation formulas] for key calculations within the workbook or sum checks-for example if doing a SUMIF analysis ensuring that the total of the SUMIF figures equals the total.
• Input worksheet(s): These should contain unlocked cells for users to input data into, formatted specifically for this purpose, or your source data. There should be no formulas on these sheets-just numbers!
• Calculation worksheet(s): this is the engine of your workbook which takes the source data/inputs and performs calculations on them or manipulates them into the format required to be presented. These sheets should be protected and contain only formulas with no hard-coded numbers!
• Output worksheet(s): these should display summary numbers in the format required to be presented-for example ratios or dashboard metrics.

Ideally you want to limit the number of input worksheets so that users have few points of reference for input or so that source data is visible is one place. However if you do have inputs spanning multiple sheets you can use 3D formulas to combine them provided all input sheets are in the same format.

Once you have these core worksheets set up in the format required (including protection options and custom styles) it is worth saving a blank copy of the workbook as a template. To do this, in the ‘save as’ dialog box select type ‘Excel Template’. This will then save a copy of the workbook into your local Excel templates folder so that every time you create a new Excel File (from the File menu/Ribbon button) the file will appear under ‘My templates’.

An alternative way to use the template workbooks you create is to use them in an existing workbook you already have open. To do this in your existing workbook right click on one of the worksheet tabs at the bottom, and select ‘ Insert’ and then in the ‘General’ tab your template file should appear. Selecting this will then insert all sheets from your template file into your existing workbook.

Note that this will bring the sheets exactly as they are in the template file, including Cell Styles, Data Validation, Protection and Conditional Formatting so where these are to be re-used across workbooks this is a useful way of applying these features. You can also have particular templates containing particular types of sheets (e.g. Input sheets, output sheets, calculation sheets) and then you can pick and choose which ones you want in any particular file by using the ‘Insert’ option from the tabs.

If you have a workbook with multiple sheets, or are accessing different sheets across various workbooks that are open it can be useful to know some shortcuts for switching between the different sheets and workbooks.

The most obvious way to navigate between worksheets is by using the tabs at the bottom of the workbook (or the arrows to the left of the tabs), however you can also use the Keyboard shortcut Ctrl + Page Up to go to the next sheet and Ctrl + Page Down to go to the previous sheet. This can still be quite time consuming when you have lots worksheets so an alternative is to use the worksheet menu which lists out all the sheets in the workbook and you can select the one you want. To access this, right click on the arrows next to the worksheet tabs and you will see a list of all the worksheets similar to that shown below. Simply click on the worksheet you wish to navigate to and it will take you there.

Alternatively where there are multiple sheets it is often useful to have a documentation worksheet explaining the structure of the file which has links to the salient worksheets and the user can then use this sheet to navigate the workbook. To create a link to a worksheet we select the location where we want the link to be and then select ‘Hyperlink’ from the Insert tab (or right click on the cell and select ‘Hyperlink). This brings up the dialog box shown below.

To link to another worksheet in the same file we select ‘Place in this document’ and our various worksheets are listed under ‘Cell Reference’. Selecting a worksheet will by default take you to the first cell (A1) of that worksheet – to go to a different location within the sheet change the cell reference in the ‘Type the cell reference’ box. Alternatively you can reference any named ranges in the file by selecting them underneath the ‘Defined names’ section. In the ‘Text to display’ text box type the text you want to show as the link e.g. the worksheet name. Selecting OK will then display the text in the cell you were in which links to the worksheet specified.

Note that it is also possible to link to another Excel file by using the ‘Existing File or Web page’ and browsing to the file.

Where you are working on multiple files at the same time, you can open them all up and then switch between them by using the ‘Switch windows’ option in the View tab. Alternatively you can re-arrange your windows by reducing each one (clicking on the double panel icon in the top right hand corner in-between minimise and close) and dragging them into different areas of the Excel window as shown in the screenshot below.

However opening all the files you need to access and re-arranging them each time is time consuming so fortunately Excel has provided a virtually unknown feature called ‘Save workspace’, located next to ‘Switch Windows’ in the View tab. Once all the related files you need (e.g. for a particular project) are open and arranged in the way you want, then click on this icon and it will bring up the ‘save workspace’ dialog box as shown below. This allows you to save the list of open files and the order in which they are arranged as a template workspace file.

Saving the workspace is like saving any other file – it is stored as a workspace file in the location you specify. Opening this file in Excel will then automatically open up all the relevant Excel files and arrange them in the way they were arranged when you created the workspace.

Where you are interested in particular cells within different worksheets or workbooks, another useful tool is the Watch Window which allows you to see the value in any particular cells you specify across any open workbook or anywhere in the file. This is very useful for business modelling as it allows you to change inputs and instantly see how the outputs (located on a different sheet) are impacted, without having to constantly switch between the worksheets.

To set up a watch, click on ‘Watch Window’ located in the Formulas tab. This will bring up a list of all the cells you are ‘watching’ with their corresponding values. The window will be visible above your workbook regardless of which worksheet or workbook you are in. To add a cell which you want to watch the value of, click on ‘Add Watch’ and then in the dialog box that comes up (shown below) click on the range icon to select a cell (or multiple cells) which you want to monitor. Note that you can only select a range in the current open workbook so to add a watch in another open workbook, switch to this workbook first.

This then adds this cell to the watch window, showing you the Workbook, sheet name, cell reference, value and formula. Now the value of these cells will be visible regardless of where you are in the file and you can see how these cell values change when you change something else in any open workbook.

Watch windows are also extremely useful for navigation – double clicking on any watches in the watch window will take you to that cell. So where you have cells that you are continuously switching between (in the same workbook or a different one) then adding them all as watches will allow you to easily access them when you want.

• In the Documentation worksheet, create hyperlinks to the Control, Input, Calculation, Output and Lists sheets
• In cell D12 and D14, use COUNTIF formulas to count the number of errors/blank inputs on the input sheet. Use conditional formatting to highlight a returned value of 0 in Green and any number greater than 0 in Red
• In the Input sheet, format the input cells (B3:B5 and B7) according to an input format. Change the column heading in cell B2 so that it reads ‘Value as at …’ referencing the month in the control sheet. In Column F write a validation formula that checks each input row to ensure the input is within the minimum/maximum allowed range (in Columns D and E), returning ‘OK’ if it is and ‘Invalid’ if it isn’t. Use conditional formatting to highlight invalid entries in red and valid entries in Green. Protect the worksheet (unlocking the input cells) so that users can only input into the appropriate cells
• The calculation and output sheets are already formatted with formulas that reference the inputs. Ensure that these worksheets are protected as appropriate.

A demonstration is shown in the video clips below. Resist the temptation to view the answers file below until you have tried this!

Hyperlinks

Workbook Templates

Adding a watch

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.