Quick Links

• The Problem: Dynamic Array Formulas Don't Work in Excel Tables

• Solution 1: Convert the Table to an Unformatted Range

• Solution 2: Use Alternative Functions

I've written many times about the many benefits of formatting your data as a structured table in Microsoft Excel. However, despite this, there's one major issue that continues to throw a spanner in the works—Excel tables are incompatible with dynamic array formulas.

The Problem: Dynamic Array Formulas Don't Work in Excel Tables

A dynamic array formula is a formula whose result spills over from one cell to other cells. What's more, if the result of the formula increases or decreases in size, the array does the same.

Related

Everything You Need to Know About Spill in Excel

It's not worth crying over spilled references.

Posts

For example, typing:

=UNIQUE(A2:A21)

into cell B2 and pressing Enter returns all the unique values from cells A2 to A21 in separate cells in column B. Notice how, when you select any of the cells containing one of those unique values, a blue line reminds you that this is a spilled array.

However, formatted Excel tables are designed to hold rows and columns of independent data, rather than data that spills over from a single cell. So, if you try to enter the same dynamic array formula into a cell in a table, you'll see the #SPILL! error.

Related

Everything You Need to Know About Excel Tables (And Why You Should Always Use Them)

This could totally change how you work in Excel.

Posts 2

Both Excel tables and dynamic array formulas are containers for automatically expanding data. So, if you try to put one thing that automatically expands (such as a dynamic array formula) inside something else that also automatically expands (such as an Excel table), Excel can't work out which of the two should dictate the size of the dataset.

In an ideal world, Microsoft would find a way to let you use dynamic array formulas within tables, with the table expanding and shrinking automatically to accommodate the spilled array. Until it does, however, I'll continue to use the following alternative approaches, which, although useful, aren't perfect fixes.

Solution 1: Convert the Table to an Unformatted Range

One way to get around this issue is to convert the Excel table to a range by selecting any cell in the table, and clicking "Convert To Range" in the Table Design tab.

Now, because the data is no longer in a structured Excel table, you can use dynamic array formulas to your heart's content.

How to Format a Spilled Array in Excel

Don't apply direct formatting.

Posts

Instead, to keep your data in a structured tabular format, you could use alternative functions that can be applied to individual rows in a table column without spilling the results.

Solution 2: Use Alternative Functions

Microsoft is always looking for ways to simplify formula-writing tasks in Excel, which is why it's introduced many swanky functions that return more than one result in recent years. However, because these don't work in tables, you could revert to some of the older functions that don't have this capability.

That said, because older functions often require more complex formulas than new, dynamic functions, they usually require more arguments and may be less efficient, especially in larger datasets.

Generating a Dynamic Numbered List

Dynamic Array Function	SEQUENCE (with COUNTA)
What This Function Does	Returns a sequence of numbers
Alternative Function	ROW

In this example, typing:

=SEQUENCE(COUNTA(B:B)-1,1,1)

into cell A2 counts the number of non-blank cells in column B, minuses one to account for the header row, and creates a single column of sequential numbers, starting at 1. What's more, if data is added to or removed from column B, the count in column A will update automatically.

Related

Don’t Create Numbered Lists Manually in Excel: Use SEQUENCE and COUNTA Instead

This simple formula will save you lots of time.

Posts 6

To get the same outcome in an Excel table without using a dynamic array formula, in cell A2, type:

=ROW()-ROW(TableX[[#Headers],[Name]])

Creating an Array of Random Numbers

Dynamic Array Function	RANDARRAY
What This Function Does	Returns an array of random numbers
Alternative Function	RANDBETWEEN

In this unformatted range, typing:

=RANDARRAY(20,1,50,100,TRUE)

into cell A2 returns a random list of whole numbers between 50 and 100 in cells A2 to A21.

Related

How to Generate Random Numbers in Microsoft Excel

Use a simple function, or get more advanced control with a generator tool.

Posts

To do the same in a formatted table, in cell A2, type:

=RANDBETWEEN(50,100)

into cell A2, and click and drag the table handle to expand the table downwards until there are 20 rows of data.

Splitting a Cell Into Separate Columns

Dynamic Array Function	TEXTSPLIT
What This Function Does	Splits text into rows or columns using delimiters
Alternative Functions	TEXTBEFORE and TEXTAFTER

In this example, TEXTSPLIT takes the name in cell A2, and spills the split version of each name into cells B2 and C2, with the comma-space string acting as the delimiter. Then, selecting cell B2 and double-clicking the fill handle applies the dynamic array formula to the remaining cells in the range.

=TEXTSPLIT(A2,", ")

When working with Excel tables, instead of using this dynamic array formula, you can use TEXTBEFORE and TEXTAFTER.

In cell B2, type:

=TEXTBEFORE([@Name],",")

to extract the text before the comma from the Name column.

Then, in cell C2, type:

=TEXTAFTER([@Name]," ")

to extract the text after the space.

Related

4 Ways to Split Data Into Multiple Columns in Microsoft Excel

You're spoiled for choice!

Posts 1

Extracting Unique Values

Dynamic Array Function	UNIQUE
What This Function Does	Returns unique values from a range
Alternative Functions	INDEX, UNIQUE, and ROW

In this regular range, this UNIQUE formula lists all the unique values in cells A2 to A50.

=UNIQUE(A2:.A50)

How to List and Sort Unique Values and Text in Microsoft Excel

Create a list of unique names, dates, or other data in your spreadsheet with a simple function.

Posts

To achieve a similar outcome in an Excel table, in cell B2, type:

=INDEX(UNIQUE($A$1:$A$50),ROW(A2))

where

• INDEX() returns a value,
• UNIQUE($A$1:$A$50), which is the first argument of the INDEX formula, finds unique values in cells A1 to A50, and
• ROW(A2), which is the second argument of the INDEX formula, returns the nth unique value, where n is the row number of the active cell.

However, although this successfully finds all the unique values from the source data, the table doesn't expand and contract automatically. To account for this, in the Table Design ribbon, check "Total Row," and expand the resultant drop-down at the bottom of the table to change the aggregation type to "Count." This counts the unique values in the table column.

Then, in a separate cell, type:

=COUNTA(UNIQUE($A$2:.$A$50))

to count unique values in the source range.

Now, if the count in the total row of the table doesn't match the count in the COUNTA-UNIQUE cell, you know you need to expand or reduce your table size accordingly.

---

Yes, there are ways to navigate the incompatibility between Excel tables and dynamic array formulas, but admittedly, they're cumbersome and time-consuming. That's why we all need Microsoft to do us a favor and find a way to integrate these two useful tools soner rather than later.

The above is the detailed content of I Love Using Excel Tables, but I Wish Microsoft Fixed One Major Problem. For more information, please follow other related articles on the PHP Chinese website!