Excel Top Tip #12 - The ICAEW Spreadsheet Competency Framework

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

Hello

If you’re creating spreadsheets from scratch to be used by yourself and your colleagues, then here are the core Excel features that you should already have in your tool belt (according to ICAEW); but most importantly, you may find many of your tasks in Excel become easier if you learn more about any of these that are missing:

DESIGN AND BEST PRACTICE

Twenty principles for good spreadsheet practice - Creators should understand the importance of applying good practice to reduce risk. For example, "9 - Focus on the required outputs". Here's the full list as set by the team at ICAEW.

Label data, sheets, ranges - Be kind to those that follow you in attempting to understand your structure by suitably naming the areas in your worksheet.

FORMULAS

Audit formulas - Get familiar with the options available to you under the ‘Formulas’ menu.

Logical operators (like: >, <, =) - Although these are often used as an argument of an Excel function such as IF, they can also be used as a simple ‘statement’ that will return a value of TRUE or FALSE.

Logical formulas - This includes IF, SUMIF(S), and COUNTIF(S).

Lookup formulas - Such as, VLOOKUP, HLOOKUP, and INDEX/MATCH

FORMATTING

Apply regular cell formatting - Make simple custom formats for yourself - such as, #,##0;[Red](#,##0)

Conditional formatting - Conditional formatting is a deep tool that allows for a lot of customisation of cell appearance and automation of formatting.

Hiding, grouping, merging - Know when to use each to add clarity and not confuse the user.

PROTECTION AND ERRORS

Error messages - Use shortcuts like Ctrl + [ to trace the formula output to its source

Worksheet protection - Understand the protection limits offered by Excel

DATA ANALYSIS

Insert and build simple PivotTables - Click Insert -> PivotTable to get started.

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