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!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#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
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
In today's video: replacing a pivot table with three dynamic array formulas.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
Bill's book on Dynamic Arrays: https://www.mrexcel.com/products/exce...
#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
Auf dieser Seite können Sie das Online-Video Excel Replace a Pivot Table with 3 Dynamic Array Formulas - Episode 2244 mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer MrExcel.com 02 Oktober 2018 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 15,472 Mal angesehen und es wurde von 461 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
