ICAEW.com works better with JavaScript enabled.

Formulas and functions

View a list of articles and webinars produced by the Excel Community on formulas and functions.

Displaying 1-20 of 468 results
Headshot of Mark Proctor
Exclusive Unlocking the IMAGE function
  • Article
  • 19 Jan 2026
  • Mark Proctor
In this creator level post, we explore how to use the IMAGE function to insert pictures into worksheet cells using the file page of the picture.
Liam Bastick
Exclusive End of Monthly Month End Reporting Problems
  • Article
  • 15 Jan 2026
  • Liam Bastick
As many of us return to work at the beginning of the new year, many of us reflect on our new year resolutions already broken, stalled weight loss programmes, giving up smoking failures, and, er, automating month end reporting. Well, maybe not the last one. However, monthly reporting is here to stay and the more we can reduce manual intervention the better. With 2026 now fully under way, I thought this would be a good time to consider how to work out – and automate – those month end dates for reporting purposes for once and for all.
Simon Hurst
Exclusive Questioning the value of training – part 1
  • Article
  • 08 Jan 2026
  • Simon Hurst
Simon Hurst asserts that, while online resources and AI can solve Excel problems, structured training helps users identify the right problems, choose optimal solutions, and design better spreadsheets, preventing future issues.
James Berridge
Exclusive Excel Essentials for Auditors: Part 3 Techniques for stock and sales analysis
  • Article
  • 02 Jan 2026
  • James Berridge
In this series, James Berridge, Director at Saffery LLP, explores core Excel functionality and essential skills that every auditor should master to support their audit roles. The last article in the series focuses on how to apply practical, audit-focused Excel features including pivot tables, lookups, dynamic arrays and more to analyse a realistic stock listing and sales dataset.
Headshot of Bani Lamba
This year’s 10 best Excel highlights
  • Article
  • 24 Nov 2025
  • Bani Lamba
As we approach a new year, it’s time to reflect on some of the content and highlights from the Excel Community in 2025 and look ahead as Excel continues to change and develop with new functionality. Whether you’re new or an existing member, in this article we invite you to explore the insights and learnings the community has delivered for you over this last year.
Liam Bastick
Exclusive Partial Unpivoting
  • Article
  • 20 Nov 2025
  • Liam Bastick
Sometimes, when you work with pivoted data that has a structure similar to a PivotTable, it is difficult to look up a value based on multiple column and row criteria. To make it simpler, we usually unpivot the data. Most of the time, you may choose to use the ‘Unpivot columns’ function in Power Query.
Image of meeting
Exclusive A practical guide to using dynamic arrays in Excel
  • Webinar
  • 19 Nov 2025
  • Excel Community
This webinar will cover a practical introduction to Dynamic Arrays in Excel and how dynamic arrays address the common problems faced when using traditional Excel formulas in financial modelling.
Simon Hurst
Exclusive Dealing with complex conditional formatting rules simply
  • Article
  • 10 Nov 2025
  • Simon Hurst
Last time, we continued our exploration of some of the advanced options available for working with Conditional Formatting Icon Sets.
Liam Bastick
Exclusive Running totals in filtered tables
  • Article
  • 27 Oct 2025
  • Liam Bastick
In this article, Liam Bastick considers the problem of ensuring running totals still work, whether an Excel table is filtered or not.
Simon Hurst
Exclusive Controlling your icons with conditional formatting
  • Article
  • 14 Oct 2025
  • Simon Hurst
Last time, we looked at one way of automatically highlighting certain cells in a PivotTable using conditional formatting. There are several different ways of using conditional formatting to apply highlights, with Colour Scales and Icon Sets being perhaps the most obvious. These two graphical conditional formats are included in the Conditional Formatting dropdown and can easily be applied with a mouse click or two. However, sometimes using too many colours and too many different icons can have the opposite effect to that intended, obscuring the information rather than drawing attention to the k...
Simon Hurst
Exclusive This month’s highlights
  • Article
  • 18 Sep 2025
  • Simon Hurst
Perhaps every month you wonder how sales have gone compared to the same month last year. This post demonstrates one way of automatically highlighting the comparison using conditional formatting. Also, just because we can, we’ve used an example based on a PivotTable to demonstrate the technique.
Liam Bastick
Introducing Excel’s New COPILOT Function
  • Article
  • 22 Aug 2025
  • Liam Bastick
Excel has launched it’s “latest and greatest” function, COPILOT. Currently it is in Beta and only available to users on the Beta Channel, through the Microsoft Insider Program.
Simon Hurst
Exclusive Not dead yet – refreshing update for PivotTables
  • Article
  • 04 Aug 2025
  • Simon Hurst
Rarely do meetings of the Excel Community Advisory Group descend into actual fisticuffs but, on the rare occasions when an unseemly brawl has erupted, an argument over the benefits or otherwise of PivotTables has invariably been the cause. For some, the recent introduction of dynamic array functions, and GROUPBY() and PIVOTBY() in particular, seemed to have turned the tables firmly in favour of the PivotTable sceptics. However, just as they were basking in the warm glow of vindication, a new option has appeared for Excel Beta Channel users in the PivotTable Analyze ribbon tab, Data group – Aut...
Headshot of Bani Lamba
Exclusive EXCEL TIP OF THE WEEK Excel Tips and Tricks #501
  • Article
  • 28 Jul 2025
  • Bani Lamba
Welcome back to Excel Tips and Tricks! The Excel Community has been around for over 10 years, and last month we published our 500th Tip. In this tip, we take a look at what we have covered so far, and where our tips and tricks are headed next.
Simon Hurst
Exclusive Excel, what’s occurrin’ 10 – a date with PivotTable destiny
  • Article
  • 22 May 2025
  • Simon Hurst
This series looks at some of the things that can go wrong in an Excel spreadsheet and at what we can do to avoid or resolve the issue. The first six parts dealt with issues that cause numbers to appear not to add up correctly. This time, we move on from lookup functions to dates in PivotTables.
Liam Bastick
Exclusive Comparing SUMIF, SUMIFS and SUMPRODUCT
  • Article
  • 12 May 2025
  • Liam Bastick
Liam Bastick explains the use of three Excel functions commonly used for aggregating values based on conditions: SUMIF, SUMIFS and SUMPRODUCT.
David Beniam
Is Canva Sheets the new green?
  • Article
  • 08 May 2025
  • David Benaim
The Canva Suite has become a force of nature with 1 billion designs being produced monthly, and now it finally launched Canva Sheets, but how does that compare to Excel or Google Sheets.
Lego graph image
EXCEL COMMUNITY ROUND UP Excel Community round up - April 2025
  • Article
  • 01 May 2025
  • Excel Community
Here are all the new Excel blogs and webinars from the Excel Community in April 2025.
Headshot of Mark Proctor
Exclusive Adding calculated fields to the GROUPBY function
  • Article
  • 24 Apr 2025
  • Mark Proctor
In this article, Mark Proctor explores the concept of PivotTable calculated fields and reimagining them with Excel’s new GROUPBY function. Discover how to build dynamic, formula-based summaries that can go beyond what PivotTables can do.
Headshot of Bani Lamba
Exclusive EXCEL TIP OF THE WEEK Excel Tips and Tricks #498 – Introducing TRIMRANGE
  • Article
  • 15 Apr 2025
  • Bani Lamba
A Creator level post introducing one of Excel’s newer functions – TRIMRANGE. TRIMRANGE is a new function in Excel that allows users to easily exclude leading or trailing blank rows or columns in a range.
Displaying 1-20 of 468 results

Search the Excel Community archive

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.

Search the archive Access the knowledge base
A colourful wooden abacus placed on a wooden surface, featuring multiple rows of beads in vibrant colours used for counting or basic arithmetic.