by Due to the revolutionary update in the Excel 365 calculation engine, array formulas become very straightforward and understandable for everyone, not just for super users. The tutorial explains the concept of new Excel dynamic arrays and shows how they can make your worksheets more efficient and a lot easier to set up.
Excel array formulas have always been considered a prerogative of gurus and formula experts. If someone says "This can be done with an array formula", an immediate reaction of many users is "Oh, isn't there another way?".
The introduction of dynamic arrays is a long awaited and most welcome change. Due to their ability to work with multiple values in a simple manner, without any tricks and quirks, dynamic array formulas are something that every Excel user can understand and enjoy creating.
Excel dynamic arrays
Dynamic Arrays are resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell.
Through over 30 years of history, Microsoft Excel has undergone many changes, but one thing remained constant - one formula, one cell. Even with traditional array formulas, it was necessary to enter a formula into each cell where you want a result to appear. With dynamic arrays, this rule is no longer true. Now, any formula that returns an array of values automatically spills into neighboring cells, without you having to press Ctrl + Shift + Enter or do any other moves. In other words, operating dynamic arrays becomes as easy as working with a single cell.
Let me illustrate the concept with a very basic example. Supposing, you need to multiply two groups of numbers, for example, to calculate different percentages.
In pre-dynamic versions of Excel, the below formula would work for the first cell only, unless you enter it in multiple cells and press Ctrl + Shift + Enter to explicitly make it an array formula:
=A3:A5*B2:D2
Now, see what happens when the same formula is used in Excel 365. You type it in just one cell (B3 in our case), press the Enter key… and have the whole rage filled with the results at once:
Filling multiple cells with a single formula is called spilling, and the populated range of cells is called the spill range.
An important thing to note is that the recent update is not just a new way of handling arrays in Excel. In fact, this is a groundbreaking change to the entire calculation engine. With dynamic arrays, a bunch of new functions have been added to the Excel Function Library and the existing ones started to work faster and more effectively. Eventually, new dynamic arrays are supposed to completely replace the old-fashioned array formulas that are input with the Ctrl + Shift + Enter shortcut.
Excel dynamic arrays availability
Dynamic arrays were introduced at the Microsoft Ignite Conference in 2018 and released to Office 365 subscribers in January 2020. Currently, they are available in Microsoft 365 subscriptions and Excel 2021.
Dynamic arrays are supported in these versions:
- Excel 365 for Windows
- Excel 365 for Mac
- Excel 2021
- Excel 2021 for Mac
- Excel for iPad
- Excel for iPhone
- Excel for Android tablets
- Excel for Android phones
- Excel for the web
Excel dynamic array functions
As part of the new functionality, 6 new functions were introduced in Excel 365 that handle arrays natively and output data into a range of cells. The output is always dynamic - when any change occurs in the source data, the results update automatically. Hence the group name - dynamic array functions.
These new functions easily cope with a number of tasks that are traditionally considered hard nuts to crack. For example, they can remove duplicates, extract and count unique values, filter out blanks, generate random integers and decimal numbers, sort in ascending or descending order, and a lot more.
Below you will find a brief description of what each function does as well as the links to in-depth tutorials:
- UNIQUE - extracts unique items from a range of cells.
- FILTER - filters data based in the criteria you define.
- SORT - sorts a range of cells by a specified column.
- SORTBY - sorts a range of cells by another range or array.
- RANDARRAY - generates an array of random numbers.
- SEQUENCE - generates a list of sequential numbers.
- TEXTSPLIT - splits strings by a specified delimiter across columns or/and rows.
- TOCOL - convert an array or range to a single column.
- TOROW - transform a range or array into a single row.
- WRAPCOLS - converts a row or column into a 2D array based on the specified number of values per row.
- WRAPROWS - re-shapes a row or column into a 2D array based on the specified number of values per column.
- TAKE - extracts a specified number of contiguous rows or columns from the start or end of an array.
- DROP - removes a certain number of rows or columns from an array.
- EXPAND - grow an array to the specified number of rows and columns.
- CHOOSECOLS - returns the specified columns from an array.
- CHOOSEROWS - extracts the specified rows from an array.
- GROUPBY - group and aggregate data in rows based on the values in one or more columns.
- PIVOTBY - group data in rows and columns and aggregate the associated values.
Additionally, there are two modern replacements of the popular Excel functions, which are not officially in the group, but leverage all the advantages of dynamic arrays:
- XLOOKUP - is a more powerful successor of VLOOKUP, HLOOKUP and LOOKUP that can look up both in columns and rows and return multiple values.
- XMATCH - is a more versatile successor of the MATCH function that can perform vertical and horizontal lookups and return a relative position of the specified item.
Excel dynamic array formulas
In modern versions of Excel, the dynamic array behavior is deeply integrated and becomes native for all functions, even those that were not originally designed to work with arrays. To put it simply, for any formula that returns more than one value, Excel automatically creates a resizable range into which the results are output. Due to this ability, the existing functions can now perform magic!
The below examples show new dynamic array formulas in action as well as the effect of dynamic arrays on existing functions.
Example 1. New dynamic array function
This example demonstrates how much faster and simpler a solution can be accomplished with Excel dynamic array functions.
To extract a list of unique values from a column, you'd traditionally use a complex CSE formula like this one. In dynamic Excel, all you need is a UNIQUE formula in its basic form:
=UNIQUE(B2:B10)
You enter the formula in any empty cell and hit Enter. Excel immediately extracts all different values in the list and outputs them into a range of cells beginning from the cell where you entered the formula (D2 in our case). When the source data changes, the results are recalculated and updated automatically.
Example 2. Combining several dynamic array functions in one formula
If there is no way to accomplish a task with one function, chain a few ones together! For example, to filter data based on condition and arrange the results alphabetically, wrap the SORT function around FILTER like this:
=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))
Where A2:C13 are the source data, B2:B13 are the values to check, and F1 is the criterion.
Example 3. Using new dynamic array functions together with existing ones
As the new calculation engine implemented in Excel 365 can easily turn conventional formulas into arrays, there's nothing that would prevent you from combining new and old functions together.
For instance, to count how many unique values there are in a certain range, nest the dynamic array UNIQUE function into the good old COUNTA:
=COUNTA(UNIQUE(B2:B10))
Example 4. Existing functions support dynamic arrays
If you supply a range of cells to the TRIM function in an older version such as Excel 2016 or Excel 2019, it will return a single result for the first cell:
=TRIM(A2:A6)
In dynamic Excel, the same formula processes all of the cells and returns multiple results, as shown below:
Example 5. VLOOKUP formula to return multiple values
As everyone knows, the VLOOKUP function is designed to return a single value based on the column index that you specify. In Excel 365, however, you can supply an array of column numbers to return matches from several columns:
=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)
Example 6. TRANSPOSE formula made easy
In earlier Excel versions, the syntax of the TRANSPOSE function left no room for mistakes. To rotate data in your worksheet, you needed to count the original columns and rows, select the same number of empty cells but change the orientation (a mind-boggling operation in huge worksheets!), type a TRANSPOSE formula in the selected range, and press Ctrl + Shift + Enter to complete it correctly. Phew!
In dynamic Excel, you just enter the formula in the leftmost cell of the output range and press Enter:
=TRANSPOSE(A1:B6)
Done!
Spill range - one formula, multiple cells
The spill range is a range of cells that contains the values returned by a dynamic array formula.
When any cell in the spill range is selected, the blue border appears to show that everything inside it is calculated by the formula in the top-left cell. If you delete the formula in the first cell, all the results will be gone.
The spill range is a really great thing that makes the lives of Excel users a lot easier. Previously, with CSE array formulas, we had to guess how many cells to copy them to. Now, you just enter the formula in the first cell and let Excel take care of the rest.
Note. If some other data is blocking the spill range, a #SPILL error occurs. Once the obstructing data is removed, the error will be gone.
For more information, please see Excel spill range.
Spill range reference (# symbol)
To refer to an entire spill range returned by a dynamic array formula, put a hash tag or pound symbol (#) after the address of the upper-left cell in the range.
For example, to find how many random numbers are generated by the RANDARRAY formula in A2, supply the spill range reference to the COUNTA function:
=COUNTA(A2#)
To add up the values in the spill range, use:
=SUM(A2#)
Tips:
- To quickly refer to a spill range, simply select all the cells inside the blue box using the mouse, and Excel will create the spill ref for you.
- Unlike a regular range reference, the spill range ref is dynamic and reacts to the range resizing automatically.
For more details, please see Spill range operator.
Implicit intersection and @ character
In dynamic array Excel, there is one more significant change in the formula language - the introduction of the @ character, known as the implicit intersection operator.
In Microsoft Excel, implicit intersection is a formula behavior that reduces many values to a single value. In old Excel, a cell could only contain a single value, so that was the default behavior and no special operator was needed for it.
In new Excel, all formulas are regarded as array formulas by default. The implicit intersection operator is used to prevent the array behavior if you do not want it in a specific formula. In other words, if you wish the formula to return just one value, put @ before the function's name, and it will behave like a non-array formula in traditional Excel.
To see how it works in practice, please take a look at the screenshot below.
In C2, there's a dynamic array formula that spills results in many cells:
=UNIQUE(A2:A9)
In E2, the function is prefixed with the @ character that invokes implicit intersection. As the result, only the first unique value is returned:
=@UNIQUE(A2:A9)
For more information, please see Implicit intersection in Excel.
Advantages of Excel dynamic arrays
Undoubtedly, dynamic arrays are one of the best Excel enhancements in years. Like any new feature, they have strong and weak points. Luckily for us, the strong points of new Excel dynamic array formulas are overwhelming!
Simple and more powerful
Dynamic arrays make it possible to create more powerful formulas in a much simpler way. Here are a couple of examples:
- Extract unique values: traditional formulas | dynamic array functions
- Count unique and distinct values: traditional formulas | dynamic array functions
- Sort columns alphabetically: traditional formulas | dynamic array functions
Native for all formulas
In dynamic Excel, you do not need to bother which functions support arrays and which do not. If a formula can return multiple values, it will do so by default. This also applies to arithmetic operations and legacy functions as demonstrated in this example.
Nested dynamic array functions
To work out solutions for more complex tasks, you are free to combine new Excel dynamic array functions or use them together with old ones like shown here and here.
Relative and absolute references are less important
Thanks to the "one formula, many values" approach, there is no need to lock ranges with the $ sign since, technically, the formula is in just one cell. So, for the most part, it does not really matter whether to use absolute, relative or mixed cell references (which has always been a source of confusion for inexperienced users) - a dynamic array formula will produce correct results anyway!
Limitations of dynamic arrays
New dynamic arrays are great, but as with any new feature, there are a few caveats and considerations that you should be aware of.
Results cannot be sorted in the usual way
The spill range returned by a dynamic array formula cannot be sorted by using Excel's Sort feature. Any such attempt will result in the "You cannot change part of an array" error. To arrange the results from smallest to largest or vice versa, wrap your current formula in the SORT function. For example, this is how you can filter and sort at a time.
Cannot delete any value in spill range
None of the values in a spill range can be deleted because of the same reason: you cannot change part of an array. This behavior is expected and logical. Traditional CSE array formulas also work this way.
Are not supported in Excel tables
This feature (or bug?) is quite unexpected. Dynamic array formulas do not work from within Excel tables, only within regular ranges. If you try to convert a spill range to a table, Excel will do so. But instead of the results, you will only see a #SPILL! error.
Do not work with Excel Power Query
The results of dynamic array formulas cannot be loaded into Power Query. Say, if you try to merge two or more spill ranges together using Power Query, this won't work.
Dynamic arrays vs. traditional CSE array formulas
With the introduction of dynamic arrays, we can talk about two types of Excel:
- Dynamic Excel that fully supports dynamic arrays, functions and formulas. Currently it's only Excel 365 and Excel 2021.
- Legacy Excel, aka traditional or pre-dynamic Excel, where only Ctrl + Shift + Enter array formulas are supported. It's Excel 2019, Excel 2016, Excel 2013 and earlier versions.
It goes without saying that dynamic arrays are superior to CSE array formulas in all respects. Although the traditional array formulas are retained for compatibility reasons, from now on it is recommended to use the new ones.
Here are the most essential differences:
- A dynamic array formula is entered in one cell and completed with a regular Enter keystroke. To complete an old-fashioned array formula, you need to press Ctrl + Shift + Enter.
- New array formulas spill to many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.
- The output of dynamic array formulas automatically resizes as the data in the source range changes. CSE formulas truncate the output if the return area is too small and return errors in extra cells if the return area is too large.
- A dynamic array formula can be easily edited in a single cell. To modify a CSE formula, you need to select and edit the whole range.
- It is not possible to delete and insert rows in a CSE formula range - you need to delete all existing formulas first. With dynamic arrays, row insertion or deletion is not a problem.
Backward compatibility: dynamic arrays in legacy Excel
When you open a workbook containing a dynamic array formula in old Excel, it is automatically converted to a conventional array formula enclosed in {curly braces}. When you open the worksheet again in new Excel, the curly braces will be removed.
In legacy Excel, the new dynamic array functions and spill range references get prefixed with _xlfn to indicate that this functionality is not supported. A spill range ref sign (#) is replaced with the ANCHORARRAY function.
For example, here's how a UNIQUE formula appears in Excel 2013:
Most dynamic array formulas (but not all!) will keep displaying their results in legacy Excel until you make any changes to them. Editing a formula immediately breaks it and displays one or more #NAME? error values.
Excel dynamic array formulas not working
Depending on the function, different errors may occur if you use incorrect syntax or invalid arguments. Below are the 3 most common errors that you may run into with any dynamic array formula.
#SPILL! error
When a dynamic array returns multiple results, but something is blocking the spill range, a #SPILL! error occurs.
To fix the error, you just need to clear or delete any cells in the spill range that are not completely blank. To quickly spot all the cells that get in the way, click the error indicator, and then click Select Obstructing Cells.
Apart from a non-empty spill range, this error may be caused by a few other reasons. For more information, please see:
#REF! error
Because of the limited support for external references between workbooks, dynamic arrays require both files to be open. If the source workbook is closed, a #REF! error is displayed.
#NAME? error
A #NAME? error occurs if you attempt to use a dynamic array function in an older version of Excel. Please remember that the new functions are only available in Excel 365 and Excel 2021.
If this error appears in supported Excel versions, double-check the function's name in the problematic cell. Chances are it is mistyped :)
That's how to use dynamic arrays in Excel. Hopefully, you will love this fantastic new functionality! Anyway, I thank you for reading and hope to see you on our blog next week!
40 comments
First, I'd like to say thank you for providing articles on creating dynamic named ranges in excel. This has been an extremely helpful tool with other workbooks I have created since reading your article on how to create a dynamic named range using OFFSET/COUNTA and INDEX/COUNTA.
I am looking for some assistance with a dynamic range not including the last column intended to be in the range; however, unfortunately I cannot share the data because of its sensitive nature.
I used a filter function to pull personnel info that work for a specific department based on the job code with data from a sourced workbook. The filter function results with the intended 10 columns. On the same sheet, just beside the filters final/10th column, I added 8 columns: Column K, prepared with IF functions for assigning a work shift (all day, am, pm), Column L for Positions that will be assigned by the supervisor of the department (blank), Columns M:R contain Index/Match functions to source 3 levels and dates of occurrence for disciplinary actions entered on another sheet and then load the most recent disciplinary actions and dates into these columns, of course I have it set to leave blank if conditions are not met.
The problem I am running into is that the dynamic range I am trying to create for this new Roster is excluding the last column, column R, that contains the infraction code for the last disciplinary date in column Q. The dynamic range for the workday and the array as a whole are counting the IF/Index/Match/Leave Blank functions as cells that are not blank. To compensate for this I am trying to create the named range using following function: ='Inside Grounds Roster'!$A$3:INDEX('Inside Grounds Roster'!$1:$1048576,COUNTA('Inside Grounds Roster'!$K:$K),COUNTA('Inside Grounds Roster'!$3:$3)); keep in mind, using the function as it is in the "how to" article didn't work in the filter function because the named range for Shift (column k) is longer than the roster array for the data all together... I had to use column K in the first COUNTA function because of the cells preloaded with INDEX/MATCH functions that go beyond the final row of the Filter functions results when using column $A:$A.
I am hoping someone will be able to shed some light on this column exclusion issue when creating dynamic named ranges.
Any help would be greatly appreciated. - Thank you.
Hi! Unfortunately, I have not been able to understand your problem. I can't guess what you want to do with your formula and which function and from which article you have failed. Try to explain it more simple and clearly.
I have an array formula that calculates whether something is true over time, giving a long list of true/false values. Now I wish to calculate the longest uninterrupted set of true values in that array. In the olden days the array had a fixed length, and next to it I'd have a repeated calculation e.g. list of true & false values in a1:a100, then in b1 =if(a1, 1,0) in b2 =if(a2,b1+1,0) b3 =if(a3,b2+1,0)
then max(b1:b100) gives me the longest consecutive active duration.
B1 to B100 cannot ( I think) be an array, as it refers to itself (b23 refers b22, which refers to b21, etc.
Is there another array function that allows me to have a dynamic implementation, and not have to set up a long list of fixed calculation steps?
Hi! To determine the maximum number of consecutive TRUE values in an Excel column, you can use the following formula:
=MAX(FREQUENCY(IF(A:A=TRUE,ROW(A:A)), IF(A:A<>TRUE,ROW(A:A))))
In this formula, we first define a range of consecutive TRUE values, and then we find the maximum value in that range. You must enter this formula as an array formula.
Are dynamic array functions also applicable as dynamic in VBA codes? Could you guide using these functions in VBA coding too? Thanks.
How do you mix the dynamic formulae with normal row formulae, e.g in this formula:
=IF(tblAnalysis[Type]="sell",tblAnalysis[Price]-MIN(Y2#,tblAnalysis[Price2]),MAX(Y2#,tblAnalysis[Price2])-tblAnalysis[Price])*10000
I am using a MAX and MIN, but I want those formula to apply to the value in cell Y2 and the value from tblAnalysis[Price2], in other words it should take the miminum of 2 values; but instead it calculates the minimum of the Y column and the table column Price2.
I have tried this version also, but this does not give the right results:
=IF(tblAnalysis[Type]="sell",tblAnalysis[Price]-MIN(Y2,tblAnalysis[@Price2]),MAX(Y2,tblAnalysis[Price2])-tblAnalysis[@Price2])*10000
Any help appreciated.
Tony
Hi! It is very difficult to understand a formula that contains unique references to your workbook worksheets. The formula uses structured references to table columns but not to single cells in the worksheet. Therefore, it calculates the column minimum.
We have an Office 365 for Enterprise subscription and I've updated to the latest build, but no matter what I try, I cannot get dynamic arrays functionality to work.
Even a simple "=A2#" that is referring to a simple table always return #REF. Any ideas?
Hi! In your case, A2 should not just be a regular data cell, but the first cell of a dynamic range. This dynamic range is created by the dynamic functions described in the article above.
It would be worth giving an explicit mention in your “Limitations of dynamic arrays” section some extra show stoppers:
a) any function that would already consume array parameters before the introduction of dynamic arrays will return a single result (so as not to break backward compatibility) and so preventing dynamic array values being passed any further. I've just bashed my head on this with logical functions OR, AND (and XOR), which flew completely under my radar. After more digging, I found the workaround is to use mathematical expressions instead, e.g., in place of =AND(val1,val2) use =1=(TRUE=val1)*(TRUE=val2). I'm sure there are many more that I'm not aware of. To see the problem in action, put each of these…
={FALSE;FALSE;TRUE;TRUE}
={FALSE;TRUE;FALSE;TRUE}
=AND(A1#,B1#)
=(TRUE=A1#)*(TRUE=B1#)
…A1:D1.
b) using dynamic arrays in recursive lambda functions is not fully supported. Using RemoveChars(string,charsToRemove) from Example 1 in your recursive lambdas article (https://www.ablebits.com/office-addins-blog/write-recursive-lambda-function-excel/#example1),
put…
={"abc•de¶fgh";"a§bcd¶¶efgh";"abcd••efgh";"abc§d¶e•fgh"}
={"•¶§";"•¶§";"•¶§";"•¶§"}
=RemoveChars(A1,B1)
=RemoveChars(A1#,B1)
=RemoveChars(A1,B1#)
=RemoveChars(A1#,B1#)
…into A1:E1.
The RemoveChars lambda works fine in C1 and D1, but with a dynamic array passed to the second parameter in E1 and F1 it generates #NUM!. There is no way to look inside and see what is happening. Your insights into what is going on and any work arounds would be extremely helpful and save a lot of wasted time. (And if you have an inside contact to tell at MS, even better!)
Many thanks for your already very helpful blog posts!
Torsten
Hi Torsten,
Thank you for your thoughtful feedback!
I cannot agree with you on point a). in Dynamic Array Excel, any function that can potentially return (return, not process!) multiple results does it. The logical functions are design to evaluate multiple conditions, but return a single logical value TRUE or FALSE.
Point b) requires further investigation, thank you for brining it to focus.
Hi,
I somewhat agree with Torsten on point a), since logical AND/OR/XOR functions are not 'obviously' non-matrix-functions, eg function NOT is matrix being a logical function too.
And finding information about it is complicated and so is the workaround.
Maybe a list of non-matrix-functions (like volatile-functions) can help more.
Thanks
I was just passing through and noted the comment on AND/OR/XOR.
You most likely have the issue sorted by now but, if not, to get an array of results one can use MAP
= MAP(BooleanA, BooleanB, LAMBDA(x, y, AND(x, y)))
If that is too ugly you can name the Lambda function as ANDλ to give
= MAP(BooleanA, BooleanB, ANDλ)
It is possible to go a step further
= PairwiseAndλ(BooleanA, BooleanB)
but probably not worth the effort!
Thanks Peter!
I only spotted your post just now when coming back to post the same answer, independently found after much pain, and with a view to share with all! Using MAP indeed wraps a single row processing lambda for the passed array arguments, and is useful for any of the functions that otherwise consume entire arrays in one or refuse to work with dynamic arrays, e.g. ISODD, ISEVEN, MIN, MAX, AND, OR, XOR, many date functions such as EDATE, etc.
@SvetlanaCheusheva this use of MAP is worthy of a mention in your main article next to the implicit intersection operator, as it is the corollary: preserve dynamic array results, instead of collapsing them to single results.
One of the most comprehensive and well explained articles about Dynamic Array Formulas om the web.
Well done!
I was wondering whether there is a section available to list or document examples of (combinations of functions with) Array functions.
Regards: Mark - the Netherlands
Thank you for your article.
Can you please help me with one thing. I come from Google sheets and find Excel so confusing on some occasions.
={B2:B5;C2:C4}
Why this doesn't work? I googled, I used ChatGPT, I tried it with many different value sets, formula combinations etc. I see it as such a normal and simple array formula I would use when coding as well, BUT it does not work in Excel and I can't even find a simple alternative.
I stumbled upon this issue when I tried to "flatten" an array that was a result of a filter function (ended up being 5x10 array table) which I simply tried to flatten to 1x50. This is easily done in google sheets (This is the exact alternative formula that produced the result: =flatten(filter(List1!A2:CG1000;List1!A1:CG1="Name")).
BUT in excel even after hours of work only bad inappropriate results.
Thank you so much for any help!
FYI using latest Office 365 subscription and Excel for mac (I deleted and re-installed the app today, because I was certain it is not working properly.
You can get single column of values from a range of cells with the TOCOL function. You can combine several arrays into one using the VSTACK function.
=VSTACK(TOCOL(B2:C5),TOCOL(D2:D4))
or
=VSTACK(B2:B5,C2:C4)
I hope it’ll be helpful.
Thank you very much! I hope I get this to work.
But do you have any clue why the behaviour of an array is different than that of Google sheets or lets say java script?
For anyone looking for such a solution, here is my final version that worked perfectly:
=TOCOL(FILTER('Data entry'!B1:AA5000;'Data entry'!A1:A5000="(your filter or a cell reference)";""))
This solved the above mentioned question and also allowed unique to be applied on top of it. This in turn made a simple vertical list that can even be conditionally linked to a referenced cell which can e.g. be a dropdown list.
I found this to be one of the simplest solutions for my problem.
I have made a dynamic table of EURUSD with the daily highs and lows price data.This data is prepared in daily time frame with two data points-HIGH and LOW for everyday as per the order they occured. I have created a dynamic table but the highs and lows data points are not as per my requirements as per given below:-
First Requirement:- in Dynamic table I required to devide each 24 hours period into two equal section i.e. each section of 12 hours.
Second Requirement:- I required the first data point of excel sheet(high or low) in the exactly middle of first section and second data point of excel sheet(high or low) in the exactly middle of the second section in the time axis side dynamic table.
Please provide me your mail id to share the excel sheet with you.
Regards.
Thanks for this informative article!
I'm having trouble with the sort feature, and hope you can help me. I have started out using the formula =SORT(UNIQUE('sheet1'!C:C)).
I am looking up data in column C rather than a specific range because the data-gathering is ongoing, so the range has no final row.
(1) Is there a way to exclude the header from my list of sorted unique values without knowing what the final row of the referenced range will be?
(2) Is there a way to exclude values of 0 and/or text?
Thank you
Hello!
I recommend converting your data range to an Excel table. Then you don't need to refer to the whole column and the heading will be excluded automatically.
Here is an example formula for a table column
=SORT(UNIQUE(FILTER(Table2[list],(Table2[list]<>0)*(ISNUMBER(Table2[list])))))
I hope my advice will help you solve your task.
I too originally thought that the inability to write dynamic array formulas to a table was a defect, but I now think that shortcoming is inevitable and probably necessary. Tables organize their data in a one-record-per-row fashion and because dynamic array formulas have multi-cell spill capabilities, if a dynamic array formula in a table were allowed to spill down its column, other records in the table would receive the spilled data from that formula.
Thank you for your feedback, Abbott. That does make perfect sense!
Great article, but I still have 1 question.
The result of a dynamic array function is a range of results whereby the range can extend if you add data to the source table. The problem is the fact that the formatting of this range is not extended automatically. Or did I miss something?
Hello!
You're right. You can format the cells of the worksheet. The results of calculations are recorded in these cells.
Hi Dominique,
That actually depends on the reference used in your formula. For example, if you supply the range A2:A10 to the UNIQUE function, and then add a unique value in A11, that value won't get in the spill range until you change the reference in the formula.
But if you initially refer to a bigger range, e.g. A2:A10000, or use a column reference such as A:A (please be very careful with column references as they may cause a #SPILL! error in some cases!), then the spill range will extend automatically to include newly added data.
Hi,
In my case it doesn't automatically update.
Case at hand:
1) w/sheet 1 a simple table with data: name, function, product, service center, working weeks (1week/1cell). Total no of rows ~ 800
2) w/sheet 2 is a summary table with counts based on 2 conditions: product & service center.
Constructing formula like:
= COUNTA(UNIQUE(FILTER('Project 2022'!$I$1:$I$1000,
('Project 2022'!$D$4:$D$1000=$C1)*('Project 2022'!$K$4:$K$2000=Z$3)))
produces a result say Summary (product C4; center Z3) = 4
If I go in w/sheet 1 and update either product or center to any value, if I delete 1 of those 4 lines or if I add 1 line (we're under the range of 1000) the result doesn't get updated regardless manual or auto calc, regardless save - close Excel process etc.
I suspect once Excel memory buffer is filled in with a value -> this case value 4, it doesn't get overwritten.
Case at hand - shorter formula, taking only FILTER portion from it, returns 4 names. No updates in the spilled range regardless the updates done in the source table.
Appreciated any hints in getting this working.
Apologies, just realized I've cloned the source w/sheet and I was actually pulling data from there while I was making changes in initial 1st w/sheet. Mea culpa, formula works and gets updated as it should. Thanks!
Thanks for the article! I have a problem I'm running into - I cannot copy the cell values within an array and paste them elsewhere. I'm using dynamic array to create a dynamic search tool for projects on my team. The searching works well - I'm able to search with multiple paramters and the array updates as I change them. However when I go to copy the cell data to paste in another worksheet, I can't. Is this just inherent to the array? Is there a way around that?
Hello!
When copying, you need to select the first cell in which the array formula is written. Part of the array cannot be copied.
Hi Jenn,
To copy part of a returned array (spill range) to another sheet, use the Paste Special feature:
1. In the spill range returned by your formula, select the values you want to copy and press Ctrl+C.
2. Right-click the destination cell, and then click Paste Special > Values. Or click Values under Paste Options.
Great article! I have a problem and from this article understand why the power query isn't working as I expected it to.
I have multiple sheets (different classes) containing tables which is a register of attendance, then a summary sheet. On the classes sheets column A has names, with attendances across the columns the total attendances in column M. On the summary sheet, I can search the class sheets to find each name to tell me the number of sheets each name appears on (thereby telling me how many different subjects/classes they attend), but how can I also summarise the totals for each name, giving me the number of times they have turned up across all classes?
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice.
You can try using the COUNTIF function as described in this tutorial: How to use Excel COUNTIFS and COUNTIF with multiple criteria
I hope my advice will help you solve your task. If something is still unclear, please feel free to ask.
That did it, thanks!
I really like the spill functions, and I am slowly getting used to them.
I have a problem with a formula, where the reference is treated as an absolute reference even though I do not have a $.
My sheet shows a list of accounts vertically, and the 12 months across the top. The data are amounts, and represent the YTD totals through the month in the header. It is worth noting that the months begin with the current month and count backwards to January. So depending on what the current month is that you are working on, the first column may be any month, and the next column will be the previous month, and so on until you get to the January column. This is because of the way the data comes to me from a different program. No problem yet.
I want to also show the amount by month (not the YTD amounts, but amounts in each month. So to the right I build another set of data. This time, the months are in order: January through December.
To calculate the amounts for January, I use the new XLOOKUP spill function to find the amount in January:
=XLOOKUP(Q$6,$D$6:$O$6,$D11:$O4000)
This brings in the January information nicely.
For February, I can use the same function, but I also need to subtract off the January amounts to get the total for February:
=XLOOKUP(R$6,$D$6:$O$6,$D11:$O4000)-Q11)
The XLOOKUP portion of the formula is spilling down just fine. But the "-Q11" is not. It spills down as an absolute reference, not a relative reference. Even though I didn't use a $. This causes every account to be reduced by the January amount for the first account.
Any ideas on how to make Excel see this as a relative reference instead?
Thank you!
Hello!
If I understood the problem correctly, you need to make a reference in the formula not to a cell, but to write down the formula that is in this cell. If a cell contains an array of values, then a reference to this cell will return only the first value of the array.
Or change the formula something like this:
=XLOOKUP(R6,$D$6:$O$6,$D7:$O4000)-Q11:Q4000
I hope I answered your question. If something is still unclear, please feel free to ask.
I need help. I'm using the FILTER function to pull a subset of data (F1) from a Table. I then pull a Filter again to show certain characteristics of the data (F2) to assist analysis. And then I want to count how many times the F2 combination shows up in F1.
But I don't know how to reference F2 results and count what matches in F1. I used to do this with SUMPRODUCT (after old Filter means).
Here's the old calculation: SUMPRODUCT((G$27=$CD$100:$CD$20000)*(G31=$CE$100:$CE$20000)*(H31=$CF$100:$CF$20000)) The criteria (G$27,G$31,H$31) or F2 is now in the spill array G29# and the range is now D$100# or F1.
But how do I reference these individual criteria within a spilled array and perform a boolean argument?
Thanks!
Is there any way to use dynamic array functions to reference multiple ranges, for example:
=unique({A1:A6,D1:D6})
This was a great article, thank you very much.