Beginner to Pro ~ Excel Dynamic Array Formulas - FREE Class

Excel Dynamic Array functions are going to change the way to you work your data forever. Join me for this epic live event where I will demonstrate:

* What is so different about Excel's dynamic array functions
* The new # operator
* 6 important dynamic array functions - FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY
* 25 practical examples of dynamic array functions for business analysis
* Your questions & answers

Sample data, completed example file & more for this lesson:
================================================

Timestamps:
===========
0:00 - Introduction & Welcome
4:30 - Tell me what you are drinking
5:15 - Example data explained
7:40 - What is dynamic array feature and how it differs?
12:20 - The FILTER function
28:00 - Using FILTER with other functions like SUM, COUNTIFS, VLOOKUP
33:00 - FILTER but show non-adjacent columns
40:22 - UNIQUE function
43:12 - UNIQUE with Text functions to generate alphabet frequency
47:12 - UNIQUE combinations
51:00 - SORT & SORTBY functions
53:10 - SEQUENCE function
55:20 - Using SEQUENCE to show top 10 countries by population
1:00:00 - RANDARRAY function
1:05:45 - Your Questions (refer to live chat messages)

#ExcelTips #DynamicArrayFunctions

[Excelambda]

Great session !! if you want to keep the headers of a table after applying any kind of filter to it we select #All table and use this simple trick in any filter formula:
any filter conditions like before. Example:
=FILTER(data[#All], (SEQUENCE(ROWS(data[#All]))=1)+(data[[#All], [Agricultural land]]>20%)) 😉

[wayneedmondson]

Hi Chandoo. Great session on Dynamic Array functions! Lots of useful tips and tricks there. Also, many thanks for posting the YouTube replay, as I was tied up during the live session.. and for providing the sample data to follow along and practice after the fact. That is super helpful! Looking forward to more of these in the future. Always learn something new at your channel and via your website. Thanks for generously sharing your knowledge and skills :)) Thumbs up!!

[rhonamocke]

Hallo Chandoo, been following you almost for 8 years and I think your work is stunning. Your a very cleaver boy

[AM-mphy]

Thanks Chandoo. This is a great live class. You had been teaching me great techniques enabling me to do my work much easier. God Bless You and your families.

[aiasaiascon]

Thanks for generously sharing your knowledge and skills :)) Thumbs up!! (I borrowed that bit it is the TRUTH!!!)

[jeffvannoy]

Terrific Class ! Do you ever use the formatting brush, on the Clipboard from the HOME page ? If you highlight all the cells in the Data spreadsheet from B3 to L102, then click on the paintbrush, then go to the DA Examples spreadsheet, and click on cell B4, the formatting from the Data spreadsheet is applied to the same range of cells in the DA Examples spreadsheet. Then when you change the % value in cell B2, there's no need to update the formatting.

[rautoramarautorama]

Love you chandoo..my first and the best excel teacher

[rhonamocke]

sorry i missed the live session, but hi from Australia - previously from South Africa

[Lovelynuts]

Thanks for the video Chandoo, Another long-time user here, I had signed up to your website 7 or 8 years ago and was sad to discover recently that my subscription ran out. When life stops getting in the way, I'll be re-subbing!

[MrMrferguson]

Amazing demo and explanations as always! Thanks, Chandoo!

[keshavprasadsingh]

thank you so much sir this live class.😊

[seen]

Thanks Chandoo for such a great session, the Indian movie joke is from "Nuvve Nuvve" :)

[luisfernandocuestasanchez]

You are the best in the world
Thanks a lot man ¡¡¡

[canirmalchoudhary]

Absolutely nice session... Specially I enjoy your old vs hot coffee joke. Thanks a lot chandoo.

[usmaniqbal]

I think, this is better way...
=index(filter (data, data[agricultural land]>50%), sequence (count(V:V)), {1, 5})

[visvasadevelopers]

How to bookmark and get the second file with all formula

[JoseAntonioMorato]

To filter non-adjacent columns:
=FILTER(CHOOSE({1, 2}, data[Country], data[Population]), data[Agricultural land]>65%)
=FILTER(FILTER(data, data[Agricultural land]>65%), {1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0})
=INDEX(FILTER(data, data[Agricultural land]>65%), SEQUENCE(COUNTA(FILTER(data, data[Agricultural land]>65%))/COLUMNS(data[#Headers])), {1, 5}) 🤗

[rohitgoyal]

Sir How to click on enable editing using keyboard in word or excel