Excel Charles Williams Excel Revolution: Making Dynamic Arrays Better - Episode 2310

Microsoft Excel Tutorial: Walkthrough of a new add-in from Excel MVP Charles Williams.

Welcome back to the MrExcel netcast! In this episode, we have a special guest, Excel MVP Charles Williams, who has made dynamic arrays even better with his new add-in. As Charles says, "Once you've had a chance to use dynamic arrays for awhile, you start to realize what new features would make dynamic arrays a bit more useful". And that's exactly what Charles has done with his add-in.

After using the add-in for just an hour, I was blown away by the improvements Charles has made. It took a team of developers in Redmond over a year to develop dynamic arrays, but Charles has made them an order of magnitude better in just 3 weeks! That's the power of Charles' expertise and dedication to making Excel even more efficient and user-friendly.

For those who don't know Charles, he is a legend in the Excel community. He worked on the original IBM PC and his code is still in the operating system that we all use today. His new speed tools add-in introduces over 100 powerful, fast-calculating functions for Excel. And in this episode, I'll be showing you just a few of these amazing functions.

One of the most useful functions in Charles' add-in is the TOTALS function. This function allows you to add a total row or column to a dynamic array, even as it changes size. No more worrying about adjusting your formulas or getting SPILL errors. And for those who prefer their totals at the top or left of the array, Charles has also included the SUMCOLS and SUMROWS functions.

But that's not all, Charles' add-in also has the ability to exclude certain columns from the total calculation. And for those who need more than just a simple sum, there are 11 different functions to choose from, including average, count, and count numbers. And the best part? Charles is not doing this for the money, he just believes that these features should be included in Excel and has even offered them to the Excel team.

So, if you want to support Charles and his amazing work, be sure to check out his add-in and show him some love. And let's also try to get an Excel.UserVoice going to encourage Microsoft to include these features in Excel sooner rather than later. The link to Charles' add-in and the Excel.UserVoice page can be found in the description below.

And as always, if you enjoyed this episode, please subscribe to our channel and hit the notification bell to stay updated on all our latest videos. And don't forget to leave a comment or question down below, we love hearing from our viewers. Thanks for watching and we'll see you next time for another netcast from MrExcel.

#excel
#microsoft
#excelformula
#excelformulasandfunctions
#exceladdin
#exceldynamicarray

This video answers these common search terms:
how to add sum to dynamic array in excel
how to add total row below array formula in excel
add to add a total to FILTER function in excel
what is the fastexcel add-in from charles williams

Table of Contents
(0:00) Walkthrough: Charles Williams improves Dynamic Arrays
(0:15) Feature gaps patched by Charles in 3 weeks
(1:43) Summing a Formula of unknown size
(2:41) Add Totals to array formula in Excel
(4:55) Add totals to FILTER function in Excel
(5:43) Buy Charles Williams add-in
(6:18) Clicking Like really helps the algorithm

[excelisfun]

I can't wait to vote for these new Charles Williams functions!!!!

[excelisfun]

Thanks for the great video, Mr Excel!!!

[sasavienne]

Thanks Mr. Excel.. Thumps up.
Long live our great Excel community. Best regards from Germany. Salim

[peterbartholomew]

I recommend trying Charles's add-in. It is, as the name might suggest, extremely fast and offers all the functionality (like IntelliSense) of native functions. The array functions are the main event for me but the regular expression functions knock the socks off native Excel search and extract functions.

[spilledgraphics]

Very cool stuff Bill! Shout out to Charles!

[bevonclarke]

Wish it could be extended to add more than one column where you might want use sumif and countif based on certain criteria or just count and countif and maybe divide the two to get a percentage as an answer...would make my life very easy...

[ricos]

Those are some pretty special functions. It's how I've always imagined excel might be built, with functions expressing entire tables. Maybe even see some formatting functions too.

[DougHExcel]

wat?!? It's like magic...excel magic.

[GeertDelmulle]

Back in the day (about 1.5 years ago) when Dynamic Arrays first came out, ExcelisFun (Mike) went “crazy array formula” on the dynamic arrays and added those sums that way. So it is doable, but rather complex. Having a dynamic function to do this would solve this problem the easy way, and —dare I say it— in a way that is completely following the filosophy of dynamic arrays.

[barttitulaerexcelbart]

Thanks Bill. One tip: please check the real formula behind the AVERAGE. If you use AUTOSUM the actual formula of the average is not quite correct, because it should take into account all "numbers", not the average of the columns. If you have always data in the array that is not a problem. Well .... now I have another question: is it possible at all to have empty values into a dynamic array? Since it is the beginning of this DAF revolution, I hope this theoretical question might be relevant at all....;)

[JonathanExcels]

Charles Williams post in Excel User Voice

[HusseinKorish]

Absolutley amazing ... i used a trick made by Mike Girvin (mmult + sequence + Rows) to get the totals .... is it available now in office 365 ? ....please put a link to Charles Williams channel

[roywilson]

Very interesting video, Bill. This extra functionality is something that a lot of us have been waiting for. Let's hope that the Excel developer team implement it at the earliest possible time!