Library

Course: 12 Advanced Excel Formulas

12 Advanced Excel Formulas

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

Learn 12 useful advanced formulas in Excel from Bill Jelen - host of MrExcel.com.

Here you will learn about some of the advanced Excel formulas that I've learnt and have been teaching for the past 20 years. It's really fun to learn Excel.

Join me in the course and you will see the difference.

Enroll NOW.

Basic knowledge
  • This course is designed for someone who uses Excel 20+ hours per week. I assume you are already familiar with sorting, filtering, and pivoting data
What you will learn

This course covers twelve topics:

  • Formula for the Total of Visible Cells After Applying a Filter
  • Spearing Formula to Create a 3D Reference Type
  • Formulas for the new Geography Data Types
  • How to Use Function Arguments with Nested Functions
  • VLOOKUP between worksheets
  • Using TRUE version of VLOOKUP
  • Find the Last Value in a Row or Column
  • Calculate Workdays for Non-Standard Work Weeks
  • When Would Anyone Actually Use GetPivotData Functions
  • Use INDIRECT to Always Point to a Particular Cell
  • Replace OFFSET with INDIRECT to Avoid Volatility
  • Create Business Diagrams Populated by Formulas
  • A bonus tip to make a Baker's Dozen!
Curriculum
Number of Lectures: 16
Total Duration: 00:55:13
Welcome & New Functionality
  • Total the Visible Rows After Applying Filter by Selection  

    Create an ad-hoc total at the top of your data set after using the Filter by Selection feature.

  • Spearing Formula to Create a 3D Reference  

    There is a lot of subtle content in this lecture. Randbetween, Faster AutoSum, CELL, TRIM, MID, and 3-D References.

  • Referring to Geography Data Types  

    The new geography and stock data types introduced in Office 365 introduce a new world of formulas. Learn how to use =A2.Population and similar formulas.

  • Download the Excel Workbook Used in these Lectures  

    If you would like to work along, download the zip file attached to this session. All of the worksheets are in the single Excel workbook so you can try anything on your computer.

  • Check Your Understanding of Session 1  

    A short quiz to test your understanding of the first session.

INDEX+MATCH and Other VLOOKUP Oddities
  • How to Build INDEX & MATCH with the Function Arguments Dialog  

    The Function Arguments dialog is great when you have a formula with a single function. But what if you need to use INDEX with MATCH and MATCH? This lecture shows you how to change the Function Arguments on the fly.

  • VLOOKUP Between Worksheets  

    You have to build a VLOOKUP on Sheet1 that points to Sheet2. This lecture shows an alternative using Named Ranges and expanding tables.

  • Using the "True" Version of VLOOKUP  

    When would you ever use the =VLOOKUP(A2,Table,2,TRUE) version of VLOOKUP? This lecture walks through an example.

  • Find the Last Value in a Column or Row  

    This lecture shows a technique where you use the True version of VLOOKUP to find the last number in a column.

  • Quiz for Session 2  

    One question to test your understanding of Session 2.

The Oddities: NetWorkDays, GetPivotData, Indirect, Offset, and Index
  • How Many Workdays Between Two Dates?  

    Dates in Excel are great. Learn how to successfully count the number of workdays between now and a project deadline. Ignore holidays and weekends, even if you have a non-standard work week.

  • Embracing GetPivotData  

    Most people hate the GetPivotData function and with good reason. This lecture shows you how Microsoft originally intended for GetPivotData to be used and how it can be useful.

  • Use INDIRECT or OFFSET  

    This lecture covers two unusual topics:

    • Using INDIRECT to always point to the same cell
    • Using OFFSET to always point to the cell above.
  • Replacing a Volatile OFFSET with two INDEX functions  

    This lecture starts off with how most people use INDEX. But it turns out that by adding one character next to the INDEX, it changes the behavior. This results in a cool replacement for OFFSET.

Formula-Based Graphics, Bonus TextJoin and a Wrap-up
  • Create Business Diagrams Populated by Formulas  

    Office 2007 introduced the SmartArt feature. But these diagrams were invented by the PowerPoint team, not the Excel team, so they never thought to allow formulas. This lecture shows how to leverage all of the coolness of SmartArt but populating the diagrams on the fly with formulas.

  • Bonus: Using TextJoin with an Array  

    In February 2017, the Excel team added CONCAT, TEXTJOIN, and other functions to Excel. In this bonus lecture, learn how to use TEXTJOIN with an array.

  • Wrap Up  

    Wrap-up of what was covered in the course.

  • About the Author  

    Where to find more resources from Bill Jelen.

Reviews (0)