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 530 results
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.
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.
Liam Bastick
Exclusive Category sums
  • Article
  • 20 Feb 2025
  • Liam Bastick
Liam Bastick discusses summarizing dynamic arrays by category in Excel. It explores using functions like SUMPRODUCT, SUMIFS, and MMULT to achieve this. He also offers a quirky solution using SUBTOTAL and OFFSET for dynamic range summation.
Lego graph image
EXCEL COMMUNITY ROUND UP Excel Community round up - January 2025
  • Article
  • 03 Feb 2025
  • Excel Community
Here are all the new Excel blogs and webinars from the Excel Community in January 2025.
Liam Bastick
Exclusive LIFO Modelling
  • Article
  • 29 Jan 2025
  • Liam Bastick
In previous articles, I have twice previously considered modelling inventory, using both a simple averaging method to value the stock sold and then on a First In, First Out (FIFO) basis, which was a little bit trickier. Since then, I have been inundated with people asking me to complete the set: how do you model on a Last In, First Out (LIFO) basis?
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.
Simon Hurst
Exclusive Excel, what’s occurrin’ – it doesn’t add up
  • Article
  • 20 Jan 2025
  • Simon Hurst
This series looks at some of the things that can go wrong in an Excel spreadsheet and at what we can do to avoid or resolve the issue. We start with the basics of making sure our numbers add up.
David Beniam
100 new Excel features from the first half of the 2020s
  • Article
  • 13 Jan 2025
  • David Benaim
We’re halfway through the decade and Excel has released so many features that its hard to keep track, so here is a whistle stop tour with over 100 features. Items marked with * are released on the current channel but not all channels, so these items may not be available to all now but should be by July 2025. Click File > Account to see which channel you are on.
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 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.
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.
Displaying 1-20 of 530 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