Weekly Meal Planner in Excel That Builds Your Shopping List Automatically (No Macros)

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: weekly-meal-planner-shopping-list-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Shraddha's week (made-up) has 21 meals from 7 recipes: Poha 4 times, Upma 3, Dal rice 4, Chapati bhaji 4, Paneer bhurji 2, Pithla bhakri 2, Masale bhat 2.

Onions are in four recipes:

The kitchen already has 0.5 kg, so to buy = 1.8 − 0.5 = 1.3 kg, costing 1.3 × ₹32 = ₹41.60. Besan needs 0.5 kg and 0.5 kg is in the kitchen, so it drops off the list. The final list has 11 items costing ₹921.

2. Build it step by step

Step 1. Recipes sheet

1. In A4:E4 type Recipe, Ingredient, Qty per meal, Times this week, Qty needed.
2. One row per ingredient of each recipe from row 5 (Poha has three rows: poha, onions, peanuts).
3. In G5:G11 type the seven recipe names once: this is the list for the drop-downs.

Step 2. Week plan with drop-downs

1. On the Week plan sheet type Day, Breakfast, Lunch, Dinner in A4:D4 and Mon to Sun in A5:A11.
2. Select B5:D11 > Data > Data Validation > List, Source =Recipes!$G$5:$G$11 (named lists for drop-downs).
3. Pick a meal in every cell. G4 =COUNTA(B5:D11) shows 21.

Step 3. Times this week and quantity

1. Back on Recipes, in D5 type =COUNTIF('Week plan'!$B$5:$D$11,A5): Poha 4.
2. In E5 type =C5*D5: 0.3 × 4 = 1.2 kg poha.
3. Copy D5:E5 down to the last recipe row.

Step 4. Shopping list: needed

1. On the Shopping list sheet type the 12 ingredients in A5:A16 with unit, what is in the kitchen (D) and price (F).
2. In C5 type =SUMIF(Recipes!$B$5:$B$200,A5,Recipes!$E$5:$E$200): the total of that ingredient across all recipes (SUMIF).

Step 5. To buy and cost

1. In E5 type =MAX(0,C5-D5): never a minus number.
2. In G5 type =E5*F5.
3. Copy C5:G5 down to row 16. G17 =SUM(G5:G16): ₹921; G18 =COUNTIF(E5:E16,">0"): 11 items.

Step 6. Print list of only what to buy

1. In H5 type =IF(E5>0,COUNTIF(E$5:E5,">0"),"") and copy down: 1, 2, 3… for each item to buy.
2. In I5 type =IFERROR(INDEX($A$5:$A$16,MATCH(ROWS(I$5:I5),$H$5:$H$16,0)),"") and copy down. The list closes up with no gaps.
3. In Microsoft 365 a single =FILTER(A5:A16,E5:E16>0) does the same (FILTER).
4. A formula rule =$E5=0 greys out items you already have.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Ingredient spelled differently in two sheets. "Onion" on Recipes and "Onions" on the Shopping list gives 0 needed. Copy the names, do not retype them.
2. Recipe names in the plan typed by hand. COUNTIF needs exactly the same name; the drop-down guarantees it.
3. A negative to-buy amount. C5−D5 goes below 0 when the kitchen has more than enough. MAX(0, …) fixes it.
4. Recipe list range too short. A new recipe added below G11 is missing from the drop-down. Extend the Source range.
5. Mixing units. If a recipe uses grams and the list uses kg, the totals are wrong by 1,000. Use one unit per ingredient.

5. More real examples

Example 1: Tiffin planner. Only lunch for two children; the list covers the school week.

Example 2: Fasting days. Add recipes like sabudana khichdi; the list adds sabudana and peanuts on those days.

Example 3: Guests on Sunday. Add a recipe "Puran poli for 8" with bigger quantities, or a Servings column that multiplies Qty per meal.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make a meal planner in Excel?

A recipe table, a week grid with drop-downs picking from the recipe list, and formulas that count each recipe and add up the ingredients.

How does the shopping list update automatically?

COUNTIF counts how often each recipe is planned, SUMIF adds the quantities per ingredient, and MAX(0, need − in kitchen) gives what to buy.

Do I need macros?

No. Everything here is normal formulas, so the file is a plain .xlsx and works on the Excel mobile app.

How do I list only the items to buy?

In Microsoft 365 use FILTER. In any version, number the items to buy and pull them up with INDEX and MATCH, as in columns H and I.

Can I plan for more people?

Change Qty per meal on the Recipes sheet, or add a servings column and multiply.

7. Quick quiz

Ten questions on this page. Sign in to save your score.