Contextures

Excel Weekly Meal Planner

Get this free Excel weekly meal planner. Enter your favourite meals and their main ingredients. Click button to create weekly shopping list

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.

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

Note: For special occasions, see my Excel holiday dinner planner.

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 go to 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. Then, if you like the recipe, print a copy, to refer to later.

Excel recipes finder

Enter the 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.

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

List Recipe Ingredients and Costs

After you have entered the recipe names, 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.

To check for ingredients, and add new ones:

  1. Go the Recipe_Ingredients worksheet
  2. Sort the list by ingredient name, to see what ingredients have already been entered.
  3. If an ingredient that you need is not already in the list, enter it in column B.
  4. In column C, enter the ingredient category, such as Meat or Dairy.
  5. In column D, enter the unit, such cup, or lb, or 14 oz can.
  6. In column E, enter the unit cost, or an approximate cost.

ingredients and costs

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.

  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

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

Tip: If you want to find recipes that you have already entered, and that use a specific ingredient, go to the MyRecipes sheet. Enter an ingredient name in cell A2, then click the Get My Recipes button, to see a list of recipes.

check your recipes for a specific ingredient

Create the 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

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 on the Meal_Ingredients worksheet, in column G. 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 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

Weekly Meal Planner VBA Code

Shown below is the code that is in the weekly meal planner sample file.

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

CreateShoppingList Macro

In the sample file, you'll find 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"), _
    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

Worksheet Code

In the sample file, this code is on the Recipes sheet code module (right-click the Recipes sheet tab, click View Code, and view 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
    ActiveWorkbook.XmlMaps.Item(1).Delete
  End If

  ' clear out range to prep for new import
  Range(RangeToImport).CurrentRegion.Cells.Clear

  ' import web XML to destination range
  ActiveWorkbook.XmlImport URL:= _
        "http://www.recipepuppy.com/api/?q=" _
        & URLEncode(Range("SelItem").Value) _
        & "&format=xml", ImportMap:=Nothing, _
        overwrite:=True, Destination:=Range(RangeToImport)

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

'------------------------------

Public Function URLEncode(EncodeStr As String) As String
' http://www.freevbcode.com/ShowCode.Asp?ID=5137
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
  Next

  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.

OR, 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.

excel weekly meal planner http://www.contextures.com/excel-weekly-meal-planner.html

 

Search Contextures Sites

 

pivot power premium

 

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

Last updated: September 13, 2016 2:34 PM