Excel 2020: Replace a Pivot Table with 3 Dynamic Arrays
October 01, 2020 - by Bill Jelen
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:
Title Photo: Nazar Sharafutdinov at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.