Grocery Price Book Template
One of the best ways to save money on groceries is to buy in bulk when things are on sale. But how are you supposed to know when a price is a good price, without an amazing memory and a lot of experience? One way is to use a grocery price book - a list of groceries that allows you to track the store, brand, date purchased, and the unit prices. You could easily make something like this yourself, but a user recently requested that we create an Excel template for this - something that you can refer to on your phone if you have the mobile Excel apps.
In addition to the grocery list in this template, we've added a Grocery Price Calculator worksheet that you can use to calculate the unit prices for items that don't have the unit price already listed on the tag. The calculator also lets you include coupons.
Grocery Price Book
for Excel
How to use the Grocery Price Book Template
Unit Prices
The best way to compare prices is to compare unit prices such as the cost per ounce or cost per egg. In the price book, you enter the price of the item and then the size of the item and the units. The unit price is calculated for you.
Coupons can make calculating unit prices a bit more tricky, so there is a Grocery Price Calculator worksheet included that can help you. Also, if you use coupons a lot, you can unhide the hidden columns in the price book worksheet to include coupons. Make sure you understand how the spreadsheet calculates prices when using coupons. The hidden columns in the price book function the same was as the grocery price calculator worksheet.
Comparing Brands, Stores, Package Sizes
When you are comparing brands, stores, or package sizes for the same item type, you can use the filtering and sorting features in Excel to help you. For example, you may want to sort by unit price (low to high) and item. This will group all the same items together and sort them by price.
Best Price / Date
Deals and sales are often seasonal, so the purpose of the Best $/Unit and Date columns is to keep track of when you have gotten exceptional deals.
Customize Drop-Down Lists
The drop-down lists for the Brand, Store, and Units can be customized via the Lists worksheet.
You can enter values that are not in the drop-down lists if you want to. The drop-downs are just for convenience.
Google Sheets uses a red triangle in a drop-down cell to warn when a value is entered that is not in the list (in Excel this can be turned off). You can still enter anything you want, but if you want the red triangle to go away, the brand/category/unit/store would need to be entered in the Lists worksheet.
for Category/Item
In Excel: When you select a grocery category in the Category column, the drop-down in the Item column will show only items listed for that category.
You can customize these lists in the Items worksheet.
The Category list is based on the column headers in the Items worksheet. You can edit the category labels by editing the column headers. You can also reorder the categories by cutting/pasting/inserting columns. You can add categories by inserting columns (note that the list is currently alphabetical).
This isn't a tutorial on how to use Excel, but if you are curious how these dependent drop-down lists work, you can read the article Create Dependent Drop Down Lists by Excel expert Debra Dalgleish. The technique I used is a bit different from the article because I wanted to make it easy to add, rename, and rearrange the categories, but the general idea is to use a dynamic range formula for the data validation drop-down list.
Google Sheets: Unfortunately, it isn't possible to do dependent drop-down lists in Google Sheets, so that means that the Item column is entered manually (rather than a sub list that is based on the Category).
