🟠 Advanced 25 minadvanced path

Advanced Excel: Nested IFs, Dynamic Arrays & Auditing

Advanced Excel is where analysts, controllers, and power users spend their days. This lesson covers nested IFs (and when to drop them for IFS), dynamic array functions, data validation, named ranges, and how to audit a workbook you didn't build.

Learning objectives

Refactor nested IFs into IFS or SWITCH for readability.
Use dynamic array functions (FILTER, SORT, UNIQUE, SEQUENCE) confidently.
Add data validation and named ranges to make workbooks safer.
Use Trace Precedents/Dependents and Evaluate Formula to debug.

Step-by-step

1. From nested IF to IFS

Nested IFs are hard to read: =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","F"))). IFS is cleaner: =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F"). The final TRUE acts as ELSE.

2. Dynamic arrays

One formula spills into many cells. =SORT(UNIQUE(A2:A200)) returns a clean sorted distinct list. =FILTER(A2:C200, C2:C200>1000) returns only rows where the amount beats 1000. Refer to the spill with A5#.

3. Data validation

Data → Data Validation → List gives you drop-downs. Use it for status columns, regions, and account codes so users can't invent new values. Point the list at a named range and it stays in sync.

4. Named ranges

Formulas → Name Manager. Name a rate cell TaxRate and write =B2*TaxRate. Self-documenting, and the name survives if you insert rows.

5. Formula auditing

Select a formula → Formulas → Trace Precedents draws arrows to its inputs. Evaluate Formula steps through the calculation piece by piece — the fastest way to understand a formula you inherited.