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 459 results
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.
Liam Bastick
Exclusive Top 12 Excel Combinations: 1. INDEX MATCH
  • Article
  • 13 Dec 2024
  • Liam Bastick
For 2024, I thought I would count down a very subjective “Top 12” of Excel function combinations. And this month’s offering makes it very clear that this is a highly subjective list. If you don’t like this month’s suggestion, that’s fine; life would be boring if we were all in agreement.
Simon Hurst
Exclusive Spoiler alert: the answer is 364
  • Article
  • 09 Dec 2024
  • Simon Hurst
It’s festive-themed quiz season. We use a common Christmas quiz question as the pretext for examining a range of LAMBDA() based Excel functions.
David Beniam
Excel’s GROUPBY and PIVOTBY functions vs PivotTables
  • Article
  • 29 Nov 2024
  • David Benaim
One of Excel’s most significant updates this decade is without a doubt the new GROUPBY and PIVOTBY functions, these can replicate PivotTables functionality through formulas, so here I will compare and contrast both methods whilst providing possibly the most complete guide on the internet currently of GROUPBY.
David Beniam
Excel’s GROUPBY & PIVOTBY functions vs PivotTables
  • Article
  • 29 Nov 2024
  • David Benaim
One of Excel’s most significant updates this decade is without a doubt the new GROUPBY and PIVOTBY functions, these can replicate PivotTables functionality through formulas, so here I will compare and contrast both methods whilst providing possibly the most complete guide on the internet currently of GROUPBY.
Displaying 1-20 of 459 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