Excel Dynamic Arrays Guide Covers FILTER, SORT, UNIQUE for Business Use

What Are Dynamic Arrays

Dynamic arrays are a mechanism that lets a single formula return multiple results. The results automatically expand into adjacent cells. This automatic expansion is called spill. Previously, you had to copy formulas across multiple cells. With dynamic arrays, you only need to enter the formula in the starting cell. Even if the number of rows in the source changes, the output range follows automatically. This greatly reduces the maintenance burden for summary and extraction tables. The official Microsoft documentation (https://support.microsoft.com/ja-jp/office/動的配列数式とスピルされた配列動作-205c6b06-03ba-4151-89a1-87a7eb36e531) also describes it as a core feature.

They are supported on Microsoft 365 and Excel 2021 and later. Excel for the web behaves the same way. In older versions of Excel, spills are not recalculated. An operational design that preserves compatibility is essential.

How They Differ from Legacy Array Formulas

Legacy array formulas required pressing Ctrl, Shift, and Enter together. You also had to pre-select the output range. Every time the range changed, you had to re-enter the formula. With dynamic arrays, those steps are unnecessary. Spill is executed with a normal Enter confirmation. The formula is stored only in the starting cell. It is not copied to other cells. This difference improves auditability and maintainability.

Old-style CSE arrays still work. For new workbooks, replacement with dynamic arrays is recommended. Replacement is especially effective for filtering and sorting.

How to Use the FILTER Function

The syntax is =FILTER(array, include, [if_empty]). Specify the entire table you want to return in the first argument. Specify the filter criteria in the second argument. The third argument specifies what to display when there are no matches. If omitted, #CALC! is returned.

For example, to extract only the Sales Dept from an employee table. Write =FILTER(A2:D100, C2:C100=“Sales Dept”, “No matches”). Match the number of rows in the criteria range and the return range. Avoid full-column references because they make calculation heavy.

AND logic for multiple conditions is expressed with multiplication. It becomes =FILTER(A2:D100, (C2:C100=“Sales Dept”)*(E2:E100>=50)). OR conditions are expressed with addition. It becomes =FILTER(A2:D100, (C2:C100=“Sales Dept”)+(C2:C100=“Planning Dept”)). For partial matches, combine it with the SEARCH function. A typical example is =FILTER(A2:A100, ISNUMBER(SEARCH(“Tokyo”, B2:B100))).

In practice, there is strong demand for date filtering. =FILTER(A2:D500, MONTH(B2:B500)=4) returns the April data. Filtering by numeric range works the same way. It is suitable for extracting only low-stock items from an inventory table.

How to Use the SORT Function

The syntax is =SORT(array, [sort_index], [sort_order], [by_col]). The second argument indicates which column to sort by. The third argument is 1 for ascending order and -1 for descending order. The fourth argument is FALSE for row direction and TRUE for column direction. Detailed specifications are available in the official Microsoft documentation (https://support.microsoft.com/ja-jp/office/sort-関数-22f63bd0-ccc8-492f-953d-c20e8e44b86c).

Here is an example of sorting a sales table in descending order by amount. =SORT(A2:D100, 4, -1) sorts by the fourth column. The SORTBY function is better for multi-key sorting. Write =SORTBY(A2:D100, C2:C100, 1, D2:D100, -1). This allows sorting in ascending order of deployment and descending order of amount.

It can also be used reliably for date sorting. Mixed text and numeric columns cause incorrect sorting. You need to standardize data types in advance. If blank rows are included, they are treated as 0 and gather at the top. It is safer to sort after removing blanks with FILTER.

How to Use the UNIQUE Function

The syntax is =UNIQUE(array, [by_col], [exactly_once]). The second argument is FALSE for row comparison and TRUE for column comparison. The third argument is FALSE to remove duplicates and TRUE to extract values that occur only once. The operating conditions are described in the official Microsoft documentation (https://support.microsoft.com/ja-jp/office/unique-関数-c5ab87fd-30a3-4ce9-9d1a-40204fb85e98).

A typical example is deduplicating a customer list. =UNIQUE(B2:B1000) returns a list of unique customers. You can also make combinations of multiple columns unique. =UNIQUE(A2:C1000) judges matches across three columns. It can also be used to detect anomalies that occur only once. =UNIQUE(B2:B1000, FALSE, TRUE) extracts one-time values.

For a sorted list, combine it with SORT. =SORT(UNIQUE(B2:B1000)) produces a name list in ascending order. Combine it with the ROWS function to count items. =ROWS(UNIQUE(B2:B1000)) tells you the number of types. It is also useful as source material for Data Validation dropdown options.

Practical Steps Using Combinations

Combined use is more valuable than standalone use. Filtering, sorting, and deduplication can be unified into one formula. Formulas subject to maintenance are consolidated into one. There is no need to create intermediate helper columns.

Step 1 is to narrow down target rows with FILTER. Step 2 is to arrange the display order with SORT. Step 3 is to remove duplicates with UNIQUE. Example: =SORT(UNIQUE(FILTER(B2:B500, D2:D500=“Completed”))). This displays assignees for completed projects in ascending order without duplicates.

Combining with the LET function improves readability. Write =LET(target, FILTER(A2:D500, C2:C500=“Sales Dept”), SORT(target, 4, -1)). This avoids repeating the same range references. It also helps improve calculation speed. Combined with XLOOKUP, it can also be used for price lookups and similar tasks.

Practical uses are wide-ranging.

• Extracting department details from monthly sales
• Creating a unique model-number list from defect history
• Extracting late arrivals from attendance records and sorting by date
• Organizing duplicate subjects from inquiry logs In all cases, the output follows automatically just by updating the source table.

Error Messages and Fixes

The most common is #SPILL!. It occurs when there are values or merged cells in the spill destination. Securing the output range resolves it. Overlap with formatted tables is also a cause. Combined use with structured references requires prior verification.

#CALC! appears when FILTER finds no matches. Specify the third argument to avoid it. Displaying “No matches” is clearer than returning an empty string. #VALUE! appears on type mismatch. Mixed text in a date column is a typical cause. #REF! appears when a reference is deleted. If columns in the source table are deleted, the formula must be fixed.

Full-column references should be avoided. An A:A reference causes evaluation of over one million rows. It leads to file bloat and slow recalculation. Limiting the range used is the basic rule. Use Paste as Values to fix output results. This is required work when distributing to older versions of Excel.

Supported Environments and Operational Notes

Dynamic arrays work fully in Microsoft 365. For perpetual versions, Excel 2021 and later are supported. New calculations cannot be made in Excel 2019 and earlier. Values may be displayed if you only open the file. There is a risk that values will break upon recalculation.

For shared use, unifying versions is desirable. For distribution to business partners, convert to PDF or paste as values. Care is also needed with compatibility with macro recording. Spill ranges change with Resize. Formatting with fixed row counts in VBA tends to break. Applying Conditional Formatting broadly to the output destination is a more stable approach.

Editorial Opinion

In selection, recalculation speed and maintainability should be prioritized. Practical efficiency depends less on FILTER alone than on whether it can be combined with SORT and UNIQUE. When complex conditional branching is required, whether XLOOKUP and LET can be used together is also likely to be a deciding factor.

The pitfall in practice lies in how reference ranges are specified. Full-column references increase calculation load and are likely to cause issues on save. In shared environments, compatibility breakdowns with older versions of Excel occur frequently. They can be avoided with pasting values and limiting ranges.

Going forward, formula generation through Python integration and generative AI support is expected to become mainstream. Dynamic arrays will strengthen their position as the foundation for automated processing. Manual aggregation will decline, and design and validation skills will be tested.

References

• Dynamic array formulas and spilled array behavior
• FILTER function
• SORT function
• UNIQUE function