If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
Tag Archives: Dynamic Arrays
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.
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.
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.
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.
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.
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.
Macro to Format a Spill Range in Excel
VBA and spill ranges can work together
Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
Benford’s Law in Excel – Part One
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In actual data sets when reviewing values the 1st digit of the values tends to follow a predetermined frequency. For example, roughly 30% of the values should start with a 1.
Unique Random Whole Numbers in Excel
Excel has three functions that can provide random numbers. But the random numbers created may not be unique random whole numbers. Here is one way to get a list of unique random whole numbers.
One Minute to Excel #30 – Extract Data Based on Sheet Name
Shortcut to speed up name creation
Here’s a technique I use a lot to speed up report development.
Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.
This short video combines a few techniques to extract from a data set based on the sheet name.
All in less than a minute.
UNIQUE Function and Blank Cells in Excel
Zero in on a problem
The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.
Adding up Text Numbers In Excel Another Technique
If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
Removing Outliers in Excel
Dynamic array solution
I wrote a blog post a while back about outliers and Excel and I thought I would revisit it thanks to dynamic arrays.
One Minute to Excel #24 – 1,000 random dates
A RANDARRAY solution
Let’s say we need to do some testing and we need 1,000 random dates in 2022.
We can use a new function to make this easy to create and easy to change.
RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.
I set myself a challenge to do this in less than minute – see how I went in the video below.
Switching Reports from Rows to Columns in Excel
TRANSPOSE and OFFSET solution
I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.
Dynamic Arrays and a Book Index
Another solution
Years back when I wrote my Excel book, I had to create an index for the book. I shared the file I used including the macro in this post. Recently I thought dynamic arrays could do much of the work for this.
Let’s TRIM with Dynamic Arrays in Excel
Removing problematic spaces with a single function
Dynamic arrays allow you to use a function normally built to handle a cell, with a range of cells. The TRIM function can remove extra space characters in cells. So with dynamic arrays it can handle ranges.
Sort by value and ignore sign revisited
Dynamic array solution
I covered a solution to sorting and ignoring the sign a couple of years back, but it is time to revisit this thanks to dynamic arrays.
Distinct Count Formula in Excel
New and old functions combined
It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.
How to Refer to a Column in a Spill Range
INDEX rules
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
Sequences of a Repeating Series of Numbers in Excel
MOD and SEQUENCE used together
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
Summing Text Numbers in Excel Updated
Dynamic arrays to the rescue
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
Prefix Numbers Conditionally in Excel
Conditional Format to the rescue
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
How to Create Linked Array Syntax in Excel
It is now possible
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
Using SEQUENCE with Dates in Excel Part 3
How to highlight specific dates
In this post I finish off the Calendar matrix by adding holidays.
Using SEQUENCE with Dates in Excel Part 2
Time to enter the matrix
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
Using SEQUENCE with Dates in Excel Part 1
Date listing
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Time to Play with Dynamic Arrays
Let play time begin
Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.
Switching Rows and Columns is Now Easier
TRANSPOSE rules
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.