XLOOKUP and the Last Entry
One major advantage XLOOKUP has over VLOOKUP is that it can search from right to left. That makes it useful for finance models where you need to identify the last month with a payment, balance, or forecast value.
(more…)Alphanumeric Sort in Excel
If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
(more…)Yet Another Blank Cell Solution
When you link to an empty cell Excel it displays as zero. The same happens when you filter a list using the FILTER function. Blank cells display as zeroes. Here’s how to stop that.
(more…)Excel Pie in Pie Workaround
I did a post on the Pie in Pie chart a few weeks back, but it can be a bit clunky to use. I thought about using a few dynamic array functions to make it a bit more interactive and flexible. The source data is in a formatted table called Table1. In a separate sheet there are two input cells. One for the state to report on and the other to enter the number of “main” segments to use. The…
TRANSPOSE and TOROW Functions in Excel
When you have a vertical list that you need to make horizontal you can use the TRANSPOSE function to switch it. There is an alternative function that gives you extra options.
(more…)UNIQUEIF Excel Formula
I recently had an interesting question in a webinar. Could I create a formula like UNIQUEIF. I like a challenge so here is my answer.
(more…)Updating Sentences in Excel with New Techniques
I wrote a blog post back in 2012 and showed how to use checkboxes to build a paragraph based on sentences you select. I thought I would update it with recent new techniques.
(more…)Unique Entries in a Range in Excel
Sometimes the UNIQUE function won’t return what you’re expecting when you refer to a range of cells. You can use another function to allow you to extract the unique individual values from a range.
(more…)Adding Up a Specific Column in an Excel Spill Range
If you have a 2-dimensional spill range in Excel and you need to add up a specific column here is an easy formula to use.
(more…)Excel Formula for Duplicates
I had question in a webinar recently about a formula to list duplicated values. There may be a shorter formula, but I found and shared a solution.
(more…)Convert Line Chart to Dynamic Step Chart
Excel and Chart legend and MVP Jon Peltier has blogged about how you can convert a line chart into a step chart. I have used the new TRIMRANGE functionality and range names to make a dynamic solution.
(more…)TRIMRANGE Referencing in Excel
A recent update has introduced a new way to refer to ranges in Excel. This update makes it much easier to refer to flexible ranges in Excel.
(more…)Comparing Lists in Excel
I have been going through Mike Girvin’s recent book Microsoft 365 Excel: The Only App That Matters, and found an elegant technique of comparing lists using FILTER and XMATCH. It inspired me to create a custom function to show missing or matching entries.
(more…)Last Used Row Number in a Column
The recent TRIMRANGE function update to Excel makes it easier to determine the last used row in a column.
(more…)Add Hierarchy to Excel’s SORT Function
The SORT function allows you to specify which column to sort by. You can also include a hierarchy to sort by more than one column.
(more…)Excel YTD Reporting Solution
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
(more…)Excel Dynamic Arrays and Print Ranges
Print ranges can accept dynamic arrays. This means you can set up print ranges that automatically expand or contract based on dynamic array spill ranges.
(more…)Excel Auto-Adjusting Drop-Down List
If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
(more…)Array Syntax Custom Function
Array syntax is a powerful feature in Excel. Array syntax allows you to create a list within your formulas which makes them self-contained. Typing array syntax is difficult as you need to use a combination of quotation marks, commas or semi-colons plus braces (curly brackets).
(more…)Olympic Average in Excel
Averages are affected by outliers. If Bill Gates walks into a room the average net worth per person jumps substantially. In the Olympics some sports deduct the top and bottom scores before calculating the average score. Here’s a formula to do that in Excel. You need the subscription version of Excel for this solution.
(more…)Creating A Text String of Lowercase And Uppercase Letters
As a follow on from last week’s post you may want to create a text string of all of the lowercase and uppercase letters. Again we can combine some Excel functions to achieve this.
(more…)Listing Alphabetic Letters in Excel
If you need a listing of the letters of the alphabet you can combine a couple of functions to provide the list.
(more…)Solving a Conditional Summing Text Problem in Excel
I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.
(more…)A New Month of Sundays
One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.
(more…)Dynamic List of Weekday Dates in Excel
Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.
(more…)Introduction to Dynamic Arrays Webinar Recording
Learn about the new way to create formula and functions in Excel. This webinar recording from April 2024 will get you started.
(more…)Excel Dynamic Arrays in April
April is Dynamic Array month. This month I ran 4 new live one hour webinars dedicated to all things Dynamic Arrays. Dynamic Arrays change the way you create and maintain formulas in Excel. They expand Excel's capabilities and make it even more flexible. All four webinars are now online courses that you can buy as part of the Dynamic Arrays 2024 Bundle for AU$60. PLUS you get access to future Dynamic Array sessions this year. As new Dynamic Array courses…
Does an Excel range contain sequential numbers?
I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.
(more…)Extracting Initials in Excel – Part 2
In the previous post we looked at extracting initials when we only had a first name and a last name in this post, we will look at handling more than two names.
(more…)Extracting Initials in Excel – Part 1
Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.
(more…)