Excel Meal Planner With Pivot Table

Now that fall is here, you might be back into a routine of weekly meal planning, and Excel is a great tool to use for that.

To help you get started, there is a weekly meal planner on my Contextures website, that you can download. Enter the meal and product information, based on your favourite meals.

Then, pick your meals for the upcoming week, and click a button  to create a weekly shopping list.

mealplannerpivot03

The shopping list is a pivot table, that summarizes the grocery items by category, to help make it easier when you get to the grocery store.

mealplannerpivot01

Formula Fix

Last week, someone asked about adding lunch items to the meal planner too. They had added meals to the main list, but the new items weren’t showing up in the shopping list.

I found that there was a problem in one of the formulas – it was set for the original rows, and not including the new ones. I modified the formula, so it now refers to the column names, instead of cell references.

The formula checks the weekly list, to see how many time you have included each meal in the planner. Then, it multiplies the quantities and costs by that number, to get the total.

mealplannerpivot02

So, if you’ve previously downloaded the meal planner, you can make sure that formula is fixed in your file, if you plan to add extra meals on the main sheet.

  • On the Meal_Ingredients sheet, clear all the numbers in column G, under the “List” heading
  • Click on cell G2, and paste in this formula:  =COUNTIF(Table3[[Meal Item 1]:[Meal Item 3]],B2)
  • The formula should automatically fill down all the rest of the rows.

Download the Updated Excel Meal Planner

You can see the full details for the Excel Weekly Meal Planner on my Contextures website, and download an updated copy to help plan your meals.

____________

This entry was posted in Group and Total. Bookmark the permalink.

One Response to Excel Meal Planner With Pivot Table

  1. Pingback: Excel Roundup 20141006 « Contextures Blog

Leave a Reply

Your email address will not be published. Required fields are marked *