Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for public health professionals

A guide for public health officers, analysts and programme staff processing health data in spreadsheets. Although The Complete Excel Course with AI Integration features no health datasets, it teaches the mechanics those tasks require: absolute references for rate equations, data cleaning tools, IFS and COUNTIFS for case stratification, charting, PivotTables, population lookups, and Power Query with Power Pivot for facility returns.

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:

  • Tidy up survey extracts and patient line lists using Text to Columns, Remove Duplicates and TRIM, then classify ages into distinct brackets with IFS.
  • Compute incidence rates per 1,000 or 100,000 people by locking the multiplier in a fixed cell and pulling district population counts via XLOOKUP.
  • Summarise health indicators and patient totals across sex, age band, district and month by means of PivotTables and COUNTIFS.
  • Plot an epidemic curve using a column chart grouped by onset date, adding in-cell sparklines to display local district trajectories.
  • Append multiple monthly facility files stored in a single folder using Power Query, unpivoting monthly columns into a normalised row structure.
  • Construct a health indicator dashboard driven by slicers and DAX measures atop a Power Pivot data model.

Projects

What you will build.

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

  • Nine Excel chart types from the Marketing Engagement Dashboard: combo, bar, funnel, Pareto, waterfall, histogram, box and whisker, sunburst and treemap.

    Project 04

    Marketing Engagement Dashboard

    Nine charts, each picked for the question it answers.

    Visualise surveillance data through weekly onset column graphs, longitudinal trend lines, age distribution histograms, and localised district sparklines.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Break down indicators and case volumes by month, district and age bracket within an interactive PivotTable that features grouped onset dates and district slicers.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Retrieve district denominator figures from separate lookup tables using XLOOKUP, displaying rates per 100,000 directly alongside recorded case counts.

  • 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.

    Consolidate monthly facility spreadsheets from a shared directory via Power Query, convert wide month headers into rows, and present indicators on a data model dashboard.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 19 Excel tables

Start with Lesson 19 if you are comfortable with spreadsheet formulas, because dependable analysis starts with clean records, and Managing data in Excel, which covers this, is free by email along with exercise workbooks. If you need to learn absolute references, turn to Lesson 10, or begin at Lesson 1 if you are completely new to spreadsheets. Those working with prepared data can jump straight to Lesson 48 for charting techniques, or proceed directly to Lesson 153 for the introduction to Power Query.

Get the free section by email

64 of 186 lessons · 5 h 45 min

01 Microsoft Excel fundamentals: building a foundation from scratch 2 of 13 lessons · 6 min Learn rate arithmetic: pin a multiplier such as 100,000 into a fixed cell with an absolute reference, and calculate proportions using percentage formatting. The exercises demonstrate this on personal budget numbers, which relies on the same division and multiplication needed for a rate per 100,000.
  1. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Treat survey extracts and line lists as structured Excel Tables: apply sorting and filters, strip stray spaces with TRIM, substitute text with Find and Replace, delete duplicate entries, separate variables using Text to Columns and Flash Fill, lock top rows with Freeze Panes, and prepare tidy print layouts. The exercises practise these skills on staff records.
  1. 19 Start here Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 9 of 13 lessons · 55 min Apply formula logic to public health records: categorise age brackets with IFS and nested IF, evaluate multiple criteria using AND and OR, verify field completeness with COUNT and COUNTA, tally case numbers across sex, district and age band via COUNTIF and COUNTIFS, and isolate reporting intervals with date utilities like MONTH.
  1. 31 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
  8. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  9. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
06 Data visualisation: charts and graphs 9 lessons · 45 min Visualisations suited for routine surveillance and programme monitoring: plot onset timelines with column charts, track progress over time with line graphs, combine metrics on dual-axis combo charts, embed district sparklines, and inspect age distributions with box and whisker plots and histograms. Practical exercises work with marketing figures.
  1. 48 Choosing the right Excel chart Column, line, pie, combo and modern chart types 4 min
  2. 49 Column charts in Excel Insert, format and label a column chart 5 min
  3. 50 Line charts in Excel Show trends over time with a line chart 4 min
  4. 51 Pie charts in Excel Show proportions and parts of a whole 4 min
  5. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
  6. 53 Formatting charts in Excel Titles, data labels, colours and chart styles 7 min
  7. 54 Sparklines in Excel Line, column and win-loss mini charts in cells 5 min
  8. 55 Funnel, Pareto and waterfall charts in Excel Modern charts for business data 5 min
  9. 56 Histogram, box and whisker, treemap and sunburst Advanced Excel chart types 6 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Generate multi-dimensional summaries of indicators by month, sex, age group and district using PivotTables: aggregate onset dates into time intervals, filter interactively with slicers, display visual PivotCharts, refine table presentation, and update figures when importing new data extracts. Training modules use sales transaction records.
  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 4 of 12 lessons · 19 min Import population denominators into case lists: pull district headcounts from reference sheets using XLOOKUP, VLOOKUP, or INDEX and MATCH, while handling unlisted administrative units with IFERROR. The source tutorials illustrate these lookup techniques on human resources files.
  1. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  2. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  3. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  4. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min Process facility returns using Power Query: ingest CSV exports, refine text fields, correct column data types, merge separate files from a single folder, join reference tables, validate aggregates with Group By, and transform wide month columns into rows. Power Pivot then establishes table relationships, DAX formulas produce rates much like the lesson margin percentages, and an interactive dashboard connects to the data model. Note that the Power Pivot lessons require Excel for Windows.
  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
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 8 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.

Can public health professionals find an Excel course for their work?

Yes. The Complete Excel Course with AI Integration from Coding Liquids provides instruction in the practical spreadsheet techniques required in public health roles. Their uses include tidying survey files and patient line lists, structuring age intervals with IFS, aggregating district case tallies by sex and age in PivotTables and COUNTIFS, computing rates per 100,000 using XLOOKUP population retrievals, displaying epidemic curves via column charts, and consolidating monthly facility submissions with Power Query. The syllabus relies entirely on non-health practice datasets. The curated sequence featured here comprises 64 lessons, taking 5 hours 45 minutes altogether.

Can a public health analyst calculate rates per 100,000 in Excel?

Yes. You can store district denominator figures in a reference table, pull each catchment population next to its recorded total via XLOOKUP, and divide cases by population before multiplying by 100,000 anchored in an absolute cell reference. Any missing district entries can be caught cleanly using IFERROR. The Complete Excel Course with AI Integration walks through these mechanical steps using human resources and financial budget exercises. Note that statistical concepts like confidence intervals and age standardisation are omitted from the course.

Can public health officers plot an epidemic curve in Excel?

Yes, by building a column chart that plots case volumes across intervals of onset. You can calculate weekly or daily totals using COUNTIFS, or place onset timestamps into a PivotTable to group them before rendering a column graph. In The Complete Excel Course with AI Integration, lessons demonstrate column chart creation, visual formatting and PivotTable date aggregation, though no lesson constructs an outbreak curve. The course also demonstrates histogram charts, which work well for plotting patient age distributions.

Can public health teams use Power Query to consolidate monthly health facility reports?

Yes, provided the incoming templates share a uniform layout. In The Complete Excel Course with AI Integration, Power Query consolidates the files in a folder into a unified table, standardises text, corrects data types, joins an external lookup table, validates group totals via Group By, and transforms wide month columns into rows. Further lessons connect Power Pivot and DAX measures to an interactive reporting dashboard, though these specific modelling sections require Excel for Windows. Future reporting cycles simply require dropping new monthly files into the folder.

Does the course cover public health epidemiology, statistical methods or DHIS2?

No. The Complete Excel Course with AI Integration focuses strictly on spreadsheet operations rather than epidemiological science. It omits study methodology, statistical techniques, public health surveillance architectures, and specialised software like DHIS2, Epi Info or R, and no lesson uses health data. Furthermore, guidelines on managing confidential patient data are not addressed, meaning data governance rules remain the responsibility of your organisation. The instruction concentrates on tabulating, plotting and joining metrics after they have been entered into Excel.

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