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

Lessons

  1. 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. 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. 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.

Module practice

Module quiz

1. What does =UNIQUE(A2:A100) do?
2. What does Power Query do when you click Refresh?
3. Which protection actually stops someone reading a file?
4. What does #SPILL! mean?
5. Which Power Query operation turns month columns into rows?
6. Why use LET in a long formula?

‹ Analysis and chartsFinal assessment ›