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 481 results
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.
Simon Hurst
Exclusive Finding the missing link – Edit links enhanced
  • Article
  • 25 Apr 2024
  • Simon Hurst
Links to cells in other workbooks are a frequent cause of confusion. It can be particularly difficult to track down some of the more elusive ways in which one spreadsheet can be linked to another. A Find Next command available as part of an enhancement to the previous Edit Links command might now be able to help.
Headshot of Mark Proctor
Exclusive IRR, XIRR & MIRR – What are they? When to use them?
  • Article
  • 23 Apr 2024
  • Mark Proctor
IRR is commonly used to help make project investment decisions. In this article, Mark Proctor returns to explore the use of the IRR function in Excel and potential alternatives.
Simon Hurst
Exclusive SIMPLE BUT EFFECTIVE TABLES Simple but effective tables, part 2
  • Article
  • 18 Apr 2024
  • Simon Hurst
In this series we are going to examine some of the capabilities of Excel Tables. In part 1 we considered different ways of turning a range of cells into an Excel Table and demonstrated the importance of Tables in helping to ensure that ranges in formulas adapt automatically to changes in the dimensions of the Table. This time, we will look in more detail at adding rows and columns to existing Tables and see how this can contribute to automating your spreadsheets.
Liam Bastick
Exclusive Top 12 Excel Combinations: 10. SUMPRODUCT SIGN (Multiple Conditions with OR)
  • Article
  • 03 Apr 2024
  • Liam Bastick
I the latest instalment of his series counting down the “Top 12” of Excel function combinations, Liam Bastick considers . SUMPRODUCT SIGN (Multiple Conditions with OR).
Simon Hurst
Exclusive Including calculations in Microsoft Word
  • Article
  • 27 Mar 2024
  • Simon Hurst
Excel might be the obvious application for all sorts of calculations, but Word also has a calculation feature based on Field Codes that can perform simple calculations, use some functions and also format the result.
Displaying 1-20 of 481 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