ICAEW.com works better with JavaScript enabled.

Excel Community round up - July 2022

Author: Excel Community

Published: 02 Aug 2022

Here are all the new Excel articles and webinars from the Excel Community in July 2022.

Posts marked as public can be accessed by anyone; other articles are accessible to Excel Community subscribers and can be accessed by logging in to your ICAEW account.

Webinar 

You can find our archive of all previous webinars here, many of which remain exclusive to Excel Community members.

Hooray for Arrays! The formula revolution that lets Excel take the strain

f you don’t know what array formulas are, or don’t know how to use them, then this webinar will provide the answers.

We have a number of further webinars planned for the rest of the year– check our list of upcoming webinars or refer to the latest newsletter. 

Excel Tips and Tricks

Excel Tips and Tricks #448 – A distinct count conundrum

Looking at the challenges around generating distinct counts by category and correctly calculating these as a percentage of the total population. This tip is based on a genuine scenario encountered when trying to produce some MI in relation to audit clients.

Excel Tips and Tricks #449 – Let LET simplify formulas

Introducing the LET formula in Excel and how this can be used to simplify otherwise long and complicated Excel formulas.

Excel Tips and Tricks #450 – Revisiting custom data validation

Revisiting the topic of how to make custom data validation rules with functions. Data validation can be used to help avoid input errors, especially those that have a knock-on effect elsewhere.

Other blogs

Planner: a lightweight project management/task allocation tool (public)

David Benaim shares his views on Planner, a Kanban board application (similar to Trello) that allows you to create cards in boards and easily move them between groups, to manage and allocate tasks.

How to review a spreadsheet part 3: Analytical review

In any spreadsheet review you are looking for signals of validity or potential error. A key part of this is a sense check that the numbers ‘look right’. This is supported by using charts (including sparklines), rations and recalculation.

Excel how to: speed up formatting using Excel Styles

Having considered ways to speed up entering formulas in Excel in the first six posts of the series, Simon Hurst moves on to formatting and starts by considering the use of Excel Styles and show how they can also help in applying several of the ICAEW Twenty principles for good spreadsheet practice.

Slicing one element on a chart only

Consider a scenario where creating a chart, say a line chart, but you only want one element (one of the lines) to be modified using a slicer. Liam Bastick demonstrates.

Is this the end for Excel’s SUMIFS() function?

The conditional aggregate functions such as SUMIFS() have long been some of the most useful functions for working with financial data, but they have their shortcomings and there might now be a more flexible and capable alternative.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250