Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for PMO analysts

PMO analysts bring status, risks and spending from multiple projects into a portfolio report. This guide picks out parts of The Complete Excel Course with AI Integration that support that work: Power Query for combining reports, IFS and conditional formatting for health ratings, TaskFlow as a model for RAID logs, FinanceGuard for spend against budget, PivotTables for portfolio views and macros for monthly packs.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Build a portfolio table from project status reports in Power Query, then append further reports individually or combine a folder of them.
  • Apply IFS and AND to project health rules, use conditional formatting to colour the results, and total the projects in each rating with COUNTIFS.
  • Compare project spending with budgets using SUMIFS, calculate variance and percent variance, and use custom number formats to colour overspending.
  • Maintain milestone lists or RAID logs on a protected sheet, using drop-down choices, highlighted overdue entries and a Gantt-style timeline.
  • Use PivotTables and slicers to report on the portfolio by programme, status and month, with XLOOKUP retrieving project names from their codes.
  • Create a recorded macro for the monthly pack and attach it to a button, then write VBA to flag missing entries in status reports.

Projects

What you will build.

The course has 14 projects. These four matter most here.

  • Task tracker in Excel (TaskFlow): start and due dates, status, priority and a progress bar for each task, with working days left, days past due and unfinished high-priority tasks highlighted.

    Project 07

    TaskFlow

    One sheet that shows what is done, what is late and what is urgent.

    Adapt the tracker to a milestone list or RAID log, with set choices for owners and status, red highlighting for late entries, days remaining to milestones and protection against accidental overwriting.

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Prepare for the steering meeting by comparing each project's spending with its budget, calculating variance and percent variance, and using colour to draw attention to overspending.

  • Power Query sales report in Excel (SalesFusion): sales by category, sales by region and a monthly trend, with slicers for channel, year, category and region.

    Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    Gather project status reports in a shared folder, then use Power Query to bring their contents together as a portfolio table.

  • Excel VBA macro editor (FormatWizard): a macro that runs every validation check on a sheet and reports when it has finished.

    Project 11

    FormatWizard

    One click that checks a whole sheet for you.

    Check for gaps before distributing the monthly pack with a VBA loop that highlights empty cells as it moves through the rows. The course applies the loop to sales data; it can also check a status report.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 72 of them, 5 h 52 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 31 Nested IF and IFS in Excel

If simple formulas are familiar and health-rating logic is your priority, start with Lesson 31. Excel newcomers should start with Lesson 1, since the earlier material builds towards these skills. If you already use IFS, SUMIFS and PivotTables routinely, choose Lesson 115 to compare spending with budgets or Lesson 153 to consolidate reports.

72 of 186 lessons · 5 h 52 min

04 Essential formulas and functions (IF, SUMIF and more) 7 of 13 lessons · 45 min Turn health rules into red, amber and green results with nested IF and IFS, using AND and OR where ratings depend on two conditions. COUNTA and completeness checks help identify outstanding reports; SUMIFS and COUNTIFS provide totals by status and programme. These formulas are taught with sales data.
  1. 31 Start here Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  2. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  3. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  4. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  5. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  6. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  7. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Explore projects, milestones and spending across programmes, statuses and months through PivotTables. Group by date, select views with slicers and refresh the summaries after another round of reporting. Sales orders provide the lesson examples.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 5 of 12 lessons · 21 min Retrieve a project's sponsor, budget or name from its code using VLOOKUP, XLOOKUP or INDEX and MATCH, and handle unlisted codes with IFERROR. The lessons demonstrate these lookups on HR records.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
09 Project: task automation with data validation and forms 9 lessons · 49 min Use TaskFlow as the basis for milestone lists or RAID logs. Its protected sheet combines status and priority drop-downs, overdue highlighting through conditional formatting, a Gantt-style timeline, data bars and icon sets. NETWORKDAYS and TODAY calculate the days remaining before deadlines.
  1. 79 TaskFlow project tracker in Excel Overview of the task tracker dashboard 4 min
  2. 80 Designing a task tracker in Excel TaskFlow layout, columns and headers 5 min
  3. 81 Data validation in Excel Status and priority drop-down lists in TaskFlow 8 min
  4. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
  5. 83 Gantt chart in Excel Build a project timeline with conditional formatting 6 min
  6. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  7. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  8. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
  9. 87 Testing an Excel task tracker Stress test TaskFlow with sample data 3 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min Compare planned and actual spending through FinanceGuard: organise budget and actual tables, build the model with dynamic arrays and SUMIFS, and calculate variance and percent variance with colour-coded number formats. A PivotTable dashboard presents KPI cards, slicers and a combo chart. The course uses a budget and its actual spending, and the model also suits a project budget.
  1. 115 FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 min
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min Build up from recording a FormatWizard macro to running it through a button and saving a workbook that supports macros. Then use VBA variables, If statements and loops to move through worksheet rows and highlight missing entries.
  1. 136 Excel macros and VBA for beginners What macros are, the FormatWizard project 3 min
  2. 137 Record your first Excel macro Name, run and use absolute references 6 min
  3. 138 Relative references in Excel macros Make recorded macros dynamic 4 min
  4. 139 Developer Tab in Excel Run macros from a button and the quick access toolbar 4 min
  5. 140 Save macros in Excel Macro-enabled workbooks and the XLSM file format 3 min
  6. 141 The VBA Editor in Excel Open it with Alt+F11 and learn the workspace 4 min
  7. 142 Working in the VBA editor Macro security, trust settings, switching to Excel 6 min
  8. 143 Your first VBA code Modules, worksheets, rows, columns and formatting 4 min
  9. 144 VBA variables and data types Sub vs function for Excel beginners 6 min
  10. 145 VBA functions in Excel Build GetLastUsedRow with find and exit function 5 min
  11. 146 Clear old flags with VBA Reset messages and fill colours with if statements 5 min
  12. 147 For Next loop in VBA Flag missing region values in Excel 4 min
  13. 148 VBA If Else and loops Check missing sales data and build a FlagText message 5 min
  14. 149 Highlight cells with VBA Interior.Color and RGB for missing data 5 min
  15. 150 IsNumeric in VBA Validate unit price and amount data types in Excel 7 min
  16. 151 Debugging VBA for beginners Debug.Print and the Immediate window 5 min
  17. 152 Complete Excel validation macro One VBA subroutine with an error handler 3 min
15 SalesFusion: advanced data tools and a Power Query modelling system 14 of 21 lessons · 1 h 16 min Bring reports together with Power Query by importing a CSV, cleaning text and columns, correcting data types and adding month fields. Merge in names, append successive periods and combine files from a folder into a table. The examples use sales files; the same process applies to a folder of status reports.
  1. 153 SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 min
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course covers the reporting work of a PMO analyst?

The Complete Excel Course with AI Integration from Coding Liquids covers Excel tasks used in a project management office: consolidating status reports with Power Query, assigning health ratings with IFS and conditional formatting, adapting TaskFlow for RAID logs, comparing spending with budgets in FinanceGuard, summarising portfolios with PivotTables and preparing monthly packs with macros. Sales orders, HR records and budgets supply most of the course data; TaskFlow is the project centred on tasks. This guide selects 72 lessons, taking 5 hours 52 minutes from the course's 16 hours.

How can a PMO analyst bring multiple project status reports together in Excel?

Reports need a shared layout. In The Complete Excel Course with AI Integration, Power Query From Folder uses a query to clean and stack reports saved in a folder into a table, while Append joins a further reporting period to the history. The demonstrations use CSV sales files, and status reports exported with a common layout can follow that process.

Does the course show a PMO analyst how to build RAG ratings and track project budgets?

The Complete Excel Course with AI Integration teaches the Excel work behind both tasks. You decide the rating rules, then IFS assigns green, amber or red, AND handles rules requiring two conditions, and conditional formatting adds the colours. FinanceGuard compares budget with spending, calculates variance and percent variance, and presents a dashboard containing KPI cards.

Does the course teach a PMO analyst PRINCE2, MS Project or Power BI?

The Complete Excel Course with AI Integration does not teach PRINCE2, the PMI approach or other project management methods, scheduling tools such as MS Project, or Power BI. Its focus is the Excel workbooks a PMO uses for tracking, consolidation, health ratings and reporting.

Can a PMO analyst reduce the effort of preparing the monthly portfolio pack?

Yes. The Complete Excel Course with AI Integration shows how Power Query rebuilds the consolidated table when you save the incoming reports and press Refresh. FormatWizard also teaches macro recording, running a macro from a button, and writing VBA to inspect rows and highlight missing information before distributing the pack.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic