How to Use Excel with Copilot and AI for M&E Data Analysis, Formulas, and Charts
EvalCommunity Tutorial
How to Use Excel with Copilot and AI for M&E Data Analysis, Formulas, and Charts
A practical guide for monitoring, evaluation, and development professionals who want to use AI responsibly inside Excel for spreadsheet analysis, formula generation, visualization, and evidence-informed reporting.
With Microsoft Copilot in Excel, M&E professionals can now use artificial intelligence directly inside a familiar spreadsheet environment. Instead of writing every formula manually, users can ask AI to explore data, suggest formulas, generate charts, create PivotTables, detect outliers, and summarize patterns.
The goal of this tutorial is not only to teach Excel. The goal is to show how M&E and development professionals can use AI responsibly while maintaining human oversight, evidence validation, and ethical judgement.
Learning Objectives
By the end of this tutorial, learners will be able to:
- Prepare an M&E dataset for AI-assisted analysis in Excel.
- Use Copilot prompts to explore monitoring and evaluation data.
- Generate and validate formulas using AI.
- Create charts, PivotTables, and basic visual summaries.
- Identify missing values, duplicates, outliers, and data quality concerns.
- Use AI to draft cautious analytical summaries.
- Apply human oversight before using AI-supported outputs in reports.
- Document how AI was used in the analysis process.
Why AI Matters in This Tutorial
In traditional Excel work, the user usually needs to know exactly which formula, chart, or PivotTable to create. With AI-assisted Excel, the workflow changes. The M&E professional can describe the task in plain language, and Copilot can suggest formulas, summaries, charts, or tables.
However, AI does not replace the evaluator. AI can support analysis, but the evaluator remains responsible for checking formulas, validating calculations, interpreting context, protecting sensitive data, and ensuring that every finding is supported by evidence.
The AI-Assisted M&E Workflow in Excel
- Prepare the dataset.
- Ask AI to understand the structure.
- Use AI to check data quality.
- Use AI to generate formulas.
- Validate formulas manually.
- Use AI to create PivotTables and charts.
- Use AI to summarize observed patterns.
- Compare AI outputs with the original data.
- Add contextual interpretation.
- Document how AI was used.
This sequence is important because AI can produce convincing outputs even when the dataset is messy, incomplete, or misunderstood. In M&E, a technically correct chart can still lead to the wrong conclusion if the indicator definition, sample size, reporting period, or denominator is incorrect.
1. Prepare Your M&E Dataset for AI Use
Copilot works better when the spreadsheet is organized clearly. Before using AI, prepare the data so the tool can understand it.
| Column Name | Example |
|---|---|
| District | Kukës |
| Reporting Month | March 2026 |
| Indicator | Number of households reached |
| Target | 1,000 |
| Actual | 820 |
| Implementing Partner | Local NGO A |
Good AI-Ready Data Practices
- Use clear column names.
- Use one row per observation.
- Avoid merged cells inside the dataset.
- Avoid using color as the only source of meaning.
- Use consistent names for districts, partners, indicators, and reporting periods.
- Remove or anonymize sensitive personal data where possible.
AI Prompt
Review this table as an M&E dataset. Identify unclear column names, possible formatting problems, inconsistent values, and any issues that could affect analysis. Do not change the data yet. Return a checklist of issues to review.
2. Convert the Dataset into an Excel Table
Before using Copilot, convert the data range into an Excel Table. This helps Excel and Copilot understand where the dataset begins and ends.
- Click inside the dataset.
- Press Ctrl + T.
- Confirm that the table has headers.
- Rename the table using a clear name, such as Indicator_Data, Survey_Results, or Partner_Reports.
AI Prompt
Use the table named Indicator_Data. Summarize what this dataset contains, including the indicators, locations, reporting periods, numeric columns, and categorical columns.
3. Use AI to Explore the Dataset
Start with descriptive exploration. Do not ask AI for conclusions too early. First, ask Copilot to help you understand what is inside the file.
Useful Copilot Prompts
- List all indicators in this dataset and count the number of records for each indicator.
- Identify the reporting months covered in this dataset.
- Show the number of records by district.
- Which columns appear to be numeric, categorical, and date-based?
- Summarize the structure of this dataset for an M&E officer reviewing it for the first time.
This step helps the learner check whether AI has correctly understood the dataset before moving into formulas, charts, or findings.
4. Use AI to Check Data Quality
Data quality is one of the most important areas where AI can support M&E work. Before analyzing performance, ask Copilot to inspect the dataset for possible problems.
AI Prompts for Data Quality Review
- Check this table for missing values by column. Return a summary table showing the number and percentage of missing values.
- Identify duplicate records based on District, Indicator, and Reporting Month.
- Find possible outliers in the Actual column and explain why they may need review.
- Check whether any Actual values are higher than the Target by more than 200%.
- Identify districts or indicators with missing target values.
- Create a data quality review table with the columns: Issue, Location, Indicator, Explanation, and Recommended Follow-up.
Human Validation
Do not automatically accept AI-flagged issues. Check the original source, review the partner submission, ask the field team if needed, and document any correction.
Good Practice
Create a separate sheet called Data_Quality_Log to record the issue found, the AI suggestion, the human review, the final decision, and the date corrected.
5. Use AI to Generate Formulas
One of the most useful features of Copilot in Excel is formula generation. M&E professionals can describe the calculation they need, and Copilot can suggest or create a calculated column.
Example 1: Achievement Rate
AI Prompt
Add a new column called Achievement Rate. If Target is blank or zero, return “No target available.” Otherwise calculate Actual divided by Target and format the result as a percentage.
Achievement rate helps answer: How much of the planned target has been reached?
Example 2: Performance Status
AI Prompt
Add a new column called Performance Status. Label results as Achieved if Achievement Rate is 100% or above, On Track if 80% to 99%, Behind Target if below 80%, and No Target if no target is available.
Example 3: Variance from Target
AI Prompt
Add a new column called Variance from Target. Calculate it as Actual minus Target.
Important
Always check AI-generated formulas manually. Test a few rows, review blank values, check zero targets, and confirm that the calculation matches the indicator definition.
6. Use AI to Create PivotTables
PivotTables are useful for summarizing indicators by district, month, partner, beneficiary group, or performance status.
AI Prompts for PivotTables
- Create a PivotTable showing total Actual and total Target by District.
- Create a PivotTable showing average Achievement Rate by Indicator and District.
- Create a PivotTable showing performance status by implementing partner.
- Create a PivotTable showing monthly progress for each indicator.
- Create a PivotTable that compares results by beneficiary group and district.
M&E Warning
Be careful with average percentages. Averaging district-level achievement rates can be misleading when districts have very different target sizes. In many cases, the better calculation is total Actual divided by total Target.
Better AI Prompt
Create a PivotTable showing overall Achievement Rate by District. Calculate Achievement Rate as total Actual divided by total Target, not as the average of row-level percentages.
7. Use AI to Generate Charts
Charts help translate monitoring data into evidence that decision-makers can understand. Copilot can support chart generation, but the evaluator must choose the right chart type.
| M&E Question | Useful Chart Type |
|---|---|
| Which districts are behind target? | Bar chart |
| Is performance improving over time? | Line chart |
| How do actual results compare with targets? | Clustered column chart |
| How are beneficiaries distributed by group? | Stacked bar chart |
| Are there unusual values? | Scatter plot or outlier table |
AI Prompts for Charts
- Create a bar chart showing Achievement Rate by District, sorted from lowest to highest.
- Create a line chart showing monthly Actual values for the indicator “Households reached.”
- Create a clustered column chart comparing Target and Actual by District.
- Create a chart showing the number of beneficiaries reached by sex and age group.
- Create a dashboard-style summary showing total Target, total Actual, overall Achievement Rate, and districts below 80%.
Chart Validation Checklist
- Check that the chart uses the correct data range.
- Confirm that the correct indicator is selected.
- Check whether the reporting period is correct.
- Make sure the chart title is clear.
- Avoid charts with too many categories.
- Check whether the denominator is correct.
- Add a note if the data is incomplete or provisional.
8. Use AI to Summarize Findings Carefully
Copilot can help draft summaries, but M&E professionals should distinguish between observations, findings, conclusions, and recommendations.
Weak Prompt
Write the findings from this data.
Better Prompt
Based only on the data in this workbook, summarize observed performance patterns. Do not speculate about causes. Separate observations, data quality issues, and questions for follow-up.
This type of prompt helps prevent AI from inventing explanations that are not supported by the data.
9. Use AI to Generate Follow-Up Questions
A strong M&E professional does not stop at the chart. The next step is inquiry. AI can help generate follow-up questions for field teams, partners, or program managers.
AI Prompt
Based on the districts below target, generate follow-up questions for the field team. Separate questions about implementation, data quality, access constraints, and partner reporting.
- Were activities delayed in the districts below target?
- Were targets realistic for the reporting period?
- Were any data collection tools submitted late?
- Were there access, security, weather, or staffing constraints?
- Were actual values verified against source documents?
- Did the definition of the indicator change during the reporting period?
10. Use AI to Support Triangulation
Excel data often shows what happened, but not why it happened. In M&E, quantitative data should often be triangulated with qualitative evidence, field notes, interviews, observation reports, or partner explanations.
AI Prompt
Create a table that lists each underperforming indicator, the quantitative pattern observed, possible qualitative evidence needed, and follow-up questions for triangulation.
| Indicator | Quantitative Pattern | Evidence Needed | Follow-Up Question |
|---|---|---|---|
| Households reached | Below 80% in 4 districts | Field visit notes, partner reports | Were activities delayed or underreported? |
| Training participants | Target exceeded in 2 districts | Attendance sheets, training reports | Were participants counted once or multiple times? |
11. Use AI Responsibly with Sensitive M&E Data
M&E datasets may include sensitive information about beneficiaries, households, children, health status, disability, income, migration status, protection risks, gender-based violence, or exact location.
Before Using AI, Ask:
- Does the dataset include names, phone numbers, ID numbers, addresses, or GPS points?
- Does it include sensitive demographic or protection information?
- Does the consent form allow this kind of processing?
- Does the donor or organization allow AI-assisted analysis?
- Is the file stored in an approved organizational environment?
- Are permissions limited to the right staff?
- Is the dataset anonymized or aggregated where possible?
Responsible AI Reminder
Do not use AI tools with sensitive beneficiary-level data unless organizational policy, consent conditions, donor rules, and data protection requirements allow it.
12. Validate AI Outputs Before Reporting
AI-assisted analysis should never move directly into a report without validation.
Validation Checklist
- Did Copilot understand the dataset correctly?
- Are formulas correct?
- Are blank and zero values handled correctly?
- Are PivotTables aggregating data appropriately?
- Are charts linked to the correct data?
- Are filters applied correctly?
- Are denominators correct?
- Are percentages calculated correctly?
- Are outliers reviewed?
- Are missing values disclosed?
- Are conclusions supported by the data?
- Are limitations clearly stated?
AI Prompt for Validation
Review the formulas, PivotTables, and charts in this workbook. Identify any possible calculation, aggregation, or interpretation risks that an M&E analyst should manually verify.
Practical Exercise for Learners
You are an M&E officer reviewing a project monitoring dataset from 12 districts. The dataset includes monthly reporting data from implementing partners.
Task 1: Ask AI to Understand the Dataset
Act as an M&E data analyst. Review the table named Indicator_Data. Summarize the dataset structure, including indicators, districts, reporting periods, numeric columns, and possible data quality concerns. Do not make conclusions yet.
Task 2: Ask AI to Check Data Quality
Check the table for missing values, duplicate records, inconsistent indicator names, missing targets, and possible outliers. Return a data quality review table with the columns: Issue, Location, Indicator, Explanation, and Recommended Follow-up.
Task 3: Ask AI to Create Formulas
Add an Achievement Rate column. If Target is blank or zero, return “No target available.” Otherwise calculate Actual divided by Target and format the result as a percentage.
Task 4: Ask AI to Create a Chart
Create a bar chart showing Achievement Rate by District, sorted from lowest to highest. Use a clear title and do not make conclusions in the chart title.
Task 5: Ask AI to Draft a Cautious Summary
Write a short internal M&E summary of the chart. Focus only on observed patterns. Do not speculate about causes. Include three follow-up questions for the field team.
Documenting AI Use in the Methodology
Learners should practice documenting AI use. This improves transparency and helps teams explain how AI supported the analysis.
Example AI-Use Disclosure
Microsoft Copilot in Excel was used to support exploratory data analysis, formula generation, PivotTable creation, chart generation, and preliminary summary drafting. All AI-assisted formulas, charts, summaries, and interpretations were reviewed by the evaluation team against the underlying dataset, indicator definitions, and source documentation before use.
Final Takeaway
Excel with Copilot gives M&E professionals a practical way to use AI inside a familiar spreadsheet environment. It can help with data exploration, formula generation, PivotTables, charts, outlier detection, summaries, and follow-up questions.
But the value of AI in M&E does not come from speed alone. It comes from using AI carefully within a responsible evaluation workflow. The evaluator remains responsible for preparing the data, checking AI-generated outputs, protecting sensitive information, interpreting results in context, triangulating evidence, and ensuring that every conclusion is supported by evidence.
Continue Learning with EvalCommunity Academy
Explore practical resources for evaluators, M&E officers, development professionals, and humanitarian practitioners who want to use AI responsibly in real evaluation workflows.
