Excel Top Tip #13 - The ICAEW Spreadsheet Competency Framework (part 2)

Here is the email sent to all subscribers on 15 July 2021. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello Last month we learnt about the core Excel features that ICAEW recommends already having in your tool belt as a finance professional; and now we’re looking at the beneficial items - the nice to haves that could enhance your reputation with colleagues and clients:

DESIGN AND BEST PRACTICE

File naming and version control - Have an organisation-wide system for backups and version control. Layout design - Keep data inputs and outputs separate to avoid hardcoding constants in your formulas. Documentation - Include an ‘about’ or ‘welcome’ sheet to document the spreadsheet.

REVIEWING AND TEAM WORKING

Detect inconsistent formulas - Use fixed (a.k.a absolute) references in your formulas, to reduce the number of individual formulas you need to write, for reliability and to make it easy to spot inconsistent formulas Printing - Always preview, set the print area, and add print titles and page breaks

EFFICIENCY

Shortcuts - PgUp/PgDn, arrow keys (with Ctrl) and Tab are more basic, now explore using the function keys and Ctrl + [ and ] for reviewing formula precedent and dependent cells. Group Sheets - Use Ctrl to select multiple sheets and make changes to the cells altogether. Go To Special - Quickly select blanks, then press Ctrl + - to remove all rows.

FORMULAS

Rounding approaches - ROUND, ROUNDUP and ROUNDDOWN round to specified numbers of decimal places. Negative numbers of decimal places can be used, e.g. -3 will round to thousands. Text formulas - Like &, LEN, LEFT/RIGHT/MID, FIND (case sensitive)/SEARCH (not case sensitive), and SUBSTITUTE Date formulas - This includes TODAY, NETWORKDAYS, WORKDAY, EOMONTH, EDATE, YEARFRAC, and DAY/MONTH/YEAR, as well as date arithmetic. A creator should be able to follow what one of these formulas does in practice and check its operation manually.

FORMATTING

Custom number formats - Using a suitable number format can make a significant difference to the clarity and impact of a spreadsheet. We use: #,###,##0.00_);(#,###,##0.00)

PROTECTION AND ERRORS

Build error-resistant formulas - This may involve the IFERROR function, but should also include less broad approaches. For example, using an IF function to catch divide-by-zero errors, without also masking other genuine errors. Manual vs. Automatic calculation - Got a slow Excel with lots of calculations? Turn on manual calculations. Or, got an Excel that’s not recalculating the formulas? Check that it’s set to automatic. Build error checking formulas - For example, when creating management reports, check that the sum of each individual department’s report adds to the company-wide total. [https://www.dummies.com/software/microsoft-office/excel/build-error-checks-excel-financial-model/] Data validation - Know when to use a dropdown list for fast and error-free data entry

DATA ANALYSIS

Use rule-based filters - For example ‘Top 10’ filters can be set to include any number of top or bottom items, by number or by percent. Excel tables - Useful for totals that get updated even when a filter is applied.

MACROS AND AUTOMATION

Find and run an installed macro Run a goal seek - Are you guessing the answer? This feature might just help you out.

An understanding across these areas in Excel is key to working to a consistent high standard with your colleagues - why not spread your knowledge in your team by running your own Excel tip discovery session? Find all our Excel tips here. And if there’s anything else you’d like to see, just get in touch.

David - The StatementReader Team