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 521 results
Lego graph image
EXCEL COMMUNITY ROUND UP Excel Community round up - December 2024
  • Article
  • 01 Jan 2025
  • Excel Community
Here are all the new Excel blogs and webinars from the Excel Community in December 2024.
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 From numbers to pictures – using INDEX() with MAKEARRAY()
  • Article
  • 13 Dec 2024
  • Simon Hurst
Having in the previous post used several of the new LAMBDA() helper functions, most notably MAKEARRAY(), to calculate the total number of gifts given across the 12 days of Christmas, this time we are going to replace our numbers with pictures that represent each of the gifts.
David Beniam
NEW TECH FEATURES Excel, Teams, Power BI, Canva, Zoom, Google Sheets++: New tech features for Sep-Dec 2024
  • Article
  • 09 Dec 2024
  • David Benaim
Our digest of new features across Microsoft 365 & other business tech products in written, images & video format covering the last three months.
Lego graph image
EXCEL COMMUNITY ROUND UP Excel Community round up - November 2024
  • Article
  • 02 Dec 2024
  • Excel Community
Here are all the new Excel blogs and webinars from the Excel Community in November 2024.
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.
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.
Simon Hurst
Exclusive Separating your clients
  • Article
  • 27 Nov 2024
  • Simon Hurst
This post was prompted by a support query about automating the extraction of client names from cells that could contain other information after the name. Based on the use of one or more delimiter characters to separate the client name from the additional information, we are going to look at using the TEXTBEFORE() function with different delimiter alternatives.
Liam Bastick
Exclusive Top 12 Excel Combinations: 2. SUMPRODUCT OFFSET
  • Article
  • 25 Nov 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers SUMPRODUCT OFFSET.
Simon Hurst
Exclusive Taking the new GROUPBY() and PIVOTBY() functions further
  • Article
  • 20 Nov 2024
  • Simon Hurst
The main capabilities of the recently introduced GROUPBY() and PIVOTBY() functions have already been covered in recent articles. Here we are going to look at one aspect of their use in a bit more detail: the ability to use functions and formulas within the individual arguments of each function.
Simon Hurst
Exclusive Ticks, crosses and noughts
  • Article
  • 05 Nov 2024
  • Simon Hurst
Experimenting with an Excel tip in a recent LinkedIn post leads to an exploration of the use of conditional formatting for adding ticks and crosses to Excel cells, and the discovery of a surprising comparison capability.
Lego graph image
EXCEL COMMUNITY ROUND UP Excel Community round up - October 2024
  • Article
  • 01 Nov 2024
  • Excel Community
Here are all the new Excel blogs and webinars from the Excel Community in October 2024.
Liam Bastick
Exclusive Top 12 Excel Combinations: 3. IFERROR FORMULATEXT
  • Article
  • 31 Oct 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers IFERROR FORMULATEXT.
Headshot of Mark Proctor
Exclusive Solving the array of arrays issue
  • Article
  • 25 Oct 2024
  • Mark Proctor
In this post, we explore the "array of arrays" issue in Excel’s calculation engine. We demonstrate how this leads to unexpected results and how we can work around the issue.
Headshot of Mark Proctor
Exclusive Using slicers with PIVOTBY, GROUPBY and FILTER
  • Article
  • 01 Oct 2024
  • Mark Proctor
The newest functions, GROUPBY and PIVOTBY will be rolling out to users over the next few months. In this article, we uncover the techniques we can implement for using slicers with these new functions.
Liam Bastick
Exclusive Top 12 Excel Combinations: 4. SORT UNIQUE
  • Article
  • 24 Sep 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers SORT UNIQUE.
Liam Bastick
Exclusive Top 12 Excel Combinations: 5. SCAN LAMBDA
  • Article
  • 29 Aug 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers SCAN LAMBDA.
Simon Hurst
Exclusive SIMPLE BUT EFFECTIVE TABLES Simple but effective tables, part 8
  • Article
  • 07 Aug 2024
  • Simon Hurst
In this series we are going to examine some of the capabilities of Excel Tables. We will be concentrating on the practical use of Tables to improve spreadsheet productivity. Last time we started looking at the use of structured references to refer to all or part of an Excel Table, using examples based on referring to the Table from cells outside the Table. This time we will look at structured references within a Table and some more advanced examples of how to use structured references.
Liam Bastick
Exclusive Top 12 Excel Combinations: 6. MAP LAMBDA
  • Article
  • 18 Jul 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers MAP LAMBDA.
Liam Bastick
Exclusive Top 12 Excel Combinations: 7. OFFSET MATCH
  • Article
  • 24 Jun 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers OFFSET MATCH.
Displaying 1-20 of 521 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