Shortcuts for your spreadsheets

Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC

Here are some quick and simple shortcuts to help save you time with your financial spreadsheets.

Expanding existing formula range

Do you need to insert additional line items such as expenses and achieve an updated sum of the expense schedule? Insert rows into the second last row of the array (see image below) – this will ensure your existing formulae updates automatically.

Avoid retyping certain words or names

Does your spreadsheet contain numerous worksheets? Why not use the Defined Name feature for certain items, which will need to be entered repeatedly across the financial spreadsheets. Not only will it save time in manual entry, but it will ensure your spreadsheet is more uniform, consistent and error-free.

Restrict certain cell inputs to specific parameters with drop-down and dependent drop-down boxes

Are you using drop-down boxes in your spreadsheet? Drop-down menus and dependent drop-down boxes force users to select specified inputs, such as country and dependent states or provinces. In the spreadsheet below, this will dictate the tax rate of a business unit based on location and prevent incorrect tax rates being computed.

 Avoid repetitive charts across numerous worksheets

Different users of financial spreadsheets have different preferences and end reporting requirements. Often it is better to place numerous charts on one worksheet, or give the user the option to customise a graph’s time series and specific area to graph, i.e. comparing different insurance products’ death benefits rather than their cash surrender values or annual premiums. Adding tick-box functionality can further enhance the chart’s user-friendliness and flexibility. Users can add or remove specific items such the different products to compare.

Using drop-down boxes to construct dynamic charts will permit the user to customise the chart for their own needs. It will also save time having to recreate a chart for each area of analysis, which would increase the number of worksheets in your financial spreadsheet.

Conclusion

There are many shortcuts to implement with your financial spreadsheet which will not only save time but also reduce error. There are ways to expand an existing formula range in order to account for additional line items, for example, in an expense report. You can define cell names in order to avoid the hassle of constant retyping words or names. And the use of drop-down boxes will restrict input parameters or enable one chart to be used for different data sources or data arrays.