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 500 results
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.
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.
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.
James Berridge
Exclusive EXCEL TIP OF THE WEEK Excel Tips & Tricks #491 – Creating a dynamic lead schedule
  • Article
  • 29 May 2024
  • James Berridge
Hello and welcome back to Excel Tips and Tricks! This week, we have a Creator level post exploring how to use VSTACK, CHOOSECOLS and FILTER to create a dynamic lead schedule and dynamic dropdown options.
Headshot of Mark Proctor
Exclusive Avoid Column Name errors in Power Query
  • Article
  • 24 May 2024
  • Mark Proctor
Changing column names are a frequent cause of Power Query errors. This article explores a few techniques to avoid those errors and ensure Power Query refresh runs smoothly.
Liam Bastick
Exclusive Top 12 Excel Combinations: 8. INDEX SEQUENCE
  • Article
  • 22 May 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers INDEX SEQUENCE
Headshot of Bani Lamba
Exclusive EXCEL TIP OF THE WEEK Excel Tips & Tricks #490 – Revisiting data entry shortcuts
  • Article
  • 21 May 2024
  • Bani Lamba
Hello and welcome back to Excel Tips and Tricks! This week, we have a Basic User level post revisiting simple keyboard shortcuts that helps expedite entry of data into Excel.
Liam Bastick
Exclusive Top 12 Excel Combinations: 9. NOT ISNUMBER
  • Article
  • 08 May 2024
  • Liam Bastick
In the latest instalment of his series counting down the "Top 12" of Excel function combinations, Liam Bastick considers NOT ISNUMBER.
Displaying 1-20 of 500 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