Excel Replace a Pivot Table with 3 Dynamic Array Formulas - Episode 2244

Microsoft Excel Tutorial: Building an Excel pivot table using formulas.

Welcome back to the MrExcel netcast! In this episode, we will be diving into the world of dynamic arrays and how they can replace a traditional pivot table. If you haven't heard, these new arrays were announced at Ignite on September 24, 2018 and are causing quite a stir in the Excel community.

But before we get into the tutorial, I have some exciting news to share. I have written a 60-page eBook with 30 examples of how to use these dynamic arrays, and for a limited time, I am giving it away for free! That's right, you can get your hands on this valuable resource before I start charging for it in 2019. Just click the link in the YouTube description to download your copy.

Now, let's talk about the roll out of these dynamic arrays. As you may have noticed, the release has been a lot slower than anticipated. This is because the Excel team is being cautious and taking their time to ensure that these changes do not break any existing add-ins or formulas. They have even created a new reference notation, =E3#, to refer to the entire range that the array occupies. This is called the Spilled Formula Operator, but we've been throwing around different names for it like Array Range Reference or The Spiller.

But enough talk, let's get into the tutorial. Today, we will be replacing a pivot table with just three dynamic array formulas. We will use the UNIQUE and SORT functions to create a list of customers and products, and then use the SUMIFS function to calculate the revenue for each customer and product combination. The beauty of this is that the formulas will automatically update as the data changes, just like a pivot table.

If you want to follow along with this tutorial, you can download the workbook from the link in the YouTube description. And if you like what you see, don't forget to subscribe and hit that bell icon to be notified of future episodes. Also, please share this video with your friends and colleagues to help me reach my goal of selling 10,000 copies of my eBook and making it a best-seller.

In summary, in this episode we covered the official name for the new arrays, the slow roll out of the feature, and how to replace a pivot table with dynamic array formulas. I hope you found this tutorial helpful and stay tuned for more netcasts from MrExcel. Thank you for watching!

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
=E3# - Spilled Formula Operator
=UNIQUE and =SORT functions
Automatic updates in cross tab report
Broadcasting in arrays
Creating a pivot table with live formulas
Dynamic arrays eBook
Episode 2244
Ignite conference
Learn Excel from MrExcel podcast
New arrays
Replace a Pivot Table with Three Dynamic Array Formulas
Slow roll out of dynamic arrays

In today's video: replacing a pivot table with three dynamic array formulas.

#pivottable
#pivot_table
#excelpivottable

This video answers these common search terms:
=E3# - Spilled Formula Operator
=UNIQUE and =SORT functions
Automatic updates in cross tab report
Broadcasting in arrays
Creating a pivot table with live formulas
Dynamic arrays eBook
Episode 2244
Ignite conference
Learn Excel from MrExcel podcast
New arrays
Replace a Pivot Table with Three Dynamic Array Formulas
Slow roll out of dynamic arrays

Table of Contents
(0:00) How to create a crosstab report in Excel from 3 array formulas
(0:13) The official name for the new arrays are Dynamic Arrays, not Modern Arrays
(0:23) I've written a 60-page e-book documenting 30 ways to use them.
(0:36) The roll-out is going to be super-slow, as the Excel team tries to figure out if they will break anything.
(1:12) =E3# is an Array-Range Reference Notation
(1:44) Creating a cross-tab report to replace a pivot table with three formulas
(2:00) SORT/UNIQUE for ROWS
(2:22) TRANSPOSE/SORT/UNIQUE for COLUMNS
(2:39) SUMIFS in the Values area
(3:22) This is an example of Broadcasting arrays.
(4:12) Change the underlying data and the report updates
(4:22) Clicking Like really helps the algorithm

[khalidrehman]

"Another Wow" These new functions are amazing.

[martintaylor]

Awesome - just been looking for something to make formulas 'alongside' dynamic ranges also dynamic. 3 formulas!!! So easy - love it.

[amateurtorque]

My heart skipped a beat when the cross tab table automatically included the new customer. Thank you

[mazarata]

OMG what a great new feature. Thanks a lot for putting it together for us!

[excelisfun]

Totally amazing!!! Thanks for the video. I just wish I had the correct version...

[Luciano_mp]

Amazing, new functions, new tips, thanks for the book. Thanks for this tutorial.

[wmfexcel]

It's a whole new world. Thanks for sharing. :)

[simfinso]

just wow Trick.You are awesome Mr.Excel.

[ricos]

Holy shitballs, this is a massive change to Excel. I'm guessing mere mortals aren't ready for these updates, but I've just devoured your book anyway, t'was excellent. As an aside, I couldn't get the SUM(LEN(A1:C4)) formula to work in my existing (not updated) version of excel when typing into D4 (p39 of your book), but I'm sure it will be obselete in due course anyway! Thanks for your book and for this video highlighting said book!

[henryg]

GAMECHANGER!!! Lots of new things to learn though. Shame have to wait for it.

I use Pivot Tables almost only under duress, probably mainly because of no easy way to guaratee will be refreshd when needed.

[aycampos]

whaaaattt???? mind blowing, so cool, thank you Mr. Excel

[mattschoular]

Thanks MrExcel…..Just when I thought I had mastered Pivot Tables....And now they are obsolete... Just kidding...Thanks for the free E-book...Keep up the great work..

[ennykraft]

I can't wait till these new functions become available and I wish Microsoft would update us on their progress. These are going to be highly useful but I don't think they'll replace pivot tables. Not having to remember to update is a big advantage but realistically you will update often only if you have a connection to a database and in that case, these new functions wouldn't work. The beauty of pivot tables over sumifs is that you don't have to write a formula for them. I often teach Excel for beginners and everybody can create them easily after being shown how they work. Sumifs are a totally different ballgame.

[amateurtorque]

If the customer field in one of the cells is blank, how do you ensure the cross tab includes any revenue in adjacent rows?

[MsThesaurusRex]

This feature has been available with Google Sheets for a very long time. Excel is only catching up and it doesn't seem to work on Mac.

[QuickMadeUpName]

what i'm curious about is if these are used within tables do they stay dynamic?? so when a the table contracts and expands, do the formulas change?

[ahmedal-dossary]

You are awesome Bill..!!

Start to get jealous for not having the new version of excel :(

I’m curious, will my file slow down remarkably if I utilize lots of these new array formulas?

[mathem]

I use ms office 2019, how do you look for a unique formula ... please explain

[wayneedmondson]

Hi Mr. Excel.. excellent to see this new functionality on the horizon. I put this question to Mike Girvin.. what about a dynamic sum of the columns and rows of the spilled array? My first thought was an argument in the dynamic array function that would add the sums. Mike pointed out.. to what function would the argument be added.. all of them? So, that is probably not the way to go. Second thought was a "Totals" function that you could use to wrap around whatever dynamic array function or functions you are using to produce the spilled array. The TOTALS() function would then instruct EXCEL to dynamically add sums to the spilled columns and rows along with some formatting like a single and double underline above and below the totals, much like how you would manually design a cross-tab table with sums. What do you think? I wonder if Joe McDaid and team have thought about this or are working on it.. can only hope. In the mean time, designing sums manually below and to the right of the expected spill range should suffice. But wouldn't it be great if it were dynamic and automatic, as described above? Thanks again for all the insights. Can't wait to get my chance to work with the new functions when they are rolled out. Thumbs up!

[lazalazarevic]

Thank you, Bill. So if I understand correctly Array-Range Reference Notation is available XL 2019 only?
Thank you for the book <3