Build an M&E Program Tracker in Excel

Build a monitoring and evaluation tracker with formulas, PivotTables, and charts.

Introduction

30 Second Summary

When an organization runs a health outreach program across multiple regions, someone needs to track every person served and figure out whether the program is actually working. That role is called Monitoring and Evaluation, or M&E for short.

In this project, you will build an M&E Program Tracker workbook in Excel for a fictional community health program called "Community Health Connect." You'll structure beneficiary data with validation rules, write formulas that calculate program indicators, and create visualizations for stakeholder reporting.

What You'll Build

A complete Excel workbook that tracks, analyzes, and reports on beneficiary data for a fictional community health outreach program operating across four regions.

By the end of this project, you'll have:

A Data sheet with 20 beneficiary records protected by data validation rules like dropdown lists and date constraints.
A Summary sheet with formulas (including COUNTIF, XLOOKUP, and AVERAGE) that automatically calculate completion rates, regional breakdowns, and service targets.
A PivotTable and bar chart that visualize program performance by region, plus conditional formatting that highlights incomplete services.
Secret Mission: Build a traffic-light indicator performance tracker that flags each service as "On Track", "At Risk", or "Off Track" using nested IF formulas and color-coded formatting.

Are there any prerequisites?

This project assumes no prior Excel experience. Every click and keystroke is spelled out. All you need is Microsoft Excel 2021 installed on a Windows computer.

Get Oriented in Excel and Set Up Your Workbook

Every M&E program needs a reliable place to track beneficiary data, service delivery, and program indicators. That place is usually a spreadsheet.

In this step, you will open Excel for the first time, learn how the interface works, and set up the three worksheets your M&E tracker needs.

In this step, get ready to:

Open Excel 2021 and save a new workbook.
Create three worksheet tabs for your tracker.
Add column headers and reference data to your sheets.

Open Excel and save your workbook

Excel's interface is built around a grid of rectangles called cells. Before you can start building your tracker, you need to open Excel and save a new workbook file.

Press the Windows key on your keyboard to open the search bar.
Type Excel and press Enter to open Microsoft Excel.
Click Blank workbook to create a new file.

You should now see a large grid of empty rectangles. These are cells.

What am I looking at?

Here is what you see on screen:

The grid of rectangles in the center are cells. Each cell has a unique address made up of its column letter and row number. For example, the very first cell in the top-left corner is A1 (column A, row 1).
The Name Box is the small field to the left of the formula bar at the top. It shows the address of whichever cell you have selected.
The ribbon at the top of the screen has tabs like Home, Insert, and Data. These tabs contain all of Excel's tools.

Click on cell A1 (the very first cell in the top-left corner of the grid).
Look at the Name Box to the left of the formula bar. Confirm it displays A1.

Now save your workbook so you do not lose any work.

Click the File tab in the top-left corner of the ribbon.
Click Save As.
Choose a location on your computer (such as your Desktop or Documents folder).
In the File name field, type ME_Program_Tracker.
Make sure the Save as type dropdown is set to Excel Workbook (*.xlsx).
Click Save.

The title bar at the top of the window should now display ME_Program_Tracker.

Don't see the Save As option?

If pressing Ctrl+S opens a Backstage view (a full-screen panel showing recent files and OneDrive options), click Browse to open the traditional Save dialog. You can also press F12 at any time to go straight to the Save As dialog.

Help me save my workbook for the first time in Excel 2021.

Create your worksheet tabs

Your workbook needs three separate sheets to organize different types of data. Right now you only have one sheet.

Look at the bottom of your screen. You will see a tab labeled Sheet1.
Right-click on the Sheet1 tab.
Select Rename from the menu.
Type Data and press Enter.
Click the + icon to the right of the Data tab to add a new sheet.
Right-click the new sheet tab.
Select Rename.
Type Lookup and press Enter.
Click the + icon again to create a third sheet.
Right-click the new sheet tab.
Select Rename.
Type Summary and press Enter.
Click the Data tab at the bottom to switch back to that sheet.

What are these three sheets for?

Data will hold all 20 beneficiary records for the health program.
Lookup will hold a small reference table of service types and their targets. Formulas on other sheets will pull values from here.
Summary will hold formulas, totals, and a chart that summarize the program's performance.

You should now see three tabs at the bottom of your screen: Data, Lookup, and Summary.

Accidentally deleted a sheet?

Click the + icon at the bottom to add a new sheet. Then right-click the new tab, select Rename, and type the missing sheet name.

Help me fix my worksheet tabs in Excel.

Add column headers and reference data

Column headers define what data goes into each column. Setting them up first keeps your data organized and consistent.

On the Data sheet, click cell A1.
Type Beneficiary ID and press the Tab key to move to B1.
Type Name and press Tab.
Continue across the row, pressing Tab after each entry:

Now format the header row so it stands out from the data rows below.

Click cell A1.
Hold Shift and click cell H1 to select the entire header row.
On the Home tab in the ribbon, click the Bold button (the letter B).
Click the small arrow next to the Fill Color button (the paint bucket icon on the Home tab).
Choose a light blue color from the palette.

Why format headers?

Bold text and a colored background make headers visually distinct from data rows. This helps you spot the header row instantly, especially once you have 20 records below it.

Now switch to the Lookup sheet to enter reference data that your formulas will use later.

Click the Lookup tab at the bottom of the screen.
Click cell A1 and type Service Type.
Press Tab to move to B1.
Type Target and press Enter to move to the next row.
In A2, type Vaccination. Press Tab, type 50, and press Enter.
In A3, type Nutrition Screening. Press Tab, type 40, and press Enter.
In A4, type Maternal Health. Press Tab, type 30, and press Enter.
In A5, type HIV Testing. Press Tab, type 45, and press Enter.

What is this reference table for?

Later in the project, you will use an XLOOKUP formula to pull target numbers from this table automatically. Having the targets in their own sheet keeps them separate from raw data and easy to update.

Press Ctrl+S to save your workbook.

Verify your setup by clicking through each sheet tab at the bottom.

Click the Data tab. Confirm that row 1 has 8 bold, light-blue headers stretching from A1 to H1.
Click the Lookup tab. Confirm you see 4 rows of service types with matching target numbers in columns A and B.
Click the Summary tab. It should be blank for now.

Headers not bold or colored?

Make sure you selected the full range from A1 to H1 before applying formatting. Click A1, hold Shift, then click H1. The entire range should be highlighted. Then click Bold and Fill Color on the Home tab.

Help me format my header row in Excel.

Your workbook structure is in place. Next up, you will fill the Data sheet with 20 beneficiary records and add data validation rules to keep every entry clean.

Enter Beneficiary Data and Apply Data Validation

Your workbook has three sheets and a row of headers ready to receive data. Now it's time to protect the Data sheet with validation rules and fill it with beneficiary records for the Community Health Connect program.

Typing data by hand introduces risk. A misspelled region name or an impossible date can throw off every formula and chart you build later. In this step, you'll set up dropdown lists and date constraints first, then enter 20 records into the protected columns.

In this step, get ready to:

Set up dropdown lists that restrict data entry to valid options.
Enter 20 sample beneficiary records for the health program.
Add a date validation rule to constrain the Date of Service column to 2025.

Set up dropdown lists for data validation

Data validation controls what values can be entered into a cell. You'll create dropdown lists for four columns so that every record uses a consistent, pre-approved value.

On the Data sheet, click cell C2.
Hold Shift and click cell C21 to select the range C2:C21. This is the Region column where your 20 records will go.
Click the Data tab in the ribbon at the top of the screen.
Click the Data Validation button (it has a green checkmark and grid icon).
A dialog box appears. Click the dropdown under Allow and select List.
In the Source field, type North,South,East,West with no spaces after the commas.
Click OK.

What is data validation?

Data validation is an Excel feature that restricts what can be entered into a cell. By setting up a dropdown list, you ensure that only pre-approved values (like your four region names) can be selected.

This is a core M&E skill. Clean data starts at the point of entry. Catching errors during data collection is far easier than fixing them after hundreds of records have been entered.

Now set up the same dropdown validation for three more columns. Follow the same steps each time: select the cell range, open Data Validation from the Data tab in the ribbon, choose List under Allow, type the source values, and click OK.

Select D2:D21 (the Gender column). Source: Male,Female.
Select F2:F21 (the Service Type column). Source: Vaccination,Nutrition Screening,Maternal Health,HIV Testing.
Select H2:H21 (the Status column). Source: Completed,Incomplete.

Click any cell in the Region column (like C5). You should see a small dropdown arrow appear on the right edge of the cell.
Click the dropdown arrow to confirm it shows North, South, East, and West.
Press Escape to close the dropdown without selecting a value.

Don't see the dropdown arrow?

Make sure you selected the correct cell range. The range should start at row 2 (not row 1, which is the header).
Check that you chose List under Allow in the Data Validation dialog. If you accidentally left it on Any value, no restriction is applied.
Confirm there are no extra spaces after the commas in your Source field. Type the values with commas only, like North,South,East,West.

Still stuck? Help me fix my data validation dropdown in Excel.

Enter 20 beneficiary records

With your dropdown lists in place, you can now enter beneficiary data. The dropdowns will guide every entry in the Region, Gender, Service Type, and Status columns.

Click cell A2 and type BEN-001.
Press Tab to move to B2 and type a name (for example, Grace Amara).
Press Tab to move to C2 (the Region column). Click the small dropdown arrow on the right edge of the cell and select a region.
Press Tab to move to D2 (Gender). Use the dropdown to pick a gender.
Press Tab to move to E2 (Age). Type a number between 18 and 65.
Press Tab to move to F2 (Service Type). Use the dropdown to select a service.
Press Tab to move to G2 (Date of Service). Type a date like 03/15/2025.
Press Tab to move to H2 (Status). Use the dropdown to pick Completed or Incomplete.

That completes your first record. Now move to row 3 by clicking cell A3 and enter BEN-002. Continue filling in each column across the row, then move to the next row.

Continue entering records until you reach BEN-020 in row 21. You should have 20 total records.
Aim for about 14 records with Completed status and 6 with Incomplete status.

Spread your data evenly

Distribute your records across all 4 regions, both genders, and all 4 service types. This gives your formulas and PivotTable meaningful data to work with in later steps. If every record uses the same region, your per-region analysis will not be very interesting.

Scroll through your Data sheet from row 2 to row 21. You should see 20 filled rows with IDs from BEN-001 to BEN-020.

Seeing an error when you type a value?

If Excel rejects a value in a dropdown column (Region, Gender, Service Type, or Status), it means the value does not match the validation list. Use the dropdown arrow to select from the approved options instead of typing freehand.

If a date entry is rejected, make sure you are using the MM/DD/YYYY format (for example, 03/15/2025). You will add a formal date restriction in the next substep.

Need help? Help me fix data entry errors in my Excel validation columns.

Add a date validation rule

Your dropdown lists protect four columns. The Date of Service column still accepts any value, including text or dates from the wrong year. A date validation rule locks this column to valid 2025 dates only.

Click cell G2.
Hold Shift and click cell G21 to select the range G2:G21.
Click the Data tab in the ribbon, then click Data Validation.
Under Allow, choose Date.
Set the Data dropdown to between.
In the Start date field, type 01/01/2025.
In the End date field, type 12/31/2025.
Click OK.

Why restrict dates to 2025?

Date constraints prevent impossible entries from slipping into your dataset. A typo like 2020 or a text entry like March would break date calculations in reports.

In real M&E programs, date validation rules are tied to the reporting period. If your program runs from January to December 2025, data from outside that window is likely an entry error.

Test the rule by clicking an empty cell in the Date of Service column (like G21 if it is empty) and typing hello.
You should see an error message popup because hello is not a valid date.
Click Cancel or press Escape to dismiss the error.
Press Ctrl+S to save your workbook.

Date validation not working?

Make sure you selected G2:G21, not G1:G21. Including the header cell (G1) can cause unexpected behavior.
Check that you chose Date under Allow and set Data to between.
Verify both the start date (01/01/2025) and end date (12/31/2025) are entered correctly.

Still stuck? Help me fix my date validation rule in Excel.

Your data is locked down and your 20 records are in place. Next up, you'll move to the Summary sheet and write formulas that automatically calculate key M&E indicators from this data.

Build M&E Formulas on the Summary Sheet

Your Data sheet now has 20 validated beneficiary records, and your Lookup sheet holds the target numbers for each service type. The raw data is ready for analysis.

In this step, you'll move to the Summary sheet and write formulas that automatically calculate key M&E indicators. A formula is an instruction that tells Excel to calculate something. Every formula starts with an equals sign (=).

In this step, get ready to:

Write summary formulas to calculate totals, averages, and completion rates.
Use COUNTIF to break down beneficiaries by region.
Build a service targets comparison table using XLOOKUP.

Write program summary formulas

The Summary sheet will serve as a dashboard for your program's key indicators. You'll start by calculating the most fundamental M&E metrics: how many beneficiaries you've served, their average age, and your program's completion rate.

Click the Summary sheet tab at the bottom of the screen.
Click cell A1 and type Program Summary, then press Enter.
Skip a row. Click cell A3 and type Total Beneficiaries.
Press Tab to move to B3. Type this formula and press Enter:

=COUNTA(Data!A2:A21)

What does this formula do?

COUNTA counts how many cells in a range contain data (text or numbers). Data!A2:A21 tells Excel to look at cells A2 through A21 on the Data sheet. The ! separates the sheet name from the cell range.

You should see the number 20, matching your 20 beneficiary records.

Click cell A4 and type Average Age.
Press Tab to move to B4. Type this formula and press Enter:

=AVERAGE(Data!E2:E21)

What does AVERAGE do?

AVERAGE calculates the average (mean) of all numeric values in a range. This formula averages every age value in column E of the Data sheet.

Click cell A5 and type Completed Services.
Press Tab to move to B5. Type this formula and press Enter:

=COUNTIF(Data!H2:H21,"Completed")

What does COUNTIF do?

COUNTIF counts only the cells that match a specific condition. The first part (Data!H2:H21) is the range to search. The second part ("Completed") is the condition to match.

You should see a number around 14, matching the Completed records you entered.

Click cell A6 and type Completion Rate.
Press Tab to move to B6. Type this formula and press Enter:

=B5/B3

What does this formula do?

This divides completed services (B5) by total beneficiaries (B3) to give you the completion rate. You'll see a decimal like 0.7 for now. The next step formats it as a percentage.

Now format the result so it displays as a percentage instead of a decimal.

Right-click cell B6 and select Format Cells.
Click the Number tab in the dialog box.
Select Percentage from the Category list on the left.
Set decimal places to 0.
Click OK.

Press Ctrl+S to save.
Cell B3 should show 20. Cell B5 should show your completed count (around 14). Cell B6 should display a percentage like 70%.

Seeing a #REF! or #NAME? error?

Check that you typed Data! (with the exclamation mark) before the cell range. This tells Excel to look at the Data sheet.
Make sure you wrapped Completed in double quotes in the COUNTIF formula.
Double-check that the formula starts with an equals sign (=). Without it, Excel treats the entry as plain text.

Help me fix formula errors on my Summary sheet.

Count beneficiaries by region

M&E reports almost always break down data by region or district. The COUNTIF formula you just learned can count beneficiaries in each region.

Click cell A8 and type Beneficiaries by Region, then press Enter.
In A9 type North.
In A10 type South.
In A11 type East.
In A12 type West.
Click cell B9 and type this formula:

=COUNTIF(Data!C2:C21,A9)

Press Enter. This counts how many beneficiaries have "North" in the Region column.

Instead of retyping this formula three more times, you can use the fill handle to copy it.

Click cell B9 again.
Look for the small green square in the bottom-right corner of the cell. This is the fill handle.
Click and drag the fill handle down to B12, then release.

What is the fill handle?

The fill handle copies a formula and automatically adjusts cell references. When you drag from B9 to B12, Excel changes A9 to A10, A11, and A12 in each copy.

This means B10 counts "South", B11 counts "East", and B12 counts "West" automatically. You'll use the fill handle several more times in this step.

Press Ctrl+S to save.
The four numbers in B9 through B12 should add up to 20 (your total beneficiaries).

Numbers don't add up to 20?

Make sure the region names in A9:A12 match exactly what you used in your Data sheet dropdowns: North, South, East, West.
COUNTIF ignores upper and lower case, so capitalization is not the issue. Check for extra spaces before or after the region names.

Help me fix my COUNTIF region formulas.

Build a service targets comparison table

The final section on your Summary sheet compares actual results against targets. This is core M&E work: measuring whether a program is reaching the people it set out to serve.

You'll use XLOOKUP to pull target numbers from the Lookup sheet automatically, so the Summary sheet always stays in sync with your reference data.

Click cell A14 and type Service Targets, then press Enter.
In row 15, type four column headers: A15 = Service Type, B15 = Target, C15 = Actual, D15 = Gap.
Type the four service types in column A: A16 = Vaccination, A17 = Nutrition Screening, A18 = Maternal Health, A19 = HIV Testing.
Click cell B16 and type this formula:

=XLOOKUP(A16,Lookup!A2:A5,Lookup!B2:B5,"Not Found")

What does XLOOKUP do?

XLOOKUP searches for a value and returns a matching result from another column. Here's what each part of this formula does:

A16 is the value to search for ("Vaccination").
Lookup!A2:A5 is where to search (the Service Type column on the Lookup sheet).
Lookup!B2:B5 is where to pull the result from (the Target column).
"Not Found" is a fallback value if no match exists. This prevents the formula from showing a confusing #N/A error.

Why XLOOKUP instead of VLOOKUP?

XLOOKUP is the modern replacement for VLOOKUP, available in Excel 2021. It can search in any direction, has built-in error handling with the "Not Found" parameter, and does not require you to count column numbers.

You may see VLOOKUP in older M&E templates, but XLOOKUP is the current standard for new work.

Press Enter. You should see the number 50 (the Vaccination target from the Lookup sheet).
Click cell B16 again and use the fill handle to copy the formula down to B17, B18, and B19.

Now add the Actual column, which counts how many beneficiaries received each service type.

Click cell C16 and type this formula:

=COUNTIF(Data!F2:F21,A16)

Press Enter. This counts how many beneficiaries received Vaccination services on the Data sheet.
Use the fill handle to copy C16 down to C17, C18, and C19.

Finally, calculate the gap between target and actual for each service type.

Click cell D16 and type this formula:

=B16-C16

What does the gap tell you?

The gap shows how many more beneficiaries each service type needs to reach its target. A positive number means the target hasn't been met yet. A zero or negative number means the target has been reached or exceeded.

Press Enter.
Use the fill handle to copy D16 down to D17, D18, and D19.
Press Ctrl+S to save.

The Target column (B16:B19) should show 50, 40, 30, and 45 (matching your Lookup sheet).
The Actual column should show counts for each service type from your data.
The Gap column should show the difference. Positive numbers mean the target hasn't been met yet.

Seeing #N/A or "Not Found" in the Target column?

Check that the service type names in A16:A19 match exactly what you typed on the Lookup sheet. Even an extra space will cause a mismatch.
Click on the Lookup sheet tab and verify that A2:A5 contains the four service types.
If a cell shows "Not Found" instead of a number, that means XLOOKUP could not find a match. Compare the spelling character by character.

Help me fix my XLOOKUP formula results.

✔️ Awesome, I've got everything!

Great. Double-check that you've saved your file with Ctrl+S.

ⓧ I'd like to double check the full Summary sheet

Here is every cell you should have on the Summary sheet. Compare yours against this reference:

A1 = Program Summary

A3 = Total Beneficiaries       B3 = =COUNTA(Data!A2:A21)
A4 = Average Age               B4 = =AVERAGE(Data!E2:E21)
A5 = Completed Services        B5 = =COUNTIF(Data!H2:H21,"Completed")
A6 = Completion Rate           B6 = =B5/B3  (formatted as Percentage, 0 decimals)

A8 = Beneficiaries by Region
A9 = North                     B9  = =COUNTIF(Data!C2:C21,A9)
A10 = South                    B10 = =COUNTIF(Data!C2:C21,A10)
A11 = East                     B11 = =COUNTIF(Data!C2:C21,A11)
A12 = West                     B12 = =COUNTIF(Data!C2:C21,A12)

A14 = Service Targets
A15 = Service Type   B15 = Target   C15 = Actual   D15 = Gap
A16 = Vaccination              B16 = =XLOOKUP(A16,Lookup!A2:A5,Lookup!B2:B5,"Not Found")
                               C16 = =COUNTIF(Data!F2:F21,A16)
                               D16 = =B16-C16
A17 = Nutrition Screening      B17 = =XLOOKUP(A17,Lookup!A2:A5,Lookup!B2:B5,"Not Found")
                               C17 = =COUNTIF(Data!F2:F21,A17)
                               D17 = =B17-C17
A18 = Maternal Health          B18 = =XLOOKUP(A18,Lookup!A2:A5,Lookup!B2:B5,"Not Found")
                               C18 = =COUNTIF(Data!F2:F21,A18)
                               D18 = =B18-C18
A19 = HIV Testing              B19 = =XLOOKUP(A19,Lookup!A2:A5,Lookup!B2:B5,"Not Found")
                               C19 = =COUNTIF(Data!F2:F21,A19)
                               D19 = =B19-C19

Your Summary sheet now calculates key M&E indicators automatically. Next up, you'll create a PivotTable that lets you analyze program performance from different angles with just a few clicks.

Create a PivotTable to Analyze Program Data

Your Summary sheet now calculates key M&E indicators automatically. Totals, averages, region breakdowns, and service target comparisons all update whenever your data changes.

In this step, you will create a PivotTable. A PivotTable lets you summarize and cross-tabulate your data with a few clicks instead of writing more formulas.

What is a PivotTable?

A PivotTable lets you group, count, and summarize rows of data by dragging column names into different areas. Instead of writing a new formula for every combination you want to analyze, you rearrange fields and Excel recalculates instantly.

M&E officers use PivotTables daily to slice program data by region, time period, service type, and more.

In this step, get ready to:

Insert a PivotTable from your beneficiary data on a new worksheet.
Drag fields into rows, columns, and values to cross-tabulate completion status by region.
Switch the display from raw counts to percentages for M&E reporting.

Insert a PivotTable from your data

To create a PivotTable, you need to start from the sheet that holds the data you want to analyze. Excel will detect your data range automatically.

Click the Data sheet tab at the bottom of the screen.
Click on any cell that contains data, such as cell A1.
Click the Insert tab in the ribbon at the top.
Click the PivotTable button on the left side of the ribbon.

A dialog box appears. It should automatically detect your data range as Data!$A$1:$H$21.

Under "Choose where you want the PivotTable report to be placed", select New Worksheet.
Click OK.

Excel creates a new sheet with an empty PivotTable and a PivotTable Fields pane on the right side of the screen.

Right-click the new sheet's tab at the bottom and select Rename.
Type PivotAnalysis and press Enter.

Don't see the PivotTable Fields pane?

The pane only appears when a cell inside the PivotTable is selected. Click anywhere inside the empty PivotTable area on the sheet and the pane should reappear on the right.

Help me find the PivotTable Fields pane in Excel.

Drag fields into the PivotTable

How does the PivotTable Fields pane work?

The top section of the pane lists all of your column names from the Data sheet (Beneficiary ID, Name, Region, and so on). The bottom section has four areas: Filters, Columns, Rows, and Values.

You build your PivotTable by dragging column names from the top list into these areas. Each area controls a different part of how your data is organized in the table.

Click and drag Region from the field list at the top of the pane down into the Rows area at the bottom.
Click and drag Status into the Columns area.
Click and drag Beneficiary ID into the Values area.

What should I see?

Your PivotTable now has four region names (North, South, East, West) as rows. Completed and Incomplete appear as column headers.

Each cell shows the count of beneficiaries for that region and status combination. The Values area automatically displays as Count of Beneficiary ID because Beneficiary ID is a text field. The Grand Total row at the bottom and column on the right show overall totals.

Switch from counts to percentages

Raw counts show how many beneficiaries fall into each category, but M&E reports typically need percentages. Showing each region's completion rate makes it easier to compare performance across regions of different sizes.

Right-click on any number inside the PivotTable (not the row or column headers).
Select Value Field Settings from the menu.
In the dialog box, click the Show Values As tab.
Click the dropdown that says No Calculation and change it to % of Row Total.
Click OK.

Each region's row now shows the percentage of its beneficiaries with Completed vs. Incomplete status. The percentages in each row add up to 100%.

Why % of Row Total?

This setting divides each cell's value by its row's total. It answers the question: what percentage of this region's beneficiaries completed the service?

If you chose % of Grand Total instead, each cell would show its share of the entire dataset. Per-region completion rate is the more useful metric for M&E reporting.

Press Ctrl+S to save your workbook.

PivotTable still showing counts instead of percentages?

Make sure you right-clicked on a number cell inside the PivotTable, not on a row label or column header.
In the dialog, check that you clicked the Show Values As tab at the top, not the Summarize Values By tab. These are two different tabs in the same dialog.

Help me change my PivotTable from counts to percentages.

Your PivotTable now shows completion rates by region at a glance. Next up, you will turn this data into a visual chart and add conditional formatting to highlight records that need follow-up.

Visualize Program Performance with Charts and Conditional Formatting

Your PivotTable already breaks down completion rates by region. Now it is time to make your tracker visually impactful for stakeholders who need to understand program performance at a glance.

In this step, you will create a bar chart that shows beneficiaries served per region and apply conditional formatting to flag records that need follow-up. These are essential skills for M&E reporting.

In this step, get ready to:

Create a bar chart that visualizes beneficiaries by region.
Apply conditional formatting to highlight incomplete services in red.
Test that formulas, the PivotTable, and the chart all stay connected when data changes.

Create a bar chart from the region data

The Summary sheet already has region-by-region beneficiary counts in cells A9 through B12. A chart turns these numbers into a visual that stakeholders can understand instantly.

Click the Summary sheet tab at the bottom of the screen.
Click cell A9 (which contains "North").
Hold Shift and click cell B12 to select both the region names and their count values.
Click the Insert tab in the ribbon at the top.
In the Charts group, click the Insert Column or Bar Chart button (it looks like a small bar chart icon).
From the dropdown, select Clustered Bar (the first option under 2-D Bar).

What did selecting A9:B12 do?

Cells A9:B12 contain your four region names (North, South, East, West) alongside the COUNTIF values next to them.

Excel uses the text column as chart labels and the number column as bar lengths. This is why you needed to select both columns.

Excel inserts a bar chart onto your sheet. Now give it a descriptive title and position it.

Click on the chart title text (it may say Chart Title).
Type Beneficiaries by Region to replace it.
Resize the chart by clicking and dragging any corner handle.
Position the chart below your summary data so it does not overlap any cells with formulas.

You should see a horizontal bar chart with four bars, one for each region, showing the number of beneficiaries served.

Chart shows no data or wrong labels?

Make sure you selected both columns A and B (region names and numbers). If you only selected column A, the chart has no numeric data to plot.

If the chart looks wrong, click on it and press Delete to remove it. Then re-select A9:B12 and insert the chart again.

My bar chart isn't showing the correct region data. How do I fix it?

Highlight incomplete services with conditional formatting

Conditional formatting automatically changes a cell's appearance based on its value. In M&E work, this makes it easy to flag records that still need follow-up.

Click the Data sheet tab at the bottom.
Click cell H2 (the first Status value).
Hold Shift and click cell H21 to select all 20 status cells.
On the Home tab in the ribbon, click the Conditional Formatting button.
Hover over Highlight Cells Rules in the dropdown.
Click Text that Contains... from the submenu.
In the dialog box, type Incomplete in the text field.
From the format dropdown on the right, select Light Red Fill with Dark Red Text.
Click OK.

What does this formatting rule do?

Excel now checks every cell in H2:H21 whenever the sheet recalculates. Any cell containing the text "Incomplete" gets a light red background with dark red text automatically.

This makes it easy to scan 20 (or 2,000) records and spot the ones that still need follow-up.

Every cell in the Status column that says "Incomplete" should now be highlighted in red. Cells that say "Completed" remain unchanged.

No red highlighting on your Incomplete cells?

Make sure you selected H2:H21 (the data cells), not just the header cell H1. The formatting rule only applies to the cells you had selected when you created it.

Also check that you typed Incomplete exactly as it appears in your dropdown values. A mismatch means the rule has nothing to highlight.

My conditional formatting rule isn't highlighting cells. How do I fix it?

Test that your formulas, chart, and PivotTable stay connected

The real power of your tracker is that everything updates when you change the source data. A single edit on the Data sheet should ripple through your formulas, chart, and PivotTable.

Press Ctrl+S to save your workbook.
On the Data sheet, find a row that has Incomplete in the Status column.
Click the dropdown arrow in that row's Status cell and change it to Completed.
Click the Summary sheet tab.
Check that the Completed Services count in B5 and the Completion Rate in B6 have both increased.
Click the PivotAnalysis sheet tab.
Right-click anywhere inside the PivotTable.
Select Refresh from the menu.

Why does the PivotTable need a manual refresh?

Formulas on the Summary sheet recalculate automatically whenever data changes. PivotTables do not update automatically.

You need to right-click and select Refresh (or use the PivotTable Analyze tab) to pull in updated data. This is a common gotcha in M&E reporting.

Confirm that the PivotTable percentages have changed to reflect the updated record.
Go back to the Data sheet and change the status back to Incomplete if you want to keep your original numbers.
Press Ctrl+S to save your completed workbook.

The Summary sheet formulas and the bar chart should both reflect the change you made. The PivotTable percentages should update after you clicked Refresh.

PivotTable numbers did not change?

PivotTables do not auto-refresh. Make sure you right-clicked inside the PivotTable itself (on one of the data cells) and selected Refresh.

If the Summary sheet formulas also did not change, double-check that you actually saved the edit on the Data sheet. Click the Data tab and confirm the status cell shows the new value.

My PivotTable and formulas are not updating when I change the data. How do I fix this?

Secret mission

Build an Indicator Performance Tracker

Your Summary sheet already shows targets, actuals, and gaps for each service type. But in a real M&E report, stakeholders want to see at a glance which indicators are on track. In this mission, you will build a traffic-light status system that automatically flags each indicator as On Track, At Risk, or Off Track.

Clean Up Your Resources

This project runs entirely locally in Excel, so there are no ongoing costs. Decide whether to keep your workbook for future reference or delete it.

Resources you used:

ME_Program_Tracker.xlsx workbook file saved on your computer.

Keep everything running

No action needed. Choose this if you want to keep practicing with your workbook or use it as a reference for future M&E projects.

Your ME_Program_Tracker.xlsx file stays wherever you saved it on your computer.
You can reopen it any time by double-clicking the file or opening it from within Excel.

Pause - I'll come back to this later

Since this is a local file with no running processes, there is nothing to pause. Your workbook is already saved and ready to reopen whenever you come back.

Make sure you saved your workbook with Ctrl+S before closing Excel.
Close Excel by clicking the X button in the top-right corner of the window.

Delete - I don't want to use this again

Remove the project workbook from your computer entirely.

Close Excel if it is still open.
Open File Explorer and navigate to the folder where you saved ME_Program_Tracker.xlsx.
Right-click the file and select Delete.
Empty your Recycle Bin if you want to free the disk space immediately.

Nice Work!

You just built a complete M&E Program Tracker in Excel from a blank workbook. This is the same kind of workbook that M&E officers use every day to track, analyze, and report on program data to stakeholders.

Here's what you accomplished:

Built a structured M&E workbook with 20 beneficiary records protected by data validation rules (dropdown lists and date constraints) that prevent data entry errors at the source.
Wrote M&E formulas (COUNTA, AVERAGE, COUNTIF, XLOOKUP, and percentage calculations) that automatically calculate key program indicators like completion rates, beneficiary counts by region, and service-type targets.
Created a PivotTable that cross-tabulates completion status by region with percentage breakdowns, and a bar chart visualizing beneficiaries served per region.
Secret Mission: Built a traffic-light indicator performance tracker using nested IF formulas and multi-rule conditional formatting to flag indicators as On Track, At Risk, or Off Track.

Ready to quiz yourself?