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 462 results
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
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.
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.
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.
Ruth Butler-Lee
Exclusive EXCEL TIP OF THE WEEK Excel Tips and Tricks #497 –BYROW and BYCOL
  • Article
  • 28 Mar 2025
  • Ruth Butler-Lee
Welcome back to Excel Tips and Tricks! This time, we have a Creator level post exploring BYROW and BYCOL, functions that can help troubleshoot common issues when using Dynamic Arrays.
Headshot of Mark Proctor
Exclusive Ranges vs. Arrays – What's the difference, and why you should care?
  • Article
  • 27 Mar 2025
  • Mark Proctor
In this article, we look at the differences between ranges and arrays. Compared to ranges, we will see that arrays give us total flexibility for making many Excel calculations easy to perform.
Liam Bastick
Exclusive Calculated fields and calculated items
  • Article
  • 24 Mar 2025
  • Liam Bastick
Liam Bastick looks at how calculated fields and calculated items can be used to solve problems in Excel, rather than resorting to more complicated functions.
Is this the end of Pivot Tables
  • Webinar
  • 21 Mar 2025
  • PDF (1,340kb)
  • ICAEW Excel Community
This webinar will explore the basics of Pivot Tables before diving into new and alternative functionality that can also be used to analyse and work with data in Excel.
Headshot of Mark Proctor
Exclusive Using range functions in Excel
  • Article
  • 04 Mar 2025
  • Mark Proctor
In this article, Mark Proctor, explores a group of functions which can dynamically manipulate ranges in Excel for flexible calculations.
Headshot of Mark Proctor
Exclusive Going Deep with REDUCE
  • Article
  • 27 Jan 2025
  • Mark Proctor
In this article, Mark Proctor explores the REDUCE function in Excel and dives into its power for achieving advanced calculations.
Displaying 1-20 of 462 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
Excel polaroid