Lesson 1Usin' VBA macros fi simple automation: record macros, edit module, safe execution, workbook events (Workbook_Open, Worksheet_Change)Automate repetitive tasks wid VBA macros, from recordin' to editin' code in modules. Learn safe execution practices, use workbook events like Workbook_Open an' Worksheet_Change, an' build reliable automation fi refresh an' format tasks.
Recordin' an' runnin' basic macrosEditin' macros in standard modulesUsin' Workbook_Open fi startup tasksUsin' Worksheet_Change fi input logicMacro security an' safe executionAssignin' macros to buttons an' shapesLesson 2Refresh automation: refreshin' Power Query an' PivotTables wid VBA or buttonsAutomate refresh of Power Query an' PivotTables usin' VBA an' interface controls. Build buttons an' simple macros to refresh multiple objects in sequence, handle errors, an' ensure users always see current data.
Manual vs automated refresh optionsVBA to refresh all queries at onceRefreshin' PivotTables wid macrosRefresh buttons on dashboardsHandlin' refresh errors an' loggin'Lesson 3Text an' date functions: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT fi reportin' periodsUse text an' date functions to build strong reportin' periods. Work wid TEXT, DATE, EOMONTH, MONTH, YEAR, an' formatin' to create month labels, fiscal periods, an' dynamic date-driven summaries fi dashboards an' recurrin' reports.
Buildin' dates from components wid DATEMonth, year, an' EOMONTH calculationsTEXT fi custom period labelsRollin' monthly an' year-to-date rangesHandlin' fiscal vs calendar periodsLesson 4Best practices fi versionin', backup, an' documentin' data sources within the workbookImplement best practices fi workbook versionin', backup, an' documentation. Track changes to data sources, maintain a change log, an' embed clear notes so future users can understand refresh steps an' dependencies.
File namin' an' versionin' conventionsBackup strategies an' storage locationsMaintainin' a workbook change logDocumentin' external data connectionsAnnotatin' queries an' key formulasLesson 5Advanced formulas: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, nested logicalsApply advanced formulas such as XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, an' IFS. Build nested logical expressions, handle errors gracefully, an' design robust lookup chains fi complex reportin' models.
XLOOKUP vs VLOOKUP comparisonINDEX/MATCH fi flexible lookupsSUMIFS an' COUNTIFS wid criteriaIFS an' nested logical structuresError handlin' wid IFERROR or IFNALesson 6Designin' workbook structure: separate sheets fi Sales, HR, Finance, Dashboard, Data DictionaryDesign a clear workbook structure wid dedicated sheets fi Sales, HR, Finance, dashboards, an' a data dictionary. Establish namin' standards, navigation aids, an' separation of raw data, stagin', an' presentation layers.
Separatin' data, stagin', an' reportsDedicated sheets fi Sales, HR, FinanceDashboard layout an' navigation aidsCentral data dictionary worksheetSheet namin' an' tab color schemesLesson 7Power Query fundamentals: importin', cleanin', mergin' multiple departmental filesMaster Power Query to import, clean, an' combine departmental files. Configure connections, apply transformation steps, merge an' append tables, an' load results into Excel models while keepin' queries refreshable an' well documented.
Connectin' to folders an' workbooksCleanin' an' shapin' raw tablesMergin' an' appendin' departmental filesManagin' query steps an' errorsLoadin' queries to tables or data modelDocumentin' query logic an' sourcesLesson 8Data validation an' controlled input: drop-downs, lists, error alerts fi monthly paste-inControl user input wid data validation rules, lists, an' error alerts. Build dropdowns fi monthly paste-in templates, restrict invalid entries, an' design friendly messages dat guide users while protectin' formulas an' structures.
Creatin' list-based dropdown controlsCustom validation formulas fi rulesInput messages an' error alertsValidatin' monthly paste-in templatesLockin' structure while allowin' inputLesson 9Calculated columns an' measures: structured table columns vs. Pivot measuresDifferentiate calculated columns in tables from measures in PivotTables an' the Data Model. Learn when to use each, how dem affect performance, an' how to design reusable calculations fi consistent reportin' across pivots.
Creatin' calculated columns in tablesDefinin' measures in the Data ModelRow context vs filter context basicsChoosin' between column an' measureReusin' measures across PivotTablesLesson 10Usin' Excel Tables an' named ranges fi dynamic ranges an' structured referencesLeverage Excel Tables an' named ranges to create dynamic ranges an' structured references. Convert ranges to tables, use table names in formulas, an' define named ranges dat update automatically as data grows.
Convertin' ranges into Excel TablesStructured references in formulasTotal rows an' table-based summariesDynamic named ranges wid formulasTables feedin' PivotTables an' chartsLesson 11Conditional logic fi flags an' categories: IF wid lookup, SWITCH, CHOOSEUse conditional logic to create flags an' categories dat drive analysis. Combine IF wid lookup functions, an' apply SWITCH an' CHOOSE to simplify nested logic, makin' models easier to audit an' adjust over time.
Reviewin' IF an' nested IF patternsIF wid XLOOKUP or VLOOKUP flagsUsin' SWITCH fi multi-condition logicUsin' CHOOSE fi scenario selectionAuditin' an' testin' logical formulasLesson 12Workin' wid large ranges efficiently: array formulas, spill behavior, LET functionLearn to handle large ranges wid dynamic arrays, spill ranges, an' the LET function. Understand performance considerations, error handlin', an' how to replace legacy array formulas wid modern, maintainable calculation patterns.
Legacy CSE array formulas vs dynamic arraysUnderstandin' an' controllin' spill rangesUsin' LET to simplify complex formulasCombinin' LET wid FILTER an' SORTPerformance tips fi large array ranges