Enter your favourite meals in this Excel weekly meal planner, along with their main ingredients. Click a button to create a shopping list for a week's meals.
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.
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.
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.
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.
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.
After you have entered all the ingredients, you're ready to start the weekly meal planning.
There are 5 selection cells for each meal, and you can also add snacks, and other items you need to purchase.
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.
After you select each day's meals, click the Shopping List button at the top of the WeekPlan sheet.
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.
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.
Shown below is the code that is in the weekly meal planner sample file.
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
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
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.
Last updated: August 1, 2016 2:45 PM