Use array & conditional formulas (SUMPRODUCT, SUMIFS, MAXIFS/MINIFS) with multi-criteria logic
Perform text and date transformations: join/extract text; handle month shifts and fiscal periods
Create Data Validation lists, including dependent (cascading) drop-downs using INDIRECT
Apply lookup strategies: VLOOKUP/INDEX; optimize speed; handle multi-criteria and multi-results
Build PivotTables and dashboards with charts and conditional formatting (formula-based row rules)
Use Power Query to import/clean/combine many files and load to Data Model without worksheets
Filter/transform data with Advanced Filter for complex extracts vs regular filter/Power Query
Model data with Power Pivot: relationships, DAX measures (iterators), and calendar/date tables
Understand Power BI Desktop basics: apply queries and load into the model
Use financial functions (PMT/RATE/NPER/FV) with correct period conversions
Record macros and save/run them using the macro-enabled .xlsm workbook format
Course Description
Take your Excel skills from competent to confident and start working the way analysts, finance teams, and operations pros do every day. This free online course is built for people who already know the basics and now want speed, accuracy, and clearer insight from real-world data. You will learn how to structure datasets properly so your work stays reliable as rows are added, and how to create formulas that remain flexible when assumptions change.
Instead of getting stuck cleaning messy files by hand, you will practice transforming data efficiently with Power Query, combining information from multiple sources, and choosing the right loading options for large imports. You will also develop a strong command of references and advanced formula techniques, including conditional calculations, text and date logic, and smarter lookup patterns designed to keep large workbooks responsive.
As you progress, you will shift from simply reporting numbers to building interactive analysis. You will work with PivotTables and the Data Model to connect tables through relationships, reduce the need for helper columns, and create more scalable reports. You will explore techniques that help you visualize results with charts and conditional formatting, then bring it together in dashboards that can be refreshed as new files arrive.
The course also introduces automation and productivity boosters: data validation to reduce input errors, advanced filtering when it is the fastest tool for the job, and practical macro recording foundations that open the door to VBA-driven efficiency. By the end, you should be able to handle bigger datasets, deliver cleaner reporting, and build workflows that are easier to maintain—skills that translate directly to better performance in technology, programming-adjacent roles, and any Excel-heavy job.
Course content
Video class: Free Advanced Excel Course at YouTube01m
Exercise: Which set of topics best matches what is covered in the advanced Excel course?
Video class: Highline Excel 2016 Class 01: Excel Fundamentals: Efficiency, Data, Data Sets, Formatting1h21m
Exercise: When converting a proper data set into an Excel Table to make formulas and PivotTables automatically include new rows, which keyboard shortcut creates the table?
Video class: Highline Excel 2016 Class 02: Excel Fundamentals: Comprehensive Formula Lesson (15 Examples)55m
Exercise: In Excel’s golden rule for formulas, what should you do when a formula input can change (like a tax rate)?
Video class: Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query1h43m
Video class: Introduction to Power Query50m
Exercise: When importing over a million rows from multiple text files using Power Query, which Close & Load option should you choose to avoid trying to load the data onto a worksheet?
Video class: Highline Excel 2016 Class 04: References: Relative, Absolute, Mixed, Sheet, Workbook, 3-D, Table…1h43m
Exercise: When copying a budget formula across months and down expense rows, which reference type lets you lock the revenue row while allowing the month column to change?
Video class: Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples52m
Exercise: When you need to perform multiple array calculations and then add the results in one cell without using Control-Shift-Enter, which function is designed for this?
Video class: Highline Excel 2016 Class 06: Conditional Calculations with Excel Formulas: Comprehensive Lessons1h23m
Exercise: When you supply multiple criteria_range/criteria pairs to SUMIFS, what logical test does Excel apply by default?
Video class: Highline Excel 2016 Class 07 Excel 2016 MAXIFS, MINIFS11m
Exercise: Which statement best describes why MINIFS/MAXIFS are easier than older methods for finding a minimum/maximum with multiple criteria?
Video class: Highline Excel 2016 Class 08: Text Formulas and Text Functions to Join and Extract Data29m
Exercise: Which function can join multiple cells while ignoring empty cells by using an argument like TRUE for ignoring blanks?
Video class: Highline Excel 2016 Class 09: Date Formulas and Date Functions, including Fiscal Quarter22m
Exercise: Which Excel function is best for returning the same calendar day in the next month (instead of adding a fixed number of days)?
Video class: Highline Excel 2016 Class 10: Excel Data Validation (List, Custom23m
Exercise: In a dependent (cascading) Data Validation setup, what function lets a second drop-down use the text selected in the first drop-down as a reference to a named range?
Video class: Highline Excel 2016 Class 11: Lookup Functions1h18m
Exercise: In a large workbook with many VLOOKUP formulas, which approach can reduce calculation time when lookup cells are often empty?
Video class: Highline Excel 2016 Class 12: Two Lookup Values in VLOOKUP? Helper Column or Array Formula.07m
Exercise: When you need to return a value (like Status) using two lookup criteria (Server and Event), what is the most convenient approach in many cases?
Video class: Highline Excel 2016 Class 13: One Lookup Value to Return Multiple Items: INDEX09m
Exercise: Which function can replace SMALL in an array operation to avoid using Ctrl+Shift+Enter when extracting multiple matching lookup results?
Video class: Highline Excel 2016 Class 14: VLOOKUP as Relationship in Power Pivot Data Model08m
Exercise: When is it generally better to use relationships in the Data Model (Power Pivot) instead of adding a VLOOKUP helper column?
Video class: Highline Excel 2016 Class 15: Excel Charts to Visualize Data: Comprehensive Lesson 11 Chart Examples52m
Video class: Highline Excel 2016 Class 16: Conditional Formatting to Visualize Data: Built-in44m
Exercise: Which Conditional Formatting rule type is used to highlight an entire row based on a logical test (instead of just formatting cells that directly meet a built-in condition)?
Video class: Excel Magic Trick 1379: Power Query Combine Binary Expand Button Update January 201707m
Exercise: In the January 2017 update, what key improvement occurs when using Power Query’s Combine Binaries (expand) button on multiple files?
Video class: Highline Excel 2016 Class 17: How to Build Excel Dashboard PivotTable, Chart, Conditional Formatting36m
Exercise: When importing multiple text files to build a refreshable dashboard in Excel 2016, which Power Query option best ensures the data loads into the Data Model without filling a worksheet table?
Video class: Highline Excel 2016 Class 18: Clean24m
Exercise: Which Excel feature is especially efficient for converting ISO-style text dates into real Excel serial date values?
Video class: Highline Excel 2016 Class 19: Transform Data Sets using Advanced Filter (8 Examples)33m
Exercise: When should you use Advanced Filter instead of the regular Filter feature or Power Query?
Video class: Highline Excel 2016 Class 20: Power Query Import Multiple Excel Files24m
Exercise: When importing multiple Excel files from a folder using Power Query, which step helps ensure only Excel workbooks are imported (and not other file types)?
Video class: Highline Excel 2016 Class 21: Power Query Import Multiple Excel Workbooks with Multiple Sheets09m
Exercise: In Power Query, what does using the second argument as lowercase true in Excel.Workbook(Content, true) accomplish?
Video class: Highline Excel 2016 Class 22: How To Build Data Model1h04m
Exercise: In a Power Pivot data model, which DAX approach iterates row-by-row over a table to calculate an expression (like revenue per transaction) without storing a calculated column in the table?
Video class: Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 201603m
Exercise: Why would you create an automatic Calendar (Date) table in the Data Model instead of only using PivotTable Group By Date?
Video class: Highline Excel 2016 Class 23: Intro to Power BI Desktop to Create Data Model24m
Exercise: In Power BI Desktop (compared to Excel Power Query), what button applies the query changes to load data into the model?
Video class: Highline Excel 2016 Class 24: Financial Functions: PMT, RATE, NPER and FV 12 Examples41m
Exercise: When using PMT to calculate a monthly loan payment, which inputs must be converted to monthly units to match the payment frequency?
Video class: Highline Excel 2016 Class 25: Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more…08m
Exercise: Why can a SUM of displayed tax amounts be “wrong” unless you use ROUND in the calculation?
Video class: Highline Excel 2016 Class 26: Macro Recorder Basics40m
Exercise: Which Excel file format must you use to save and run recorded macros in a workbook?
