Free course · by Debugshala, Indore
Excel and Macros Tutorial
13 free lessons, in the order we teach them in class: start at lesson 1 and work down. Every lesson has an analogy, a diagram, code you can run or copy, a quick quiz and an FAQ.
- 13Lessons
- 2Modules
- ~2 hReading time
- FreeAlways
01Excel for Data Analysis
- Excel for Data Analysis: A Beginner's Guide for Microsoft 365Learn Excel for data analysis: the five-step workflow, tidy layouts, Excel Tables, cell references and version differences, plus a map of the 13-part series.
- Excel Formulas and Functions: SUM, AVERAGE, COUNTIFS, SUMIFSExcel formulas and functions explained with a distributor's sales sheet: SUM, AVERAGE, COUNT, SUMIFS, COUNTIFS, wildcards, date criteria and error values.
- Excel IF Function with IFS, AND, OR: Logical Formulas ExplainedMaster the Excel IF function with IFS, AND, OR, SWITCH and IFERROR, using discount slabs and delivery rules from a real-looking Indian sales sheet.
- XLOOKUP vs VLOOKUP vs INDEX MATCH in Excel, with ExamplesXLOOKUP vs VLOOKUP vs INDEX MATCH: syntax, exact and approximate match, left lookups, last match, two-criteria lookups and which one to use in which version.
- Excel Text and Date Functions for Cleaning Data: TRIM to EOMONTHExcel text and date functions for messy exports: TRIM, PROPER, MID, TEXTSPLIT, SUBSTITUTE, date serials, EOMONTH, NETWORKDAYS and the Indian financial year.
- Data Cleaning in Excel: Duplicates, TRIM, Flash Fill, ValidationData cleaning in Excel step by step: fix spaces and spelling, convert text numbers, fill blanks, remove duplicates, use Flash Fill and add Data Validation.
- Excel Pivot Tables Tutorial: Build, Group, Slice and RefreshExcel pivot tables tutorial with a sales example: create a pivot, use Rows, Columns, Values and Filters, show percentages, group dates, add slicers and refresh.
- Excel Charts and Dashboard Tutorial: Build a Sales DashboardLearn which Excel chart to use, then build a sales dashboard with KPI cells, SUMIFS, pivot charts, slicers and sparklines that updates when data changes.
- Power Query in Excel: Import, Clean and Combine Data EasilyLearn Power Query in Excel step by step: import CSVs, trim and fix types, unpivot, append and merge tables, combine a folder of files and refresh in one click.
02Excel Macros and VBA
- Excel Macros Tutorial: Record, Run and Save .xlsm FilesRecord your first Excel macro, use relative references, add a button, save as .xlsm, use the Personal Macro Workbook and set Trust Center macro security safely.
- Excel VBA Basics: Variables, Loops and the Range ObjectLearn Excel VBA from zero: the VBA editor, Option Explicit, Dim and data types, the object model, Range and Cells, loops, If, functions and error handling.
- Automate Excel Reports with VBA: 5 Practical Macros to UseFive ready-to-adapt VBA macros that clean data, build a summary, split sheets by area, refresh queries and export a dated PDF, run from one safe master button.
- Excel Interview Questions for Data Analysts (With Answers)19 Excel interview questions for data analysts with model answers on references, lookups, pivot tables, Power Query and VBA, plus how to ace the practical test.