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.
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.
=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)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.
=SUMIFS(C2:C501,B2:B501,E2) =SUMIFS(C2:C501,B2:B501,E2:E5) 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.
=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)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.
=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") How is this course going to help you?
This course will teach all these functions using practical examples.
-
M0Foundations Optional1 lesson
- Function vs. Formula
-
M1What Changes with the Dynamic Arrays10 lessons
- What is an Array
- 100 Formulas vs. 1 Formula
- Spilling a Range
- Spill Error
- Spill Operator
- 3D References
- Scalar Values & Lifting
- Pairwise Lifting
- Spilling & Absolute Cell References
- Array of Arrays
-
M2Rise of Readable Formulas2 lessons
- How the LET Function has changed everything
- Name, Values, & Answers
-
M3Lookups That Don't Break8 lessons
- Why XLOOKUP is Better than VLOOKUP
- Find the Last Match, Not the First
- You need XLOOKUP, not IF
- Use XLOOKUP as the End of the Range
- XLOOKUP with 1
- A Secret Trick to Perform TWO-WAY Lookup
- Wildcard Character with XLOOKUP
- Power of Pipe with XLOOKUP
-
M4Bending Data into Shape7 lessons
- Combine Data with HSTACK & VSTACK
- Restructure data with TAKE, DROP, CHOOSECOLS, & CHOOSEROWS
- Turn a Wide Grid into a Single Clean List with TOCOL & TOROW
- Extract Top N Customers Like A Pro
- 12 monthly sheets, one table, one formula
- Add a total row that moves as the data grows
- Unpivot a crosstab without Power Query
-
M5Cleaning the Ugly Column4 lessons
- New Way of Extracting Values
- Split by One, Two, or Three Delimiters
- Flattening Data for Aggregation
- CHALLENGE - Remove Duplicate Values from a Single Cell
-
M6Reports That Update Themselves8 lessons
- Replace the Filter Button with a Formula
- TRUE is 1, FALSE is 0, and why that matters
- Two conditions, then either/or: * and +
- Sort by a column you don't want to show
- Multi-column sort, and sorting days of the week correctly
- Distinct customers vs. "who bought only once"
- Top 3 by department, values and names together
- Who's missing: customers with no orders this month
-
M7Writing Your Own Functions3 lessons
- Write a formula once, reuse it forever
- Build a Real One: Discounts and Tax in a Single Call
- Optional arguments so your function doesn't error
-
M8Summaries without PivotTables4 lessons
- Quick Summaries are now Super Easy with GROUPBY
- Using a Custom Function in the Summary Reports
- Region-by-product summary in one formula using PIVOTBY
- Creating a Perfect Report with PIVOTBY
-
M9Dynamic Arrays Change the Rest of Excel5 lessons
- Dependent dropdowns, unique and sorted
- A dropdown that hides what's already been used
- Searchable Filter
- Conditional formatting driven by a spilled result
- Charts that grow when the data grows
-
M10Data 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
This course helps you to master new age Excel functions and formulas
Best Value
This is a combo of two courses. The Power Automate Desktop is a no code automation Application for Windows. Automation using drag and drop.
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?
How long do I have access?
What if it isn't right for me?
I'm not confident with formulas yet. Is this for me?
Do I get the practice files?