DEV Community

Black
Black

Posted on

Turning a weekly meal plan into an aisle-sorted grocery list with plain spreadsheet formulas

#excel #spreadsheets #productivity #tutorial

The interesting part of a meal planner spreadsheet is not the weekly grid. It is turning "Monday dinner: fajitas, Tuesday dinner: bolognese..." into one shopping list where the onions from four recipes are added together, scaled to how many people you feed, minus the onions you already have, and printed in the order you walk the store. Here is how I did that with plain formulas (Excel + Google Sheets, no macros, no QUERY, no FILTER).

The data model

Four tables:

  • Ingredients: name, store section, unit, price per unit, amount on hand.
  • Recipes: name, meal type, servings, minutes, method.
  • Recipe Ingredients: one row per ingredient per recipe: recipe, ingredient, quantity (for the recipe's own servings).
  • Week Plan: a 7 x 4 grid of dropdowns (breakfast, lunch, dinner, snack) whose list source is the recipe names.

The long "one row per ingredient" table is the key. It is boring to type, but every total after it is a single SUMIFS.

Step 1: a week factor per recipe line

How many times does this line's recipe appear in the plan, and how big is the household compared to the recipe?

Week factor:  =COUNTIF('Week Plan'!$C$6:$F$12, B6) * Setup!$C$5 / INDEX(Recipes!$D:$D, MATCH(B6, Recipes!$B:$B, 0))
Week qty:     =D6 * F6
Enter fullscreen mode Exit fullscreen mode

Chicken curry serves 4, it is planned once, we feed 4: factor 1. Overnight oats serve 2, planned twice, we feed 4: factor 4. A recipe that is not on the plan gets factor 0 and drops out of everything that follows.

Step 2: total per ingredient

The grocery table has one row per ingredient (it just mirrors the Ingredients tab):

Needed:  =ROUND(SUMIFS('Recipe Ingredients'!$G:$G, 'Recipe Ingredients'!$C:$C, B6), 2)
To buy:  =MAX(0, D6 - E6)          (E = on hand)
Cost:    =F6 * price
Enter fullscreen mode Exit fullscreen mode

Garlic shows up in six recipes. One SUMIFS adds every clove from all of them.

Step 3: only the things to buy, in store order

The full table has every ingredient, including the 20 you do not need this week. The shopping list should skip those and group the rest by section, in the order you walk the store (set on a Setup tab: Produce, Meat & Fish, Dairy & Eggs, Bakery...).

A hidden sort key per row does both jobs:

Key:  =IF(N(F6)>0, IFERROR(MATCH(C6, Sections, 0), 99) * 1000 + ROW(), "")
Enter fullscreen mode Exit fullscreen mode
  • Nothing to buy: blank, so it is ignored.
  • Otherwise: section position x 1000, plus the row number to keep keys unique (and keep the master list order inside each section).

Then row k of the shopping list is:

Item:  =IFERROR(INDEX($B$6:$B$305, MATCH(SMALL($O$6:$O$305, k), $O$6:$O$305, 0)), "")
Enter fullscreen mode Exit fullscreen mode

SMALL skips the blanks and walks the keys in ascending order, so the list comes out Produce first, then Meat, then Dairy, with no gaps. To print a section label only where the section changes, compare the section of key k with the section of key k-1 and leave the cell empty if they match.

One more touch: nobody buys 22.67 eggs. For count units the quantity rounds up to a whole number, for the rest it rounds up to one decimal:

=IF(ISNUMBER(MATCH(unit, {"each","slice","can","clove","head","bunch"}, 0)), ROUNDUP(q, 0), ROUNDUP(q, 1))
Enter fullscreen mode Exit fullscreen mode

Step 4: a recipe card that scales

Pick a recipe from a dropdown and list its ingredients, scaled. The j-th ingredient of the chosen recipe:

{=IFERROR(INDEX('Recipe Ingredients'!$C$6:$C$1505, SMALL(IF('Recipe Ingredients'!$B$6:$B$1505=$B$5, ROW(...)-5), j)), "")}
Enter fullscreen mode Exit fullscreen mode

That is the classic "k-th match" array formula. Quantity is the same lookup times people / servings. Change Setup from 4 to 6 people and the card, the grocery list and the weekly cost all move together.

Cost per meal for free

Each recipe line also gets qty x price per unit. Cost per serving is SUMIFS(line cost, recipe, name) / servings, and the week plan multiplies that by household size per slot. The sample week of 24 meals for 4 people comes to about $205, or $7.32 per person per day, which makes it easy to see which dinners are the expensive ones.

The workbook is generated by a Python script (xlsxwriter) and checked by recalculating it headless in LibreOffice and rendering every tab to an image, so a formula change gets verified in a few seconds.

The finished file with 22 recipes is here if you want it: Meal Planner + Grocery List ($8, also on Gumroad). All the formulas above are yours to copy.

Top comments (0)