Lesson 1Using VBA macros for simple automation: record macros, edit module, safe execution, workbook events (Workbook_Open, Worksheet_Change)Automate routine tasks with VBA macros, starting from recording to editing code in modules. Master safe running practices, workbook events like Workbook_Open and Worksheet_Change, and create dependable automation for updating and formatting jobs.
Recording and running basic macrosEditing macros in standard modulesUsing Workbook_Open for startup tasksUsing Worksheet_Change for input logicMacro security and safe executionAssigning macros to buttons and shapesLesson 2Refresh automation: refreshing Power Query and PivotTables with VBA or buttonsAutomate refreshing Power Query and PivotTables using VBA and simple controls. Set up buttons and basic macros to refresh several items in order, manage errors, and make sure users always view the latest data.
Manual vs automated refresh optionsVBA to refresh all queries at onceRefreshing PivotTables with macrosRefresh buttons on dashboardsHandling refresh errors and loggingLesson 3Text and date functions: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT for reporting periodsApply text and date functions to create solid reporting periods. Handle TEXT, DATE, EOMONTH, MONTH, YEAR, and formatting to make month labels, financial periods, and dynamic date summaries for dashboards and regular reports.
Building dates from components with DATEMonth, year, and EOMONTH calculationsTEXT for custom period labelsRolling monthly and year-to-date rangesHandling fiscal vs calendar periodsLesson 4Best practices for versioning, backup, and documenting data sources within the workbookPut in place best practices for workbook versioning, backups, and documentation. Monitor changes to data sources, keep a change log, and add clear notes so others can follow refresh steps and dependencies easily.
File naming and versioning conventionsBackup strategies and storage locationsMaintaining a workbook change logDocumenting external data connectionsAnnotating queries and key formulasLesson 5Advanced formulas: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, nested logicalsUse advanced formulas like XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, and IFS. Create nested logic expressions, handle errors well, and design strong lookup systems for complex reporting models.
XLOOKUP vs VLOOKUP comparisonINDEX/MATCH for flexible lookupsSUMIFS and COUNTIFS with criteriaIFS and nested logical structuresError handling with IFERROR or IFNALesson 6Designing workbook structure: separate sheets for Sales, HR, Finance, Dashboard, Data DictionaryPlan a clear workbook structure with specific sheets for Sales, HR, Finance, dashboards, and a data dictionary. Set naming rules, navigation tools, and separate raw data, processing, and display layers.
Separating data, staging, and reportsDedicated sheets for Sales, HR, FinanceDashboard layout and navigation aidsCentral data dictionary worksheetSheet naming and tab color schemesLesson 7Power Query fundamentals: importing, cleaning, merging multiple departmental filesGet good at Power Query to import, clean, and join departmental files. Set up connections, apply changes, merge and add tables, and load into Excel models while keeping queries easy to refresh and documented.
Connecting to folders and workbooksCleaning and shaping raw tablesMerging and appending departmental filesManaging query steps and errorsLoading queries to tables or data modelDocumenting query logic and sourcesLesson 8Data validation and controlled input: drop-downs, lists, error alerts for monthly paste-inManage user inputs with data validation, lists, and error messages. Create dropdowns for monthly data entry templates, block wrong entries, and make helpful messages that guide users while safeguarding formulas.
Creating list-based dropdown controlsCustom validation formulas for rulesInput messages and error alertsValidating monthly paste-in templatesLocking structure while allowing inputLesson 9Calculated columns and measures: structured table columns vs. Pivot measuresTell apart calculated columns in tables from measures in PivotTables and Data Model. Know when to use each, their effect on speed, and design reusable calculations for steady reporting in pivots.
Creating calculated columns in tablesDefining measures in the Data ModelRow context vs filter context basicsChoosing between column and measureReusing measures across PivotTablesLesson 10Using Excel Tables and named ranges for dynamic ranges and structured referencesUse Excel Tables and named ranges for dynamic ranges and structured references. Turn ranges into tables, use table names in formulas, and set named ranges that adjust as data increases.
Converting ranges into Excel TablesStructured references in formulasTotal rows and table-based summariesDynamic named ranges with formulasTables feeding PivotTables and chartsLesson 11Conditional logic for flags and categories: IF with lookup, SWITCH, CHOOSEApply conditional logic for flags and categories to support analysis. Mix IF with lookups, and use SWITCH and CHOOSE to simplify complex logic, making models easier to check and update.
Reviewing IF and nested IF patternsIF with XLOOKUP or VLOOKUP flagsUsing SWITCH for multi-condition logicUsing CHOOSE for scenario selectionAuditing and testing logical formulasLesson 12Working with large ranges efficiently: array formulas, spill behavior, LET functionHandle big ranges with dynamic arrays, spill ranges, and LET function. Understand speed factors, error handling, and replace old array formulas with modern, easy-to-maintain patterns.
Legacy CSE array formulas vs dynamic arraysUnderstanding and controlling spill rangesUsing LET to simplify complex formulasCombining LET with FILTER and SORTPerformance tips for large array ranges
