Lesson 1Using VBA macros for simple automation: record macros, edit module, safe execution, workbook events (Workbook_Open, Worksheet_Change)Make repetitive jobs automatic with VBA macros, from recording to changing code in modules. Learn safe running methods, use workbook events like Workbook_Open and Worksheet_Change, and build dependable automation for updating and arranging tasks in local offices.
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 buttonsMake refresh automatic for Power Query and PivotTables using VBA and interface controls. Build buttons and simple macros to refresh many objects in order, handle mistakes, and make sure users always see fresh data in South Sudanese reports.
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 times. Work with TEXT, DATE, EOMONTH, MONTH, YEAR, and formatting to make month names, fiscal times, and lively date-based summaries for dashboards and regular reports in our region.
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 ways for workbook versions, backups, and notes. Track changes to data sources, keep a change record, and add clear notes so future users can understand update steps and links in South Sudan work 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 logicalsUse advanced formulas like XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, and IFS. Build nested logic expressions, handle errors well, and design strong lookup chains for complex reporting models suited to local 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 setup with special sheets for Sales, HR, Finance, dashboards, and a data dictionary. Set naming rules, navigation helps, and separate raw data, staging, and show layers for South Sudanese businesses.
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, clean, and join departmental files. Set connections, apply change steps, merge and add tables, and load results into Excel models while keeping queries updatable and well noted for regional use.
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 entry with data check rules, lists, and error warnings. Build dropdowns for monthly paste-in patterns, limit wrong entries, and design kind messages that guide users while guarding formulas and setups in local contexts.
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 the Data Model. Learn when to use each, how they affect speed, and how to design reusable calculations for steady reporting across pivots in South Sudan.
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 to make lively ranges and structured links. Change ranges to tables, use table names in formulas, and define named ranges that update on their own as data grows for better local management.
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 make flags and categories that drive analysis. Mix IF with lookup functions, and apply SWITCH and CHOOSE to make nested logic simpler, helping models easy to check and change over time in our area.
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 big ranges with lively arrays, spill ranges, and the LET function. Understand speed issues, error handling, and how to replace old array formulas with new, easy-to-keep calculation ways for South Sudanese users.
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
