A few weeks ago I posted our
updated menu. With an updated menu, I needed an updated shopping list. I wanted to share with you how I use Excel to make grocery list making easy. This isn't the only way to do it--just the way I did. I'd love to hear other tips you have!
First, I used my recipes to figure out what I needed to keep stocked. That was the most time consuming part. Remember to list even the "staples" like salt and pepper. You have to make sure you have everything you might need!
In a new workbook in Excel, I began typing each item in column A.
I didn't worry about which store I wanted to buy these items at, I just started typing my ingredients.
After I typed those all in, I moved to column B.
Here, I picked which store I would usually buy this item from. Typically on a shopping day, I go to Costco, Cash & Carry, Winco, and Wal-Mart. I also have some pantry items now that I order online (which I think will be increasing as I get a better hang on it!) I know which store I usually find the best price at, so that is the store I picked for each item.
Next came quantity in column C. I looked over my menu plan, and decided how many of each item I wanted to keep on hand. I won't need to buy each item every month, but this way I have a number in mind each time I prepare my list.
For instance, I want to have 8 cans of tomato sauce on hand. That allows me enough to use 2 cans a week. If I'm getting ready to shop, and realize I still have 5 cans, I will only need to buy 3 to get me up to 8.
After I knew how many of each item I wanted, I started filling in prices in column D. Most of the prices I knew (or know I'm at least close.) I round up, trying to overestimate not underestimate. Each time I go shopping, if the price is significantly different, I make a note on my shopping list and will update my list.
All of this has been work so far on my end. Now it's time to let Excel work its magic and do some of my work for me. I used column E for a total column. Then I needed to insert a formula. It's really simple!
In column D, for the first item on my list, I enter this: =(
And then I click on the quantity box for that item (from column C).
Then I hit * for multiply
And click on the price box for that item (from column D).
Then close up your parenthesis.
So you have =(C2*D2)
And Excel will calculate this math for you. To get this formula for every item, simply click on the cell you just used. A little box will show in the corner.
Click on that black box and drag down until you've drug it to all of your items. The formula will automatically update for each row, leaving you with correct totals for everything on your list!
Only a few steps left to make this more user friendly!
I need to know exactly what I need to buy at each store. So I need to sort my list.
To do this, click on the cell that is between column A and row 1. It has sort of a triangle in it. Doing this will highlight the whole book.
Then click on "Sort and Filter". A custom sort is needed, so select that option.
This menu will open up...
We need our list sorted by Column B--the stores. So use the drop down menu to select the correct column. I left the other two options alone.
When you hit OK, you'll find your whole list sorted by store. Yeah!
Then I copied and pasted each store section onto a new sheet. That way I only have to look at one list at a time when I'm in the store. To do that, highlight all the items for one store. Then use the Edit menu to hit copy. Then click on a tab at the bottom and a blank page will open. Hit cell A1 and use the menu to paste your selection.
Then right click on that tab and rename the sheet so it is the name of the store.
Delete the now blank cells from your original list, so you don't lose track of the items further down the list.
Repeat for every store.
Remember to save!
But, I need a total for each store. That way I can better keep my items within my grocery budget. So I go down to the very last entry on Column E and skip another row.
Time for one last formula.
=SUM(
And then use your mouse to click on the bottom item. Drag up to your top item. Then close your parenthesis.
It'll end up looking like this:
=SUM(E2:E40).
And all of your math will be done for you. Repeat for each store. Then when you change the quantity on something, it'll automatically update the total. Pretty neat!
Now each month, I can simply update my quantities, and the math will be done for me. I love Excel shopping lists! A lot of work at first, but now it'll be easy for the upcoming school year!
And if I need something else that I don't normally buy, I can simply write it in.
My next project is to put this on Evernote so I can get it onto my smartphone and not have to carry a bunch of sheets of paper with me to Spokane.
Oh--speaking of printing. Be sure to select "Entire Workbook" when you print your list, or you'll only end up with the first store.
How do you prepare your shopping list?
