Advanced Excel
Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE, LET and LAMBDA, Power Query for repeatable cleaning, and protecting, printing and sharing workbooks.
About 3 hours of study
Learning objectives
- Build live lists with dynamic arrays
- Clean data repeatably with Power Query
- Protect and share workbooks safely
Lessons
- 1
Dynamic arrays, LET and LAMBDA Advanced
Formulas that return many values: spilling, the # spill reference, FILTER, SORT, SORTBY, UNIQUE and SEQUENCE, the #SPILL! error, and naming parts of formulas with LET and writing your own functions with LAMBDA.
- 2
Power Query: getting and cleaning data Advanced
Importing and cleaning data with Power Query: connecting to files and folders, recorded applied steps, removing columns and rows, splitting, changing types, unpivoting, merging and appending queries, and refreshing.
- 3
Protecting, printing and sharing workbooks Advanced
Finishing a workbook for others: locking cells and protecting sheets and structure, passwords to open, printing with print areas, titles and scaling, sharing and co-authoring in OneDrive, and an honest look at macros and Office Scripts.