12 Advanced Excel Formulas
- Life Time Access
- Certificate on Completion
- Access on Android and iOS App
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.
- 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
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!
Create an ad-hoc total at the top of your data set after using the Filter by Selection feature.
There is a lot of subtle content in this lecture. Randbetween, Faster AutoSum, CELL, TRIM, MID, and 3-D References.
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.
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.
A short quiz to test your understanding of the first session.
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.
You have to build a VLOOKUP on Sheet1 that points to Sheet2. This lecture shows an alternative using Named Ranges and expanding tables.
When would you ever use the =VLOOKUP(A2,Table,2,TRUE) version of VLOOKUP? This lecture walks through an example.
This lecture shows a technique where you use the True version of VLOOKUP to find the last number in a column.
One question to test your understanding of Session 2.
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.
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.
This lecture covers two unusual topics:
- Using INDIRECT to always point to the same cell
- Using OFFSET to always point to the cell above.
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.
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.
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 of what was covered in the course.
Where to find more resources from Bill Jelen.