Lesson 1Employing VBA macros for basic automation: recording macros, editing modules, secure running, workbook events (Workbook_Open, Worksheet_Change)Simplify repeated 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 Eritrean office routines.
Recording and executing simple macrosModifying macros in standard modulesApplying Workbook_Open for initial tasksApplying Worksheet_Change for data entry rulesMacro protection and secure runningLinking macros to buttons and figuresLesson 2Update automation: updating Power Query and PivotTables using VBA or buttonsAutomate the updating of Power Query and PivotTables with VBA and user interface tools. Construct buttons and basic macros to update various items in order, manage mistakes, and guarantee that users in Eritrea always access the most recent information.
Hand vs automated update choicesVBA for updating all queries togetherUpdating PivotTables using macrosUpdate buttons on control panelsDealing with update mistakes and recordsLesson 3Text and date operations: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT for report timesEmploy text and date operations to form solid report times. Handle TEXT, DATE, EOMONTH, MONTH, YEAR, and styling to produce month names, financial times, and changing date-based overviews for panels and regular reports in Eritrean contexts.
Forming dates from parts with DATEMonth, year, and EOMONTH computationsTEXT for custom time namesOngoing monthly and year-to-date spansManaging financial vs standard calendar timesLesson 4Good methods for version control, copies, and noting data origins in the workbookApply good methods for workbook version control, copies, and notes. Follow changes to data origins, keep a change record, and include clear explanations so future users in Eritrea can grasp update steps and connections.
File naming and version rulesCopy plans and storage spotsKeeping a workbook change recordNoting outside data tiesAnnotating queries and main calculationsLesson 5Advanced calculations: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, combined logicUse advanced calculations like XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, and IFS. Form combined logic expressions, manage mistakes smoothly, and plan strong search chains for detailed reporting models in Eritrean businesses.
XLOOKUP vs VLOOKUP differencesINDEX/MATCH for adaptable searchesSUMIFS and COUNTIFS with conditionsIFS and combined logic formsMistake handling with IFERROR or IFNALesson 6Planning workbook design: distinct sheets for Sales, HR, Finance, Dashboard, Data GuidePlan a clear workbook design with specific sheets for Sales, HR, Finance, dashboards, and a data guide. Set naming rules, navigation helps, and divide raw data, preparation, and display layers for Eritrean professional use.
Dividing data, preparation, and reportsSpecific sheets for Sales, HR, FinanceDashboard arrangement and navigation helpsCentral data guide sheetSheet naming and tab colour plansLesson 7Power Query basics: bringing in, cleaning, combining various department filesMaster Power Query to bring in, clean, and join department files. Set up ties, apply change steps, combine and add tables, and load outcomes into Excel models while keeping queries updatable and well-noted for Eritrea's data needs.
Tying to folders and workbooksCleaning and forming raw tablesCombining and adding department filesHandling query steps and mistakesLoading queries to tables or data modelNoting query logic and originsLesson 8Data checking and guided entry: drop-downs, lists, mistake warnings for monthly inputGuide user entry with data checking rules, lists, and mistake warnings. Build drop-downs for monthly input forms, limit wrong entries, and plan helpful messages that direct users while safeguarding calculations and designs in Eritrean settings.
Forming list-based drop-down guidesCustom checking calculations for rulesEntry messages and mistake warningsChecking monthly input formsSecuring design while permitting entryLesson 9Computed columns and measures: table columns vs. Pivot measuresTell apart computed columns in tables from measures in PivotTables and the Data Model. Understand when to use each, their effect on speed, and how to plan reusable computations for steady reporting in Eritrean pivots.
Forming computed columns in tablesDefining measures in the Data ModelRow context vs filter context basicsSelecting between column and measureReusing measures in PivotTablesLesson 10Employing Excel Tables and named spans for changing spans and structured tiesUse Excel Tables and named spans to form changing spans and structured ties. Change spans to tables, use table names in calculations, and define named spans that update automatically as data expands in Eritrea.
Changing spans into Excel TablesStructured ties in calculationsTotal rows and table-based overviewsChanging named spans with calculationsTables supplying PivotTables and graphsLesson 11Conditional rules for markers and groups: IF with search, SWITCH, CHOOSEApply conditional rules to form markers and groups that support analysis. Mix IF with search operations, and use SWITCH and CHOOSE to ease combined rules, making models simpler to check and modify over time in Eritrean reports.
Reviewing IF and combined IF formsIF with XLOOKUP or VLOOKUP markersUsing SWITCH for multi-condition rulesUsing CHOOSE for case choiceChecking and testing logic calculationsLesson 12Handling large spans well: array calculations, spill actions, LET operationLearn to manage large spans with changing arrays, spill spans, and the LET operation. Grasp speed factors, mistake handling, and how to swap old array calculations with current, easy-to-maintain patterns for Eritrea.
Old CSE array calculations vs changing arraysGrasping and guiding spill spansUsing LET to ease complex calculationsMixing LET with FILTER and SORTSpeed tips for large array spans
