MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Replace a Pivot Table with 3 Dynamic Arrays


October 01, 2020 - by Bill Jelen

Excel Replace a Pivot Table with 3 Dynamic Arrays. Photo Credit: Nazar Sharafutdinov at Unsplash.com

As the co-author of Pivot Table Data Crunching, I love a good pivot table. But Excel Project Manager Joe McDaid and Excel MVP Roger Govier both pointed out that the three formulas shown here simulate a pivot table and do not have to be refreshed.


To build the report, =SORT(UNIQUE(C2:C392)) provides a vertical list of customers starting in F6. Then, =TRANSPOSE(SORT(UNIQUE(A2:A392))) provides a horizontal list of products starting in G5.

When you specify F6# and G5# in arguments of SUMIFS, Excel returns a two-dimensional result: =SUMIFS(D2:D392,C2:C392,F6#,A2:A392,G5#).

Four columns of source data: Product, Date, Customer and Revenue. Headings are in row 1, data is rows 2:392. =SORT(UNIQUE(C2:C392)) gets a vertical sorted list of customers starting in F7. Then, =TRANSPOSE(SORT(UNIQUE(A2:A392))) gets a horizontal list of products in G5. Finally, =SUMIFS(D2:D392,C2:C392,F6#,A2:A392,G5#) in G6 fills in the Revenue amount for each combination of Customer and Product.

Title Photo: Nazar Sharafutdinov at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.