Excel Intermediate Course: 12 Free Lessons With Practice Files

You know SUM, IF and VLOOKUP. This course is the next step: the formulas and features people at work actually use to build reports that update themselves. Twelve lessons, one realistic sales register (80 invoices, April-September 2026), and a final GST dashboard project.

Every lesson has a free practice workbook with automatic ✓ checks. Examples use Indian data: rupees, GST, the April-March financial year and Indian cities.

Before you start: finish the Excel Beginner Course or be comfortable with cell references, IF and basic lookups. You need Microsoft 365 or Excel 2021 for the dynamic-array lessons.

1. 1Excel Intermediate Lesson 1: Named Ranges and Structured ReferencesStop writing Sales!K2:K81 everywhere. Name your ranges or turn data into an Excel Table so formulas read like =SUM(Amount) and grow with…
2. 2Excel Intermediate Lesson 2: SUMIFS, COUNTIFS and AVERAGEIFS ReportsBuild region, month and status reports from a raw sales register with SUMIFS, COUNTIFS and AVERAGEIFS, including date ranges, wildcards and not-equal…
3. 3Excel Intermediate Lesson 3: INDEX/MATCH and Advanced XLOOKUPTwo-way lookups, lookups to the left, last-match searches and not-found defaults with INDEX/MATCH and XLOOKUP, with a regional targets grid.
4. 4Excel Intermediate Lesson 4: Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE)One formula, many results: FILTER, SORT, SORTBY, UNIQUE and SEQUENCE spill whole lists that update themselves. With real examples from a sales…
5. 5Excel Intermediate Lesson 5: Cleaning Text With TEXTSPLIT, TEXTBEFORE and TEXTAFTERClean messy ERP and bank exports: split pipe-separated text, fix case and spaces, pull invoice numbers and names with TEXTBEFORE, TEXTAFTER, TEXTSPLIT,…
6. 6Excel Intermediate Lesson 6: Business Dates (WORKDAY, NETWORKDAYS, EOMONTH, FY Quarters)Due dates, delivery dates with holidays, working days in a month, Indian financial-year quarters and FY labels, all with formulas you can…
7. 7Excel Intermediate Lesson 7: Logic With IFS, SWITCH, AND and ORReplace long nested IFs with IFS and SWITCH, combine conditions with AND/OR, and count with AND/OR logic in COUNTIFS. Grades, commissions and…
8. 8Excel Intermediate Lesson 8: LET for Readable, Faster FormulasName the parts of a long formula with LET so it is readable and calculates once. Worked examples: GST, share of total,…
9. 9Excel Intermediate Lesson 9: PivotTables Deeper (Grouping, % of Total, Slicers)Group dates by month and quarter, show values as % of total, add calculated fields, slicers and timelines, and check your pivot…
10. 10Excel Intermediate Lesson 10: Dependent Drop-Down ListsChoose a region, then only that region's cities appear: build dependent drop-downs with Data Validation, INDEX/MATCH and FILTER, and validate entries.
11. 11Excel Intermediate Lesson 11: Conditional Formatting With FormulasHighlight whole rows for overdue invoices, above-average amounts, a chosen month or the top 3, using formula rules and the $ that…
12. 12Excel Intermediate Lesson 12: Project – Build a GST Sales DashboardFinal project: KPI cards, GST totals from a product master, best month, top region and product, collection rate and a slicer-driven chart,…

Next: the Excel for Finance course, Power Query or the VBA course.