Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for monitoring and evaluation officers

This guide is for monitoring and evaluation officers using Excel to clean surveys, track indicators and prepare donor report figures. It selects the material from The Complete Excel Course with AI Integration that this work draws on: cleaning data, counting and totalling by group and period, breaking down results with PivotTables, displaying progress against targets through charts and icon sets, and combining files with Power Query, as field office data requires.

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:

  • Use SUMIFS and COUNTIFS to maintain targets and actuals for each reporting period in an indicator table, with data bars and icon sets displaying progress within cells.
  • Prepare field survey records for analysis by tidying names with TRIM and PROPER, correcting spelling inconsistencies with SUBSTITUTE and deleting repeats with Remove Duplicates. Add drop-down lists for the next entry round.
  • Create age bands with IFS, then use PivotTables to view results separately by sex, age group and location.
  • Bring files from several field offices together in a single table by using Power Query to combine a folder's contents.
  • Present achievement against targets through column, line and combo charts, and place sparklines alongside indicators to show their progress.
  • Check complete and missing responses with COUNT, COUNTA and COUNTIFS before including the figures in a donor report.

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.

    Use marketing data to practise displays for a results report: compare achievement with targets in columns, follow trends across periods with lines, and add a sparkline alongside each indicator.

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

    Explore how a few clicks can separate results by sex, age group and location, with quarterly date groups and district slicers.

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

    Prepare for the next reporting round by using Power Query to clean each field office's file, set its data types and append it to a combined table that can be refreshed.

  • Project 02

    Dunder Michael Paper Company employee database

    Practise the cleaning needed for field surveys on a long employee list: remove extra spaces and duplicate records, separate a column into several, and organise the table for sorting and filtering by any answer.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 19 Excel tables

If you already enter data in Excel, start with Lesson 19 to put survey data cleaning first. The lessons here include the whole Managing data in Excel section, available free by email with its practice files when requested on the course page. For data bars and icon sets, the earlier Lesson 15 can be taken on its own. Beginners should start at Lesson 1. If you routinely use tables and formulas, begin with Lesson 57 to break down results or Lesson 153 to work with field office data.

Get the free section by email

59 of 186 lessons · 5 h 9 min

02 Project: personal budget tracker 1 of 5 lessons · 3 min The personal budget exercise teaches highlight rules, data bars and icon sets that can display progress towards targets directly in an indicator table.
  1. 15 Conditional formatting in Excel Highlight rules, data bars and icon sets 3 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Practise managing data on an employee database, then apply the skills to surveys: organise Excel Tables, sort and filter, use TRIM and Find and Replace to clean entries, and remove duplicates. Flash Fill and Text to Columns split columns, while frozen headers and printing help with viewing and presenting the table.
  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) 7 of 13 lessons · 45 min Calculate results for groups and periods using COUNTIF, COUNTIFS, SUMIF and SUMIFS, including breakdowns by sex, age group and location. Nested IF and IFS provide age bands and achievement labels, AND and OR handle two conditions, and COUNT and COUNTA help count complete answers.
  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
05 Text functions and data validation (enhancing data quality) 7 lessons · 35 min Improve survey answer quality with CONCAT, TRIM, PROPER, FIND and SUBSTITUTE, alongside Text to Columns. Practise through a clean-up challenge and use drop-down lists to keep entries consistent in the next collection round.
  1. 41 CONCAT, the & operator and TEXTJOIN in Excel Combine text from multiple cells 6 min
  2. 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
  3. 43 FIND, SEARCH and SUBSTITUTE in Excel Locate and replace text with formulas 5 min
  4. 44 Text to Columns wizard in Excel Split a column by a comma delimiter 4 min
  5. 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
  6. 46 Ninja Challenge: problem Clean messy data with text functions and validation 4 min
  7. 47 Ninja Challenge: solution PROPER, Text to Columns and drop-down lists 7 min
06 Data visualisation: charts and graphs 9 lessons · 45 min Work with marketing data to choose charts that answer reporting questions. The lessons cover column, line and combo charts, chart formatting, sparklines and modern chart types.
  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 Use the sales order exercises to learn PivotTables, slicers and PivotCharts for breakdowns by sex, age group, location or quarter. Group dates and refresh the results when new data becomes available.
  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
15 SalesFusion: advanced data tools and a Power Query modelling system 16 of 21 lessons · 1 h 28 min Practise on sales files to prepare for combining field office data with Power Query. Import CSV files, remove or rename columns, trim text, correct data types, add month fields and merge a lookup table, then append files from a folder into a single table.
  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

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 work of monitoring and evaluation officers?

The Complete Excel Course with AI Integration from Coding Liquids covers Excel tasks used by monitoring and evaluation officers. These include survey data cleaning, tracking targets and actuals with SUMIFS and COUNTIFS, using PivotTables to separate results by sex, age and location, displaying progress with charts and icon sets, and combining field office data through Power Query. This guide selects 59 lessons, taking 5 hours 9 minutes out of the course's 16 hours.

Will monitoring and evaluation officers learn logframes, sampling or SPSS on this course?

No. The Complete Excel Course with AI Integration covers Excel rather than evaluation methods. It does not teach logframes, theories of change, survey sampling, statistical tests or tools such as KoboToolbox and SPSS. Its focus is data that has reached Excel: cleaning records, counting by group and presenting progress towards targets.

Can monitoring and evaluation officers use Excel to disaggregate survey answers by sex and age?

Yes. With the functions The Complete Excel Course with AI Integration teaches, IFS assigns ages to bands and COUNTIFS counts responses using combinations of sex, age band and location. PivotTables offer those breakdowns too, with slicers for moving between groups. The exercises use employee and sales records, and the same approach applies to survey responses arranged in a table.

How can monitoring and evaluation officers present progress towards targets in Excel?

In The Complete Excel Course with AI Integration, the conditional formatting lesson teaches data bars and icon sets, which can display progress within indicator cells. Charts beside the table offer another option: columns compare achievement with targets, a combo chart uses two axes, and sparklines show trends.

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