Free online courseExcel Intermediate to Advanced: Formulas, PivotTables, Power Query, Dashboards and VBA
Duration of the online course: 18 hours and 53 minutes
New
Free intermediate-to-advanced Excel course covering formulas, PivotTables, Power Query, dashboards, data modeling, and macro automation.
In this free course, learn about
Course Overview and Excel Fundamentals
Data Analysis Foundations with PivotTables and Power Query
Advanced Formulas: Arrays, Conditional Logic, Text, Dates, and Validation
Lookups and Data Modeling Concepts
Visualization and Dashboard Building
Data Cleaning, Transformation, and Power Query Automation
Power Pivot, DAX, and Power BI Foundations
Financial Modeling, Rounding, and Intro to Macros
Course Description
Excel Intermediate to Advanced: Formulas, PivotTables, Power Query, Dashboards and VBA is a free online course in Technology and Programming focused on Office Productivity. It is designed for learners who already know the basics and want to become faster, more accurate, and more analytical in Excel.
You will deepen your skills with formulas and functions, learning how to build reliable calculations using a wide range of referencing techniques and advanced approaches such as array formulas, conditional calculations, text and date functions, rounding methods, and financial functions. The course also strengthens your ability to control data quality with data validation and to retrieve information efficiently with modern lookup strategies.
For data analysis and reporting, it guides you through PivotTables and building dashboards that combine tables, charts, and conditional formatting to communicate insights clearly. You will also learn how to clean, transform, and combine data using Power Query, including importing from multiple files and workbooks, and shaping datasets for analysis.
Beyond worksheets, the course introduces data modeling concepts and shows how relationships can support more scalable reporting, with an entry point into tools and workflows that extend Excel-based analysis. Finally, it provides a practical introduction to macros through the Macro Recorder, helping you begin automating repetitive tasks and standardizing processes.
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?
