Exclusive
Filter tips 2 – more fun with FILTER()
- Article
- 09 Aug 2022
Last time we saw how the SUM() function can be combined with the new FILTER() function to calculate conditional aggregate values. This time we look at how to construct an aggregate formula to include all the items that are matched against an entire list of criteria values.
Exclusive
Excel Tips and Tricks #451 – Sparklines Refreshed
- Article
- 05 Aug 2022
Hello all and welcome back to the Excel Tip of the Week. This week, we have a Creator post in which we are looking at another under-utilised piece of functionality within Excel – the Sparkline, last covered in TOTW #290.
Exclusive
Is this the end for Excel’s SUMIFS() function?
- Article
- 22 Jul 2022
The conditional aggregate functions such as SUMIFS() have long been some of the most useful functions for working with financial data, but they have their shortcomings and there might now be a more flexible and capable alternative.
Exclusive
Slicing One Element on a Chart Only
- Article
- 22 Jul 2022
Consider a scenario where create a chart, say, a line chart, but you only want one element (one of the lines) to be modified using a slicer. To illustrate, let’s have some data:
Exclusive
Excel Tips and Tricks #449 - Let LET simplify formulas
- Article
- 19 Jul 2022
Hello all and welcome back to Excel Tips and Tricks! For this tip we have a Creator post where we introduce the LET formula in Excel and how this can be used to simplify otherwise long and complicated Excel formulas.
Exclusive
How to review a spreadsheet part 3: Analytical review
- Article
- 14 Jul 2022
In any spreadsheet review you are looking for signals of validity or potential error. A key part of this is a sense check that the numbers ‘look right’. This is supported by using charts (including sparklines), ratios and recalculation.
Exclusive
Excel Tips and Tricks #448 – A Distinct Count Conundrum
- Article
- 13 Jul 2022
Hello all and welcome back to Excel Tips and Tricks! For this tip, we have a Creator post looking at the challenges around generating distinct counts by category and correctly calculating these as a percentage of the total population. This tip is based on a genuine scenario encountered when trying to produce some MI in relation to audit clients.
Planner: A lightweight project management/task allocation tool
- Article
- 08 Jul 2022
David Benaim shares his views on Planner, a Kanban board application (similar to Trello) that allows you to create cards in boards and easily move them between groups, to manage and allocate tasks.
The Excel community has produced an extensive list of helpful tips and tutorials including its 'Excel tip of the week' series.
Exclusive
Excel Tips and Tricks #451 – Sparklines Refreshed
- Article
- 05 Aug 2022
Hello all and welcome back to the Excel Tip of the Week. This week, we have a Creator post in which we are looking at another under-utilised piece of functionality within Excel – the Sparkline, last covered in TOTW #290.
Exclusive
Excel Tips and Tricks #450 – Revisiting Custom Data Validation
- Article
- 26 Jul 2022
In this Creator post we revisit the topic of how to make custom data validation rules with functions.
Exclusive
Excel Tips and Tricks #449 - Let LET simplify formulas
- Article
- 19 Jul 2022
Hello all and welcome back to Excel Tips and Tricks! For this tip we have a Creator post where we introduce the LET formula in Excel and how this can be used to simplify otherwise long and complicated Excel formulas.
Exclusive
Excel Tips and Tricks #448 – A Distinct Count Conundrum
- Article
- 13 Jul 2022
Hello all and welcome back to Excel Tips and Tricks! For this tip, we have a Creator post looking at the challenges around generating distinct counts by category and correctly calculating these as a percentage of the total population. This tip is based on a genuine scenario encountered when trying to produce some MI in relation to audit clients.
Excel Tips and Tricks #447 – Rounding Errors Revisited
- Article
- 28 Jun 2022
Hello all and welcome back to Excel Tips and Tricks! For this tip, we have a General User post exploring the quirks of the Excel calculation engine, why sometimes it can mean that rounding errors are introduced into your data, and what you can do about them.
Exclusive
Excel Tips and Tricks #446 – Desktop vs Online
- Article
- 21 Jun 2022
Hello all and welcome back to Excel Tips and Tricks! For today’s General User post it’s time to answer the question I often find myself asking – what actually is the difference between the Excel Desktop and Online versions? I’ve done the digging, so you don’t have to…
Exclusive
Excel Tips and Tricks 445
- Article
- 10 Jun 2022
Hello all and welcome back to Excel Tips and Tricks! For today’s Basic User post it’s time to get collaborative. If two or more people are working on a spreadsheet at the same time, filtering can be a headache. This tip looks at the solution – Filter (or Sheet) Views.
Exclusive
Excel Tips and Tricks #444 - Revisiting XLOOKUP
- Article
- 24 May 2022
Hello all and welcome back to Excel Tips and Tricks! For today’s General User post we’re revisiting another must-use Office 365 function, XLOOKUP. While we have covered XLOOKUP a few times, including in the “Tip of the Week Live” webinar earlier this year and our comparison of lookup functions in TOTW #391, you can never have too much of a good thing, right?
Join the Excel community
Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join. You also have the option to Join the Tech Faculty, where Excel Community membership is included.