Excel Weekly Meal Planner

Enter your favourite meals in this Excel weekly meal planner, by listing their basic ingredients. Click a button to create a shopping list for a week's meals.

Thanks to Jimmy Pena, who created the Recipe Selector tool in the meal planner.

When the kids are in school, and everyone is involved in evening activities, it can be tough to get your family meals organized.

To help you make the job easier, you can use this Excel weekly meal planner. Enter your favourite meals, list their basic ingredients, and click a button to create a shopping list for a week's meals.

Note: For special occasions, there's an Excel holiday dinner planner that you can use to organize a meal for a large group, or a fancy dinner for friends.

Select Recipes

The first step is to select recipes for your weekly meals, either from your own collection, or on the Recipes sheet in the Excel Weekly Meal Planner.

On the Recipes sheet, enter an ingredient name in cell A2, then click the Get Recipes button. A macro runs, and pulls a list of recipes for the selected ingredient. To see one of the recipes, click its link.

Excel recipes finder

Enter the List of Meal Items

The next step is to list your family's favourite dishes on the Meal_Items worksheet. There are a few sample items in the Excel weekly meal planner. You can add new items to the list, or replace the existing meals with your family favourites.

In column B, enter the name of the dish, and in column C you can enter a link to a recipe online, or type a cookbook name and page number.

meal list

List the Meal Ingredients

After you have entered a list of meal items, enter the main ingredients for each item on the Meal_Ingredients worksheet.

  1. There is a data validation drop down in column B, where you can select the name of one of your meal items.
  2. In column C, enter the quantity required, and in column D, enter the unit, such a can, cup or lb.
  3. In column E, enter the name of the ingredient.
  4. Finally, in column F, enter the category for the ingredient, to help you make a shopping list.

Note: There are formulas in columns G and H, which are used to create the shopping list, so don't type in those columns.

meal ingredients

Select the Weekly Meals

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

  1. On the WeekPlan sheet, select items for each weekday's meals, by using the data validation drop down lists in columns C:E.

There are 5 selection cells for each meal, and you can also add snacks, and other items you need to purchase.

select meals

Create the Weekly Shopping List

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

shopping list button

This runs a macro, which uses an advanced filter to copy all the ingredients for the selected meals to the ShoppingList sheet, and sorts the shopping list by ingredient name.

As you select each day's meal, that meal's ingredients are marked with a 1 in column G, on the Meal_Ingredients worksheet. On the Meal_Items sheet, in cells G1:G2, 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 column G.

criteria range

Print the Shopping List

After the shopping list has been created, the items are summarized 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

Watch the Excel Weekly Meal Planner Video

To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.

Weekly Meal Planner VBA Code

Shown below is the code for the weekly meal planner sample file.

  • The first macro -- CreateShoppingList -- goes on a regular code module.
  • The remaining code goes on the sheet module for the Recipes worksheet

CreateShoppingList Macro

Put this 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"), _
With wsShop
    .Cells(1, 2).CurrentRegion.Sort _
        Key1:=.Range("C1"), _
        Order1:=xlAscending, _
End With

With wsPrint
End With

End Sub

Worksheet Code

Put this code on the Recipes sheet code module (right-click the Recipes sheet tab, click View Code, and paste the code there)

Option Explicit

Private Sub CommandButton1_Click()
  Call GetRecipes
End Sub


Sub GetRecipes()
  On Error GoTo ErrorHandler

  Const RangeToImport As String = "$A$10"

  ' delete existing xml map (if any)
  If ActiveWorkbook.XmlMaps.Count > 0 Then
  End If

  ' clear out range to prep for new import

  ' import web XML to destination range
  ActiveWorkbook.XmlImport URL:= _
        "" _
        & URLEncode(Range("SelItem").Value) _
        & "&format=xml", ImportMap:=Nothing, _
        overwrite:=True, Destination:=Range(RangeToImport)

  Exit Sub
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub


Public Function URLEncode(EncodeStr As String) As String
Dim i As Integer
Dim erg As String

  erg = EncodeStr

  ' *** First replace '%' chr
  erg = Replace(erg, "%", Chr(1))

  ' *** then '+' chr
  erg = Replace(erg, "+", Chr(2))

  For i = 0 To 255
    Select Case i
        ' *** Allowed 'regular' characters
      Case 37, 43, 48 To 57, 65 To 90, 97 To 122
      Case 1        ' *** Replace original %
        erg = Replace(erg, Chr(i), "%25")
      Case 2        ' *** Replace original +
        erg = Replace(erg, Chr(i), "%2B")
      Case 32
        erg = Replace(erg, Chr(i), "%20")
      Case 3 To 15
        erg = Replace(erg, Chr(i), "%0" & Hex(i))
      Case Else
        erg = Replace(erg, Chr(i), "%" & Hex(i))
    End Select

  URLEncode = erg

End Function

Download the Excel Weekly Meal Planner

You can download 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.

excel weekly meal planner


Search Contextures Sites






30 Excel Functions in 30 Days


Last updated: March 10, 2016 7:34 PM