Guides

Case Study: NYC Restaurant Inspections in Excel (Real NYC Open Data)

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 7 October 2026 · Last updated: 7 October 2026

A restaurant group is opening its next place in New York City and wants to pass its first health inspection. The operations manager asks: "How many restaurants score in the A range at their first inspection, and what do inspectors cite most?" We downloaded every initial cycle inspection from April to June 2026. Only 56.9% of 3,920 inspections scored 0 to 13 points, the A range, and one maintenance violation (code 10F) appeared in 55.5% of inspections.

Download the DOHMH Restaurant Inspection Results from NYC Open Data, filter to one inspection type and period, remove duplicates and rows with a missing borough, then build one row per inspection with a key (CAMIS and date), XLOOKUP for borough, cuisine and score, and COUNTIFS for violations. Compare boroughs and cuisines with COUNTIFS and AVERAGEIFS, rank violation codes with COUNTIF, and chart the results.

Download the practice file (case-study-nyc-restaurant-inspections.xlsx)

Excel file, 1291 KB. It holds the trimmed real data and every formula used below. It has every downloaded violation row for the quarter (minus the removed rows, which are kept on their own sheet), the inspection table built with formulas, the analysis and the dashboard.

The business question

NYC inspectors give points for each violation. Fewer points is better. NYC Health's letter grading page says: 0 to 13 points is an A, 14 to 27 is a B, and 28 or more is a C. A new restaurant wants to know its odds and where to focus its training. So the questions are:

1. What share of initial inspections score in the A range (0 to 13)?
2. Does it differ by borough or by type of cuisine?
3. Which violations come up most often, so staff can be trained for them?

The data: source and licence

Step 1: Clean the rows

The key thing to understand: one row is one violation, not one inspection. An inspection with five violations has five rows, each repeating the score. Counting rows would count violations.

1. Load the CSV with Data > From Text/CSV and click Load To > Table.
2. Run Data > Remove Duplicates with all columns ticked.
3. Filter Boro for 0 and delete those rows (the borough is missing).
4. Add a Key column that names each inspection: =A2&"-"&TEXT(E2,"yyyymmdd") joins CAMIS and the date.

After cleaning there are 3,920 inspections at 3,917 restaurants (three restaurants had two initial inspections in the quarter). 11 inspections had no violations at all.

Step 2: One row per inspection

Copy the Key, CAMIS and date columns to a new sheet, run Remove Duplicates, make it a table called Inspections, and fill the other columns with formulas:

Boro           =XLOOKUP(C2,Violations[Key],Violations[Boro])
Score          =XLOOKUP(C2,Violations[Key],Violations[Score])
Grade          =XLOOKUP(C2,Violations[Key],Violations[Grade])&""
Violations     =COUNTIFS(Violations[Key],C2,Violations[Violation code],"<>")
Critical       =COUNTIFS(Violations[Key],C2,Violations[Critical flag],"Critical")
A-range score  =IF(F2<=13,"Yes","No")

XLOOKUP returns the first matching row, which is fine because the score is the same on every row of an inspection. The &"" turns an empty grade into empty text instead of 0.

Step 3: Compare boroughs and cuisines

1. Insert a PivotTable from Inspections: Boro in Rows, Key in Values (Count), A-range score in Columns, then Show Values As > % of Row Total.
2. For a report that stays put, use formulas: =COUNTIFS(Inspections[Boro],A3,Inspections[A-range score],"Yes")/B3 and =AVERAGEIFS(Inspections[Score],Inspections[Boro],A3).
3. Do the same by Cuisine, keeping only cuisines with at least 100 inspections so that small groups do not mislead.

The median score is 13, exactly the top of the A range, so a typical restaurant is one violation away from missing it. Brooklyn had the highest A-range share (58.7%) and Queens the lowest (52.3%). The gap between boroughs is small next to the gap between cuisines:

Step 4: Rank the violations

Count each code with =COUNTIF(Violations[Violation code],H3) and divide by the number of inspections. No inspection had the same code twice, so this is also the share of inspections that got that violation.

Step 5: Build the dashboard

1. On the Dashboard sheet add six KPI cards that link to the Analysis sheet: inspections, A-range share, average score, closures, critical violations per inspection and the top violation's share.
2. Insert a column chart of A-range share by borough and one of inspections by score band.
3. Insert bar charts of the top 10 violation codes and of A-range share by cuisine.
4. Turn off gridlines, align the charts and set landscape, fit to one page.

The Dashboard sheet of the practice workbook (real data: NYC DOHMH via NYC Open Data).

Step 6: The recommendation

1. Fix the cheap, common items first. Code 10F (non-food surfaces and equipment not clean or not properly sealed or movable) was cited in 55.5% of inspections. A deep clean and an equipment check before opening costs little.
2. Make food temperature a daily checklist. Cold food held too warm (02G) appeared in 29.2% of inspections and hot food held too cool (02B) in 20.8%. Both are critical violations. Thermometers and a logged check twice per shift address both.
3. Book pest control before the first inspection. Conditions that attract pests (08A) appeared in 25.0% of inspections and evidence of mice (04L) in 15.0%.
4. Plan for a re-inspection anyway. Only 56.9% of initial inspections scored in the A range across the city, and 50 of 3,920 ended with the restaurant closed by DOHMH. Keep the team on the checklist until the grade card is posted.

What this data cannot tell you

• It covers restaurants that were active on the extract date. Places that closed after their inspection are not in it.
• Scores can change after a hearing; the dataset says scores are updated based on adjudication results. Re-download before you present.
• Three months is a short window, and a borough or cuisine figure can move from quarter to quarter.