A useful meal planner in Excel has three connected parts: a list of your recipes and their ingredients, a weekly plan where you pick meals from drop-down lists, and a grocery list that adds up the ingredients for everything you planned. Once it’s set up, planning a week means choosing meals, and the shopping list builds itself.
This guide shows how to set up each part, with tested formulas for the grocery list, weekly food cost and optional calorie totals. It covers newer and older versions of Excel, plus the changes you’ll need for Google Sheets.
What a meal planner template should include
| Sheet | What it holds | Key columns |
|---|---|---|
| Recipes | One row per recipe | Recipe, Meal type, Servings, Prep minutes, Calories per serving (optional) |
| Ingredients | One row per ingredient in each recipe | Recipe, Ingredient, Qty, Unit, Aisle, Cost per unit, Times planned, Qty needed |
| Plan | The week, with a drop-down in every meal slot | Day, Breakfast, Lunch, Dinner, Snack |
| Grocery list | Totals for everything planned this week | Ingredient, Qty, Unit, Have, To buy |
Keeping recipes and ingredients on separate sheets is what makes the shopping list automatic. Most printable meal planners skip the ingredient sheet, so you still write the list by hand.
Download the Excel meal planner template with the drop-downs, grocery list and cost formulas already connected. Add your own recipes and plan your first week tonight.
Weekly, monthly or printable: which meal planner to use
| Type | Works best for | Trade-off |
|---|---|---|
| Weekly meal planner | Most households; matches a weekly grocery shop | You plan every week |
| Monthly meal planner | People who repeat favorite meals | Ingredient totals for a month are less useful for fresh food |
| Printable menu planner | A plan on the fridge for the whole family | No automatic shopping list |
Build the weekly version first. A monthly plan is easiest as four finished weeks that you rotate, which the monthly section below explains, and you can print any week once it’s filled in.
How to build a meal planner in Excel
Step 1: List your recipes
On a sheet called Recipes, enter one recipe per row with its meal type, servings and prep time. Click any cell in the list, press Ctrl+T to turn it into a table, then go to Table Design and change the Table Name to tblRecipes.
Add two extra rows called Leftovers and Eating out. They let you fill a slot without adding any ingredients to the shopping list.
You don’t need every recipe on day one. Ten to fifteen meals your household already eats is enough to plan several weeks.
Step 2: List the ingredients for each recipe
On a sheet called Ingredients, enter one row for each ingredient in each recipe, then press Ctrl+T and name the table tblIngredients.
| Recipe | Ingredient | Qty | Unit | Aisle |
|---|---|---|---|---|
| Chicken stir-fry | Chicken breast | 1.5 | lb | Meat |
| Chicken stir-fry | Rice | 2 | cups | Pantry |
| Burrito bowls | Rice | 1.5 | cups | Pantry |
| Burrito bowls | Black beans | 2 | cans | Pantry |
| Lentil soup | Lentils | 1 | lb | Pantry |
The recipe names here must match the Recipes sheet exactly. Use the same ingredient name and unit every time an ingredient appears, so “Rice” in cups everywhere, not “rice” in one recipe and “white rice” in grams in another. The grocery list can only add up rows that match.
Step 3: Set up the weekly plan with drop-down lists
On a sheet called Plan, put the days in A4:A10 and the meals in B3:E3 (Breakfast, Lunch, Dinner, Snack). The cells B4:E10 are your meal slots.
Data validation can’t point at a table column directly, so give the recipe column a name first:
- Go to Formulas > Name Manager > New.
- Name it
RecipeListand set Refers to as=tblRecipes[Recipe]. - Select B4:E10, go to Data > Data Validation, choose List and enter
=RecipeListas the source.
Every slot now has a drop-down of your recipes. Because the name points at the table, new recipes appear in the drop-down as soon as you add them.
Step 4: Count how often each recipe is planned
Add two columns to the Ingredients table. Typing a heading in the first empty column next to the table extends it automatically.
Times planned:
=COUNTIF(Plan!$B$4:$E$10,[@Recipe])Qty needed:
=[@Qty]*[@[Times planned]][@Recipe] means “the Recipe value in this row”. Column names with spaces need the extra brackets, as in [@[Times planned]]. If chicken stir-fry appears twice in the plan, each of its ingredient rows shows 2 and doubles the quantity.
Step 5: Build the grocery list
The grocery list combines every row with a Qty needed above zero, adding up ingredients that appear in more than one recipe. There are two ways to build it.
Excel for Microsoft 365 and Excel 2021 or later
On a sheet called Grocery list, put this in A4:
=IFERROR(SORT(UNIQUE(FILTER(tblIngredients[Ingredient],tblIngredients[Qty needed]>0))),"Nothing planned yet")FILTER keeps the ingredients you need, UNIQUE removes duplicates and SORT puts them in A to Z order. The result spills down the column on its own.
In B4, total each ingredient:
=SUMIFS(tblIngredients[Qty needed],tblIngredients[Ingredient],A4#)In C4, pull in the unit:
=INDEX(tblIngredients[Unit],MATCH(A4#,tblIngredients[Ingredient],0))A4# refers to the whole spilled list, however long it is this week. If stir-fry is planned twice and burrito bowls once, rice comes out as 5.5 cups: 2 cups twice, plus 1.5 cups.
Any version of Excel
A PivotTable does the same job:
- Click inside
tblIngredientsand choose Insert > PivotTable > New Worksheet. - Drag Aisle, then Ingredient, then Unit into Rows.
- Drag Qty needed into Values. It should read “Sum of Qty needed”.
- Open the Ingredient filter arrow, choose Value Filters > Greater Than and enter 0.
The PivotTable groups the list by aisle, which suits a supermarket run. It doesn’t update by itself, so choose Data > Refresh All after changing the plan.
Step 6: Subtract what’s already in the pantry
Next to the formula version of the list, add Have in column D and To buy in column E. In E4, then copy down about 50 rows:
=IF(A4="","",MAX(0,B4-D4))If you need 5.5 cups of rice and have 2, To buy shows 3.5. MAX stops the result going negative when you have more than enough.
Fill in the Have column on shopping day, once the plan is final. The list re-sorts when the plan changes, and numbers typed beside it don’t move with it.
Step 7: Estimate the weekly food cost
Add a Cost per unit column to tblIngredients: the shelf price divided by the units in the pack. Chicken at $8.98 for 2 lb is $4.49 per lb. Then total the week:
=SUMPRODUCT(tblIngredients[Qty needed],tblIngredients[Cost per unit])SUMPRODUCT multiplies each row’s quantity by its price and adds the results. Unplanned recipes have a Qty needed of 0, so they add nothing. Put this next to a budget cell and the difference tells you whether to swap an expensive dinner before you shop.
Step 8 (optional): Add calories for a diet planner
If you’re following a calorie plan from a doctor or dietitian, add a Calories per serving column to tblRecipes. Then in F4 on the Plan sheet, total each day and copy down to F10:
=SUMPRODUCT(SUMIFS(tblRecipes[Calories per serving],tblRecipes[Recipe],B4:E4))SUMIFS looks up the calories for each of the four meals in the row, and SUMPRODUCT adds them. The same formula with a Prep minutes column shows how much cooking each day involves, which helps you put the longer recipes on lighter days.
Example: four days for a family of four
Here’s the start of a week:
| Day | Breakfast | Lunch | Dinner |
|---|---|---|---|
| Mon | Oatmeal | Leftovers | Chicken stir-fry |
| Tue | Oatmeal | Turkey wraps | Lentil soup |
| Wed | Oatmeal | Lentil soup | Burrito bowls |
| Thu | Oatmeal | Turkey wraps | Chicken stir-fry |
From just these four days, part of the grocery list looks like this:
| Ingredient | Qty | Unit | Where it comes from |
|---|---|---|---|
| Chicken breast | 3 | lb | Stir-fry twice at 1.5 lb |
| Rice | 5.5 | cups | Stir-fry twice at 2 cups, burrito bowls once at 1.5 cups |
| Lentils | 2 | lb | Lentil soup twice at 1 lb |
| Black beans | 2 | cans | Burrito bowls once |
Lentil soup appears twice because Wednesday’s lunch is Tuesday’s dinner cooked as a double batch. Picking the recipe again doubles its ingredients, which is right for a double batch. When a recipe already makes enough for the next day, pick Leftovers instead so nothing is counted twice.
Planning a month of meals
Monthly meal planning works best as a rotation. Fill four weeks on separate tabs (Week 1 to Week 4) using the same drop-downs. Before each shop, copy the coming week into the Plan sheet and the grocery list updates. After a month you have a cycle you can repeat, and swapping one or two dinners keeps it from getting stale.
Using the meal planner in Google Sheets
COUNTIF, SUMIFS, SUMPRODUCT, FILTER, UNIQUE, SORT, INDEX and MATCH all work in Google Sheets. A few parts need changing:
- Excel table references like
tblIngredients[Qty]may not carry over when you convert the file. If Google Sheets is your main tool, build with plain ranges, such asIngredients!G2:G300. - Sheets doesn’t use the
A4#notation. Instead of the SUMIFS above, put this in B4 and copy it down:=IF(A4="","",SUMIF(Ingredients!$B$2:$B$300,A4,Ingredients!$H$2:$H$300)), where column B holds ingredients and column H holds Qty needed. - Drop-downs are under Data > Data validation. Choose “Dropdown (from a range)” and select the recipe column.
- PivotTables are under Insert > Pivot table, and they refresh on their own.
Printing your meal plan and grocery list
Print the Plan sheet in landscape so the week fits on one page (Page Layout > Orientation > Landscape, then set Width to 1 page in the Scale to Fit group). For a blank menu planner to fill in by hand, clear B4:E10 first or print a copy of the sheet.
Print the grocery list in portrait. The PivotTable version is the better one to print, because it groups items by aisle.
Common mistakes to avoid
- Using different names for the same ingredient. “Onion” and “onions” show up as two lines. Capital letters don’t matter to SUMIFS, but spelling and plurals do.
- Mixing units. Rice in cups in one recipe and grams in another can’t be added together. Pick one unit per ingredient.
- Forgetting the weekly staples. Make a recipe called Weekly staples with milk, bread, eggs and coffee, and pick it once a week in any slot. If you’d rather keep your meal slots free, add an Extras column to the plan and widen the COUNTIF range in Step 4 to include it.
- Planning too many new recipes. One or two new meals a week is plenty; the rest can come from the meals you already know.
- Editing the plan after filling in the Have column. Finish the plan first, then check the cupboards.
Ways to improve the meal planner
Scale recipes to your household: add a cell named People with your household size, then change Qty needed to:
=[@Qty]*[@[Times planned]]*People/INDEX(tblRecipes[Servings],MATCH([@Recipe],tblRecipes[Recipe],0))INDEX and MATCH look up how many servings the recipe makes. A soup that serves 6, cooked for a household of 4, uses two-thirds of each ingredient.
Rate your recipes: a 1 to 5 rating column in tblRecipes, plus a filter, makes it quick to find the meals everyone actually liked.
Track real spending: add a column for the receipt total each week next to the SUMPRODUCT estimate. After a month, you’ll know how far off your cost-per-unit figures are.
Frequently asked questions
How do I make a meal planner in Excel?
Create a recipe list, an ingredient list with one row per ingredient, and a weekly grid with drop-down lists in each meal slot. Then use COUNTIF to see how often each recipe is planned, and multiply by each ingredient’s quantity to get the totals for your shopping list.
How do I make a grocery list from my meal plan automatically?
Count how many times each recipe appears in the plan, multiply each ingredient’s quantity by that count, then combine the results. In Microsoft 365, FILTER, UNIQUE and SUMIFS do this as you plan. In older versions, a PivotTable does the same after you click Refresh All.
Is there a meal planner template for Google Sheets?
The same setup works in Google Sheets with small changes: plain ranges instead of Excel table names, and SUMIF copied down instead of a spilled SUMIFS. The Google Sheets section above lists each change.
Can I track calories in an Excel meal planner?
Yes. Add a calories-per-serving column to your recipe list, and a SUMPRODUCT and SUMIFS formula on the plan totals each day. Use the targets your doctor or dietitian gave you; the spreadsheet only does the adding up.
How do I plan meals for a whole month?
Fill four weekly plans and rotate them. Copy the coming week into the main plan before you shop so the grocery list covers one week at a time, which works better for fresh food than a month-long list.
What’s the difference between a meal planner and a menu planner?
At home, nothing; people search for both terms to find the same kind of template. Menu planners for restaurants and events are a different job, covering costing and portions for many guests.
Getting started
Enter the ten meals you cook most, with their ingredients, before you plan anything. That’s the slow part, and you only do it once. After that, each week is a few drop-down choices and a printed list, and every new recipe you add makes the next plan quicker.
The template comes with sample recipes, so you can see how the grocery list works before entering your own. It uses the method that works in every Excel version and Google Sheets.