Lesson 1Using VBA macros for simple automation: record macros, edit module, safe execution, workbook events (Workbook_Open, Worksheet_Change)Automate routine tasks using VBA macros, starting from recording to modifying code in modules. Discover safe running methods, apply workbook events such as Workbook_Open and Worksheet_Change, and develop dependable automation for updating and styling tasks in Namibian workflows.
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 via VBA and interface elements. Construct buttons and basic macros to refresh various items sequentially, manage mistakes, and guarantee users view up-to-date information in Namibian reporting scenarios.
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 solid reporting periods. Handle TEXT, DATE, EOMONTH, MONTH, YEAR, and formatting to generate month tags, fiscal times, and dynamic date-based overviews for panels and regular reports suited to Namibian fiscal calendars.
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 top practices for workbook versioning, backups, and records. Monitor alterations to data origins, keep a change record, and include plain notes so upcoming users grasp refresh processes and links in Namibian office environments.
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 calculations like XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, and IFS. Construct layered logical statements, manage errors smoothly, and plan sturdy lookup sequences for detailed reporting models in Namibian contexts.
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 straightforward workbook layout with specific sheets for Sales, HR, Finance, dashboards, and a data guide. Set naming rules, navigation tools, and divide raw data, preparation, and display layers for Namibian business 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 bring in, tidy, and join departmental files. Set up links, use change steps, combine and add tables, and load outcomes into Excel models while maintaining refreshable and well-recorded queries for Namibian data handling.
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 check rules, lists, and mistake warnings. Build dropdowns for monthly paste templates, limit wrong entries, and create helpful messages that direct users while safeguarding calculations and setups in Namibian templates.
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 measuresDistinguish calculated columns in tables from measures in PivotTables and the Data Model. Understand when to apply each, their impact on speed, and how to plan reusable calculations for steady reporting in Namibian pivot analyses.
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 referencesUtilise Excel Tables and named ranges to form dynamic areas and structured links. Change areas to tables, employ table names in calculations, and set named ranges that adjust automatically as data expands in Namibian workbooks.
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 reasoning to make flags and categories that support analysis. Mix IF with lookup functions, and use SWITCH and CHOOSE to ease layered logic, aiding audits and changes in Namibian models 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 formulasLesson 12Working with large ranges efficiently: array formulas, spill behavior, LET functionHandle big ranges with dynamic arrays, spill areas, and the LET function. Grasp performance factors, error management, and how to swap old array calculations with current, easy-to-maintain patterns for Namibian large datasets.
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
