Power users: Excel smart tips

Users in the financial services sector who have achieved a high degree of speed and accuracy when using Excel spreadsheets are called ‘power users’. To achieve this level of proficiency, there are a number of basic changes that should be made to the default settings in Excel to allow users to work quickly and with more accuracy. Some practical tips are discussed below:

Set the ‘auto-save’ function for five minutes. Auto-saving documents every five minutes ensures that if the computer freezes, users only lose the last five minutes of their work.

Turn-off the function ‘move selection after enter’. When working in a spreadsheet, users sometimes want to access a cell above, below or to the side of the cell they have just accessed. As  the next cell  is always different, users can save time if the active cell remains stationary after the user hits the ‘enter’ key. Furthermore, a user should confirm the accuracy of their formula and in order to do so will require the cell to remain active.

Turn-off the function‘enable auto-complete’. Keeping the auto-complete option enabled increases the probability of an entry error if the subsequent entry is similar to a previous entry. Disabling the function requires the user to enter the text completely, which minimises the risk of errors.

Turn-off ‘windows in taskbar’ in view. If a user has multiple spreadsheets open, rather than use the mouse to navigate between spreadsheets in the taskbar, use  the keyboard’s CTRL-Tab function to move from one spreadsheet to another. This is at least twice as fast as using the mouse and it will keep the taskbar uncluttered since the active spreadsheet will appear in the taskbar at the same location regardless of which spreadsheet is active.

Turn-off the function ‘allow editing directly in cells’. It is best to edit formulas from the taskbar only to ensure that the user can review the entire formula and its nearby cell references without obstruction. Turning this feature off prevents users from editing the formula directly in the cell.

Turn off ‘iteration’. The only acceptable circular reference (or circ) within a financial model is when the model calculates average interest. All other circs must be identified and eliminated.  By turning off the  ‘iterative’ feature, circs will be identified by an automatic error message appearing. Users should build-in Yes/No triggers into the spreadsheet to calculate average interest and should reactivate the iteration feature on a case-by-case basis.

Turn on  the various add-ins in excel. Users may frequently find a need for add-ins such as ‘Solver’, the ‘Analysis ToolPak’ and even the ‘Analysis ToolPak-VBA’ or ‘Euro Currency Tools’, so these options should be turned on. Users who are unaware of these advanced features should learn how they can be used to solve complicated optimisation problems and optimising repeated calculation tasks by reviewing an Excel user manual.

Use just one worksheet. Unless the time series is different, it is best to build a model on one worksheet rather than on multiple worksheets. This allows the user to:
navigate quickly around the model;
audit and edit more easily because cell formulas are shorter (since they do not include a reference to another worksheet); and
minimise errors when building a model as the user will not need to navigate among worksheets and all formulas using the same time series references will be situated in the same column.

BEST PRACTICE STANDARDS FOR USING SPREADSHEETS

A common set of spreadsheet usage rules is the foundation on which the best practices are built. The following are the most commonly known and utilised rules:

Colour code cell entries. This is probably the most important rule. It helps other finance professionals understand the flow of the model within the first few seconds of reviewing the spreadsheet because the model’s assumptions (inputs) and the formulas that drive the model can be instantaneously identified without reviewing each and every cell. Colour codes, like those set out in Table 1, also allow users to audit the spreadsheet quickly. While blue and black appear to be universal among finance professionals for use in inputs and formulas other colours may be used to designate other functions. Regardless of which other colours are used, the important issue is to choose an easy to identify colour and to use it consistently. There are also a few software programmes available that colour code spreadsheets automatically.

Avoid hardcoding sums and partial formulas. This is important because hardcoding sums or partial formulas can make the model difficult to audit and test.  It also decreases the flexibility of the model. For example, if the sum of a series of numbers is hardcoded and there is then a need to change one of the inputs at a later date, the changed input would not be reflected in the model. 

Use file naming and storage conventions. All files in the investment manager’s directory should be named in accordance with a firm-wide standard and stored and accessed from a shared, but restricted drive.

Use keyboard shortcuts. Use keyboard shortcuts instead of the mouse to access the common and frequently used excel menu items such as  ‘bold’ ,  ‘cut’ ,  ‘copy’.  Keyboard short-cuts allow rapid access to common controls and functions and are perhaps five times faster than using the mouse. 

Do not combine cells. Merging cells together may later prevent users from copying and pasting cells. It can also  cause calculation errors as additional rows or columns are inserted into the spreadsheet. 

Audit the spreadsheet. This is the term used to describe the structured process by which the model is reviewed, checked for accuracy and stress-tested.

Use sum bars. Provide users with guidance on inserting numbers into the number series by inserting a new row in the spreadsheet. Provided the number and new row is inserted below the top number or at the sum bar, the =SUM function will always include the new number.

Protect cells and worksheets. This will prevent unwanted or accidential changes. Users who desire to protect specific cells and worksheets may do so using the ‘Review/Protect Worksheet’ or ‘Protect Workbook’ functions.

Avoid long and complex formulas. This makes it easier to audit and stress test the spreadsheet and to identify and fix errors. It is best to split long or complicated calculations into multiple cells. Perhaps try to keep the number of variables to a maximum of five to eight. Some formulas may need to be longer, however, but this should be the exception rather than the rule.

Position assumptions as close to the formulas as possible. This means that the person  auditing the spreadsheet does not need to navigate to another portion of the spreadsheet to confirm a particular cell reference. This also applies if one section requires data from another section to drive the formulas. If so, it is generally best to repeat the required data in the new section by providing a link to the other section and then utilising this new row in the formulas.

Name the data range. Using range names within a spreadsheet is helpful when the spreadsheet is large and is being used in collaborative team environments. It can also improve the readability of formulas. However, it takes time to code and maintain the spreadsheet. Users should read more about this function and learn how it is used before attempting to code a model with this feature. 

This was an excerpt from a chapter in PEI’s Private Equity CFO & COO Digest 2012. Available now HERE.