I’m a busy mom. And I don’t have time to make a grocery list from scratch every month before our monthly shopping trip. I’d totally forget to put down important things. So I brainstormed ways to save time. And I discovered that creating a shopping list in Excel works perfectly for me!
My master shopping list corresponds with our annual meal plan. <= you can read that post for more detail. But, each July the kids and I work together to redo our annual plan. Then while the meals are still on my brain, I sit down and make a corresponding shopping list in Excel.
I go through each recipe, and write down every single ingredient. This includes “staples” like pepper and salt too. You do eventually need to replace those and if you don’t have them written down you’ll forget!
Here’s a step-by-step look at how I use Excel to create a master shopping list.
Create Your Template
I open up a new workbook in Excel, and label the first few columns. I use the following labels:
- Column A: Item
- Column B: Store
- Column C: Quantity needed
- Column D: Price point
- Column E: Total price for item (quantity * price)
Start Filling it In
Using your recipes, start getting each ingredient needed on your list. You want to get everything you may need to purchase in column A.
If desired, you can also include household items like paper plates or diapers. I just use Amazon subscribe and save for those, so I don’t put them on my master shopping list.
(Not utilizing Amazon Prime yet? Sign up for a free 30-day trial and start saving!)
If you need one item for multiple recipes, keep track of your quantity over in column C. That way you can track how many packages you need to purchase and don’t have to reread all of your recipes again to figure it out.
Don’t worry about filling in the other columns yet though, just focus on quantity and item.
Add the Store
I wanted to be able to see what I need to purchase as each store, so I added the store column. Here I record where I typically purchase each item. I go to Cash and Carry, Costco, Winco, and Walmart each month, so it’s almost always one of those stores.
Verify Your Quantity
If you added quantity as you went, just scan through and make sure you know you have enough of each item on your list for your initial shopping trip.
If you don’t need to purchase any, make it zero. If you already have three cans of tomato sauce and you need eight for the month, you need to purchase five.
<
Add Price Points
I need to make my grocery list match my budget, so keeping track of prices is essential. I do my best guess of the price the first time I purchase something. I always round up.
Then I take a pen to the store with me and update the price. Then unless it changes, I won’t have to worry about it.
Having a price points lets me look for sales. It reminds me to stock up when the price is cheaper.
It also helps me keep track of when I should start purchasing something at a different store to get a lower price.
Let Excel Run
So far I’ve done all the work on my master shopping list. Now it’s time to let Excel take over.
I use column E for a simple formula, and let it do my math for me.
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!
Make it User Friendly
Now it’s time to sort your list to make it easier to use. I sort my list by store and then by item, alphabetically.
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!
I also add a grand total at the bottom. Then if my total is above my budget, I can start cutting items to purchase.
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.
Then when you change the quantity on something, it’ll automatically update the total. Pretty neat!
Prepare it For Use
Each month, I simply update the quantities on each item. Then the math is done for me, and I can make sure I’m below budget before stepping foot in a store.
Then I just print out my list and take it to the store with me. I can see what I need at each store, and keep my eyes peeled for prices going up. If that happens, I add a star next to it and update the price the next time I open my list up.
I love my Excel shopping lists, because it makes shopping preparation much easier.
Pin it for later?
