Find the Leading District and Publish a Verified Excel Summary

For this Excel data analysis, South has the higher completion rate: 72.5%, compared with North’s 60.0% in the fictional January–February example. Use a PivotTable to sum each district’s received and completed requests, then divide completed by received. Averaging monthly percentages gives the wrong combined rate when the months have different request counts.

Enter the received and completed totals to check a rate, or load either sample district.

Define the Cohort Before Calculating Its Rate

Enter this fictional example into cells A1:D5 on a worksheet named Source. Keep intake_month as consistent month labels; formatting A2:A5 as Text before entering them avoids automatic date conversion.

Each row represents one district’s intake cohort for one month. completed counts requests from that cohort completed by March 31, 2026—not all work completed during the intake month.

intake_month	district	received	completed
2026-01	North	100	80
2026-02	North	200	100
2026-01	South	50	25
2026-02	South	150	120

Completion rate = completed requests ÷ received requests.

If completed included requests received outside these cohorts, the numerator and denominator would describe different populations. The calculation would no longer answer which district completed the largest share of its January–February intake.

Prepare a Source Table Without Losing Unknowns

Use one header row with unique, nonblank column names. Counts should be numeric, district names should be text, and district spellings should be consistent without stray spaces. Keep subtotal rows and merged report headings outside the observations.

Investigate repeated district–month pairs rather than automatically deleting them: they may be duplicates, or they may represent separate records that need a more specific key.

Microsoft recommends clean tabular data with consistent column types for PivotTables, and unique single-row headers for Analyze Data. An Excel table is a suitable source for both. In Windows Excel, select the sample range and press Ctrl+T, confirming that it has headers.

Keep the original data unchanged and analyze a working copy. Missing counts must remain distinguishable from confirmed zeros. Resolve unknown counts before calculating a district rate, or withhold that rate and explain the missing coverage. A sum of available cells is not necessarily a complete total.

For ambiguous blanks, establish a rule using the source documentation; see empty fields, zero, and missing values.

Use a PivotTable, Not the Analysis ToolPak, for Grouped Totals

For this task, use a PivotTable to group districts and sum counts, then a formula to calculate the rate. Two similarly named Excel features serve different purposes:

Feature	Purpose	Needed Here?
PivotTable	Group rows and summarize counts	Yes
Analyze Data	Suggest summaries and explore questions	Optional
Data Analysis ToolPak	Statistical and engineering analyses	No

Analyze Data is available to Microsoft 365 subscribers in supported languages. Select a cell in the dataset, then choose Home > Analyze Data to explore suggested summaries or ask a question. Availability of natural-language queries can vary by region. Treat its output as a candidate result to verify against the source counts.

Data Analysis, supplied by the Analysis ToolPak, is not needed for this grouped total. If you need its statistical tools for a different task, enable it in Windows desktop Excel through File > Options > Add-Ins. Under Manage, choose Excel Add-ins > Go, check Analysis ToolPak, and select OK.

On Mac, use Tools > Excel Add-ins, check Analysis ToolPak, select OK, and restart Excel. The command is on the Data tab. Microsoft documents these platform-specific steps in its ToolPak setup guidance.

Sum the Counts Before Dividing

Select a cell in the source table and choose Insert > PivotTable, placing the result on a new worksheet. Arrange the fields as follows:

• Rows: district
• Values: received, summarized by Sum
• Values: completed, summarized by Sum

Check the value-field settings rather than assuming Excel chose the intended operation. If a count column appears as Count of received instead of Sum of received, inspect its data types. Microsoft notes that values interpreted as text default to Count.

Create a separate worksheet named Summary. In A1:D1, enter district, received, completed, and completion_rate. Copy the two district rows and their totals from the PivotTable into A2:C3 as values, excluding its Grand Total row.

In D2, enter =C2/B2, fill the formula into D3, and format D2:D3 as percentages with one decimal place.

district	received	completed	completion_rate
North	300	180	60.0%
South	200	145	72.5%

Monthly Percentages Are Not Equally Weighted

North’s monthly rates are 80% and 50%; their simple average is 65%. But North’s combined rate is 180 ÷ 300 = 60%, because the two months contain different numbers of requests.

The combined rate must use the combined numerator and denominator. An unweighted average gives a small intake month the same influence as a larger one.

If your real denominator is zero, use a documented “not applicable” or missing-value rule rather than publishing a division error or silently assigning 0%.

Verify Totals and Limit the Interpretation

Check North’s completed total independently. On the Source worksheet, enter =SUMIFS(D2:D5,B2:B5,"North") in an unused cell outside the source table, such as F2.

It should return 180. SUMIFS takes the range to sum first, followed by the criteria range and criterion; its range arguments must have matching dimensions. See Microsoft’s SUMIFS syntax.

Then reconcile the whole dataset:

Measure	Total
Received	500
Completed	325
Overall completion rate	65%

If you change source rows, refresh the PivotTable. When using a fixed range, also check that it includes every intended row. An Excel table includes added rows when the PivotTable is refreshed, as described in Microsoft’s PivotTable guidance linked above.

Because Summary contains copied totals, refreshing the PivotTable does not update those copied values. Replace them after refreshing, then recheck the rates.

The sample supports “South had a higher completion rate by the cutoff.” It does not establish why, or show that one district worked more efficiently: request complexity and intake timing are not controlled for here.

Publish a Separate File With Explicit Rate Values

Build a release file containing only reviewed public data. Copy the district summary as values, with one header row and no embedded subtotal rows. Keep the working workbook separately so you retain formulas and checks.

Accompany the table with its source and permission to publish, the intake period and completion cutoff, and definitions of received, completed, and the rate. Document missing-value rules, the release date, and the rate’s representation—for example, decimal fractions such as 0.6 meaning 60%.

Check the Exported CSV, Not Just the Workbook

CSV is suitable for this single plain table. Excel’s CSV export saves only the active worksheet. It saves text and values as displayed in cells, without retaining workbook formatting, so percentage formatting can affect the exported text.

For decimal rates, change the release column to General and confirm that it shows 0.6 and 0.725 before exporting. Inspect the CSV in a text editor to verify those values. Microsoft describes these limitations in its CSV export guidance.

Never upload sensitive information. Hidden rows, columns, or worksheets are not a privacy boundary; create a separate release file without them.

TablePage accepts CSV, TSV, XLSX, and XLS files and turns an upload into a public dataset page with a shareable link and a filterable table. After uploading the reviewed file, check the published headers, two district rows, totals, and rate representation against your release copy before sharing the link.