Spreadsheets for M&E

Back to Code Studio

Excel and Google Sheets do not run on this page. The grey boxes are formulas for you to type into your own spreadsheet with households.csv and districts.csv open (both are illustrative data, invented for teaching: ten real district names with made-up households). This page shows no spreadsheet screenshots. Each code cell computes the same figure in R or Python on the same file; switch language with the tabs on the cell. The first run downloads the engine once (R about 7 MB, Python about 10 MB).

Module 1 of 8

One row per record, one column per variable

A spreadsheet built to be read by people (merged headings, subtotals between rows, colour as data) is hard to count from. A sheet built to be counted from follows four rules, and every formula in this course depends on them.

One row per record. One household, one visit or one beneficiary per row. Totals go on a separate sheet.
One column per variable, one header row. Short names with no spaces (monthly_pc_exp), with the full question and units in a codebook sheet.
One value per cell. "Purnia, Bihar" in one cell is two variables. "3 (approx)" is a number and a note; split them.
A unique ID in the first column that never changes, never repeats and is not a name.

Avoid merged cells. Microsoft's merge cells guide warns that when you merge cells, only the upper-left cell's contents are kept and "the contents of the other cells that you merge are deleted". A merged district heading over 24 rows also leaves 23 rows with no district, so every COUNTIFS on district misses them. Put the district in every row instead.

Layout used in every formula on this page

households.csv opened in Excel or Google Sheets (240 data rows, header in row 1)
A hh_id   B district   C area   D caste   E head_gender   F head_edu_years   G hh_size
H monthly_pc_exp   I land_acres   J has_toilet   K has_bank_account   L shg_member   M received_transfer
Data in rows 2 to 241.   districts.csv on a sheet named districts: A district  B state  ...  rows 2 to 11.

Download households.csv and open it. The cell checks the same three things you should check by eye: size, column names and duplicate IDs.

hh <- read.csv("households.csv")
dim(hh)                         # rows, columns
names(hh)
sum(duplicated(hh$hh_id))       # repeated IDs: should be 0
sapply(hh, class)               # what each column holds

The file has 240 rows and 13 columns, no repeated hh_id, numbers in the numeric columns and text in the rest. In Excel, select column A and look at the status bar: Count should read 241 (240 IDs plus the header).

Exercise. In your spreadsheet, type 1 over the hh_id in A3, then type =COUNTIF($A$2:$A$241, A2) in N2 and fill down. Sort by column N, largest first: the two rows with a count of 2 are your duplicates. Undo the change afterwards.

Module 2 of 8

Data validation: stop bad entries at the cell

If people type data into the sheet, validation is your form's constraint column. Use dropdowns for categories and ranges for numbers.

Excel

Select the cells, for example C2:C241.
On the Data tab, in the Data Tools group, select Data Validation (Microsoft guide).
Under Allow, choose List and type Rural,Urban, or choose Whole Number, Decimal, Date, Text Length or Custom (a formula).
On the Input Message tab, tick Show input message when cell is selected and write the instruction.
On the Error Alert tab, choose Stop to refuse the entry, or Warning to let the user decide.

Google Sheets

Select the cells, then Data > Data validation (Google guide).
Choose a criterion such as Dropdown or Dropdown (from a range).
Under Advanced options, choose what happens if the data is invalid: Show a warning, or reject the input.

Custom validation formulas (Excel and Google Sheets)

Custom rule for G2:G241 (household size), typed as one formula for the first cell:
=AND(ISNUMBER(G2), G2>=1, G2<=30, G2=INT(G2))

Custom rule for H2:H241 (monthly per capita expenditure in rupees):
=AND(ISNUMBER(H2), H2>0)

Custom rule for A2:A241 (hh_id must not repeat):
=COUNTIF($A$2:$A$241, A2)=1

Build the dropdown lists and number ranges from the data you expect. The cell lists every category and the range of each numeric column in households.csv.

hh <- read.csv("households.csv")
# The values each dropdown (List) validation should allow
for (v in c("district", "area", "caste", "head_gender", "has_toilet")) {
  cat(v, ":", paste(sort(unique(hh[[v]])), collapse = ", "), "\n")
}
# The ranges for whole-number and decimal rules
sapply(hh[, c("head_edu_years", "hh_size", "monthly_pc_exp", "land_acres")], range)

The file holds 10 districts, two areas (Rural, Urban), four caste groups (General, OBC, SC, ST), and Yes or No for toilets. Household size runs from 1 to 8, years of schooling of the head from 0 to 17, and monthly per capita expenditure from Rs 700 to Rs 20,690.

Exercise. Add the custom rule for household size to G2:G241, then try typing 0, 4.5 and 31 into G2. Each should be refused. Ranges come from what is plausible, wider than what this sample happens to contain: a rule of 1 to 8 would refuse the next household of nine.

Validation checks typing, and nothing else. Data pasted over a validated cell, or already in the sheet before the rule was added, is not rechecked automatically. After a paste, check the column again, for example with the COUNTIF in module 1 or the R and Python cells here.

Module 3 of 8

XLOOKUP, INDEX/MATCH and VLOOKUP's pitfalls

A lookup copies a value from another table by matching a key: here, each household's state from districts.csv by district name.

Lookup formulas

In N2, then fill down to N241 (state for each household):

Excel 2021, 2024, Microsoft 365, and Google Sheets
=XLOOKUP(B2, districts!$A$2:$A$11, districts!$B$2:$B$11, "not found")

Any version of Excel, and Google Sheets
=INDEX(districts!$B$2:$B$11, MATCH(B2, districts!$A$2:$A$11, 0))

VLOOKUP: the last argument must be FALSE for an exact match
=VLOOKUP(B2, districts!$A$2:$E$11, 2, FALSE)

Show a message instead of #N/A (Excel and Google Sheets)
=IFNA(INDEX(districts!$B$2:$B$11, MATCH(B2, districts!$A$2:$A$11, 0)), "check spelling")

Which function your software has

XLOOKUP: Microsoft's XLOOKUP page says "XLOOKUP is not available in Excel 2016 and Excel 2019". It works in Excel 2021, Excel 2024 and Microsoft 365. Its exact-match mode is the default, and the fourth argument sets what to show when nothing is found. Google Sheets has XLOOKUP with the same argument order (it calls the fourth one missing_value).
INDEX with MATCH works in every version of Excel and in Google Sheets. The 0 in MATCH asks for an exact match.
VLOOKUP works everywhere, and has three traps.

VLOOKUP's three traps

The default is an approximate match. Microsoft's VLOOKUP page says that if you leave out the last argument, VLOOKUP "assumes the first column in the table is sorted" and "will then search for the closest value". On an unsorted district list that can return another district's state with no error. Always write FALSE at the end.
The key must be the first column of the range, so VLOOKUP cannot look left.
The column is a number. 2 means "the second column of the range". Insert a column into the districts sheet and every VLOOKUP silently returns the wrong column. XLOOKUP and INDEX/MATCH point at the column itself.

The cell does the exact lookup, then misspells one district as a merged MIS export might, and counts what fails to match.

hh <- read.csv("households.csv")
d  <- read.csv("districts.csv")
# XLOOKUP / INDEX-MATCH / VLOOKUP(..., FALSE): bring each district's state into the household sheet
hh$state <- d$state[match(hh$district, d$district)]
table(hh$state, useNA = "ifany")

# One district typed differently, as happens in a merged MIS export
hh$district[hh$hh_id == 100] <- "Purnea"
hh$state <- d$state[match(hh$district, d$district)]
sum(is.na(hh$state))                    # rows where an exact lookup finds nothing (#N/A)
hh[is.na(hh$state), c("hh_id", "district")]

Every household finds its state: 72 in Bihar, 48 in Kerala, 72 in Madhya Pradesh and 48 in Rajasthan. After household 100's district is changed to Purnea, exactly one row has no state. In the spreadsheet that row shows #N/A, or your "not found" text. That is the behaviour you want: a lookup that fails visibly.

Exercise. After you fill the lookup down, count the failures with =COUNTIF(N2:N241, "not found") (or =SUMPRODUCT(--ISNA(N2:N241)) if you did not use the fourth argument). Make that count part of every update: it should be 0 before any table is built on the column.

Module 4 of 8

Indicator tables with COUNTIFS, SUMIFS and AVERAGEIFS

An indicator table has one row per district (or block, or caste group) and one column per indicator. The *IFS functions count, add or average the rows that meet every condition you give them.

Indicator formulas (Excel and Google Sheets)

District names typed in O2:O11 of an indicator sheet, then fill each formula down:

Households                 =COUNTIFS($B$2:$B$241, $O2)
% with a toilet            =COUNTIFS($B$2:$B$241, $O2, $J$2:$J$241, "Yes") / COUNTIFS($B$2:$B$241, $O2)
Mean per capita spending   =AVERAGEIFS($H$2:$H$241, $B$2:$B$241, $O2)
Persons covered            =SUMIFS($G$2:$G$241, $B$2:$B$241, $O2)

Two conditions: rural SC households in the district
=COUNTIFS($B$2:$B$241, $O2, $C$2:$C$241, "Rural", $D$2:$D$241, "SC")

Argument order differs. Microsoft's SUMIFS page points out that the range to add comes first in SUMIFS and third in SUMIF. AVERAGEIFS follows SUMIFS. Use the *IFS forms everywhere, even with one condition, so the order is always the same.

The cell computes the same four columns for all ten districts, so you can check each cell of your table.

hh <- read.csv("households.csv")
# One row per district: COUNTIFS, AVERAGEIFS and SUMIFS
tab <- do.call(rbind, lapply(sort(unique(hh$district)), function(dn) {
  g <- hh[hh$district == dn, ]
  data.frame(district    = dn,
             households  = nrow(g),                                   # COUNTIFS
             pct_toilet  = round(100 * mean(g$has_toilet == "Yes"), 1),# COUNTIFS / COUNTIFS
             mean_pc_exp = round(mean(g$monthly_pc_exp)),             # AVERAGEIFS
             persons     = sum(g$hh_size))                            # SUMIFS
}))
tab

Each district has 24 households. The share with a toilet runs from 50.0% in Rewa to 83.3% in Indore, Kozhikode and Patna, and mean monthly per capita expenditure from Rs 2,395 in Purnia to Rs 6,758 in Kozhikode. If your spreadsheet shows 0.958 where the cell shows 95.8, format the column as a percentage.

Exercise. Add a column for the share of households that received a transfer (column M), then change the condition in the R or Python cell to match and compare. In R, add pct_transfer = round(100 * mean(g$received_transfer == "Yes"), 1), inside data.frame(.

Module 5 of 8

Pivot tables

A pivot table builds a cross-tabulation without formulas. It is the quickest way to explore; for a table you will update every month, the formulas in module 4 are easier to audit.

Click any cell in the data. In Excel select Insert > PivotTable (Microsoft guide); in Google Sheets, Insert > Pivot table (Google guide).
Put it on a new sheet.
Pivot 1: drag district to Rows, area to Columns and hh_id to Values. Change the value from Sum to Count: a sum of IDs is meaningless.
Pivot 2: caste to Rows, area to Columns, monthly_pc_exp to Values, summarised by Average.

hh <- read.csv("households.csv")
# Pivot 1: rows district, columns area, values count of hh_id
addmargins(table(hh$district, hh$area))
# Pivot 2: rows caste, columns area, values average of monthly_pc_exp
round(tapply(hh$monthly_pc_exp, list(hh$caste, hh$area), mean))

Pivot 1 has 164 rural and 76 urban households, 240 in all; Kozhikode (21) and Indore (16) have the most urban households and Barmer (2) the fewest. In Pivot 2, urban means are above rural means in every caste group. Your pivot's grand totals and cells should match these exactly.

A pivot does not refresh itself in Excel. After you add or change rows, refresh it (right-click the pivot, Refresh), and check that its source range still covers the new rows. Formatting the data as a table first (Insert > Table) makes the range grow with the data.

Exercise. In Pivot 2, swap Average for Count. The smallest cell is the number of urban ST households (5 in this file). A mean over very few households is not worth reporting; decide on a minimum cell size (many teams use 25 or 30) and grey out cells below it.

Module 6 of 8

Conditional formatting against targets

A red-amber-green column next to an indicator tells a reader which districts are behind without reading every number. Set the rule against a target cell, so the colours follow the target when it changes.

Excel: select the indicator cells, then Home tab, Styles group, Conditional Formatting. For a rule from a formula, choose New Rule and the option that uses a formula (Microsoft guide).
Google Sheets: Format > Conditional formatting, then under "Format cells if" choose Custom formula is (Google guide).
Add one rule per colour, in the order red, amber, green.

Custom formula rules

Conditional formatting, custom formula rules on Q2:Q11 (% with a bank account, as a fraction):
green   =$Q2>=0.9
amber   =AND($Q2>=0.8, $Q2<0.9)
red     =$Q2<0.8

Put the target in a cell (say T1 = 0.9) and refer to it, so a new target changes every rule:
=$Q2>=$T$1

hh <- read.csv("households.csv")
target <- 90                      # programme target: % of households with a bank account
pct <- round(100 * tapply(hh$has_bank_account == "Yes", hh$district, mean), 1)
status <- ifelse(pct >= target, "green",
          ifelse(pct >= target - 10, "amber", "red"))
out <- data.frame(pct_bank = pct, status = status)
out[order(out$pct_bank), ]
table(status)

Against a 90% target for bank accounts, 4 districts are green, 4 amber and 2 red; the lowest is Purnia at 75.0%. Your coloured column should show the same split.

Colour is not enough on its own. Some readers cannot tell red from green, and a printout may be black and white. Keep the number visible, and put the status word (or a symbol) in its own column as the cell does.

Exercise. Change target to 85 in the cell and the target cell T1 to 0.85 in your sheet (and the amber limits to 75%). The two red districts turn amber and Gaya turns green; check that your sheet agrees.

Module 7 of 8

Weighted averages with SUMPRODUCT, and dates

The average of monthly per capita expenditure over households answers "what does a typical household spend per person?". Weighting by household size answers "what does a typical person live on?". Large households are often poorer, so the two differ, and a report must say which it uses.

Weighted averages (Excel and Google Sheets)

Mean per capita spending per household, and per person (weighted by household size):
=AVERAGE(H2:H241)
=SUMPRODUCT(G2:G241, H2:H241) / SUM(G2:G241)

Per person, SC households only:
=SUMPRODUCT(($D$2:$D$241="SC") * $G$2:$G$241 * $H$2:$H$241) / SUMIFS($G$2:$G$241, $D$2:$D$241, "SC")

NFHS household weight in a new column, if your file holds the DHS variable hv005 in column X:
=X2/1000000

SUMPRODUCT multiplies the two ranges row by row and adds the results (Microsoft guide). Inside it, a condition such as ($D$2:$D$241="SC") becomes 1 or 0, which keeps only the rows you want.

hh <- read.csv("households.csv")
# AVERAGE(H2:H241) and SUMPRODUCT(G2:G241, H2:H241) / SUM(G2:G241)
c(per_household = mean(hh$monthly_pc_exp),
  per_person    = sum(hh$hh_size * hh$monthly_pc_exp) / sum(hh$hh_size))
# The same two numbers by caste
round(sapply(split(hh, hh$caste), function(g)
  c(per_household = mean(g$monthly_pc_exp),
    per_person    = sum(g$hh_size * g$monthly_pc_exp) / sum(g$hh_size))), 1)

The per-household mean is Rs 3,400.9 and the per-person mean is lower, Rs 3,335.4, because larger households in this file spend a little less per head. By caste the gap is largest for ST households (Rs 2,694.0 against Rs 2,369.9), and for SC households it runs the other way (Rs 2,355.8 against Rs 2,377.2). Which mean you report changes the comparison between groups.

Survey weights work the same way. For NFHS household data, the DHS weight hv005 (or v005 for women) is divided by 1,000,000; put the weight in the first SUMPRODUCT range and in the SUM. A weighted mean in a spreadsheet gives the right point estimate; its standard error needs the survey design (strata and clusters), which is a job for R, Stata or Python.

Dates

Excel and Google Sheets store a date as a number, which is what lets you subtract one from another. A date typed in a format the sheet does not recognise stays text, and then sorts and subtracts wrongly. Type dates as yyyy-mm-dd, check them with ISNUMBER, and say in the codebook which date a column holds (interview, enrolment, payment).

Date formulas

A real date, built from parts:        =DATE(2026, 10, 6)
Days between two interview dates:      =B2-A2
Show a date as text in ISO form:       =TEXT(A2, "yyyy-mm-dd")
Year and month of an interview:        =YEAR(A2)    =MONTH(A2)
Check that a cell holds a date (Excel stores dates as numbers):   =ISNUMBER(A2)

Be careful with DATEDIF. Excel still has DATEDIF for whole years or months between dates, but its help page warns that one of its options "may result in a negative number, a zero, or an inaccurate result". For days, subtract; for age in years, check a few results by hand.

Exercise. Change the weighting in the cell from hh_size to land_acres in both places and run again. You get mean spending per acre owned, a weighting that makes no sense. A weight should be the number of units each row stands for.

Module 8 of 8

Version habits, and when to move to R or Python

Habits that make a spreadsheet auditable

Keep the raw data untouched. Put the export on a sheet named raw, protect it, and do all work on other sheets that refer to it.
Name files by date. hh_baseline_2026-10-06.xlsx sorts in order; final_v2_revised.xlsx does not.
Keep a change log sheet: date, who, what changed, why. One line per change.
Use the built-in history. In Google Sheets, click Last edit at the top right to open version history, name a version before a big change, and restore an earlier one if needed (Google guide). Excel files saved to OneDrive or SharePoint also keep earlier versions.
Put inputs in their own cells. A target, an exchange rate or a poverty line typed into one labelled cell can be changed once and checked by anyone.

Signs it is time to move to R or Python

You repeat the same steps every month. A script runs them again in seconds, identically, and records what it did.
You need standard errors or survey weights with design. NFHS, PLFS and most evaluation samples are clustered; spreadsheets cannot compute the right standard errors.
You join several files. A roster, a household file and a village file joined by lookups in a spreadsheet are hard to check; a merge in code reports what did not match.
The file is large. Microsoft's Excel limits page gives 1,048,576 rows per worksheet; Google's Drive file limits give up to 20 million cells for a spreadsheet. A slow, heavy file usually reaches its practical limit long before either.
The data is personal. A spreadsheet of names and phone numbers emailed between staff is copied everywhere; under the DPDP Act 2023 your organisation must protect it from 13 May 2027, and should start now. Keep identifiers in one controlled file and share the analysis without them.

Every cell on this page did in a few lines what took a column of formulas. The courses below teach that way of working from the start.

Where next

→