Home > Templates > Meal Planner

Excel Weekly Meal Planner

Debra Dalgleish - Contextures

I use this Excel weekly meal planner every weekend, to prepare for the upcoming week. Download a copy, so you can use it too! Choose your favourite meals, then click a button to print your grocery list.

select meals in weekly calendar

1) Excel Meal Planner Intro

To help you make the job of meal planning easier, you can use this Excel weekly meal planner template. Enter your favourite meals, list their basic ingredients, and click a button to create a shopping list for a week's meals. To see a quick overview of how the Excel Weekly Meal Planner works, watch this short video.

 

    

Tip: For special occasions, see my Excel holiday dinner planner. Use it to create a timeline for preparing and cooking a complicated meal.

2) Select Recipes

The first step is to choose some recipes for your weekly meals.

  • There are a few recipes in the sample file, to get you started.
  • To find more recipes, you can look through your collection of cookbooks, or find recipes online.

2.1) Meal Planner Recipes

To find recipes that use a specific ingredient, you can search the recipes that are already entered on the Meal_Ingredients sheet. For example, you might want to use a specific ingredient from your pantry or fridge, before its "Best Before" date.

  • Go to the MyRecipes sheet.
  • Enter an ingredient name in cell A2
  • Click the Get My Recipes button, to see a list of recipes that are on the Meal_Ingredients sheet.

check your recipes for a specific ingredient

3) Enter List of Meal Items

After you select your recipes, you will enter them on the Meal_Items worksheet, if they haven't been entered already. There are a few sample items in the Excel weekly meal planner, and you can add new items to the list, at any time.

3.1) To add a new recipe:

  1. Go to the Meal_Items sheet
  2. In column B, enter the name of the dish
  3. In column C, you can enter a link to a recipe online, or type a cookbook name and page number.

meal list

    

Tip: You can also enter items that you purchase weekly, such as "Milk, weekly", or "Coffee, ground". Later, select those items in the "Other" row on the weekly menu, so they're added to the shopping list.

4) List Recipe Ingredients and Costs

After you have entered the recipe names in the spreadsheet, go the Recipe_Ingredients worksheet. Soon, you will be selecting the ingredients for your recipes, and those ingredients should be entered in the cost list first.

4.1) To check for ingredients, and add new ones:

  1. Go the Recipe_Ingredients worksheet
  2. Sort the list groceries by ingredient name, to see what ingredients have already been entered.
  3. If an ingredient that you need is NOT in the list, enter it in column B.
  4. In column C, enter the ingredient category, such as Meat or Dairy -- you can create your own categories, if you prefer..
  5. In column D, enter the unit, such cup, or lb, or 14 oz can. For most items, use the unit type that you'd see in most of your recipes, such as "cup" for sugar, even though you buy it in a bag.
  6. (optional) In column E, enter the unit price, or an approximate price, at your grocery shop.

ingredients and costs

4.2) Meal Planner with Inventory

If you download the Basic Meal Planner with Inventory, there are

  • additional columns in the ingredients table
  • an Admin_Lists sheet, with a locations table

4.3) Ingredient Storage Locations

On the Admin_Lists sheet, there's a small table with locations where you might store your ingredients at home. You can edit this list, and add new locations, or delete storage locations that you don't need.

storage locations

4.4) Ingredient Inventory

On the Recipe_Ingred sheet, there are location columns at the left side of the Ingredients table.

For any ingredient that you want to track:

  • Select a location in the Loc01 column
  • Enter the current quantity on hand in the Loc01 Qty column
  • If the ingredient is also stored in another location, enter that information in the Loc02 and Loc02 Qty columns
  • The Loc Qty column has a formula that sums the amounts for the 2 locations.

In the screen shot below, ground beef is in the fridge freezer, and in the basement freezer, for a total of 3 pounds.

ingredient inventory details

5) List the Meal Ingredients

After you have entered the recipe names and ingredient costs, go the Meal_Ingredients worksheet. It has a list where you will enter the main ingredients for each recipe.

    

Note: You don't need to enter ingredients that you usually have on hand, such as salt and pepper, or cooking oil.

To enter meal ingredients, follow these steps:

  1. In column B, select the name of one of your meal items from the drop down list.
  2. In column C, select the name of an ingredient from the drop down list. If the ingredient isn't in the list, go to the Recipe_Ingred list and add it. Or, just type the missing ingredient in the cell, and no cost information will be calculated.
  3. In column D, enter the quantity required for the selected recipe. For example, type a 2, if the recipe calls for 2 cans of beans.
  4. In columns E:F, formula will display the ingredient information that you entered on the Recipe_Ingred sheet.
  5. In columns G:J, formulas in the grey cells calculate the costs for the shopping list.

enter recipe ingredients

5.1) Meal Planner with Inventory

If you download the Basic Meal Planner with Inventory, there are additional columns in the Meal_Ingredients table

  • On Hand
  • Need Cost
  • Need

Those columns have formulas that calculate the amounts for the shopping list. You can ignore those columns and formulas -- they're just used for the shopping list.

On Hand calculation columns

NOTE: The On Hand column might not show the total amount that you have on hand. The formula splits the total amount by the number of times that ingredient in used in the current week's menu.

For example, we have 3 pounds of ground beef on hand, and it's used 3 times this week, so each row shows 1 pound in the On Hand column (3 divided by 3 = 1)

On Hand calculation

6) Select Weekly Meals

After you have entered all the ingredients, you're ready to start the weekly meal planning.

  1. Go to the WeekPlan sheet, where there is a one-week calendar, with a column for each day of the week.
  2. In each column, select items for that weekday's meals, by using the data validation drop down lists in columns C:E.
  3. There are 5 selection cells for each meal, where you can select a main dish, side dishes, and other food items.
  4. In the Snacks section, you can add snack foods, and other food items you need to purchase

select meals in weekly calendar

6.1) Other Items and Notes

If there are other items to add to the shopping list, enter those in the "Other" row.

  • For example, add "Milk, weekly" there, insteading of listing Milk at each meal.
  • Or, enter the cleaning products, toiletries, and other items that you usually put on your grocery list.

select other items

7) Create Weekly Shopping List

After you select each day's meals, click the Shopping List button at the top of the WeekPlan sheet.

shopping list button

7.1) Meal Planner with Inventory

If you download the Basic Meal Planner with Inventory, there are two filters at the top of the pivot table - Meal Item, and Need.

  • Use the Meal Item filter if you just want to see the items for a specific meal
  • By default, the Need filter is set to TRUE, so the list only shows items you need to purchase

shopping list filters

There are extra columns in the pivot table too:

  • On Hand - the total quantity in the inventory
  • Buy - a pivot table calculated field that shows the quantity you need to buy (the difference between recipe quantity and quantity on hand)
  • Need Cost - total price of buying the quantity shown in the "Buy" column.

extra columns in pivot table

8) Print the Shopping List

After the shopping list has been created, the items are summarized by category, in an Excel Pivot Table. You can print the list, add any other grocery items that you need, and then head to the grocery store.

shopping list

9) Weekly Meal Planner Macro

The meal planner file has a macro that creates a weekly shopping list, based on your selected meals.

    

Tech Tip: You don't need to read this section, unless you want to know how the macro works in the weekly meal planner.

9.1) Shopping List Button

When you click the Shopping List button, it runs a macro. The macro uses an advanced filter to copy all the ingredients for the selected meals to the ShoppingList sheet. Then it sorts the printable meal plan shopping list by ingredient name.

Here's how the advanced filter works:

  • As you select each day's meal, that meal's ingredients are marked with a 1 on the Meal_Ingredients worksheet, in the "List" column
  • On the Meal_Items sheet, there is a criteria range that's used in the advanced filter. It checks for ingredients that are marked with a 1 (or higher) in the "List" column

criteria range

9.2) CreateShoppingList Macro

In the sample file, you'll find the following VBA macro code on a regular code module. It creates the shopping list, based on the selected meals.

Sub CreateShoppingList()
Dim wsShop As Worksheet
Dim wsIngred As Worksheet
Dim wsList As Worksheet
Dim wsItems As Worksheet
Dim wsPrint As Worksheet

Set wsShop = Sheets("ShoppingList")
Set wsIngred = Sheets("Meal_Ingredients")
Set wsItems = Sheets("Meal_Items")
Set wsPrint = Sheets("ShopListPrint")

wsIngred.Columns("B:J").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=wsItems.Range("CritShop"), _
    CopyToRange:=wsShop.Range("ExtShop"), _
    Unique:=False
    
With wsShop
    .Cells(1, 2).CurrentRegion.Sort _
        Key1:=.Range("C1"), _
        Order1:=xlAscending, _
        Header:=xlYes
End With

With wsPrint
    .PivotTables(1).PivotCache.Refresh
    .Activate
End With

End Sub

10) Get the Excel Weekly Meal Planner

There are several menu planners, so download the meal plan templates that you need.

  1. Basic Planner: You can get a copy of the Excel Weekly Meal Planner and add your own meals and ingredients. The file is in xlsm format, and is zipped. After you unzip the file and open it, enable macros, so you can create the shopping list.
  2. Basic Planner with Inventory: If you want to keep track of which ingredients you have on hand, download the Excel Weekly Meal Planner with Inventory. The file is in xlsm format, and is zipped. After you unzip the file and open it, enable macros, so you can create the shopping list.
  3. Basic Planner with Meal Type: To choose meal items based on meal type (breakfast, lunch, dinner, snacks), download the Excel Weekly Meal Planner with Meal Type. (for Excel 365) This makes the drop down lists shorter, and easier to find what you need. The file is in xlsm format, and is zipped. After you unzip the file and open it, enable macros, so you can create the shopping list.
  4. Serving Count: If the number of people changes at different meals, you can use the Excel Weekly Meal Planner Servings workbook, to get a more accurate shopping list. On the WeekPlan sheet, enter the number of people for each meal. Also, enter the number of servings for each recipe on the Meal_Items sheet.
  5. Meal Frequency: For each meal item, set a number of times that you'd like to serve that dish each year. For example, enter 26 if you want to have it every other week. Or, enter 12, for a once-a-month dish. Then, select your entree for each day, and all the other food items that you want to include. Get the Meal Frequency Planner workbook.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Chicken Dinner Planner

Holiday Dinner Planner

Excel Calorie Counter

Data Validation Basics

Advanced Filter Introduction

Pivot Table Introduction

Excel Weekly Meal Planner contextures.com

Excel Weekly Meal Planner contextures.com

Last updated: June 3, 2024 10:06 AM