Even though Pivot Tables are very useful in data management and analysis, they don’t automatically update when you make changes. In this guide, we’re going to show you how to replace pivot tables with dynamic array formulas in Excel.

## Simple Pivot Table

The first step is to create a list for row and/or column titles. Then, you can use aggregation functions like **SUMIFS** or **COUNTIFS**.

Thanks to the dynamic array function **UNIQUE**, you can get a list of unique items from an array. Using the **SORT** or **SORTBY** functions these unique items can be sorted. The following formula lists unique items in a column in ascending order.

By default, this formula returns values in a single column, because the data is inside a single column.

If you need to use data with different column headers, you need to *transpose *the return array. Thanks to the dynamic arrays, the **TRANSPOSE** function does not require using the **Ctrl + Shift + Enter** combination anymore.

Wrap the row formula inside the **TRANSPOSE** function to *spill* the values into columns.

The final step is to use one of Excel’s aggregation functions which are,

**SUMIF, COUNTIF**and

**AVEREAGEIF**limit you to using a single condition.

Select the first (top-left) cell where the aggregated data is to be placed. The function must include the data column, as well as columns of row and column titles. The trick is selecting the entire title row or column when entering the data.

If you check the formula, you can see that there are dash (#) characters next to the row and header titles: *$P$5#* and *$Q$4#*

The dash character is the *spill range operator* which indicates a *spilled* range. As a result, you can use a range without knowing where it ends. Now you have a **dynamic** pivot table!

## Pivot Table with Filter

Next topic in how to replace pivot tables with dynamic array formulas article is adding filters. This feature can be mimicked by using formulas with certain limitations. Let’s see how you can add filter support in *dynamic array pivot tables*.

The first step is to generate the items to be used in filters. You can use the **SORT** and **UNIQUE **formulas, because items in the filter options must be unique and sorted.

Select a cell you want to use as filter items and add a data validation list. You can open the dialog from **Data > Data Validation** command in the *Ribbon*.

In the **Data Validation** dialog, select **List** in the **Allow** box and enter the cell of the filter list formula with the spill operator. This action ensures to update your list each time the data is changed. Click **OK **to apply the settings.

For adding filters, we need to add another *criteria range-value *pair. You need to add new functions into the formulas for the rows and column titles.

The **FILTER **function can return an array based on another array containing *TRUE/FALSE* values. Add a **FILTER **function into the **SORT-UNIQUE **combination to provide the filtered data to these functions.

Here is a basic syntax of filtered rows formula:

Although this formula is enough for listing the filtered rows, it doesn’t cover the scenario of an empty filter, or show all data without applying any filtering. You can handle both scenarios with a simple **IF** formula. The following formula can return all values (no filtering) if the filter cell is empty. The formula ensures this by sending a *TRUE* value when <filter cell> equals to an empty string (“”).

Remember to add the **FILTER **function into column title formula as well.

For aggregation, you need to add *the filter column* and a simple condition for *the filter cell* as individual arguments. The condition makes the function search for *not empty *cells when the <filter cell> is empty.

## Multi-column Pivot Table

Another useful feature of a Pivot Table is the multiple-field support for row and column titles. Excel can automatically group fields. You can drag and drop to add or remove fields. In this section of *how to replace pivot tables with dynamic array formulas* article, we will show you the multi-column support without filtering.

Let’s assume that you want to add a field as the second-level row. You need use the **UNIQUE** function on both columns. The **UNIQUE **function evaluates every cell as a group in each row. This behavior returns a similar result to what you get in a Pivot Table.

The tricky part is giving multiple columns to the **UNIQUE** function. You can use the **INDEX** function which can return all rows of given columns. The **INDEX **function needs numbers and indexes to return data.

Since all rows are required in the beginning, we need to number the rows, like 1 for the first row and 5 for the fifth. Instead of just putting these numbers, we can use the **SEQUENCE** function which returns an array of sequential numbers. With help of the **ROWS **function, the **SEQUENCE **formula can return high enough number to cover every row.

A static array can determine the columns. In our example, we used {2,4} to get the second and fourth columns.

Adding each column of the row titles as a separate *criteria range-criteria value* pairs will break the dynamic structure. Instead, you need a way to use the *spilled* data. The **INDEX** function can solve the problem in a similar way. Instead of static column references, using the **INDEX **function to return a row-title column by supplying column numbers.

The empty argument of the **INDEX **function tells to return all rows. This doesn’t work for row titles due to the multiple number of columns.

## Multi-column Pivot Table w Filter

The final part of our how to replace pivot tables with dynamic array formulas in Excel article combining the previous steps. When the filter cell is empty, the formula should return a two-column **array of TRUE** instead of single

*TRUE*value.

=SORT(UNIQUE(FILTER(INDEX(<entire data>,SEQUENCE(ROWS(<entire data>)),<array column numbers>),IF(<filter cell>=””,{TRUE,TRUE},<filter column>=<filter cell>))))

Since we have two columns, the array in our formula includes two *TRUE* values. Increase this number if you have more values.