All Courses \ Login

Excel’s formula engine changed

Stop using Excel like it's 2019

Now, it is not about SUMIF or COUNIFS, but about Dynamic Arrays, LAMBDAS, and Readable Formulas.

Click to play

no coding required

Build Your Own Functions...

I remember, learning to code to create my own functions. Now, it is so easy, even a beginners can create custom functions. And yes, no coding require.

OLD WAY
NEW WAY
=IF(B2>=100000,B2*10%,IF(B2>=75000,B2*7.5%,IF(B2>=50000,B2*5%,IF(B2>=25000,B2*2.5%,0))))
=COMMISSION(B2)
Copied into every sheet that needs it
Typed like any built-in Excel function
Change one rate and you edit every copy
Change a rate once and every cell updates
One missing bracket breaks the lot
Short enough to read at a glance

no coding required

Even Basics Have Changed

Back then, you only needed to learn VLOOKUP, SUMIFS and COUNTIFS, and you were done. Now the world has changed, data has changed, and so has Excel.

OLD WAY
NEW WAY
=SUMIFS(C2:C501,B2:B501,E2)
=SUMIFS(C2:C501,B2:B501,E2:E5)
EFFormula in F
1RegionSales
2East$48,250=SUMIFS(…,E2)
3West$61,900=SUMIFS(…,E3)
4Central$39,400=SUMIFS(…,E4)
5South$52,780=SUMIFS(…,E5)
EFFormula in F
1RegionSales
2East$48,250=SUMIFS(…,E2:E5)
3West$61,900spilled from F2
4Central$39,400spilled from F2
5South$52,780spilled from F2
A separate formula for every region
One formula returns every total
Change the formula and copy it down again
Change it once and all four totals update
One cell can be edited and nobody notices
Every total comes from the same formula

no coding required

Rise of Readable Formulas

One of the most annoying things while writing complex formulas is that they are hard to understand. Even for us sometimes. But that is not the problem now.

OLD WAY
NEW WAY
=ROUND(B2*C2*(1-D2),2)+ROUND(ROUND(B2*C2*(1-D2),2)*E2,2)
=LET(
  net, ROUND(B2*C2*(1-D2), 2),
  tax, ROUND(net*E2, 2),
  net+tax)
The net price is calculated twice
Net price calculated once, then reused
Change the discount and you edit two places
Change the discount on one line
Nested ROUNDs hide which part is tax
Net, tax and total are named steps

FILTER - A New Revolution

One of the most annoying things while writing complex formulas is that they are hard to understand. Even for us sometimes. But that is not the problem now.

OLD WAY
NEW WAY
=IFERROR(INDEX($A$2:$C$501,SMALL(IF($B$2:$B$501="West",ROW($B$2:$B$501)-ROW($B$2)+1),ROWS($E$2:E2)),COLUMNS($E$2:E2)),"")
=FILTER(A2:C501,B2:B501="West")
Needs Ctrl+Shift+Enter in older Excel
Typed once, then you press Enter
Copied across and down into every result cell
Returns every matching row and column
Hard to read and easy to break
Reads as: filter A2:C501 where Region is West

How is this course going to help you?

This course will teach all these functions using practical examples.

11 modules52 lessons
  1. M0
    Foundations Optional
    1 lesson
    1. Function vs. Formula
  2. M1
    What Changes with the Dynamic Arrays
    10 lessons
    1. What is an Array
    2. 100 Formulas vs. 1 Formula
    3. Spilling a Range
    4. Spill Error
    5. Spill Operator
    6. 3D References
    7. Scalar Values & Lifting
    8. Pairwise Lifting
    9. Spilling & Absolute Cell References
    10. Array of Arrays
  1. M2
    Rise of Readable Formulas
    2 lessons
    1. How the LET Function has changed everything
    2. Name, Values, & Answers
  2. M3
    Lookups That Don't Break
    8 lessons
    1. Why XLOOKUP is Better than VLOOKUP
    2. Find the Last Match, Not the First
    3. You need XLOOKUP, not IF
    4. Use XLOOKUP as the End of the Range
    5. XLOOKUP with 1
    6. A Secret Trick to Perform TWO-WAY Lookup
    7. Wildcard Character with XLOOKUP
    8. Power of Pipe with XLOOKUP
  3. M4
    Bending Data into Shape
    7 lessons
    1. Combine Data with HSTACK & VSTACK
    2. Restructure data with TAKE, DROP, CHOOSECOLS, & CHOOSEROWS
    3. Turn a Wide Grid into a Single Clean List with TOCOL & TOROW
    4. Extract Top N Customers Like A Pro
    5. 12 monthly sheets, one table, one formula
    6. Add a total row that moves as the data grows
    7. Unpivot a crosstab without Power Query
  4. M5
    Cleaning the Ugly Column
    4 lessons
    1. New Way of Extracting Values
    2. Split by One, Two, or Three Delimiters
    3. Flattening Data for Aggregation
    4. CHALLENGE - Remove Duplicate Values from a Single Cell
  5. M6
    Reports That Update Themselves
    8 lessons
    1. Replace the Filter Button with a Formula
    2. TRUE is 1, FALSE is 0, and why that matters
    3. Two conditions, then either/or: * and +
    4. Sort by a column you don't want to show
    5. Multi-column sort, and sorting days of the week correctly
    6. Distinct customers vs. "who bought only once"
    7. Top 3 by department, values and names together
    8. Who's missing: customers with no orders this month
  6. M7
    Writing Your Own Functions
    3 lessons
    1. Write a formula once, reuse it forever
    2. Build a Real One: Discounts and Tax in a Single Call
    3. Optional arguments so your function doesn't error
  7. M8
    Summaries without PivotTables
    4 lessons
    1. Quick Summaries are now Super Easy with GROUPBY
    2. Using a Custom Function in the Summary Reports
    3. Region-by-product summary in one formula using PIVOTBY
    4. Creating a Perfect Report with PIVOTBY
  8. M9
    Dynamic Arrays Change the Rest of Excel
    5 lessons
    1. Dependent dropdowns, unique and sorted
    2. A dropdown that hides what's already been used
    3. Searchable Filter
    4. Conditional formatting driven by a spilled result
    5. Charts that grow when the data grows
  9. M10
    Data Analysis using Dynamic Arrays

Puneet Gogia

Microsoft Excel MVP · Founder of ExcelChamps

I've been writing about Excel since 2015. Before that I worked as a data analyst, building forecasting and manufacturing reports in Excel and VBA. This course is how I teach formulas now: the job first, the function second.

Start using Excel Like a PRO

Modern Excel Formulas

This course helps you to master new age Excel functions and formulas

  • Lifetime Access
  • Completion Certificate
  • Downloadable Workbooks
  • 6 Hours of Content
  • Course Notes

 

Best Value

Modern Excel Formulas
+ No Code Automation

This is a combo of two courses. The Power Automate Desktop is a no code automation Application for Windows. Automation using drag and drop.

  • Lifetime Access
  • Completion Certificate
  • Downloadable Workbooks
  • 10 Hours of Content
  • Course Notes

This is by far one of the best decisions I have made to learn new age formulas. I can’t believe I was missing it.

Frequently Asked Questions

Which version of Excel do I need?
Microsoft 365 runs every lesson. Excel 2024 runs all but trim-range, GROUPBY, and PIVOTBY lessons. Excel 2021 covers the core lookup, filter, sort and LET lessons.
How long do I have access?
Microsoft 365 runs every lesson. Excel 2024 runs all but trim-range, GROUPBY, and PIVOTBY lessons. Excel 2021 covers the core lookup, filter, sort and LET lessons.
What if it isn't right for me?
You don't need to worry on that you have first 14 days. You are not satisfied with the course, contact us, we will give you a full refund.
I'm not confident with formulas yet. Is this for me?
Start with Module 0. It's a 30-minute optional refresher on data types, errors and Tables, so nobody gets lost in Module 2.
Do I get the practice files?
Yes. Every lesson comes with its workbook, including a Solutions sheet you can check your answers against.