Lesson 1Using VBA macros for simple automation: record macros, edit module, safe execution, workbook events (Workbook_Open, Worksheet_Change)Automate repetitive tasks wid VBA macros, from recording to editing code in modules. Learn safe execution practices, use workbook events like Workbook_Open and Worksheet_Change, and build reliable automation for refresh and formatting tasks in everyday office work.
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 refresh of Power Query and PivotTables using VBA and interface controls. Build buttons and simple macros to refresh multiple objects in sequence, handle errors, and ensure users always see current data for timely decisions.
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 periodsUse text and date functions to build strong reporting periods. Work wid TEXT, DATE, EOMONTH, MONTH, YEAR, and formatting to create month labels, fiscal periods, and dynamic date-driven summaries for dashboards and recurring reports in local contexts.
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 workbookImplement best practices for workbook versioning, backup, and documentation. Track changes to data sources, maintain a change log, and embed clear notes so future users can understand refresh steps and dependencies in team settings.
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 logicalsApply advanced formulas such as XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, and IFS. Build nested logical expressions, handle errors gracefully, and design robust lookup chains for complex reporting models dat suit practical needs.
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 DictionaryDesign a clear workbook structure wid dedicated sheets for Sales, HR, Finance, dashboards, and a data dictionary. Establish naming standards, navigation aids, and separation of raw data, staging, and presentation layers for efficient use.
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 filesMaster Power Query to import, clean, and combine departmental files. Configure connections, apply transformation steps, merge and append tables, and load results into Excel models while keeping queries refreshable and well documented for ongoing work.
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-inControl user input wid data validation rules, lists, and error alerts. Build dropdowns for monthly paste-in templates, restrict invalid entries, and design friendly messages dat guide users while protecting formulas and structures.
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 measuresDifferentiate calculated columns in tables from measures in PivotTables and the Data Model. Learn when to use each, how dem affect performance, and how to design reusable calculations for consistent reporting across pivots in real scenarios.
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 referencesLeverage Excel Tables and named ranges to create dynamic ranges and structured references. Convert ranges to tables, use table names in formulas, and define named ranges dat update automatically as data grows for flexible handling.
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, CHOOSEUse conditional logic to create flags and categories dat drive analysis. Combine IF wid lookup functions, and apply SWITCH and CHOOSE to simplify nested logic, making models easier to audit and adjust over time in practical applications.
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 functionLearn to handle large ranges wid dynamic arrays, spill ranges, and the LET function. Understand performance considerations, error handling, and how to replace legacy array formulas wid modern, maintainable calculation patterns for big data tasks.
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
