Lesson 1Using VBA macros for simple automation: record macros, edit module, safe execution, workbook events (Workbook_Open, Worksheet_Change)Automate repetitive tasks with VBA macros, from recording to modifying code in modules. Discover safe execution methods, employ workbook events such as Workbook_Open and Worksheet_Change, and construct dependable automation for update and formatting duties.
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 the refresh of Power Query and PivotTables employing VBA and interface controls. Construct buttons and straightforward macros to refresh several objects sequentially, manage errors, and guarantee users always view up-to-date 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 periodsEmploy text and date functions to construct robust reporting periods. Handle TEXT, DATE, EOMONTH, MONTH, YEAR, and formatting to generate month labels, fiscal periods, and dynamic date-driven summaries for dashboards and recurrent 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 workbookApply best practices for workbook versioning, backup, and documentation. Monitor changes to data sources, keep a change log, and incorporate explicit notes so subsequent users can comprehend refresh procedures and dependencies.
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 logicalsImplement advanced formulae including XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, and IFS. Construct nested logical expressions, manage errors smoothly, and devise sturdy lookup sequences for intricate 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 DictionaryDevise a clear workbook structure with dedicated sheets for Sales, HR, Finance, dashboards, and a data dictionary. Set up naming standards, navigation aids, and segregation of raw data, staging, and presentation layers.
Separating data, staging, and reportsDedicated sheets for Sales, HR, FinanceDashboard layout and navigation aidsCentral data dictionary worksheetSheet naming and tab colour schemesLesson 7Power Query fundamentals: importing, cleaning, merging multiple departmental filesMaster Power Query to import, clean, and merge departmental files. Set up connections, apply transformation steps, combine and append tables, and load outcomes into Excel models whilst maintaining queries refreshable and well 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 input with data validation rules, lists, and error alerts. Construct dropdowns for monthly paste-in templates, limit invalid entries, and design user-friendly messages that direct users whilst safeguarding formulae and structures.
Creating list-based dropdown controlsCustom validation formulae for rulesInput messages and error alertsValidating monthly paste-in templatesLocking structure while allowing inputLesson 9Calculated columns and measures: structured table columns vs. Pivot measuresDistinguish calculated columns in tables from measures in PivotTables and the Data Model. Understand when to utilise each, their impact on performance, and how to devise reusable calculations for uniform reporting across 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 referencesHarness Excel Tables and named ranges to form dynamic ranges and structured references. Convert ranges to tables, employ table names in formulae, and define named ranges that update automatically as data expands.
Converting ranges into Excel TablesStructured references in formulaeTotal rows and table-based summariesDynamic named ranges with formulaeTables feeding PivotTables and chartsLesson 11Conditional logic for flags and categories: IF with lookup, SWITCH, CHOOSEUtilise conditional logic to generate flags and categories that propel analysis. Merge IF with lookup functions, and apply SWITCH and CHOOSE to streamline nested logic, rendering models simpler to audit and modify over time.
Reviewing IF and nested IF patternsIF with XLOOKUP or VLOOKUP flagsUsing SWITCH for multi-condition logicUsing CHOOSE for scenario selectionAuditing and testing logical formulaeLesson 12Working with large ranges efficiently: array formulas, spill behavior, LET functionGrasp handling large ranges with dynamic arrays, spill ranges, and the LET function. Comprehend performance factors, error management, and how to substitute legacy array formulae with contemporary, maintainable calculation methods.
Legacy CSE array formulae vs dynamic arraysUnderstanding and controlling spill rangesUsing LET to simplify complex formulaeCombining LET with FILTER and SORTPerformance tips for large array ranges
