# Grocery List

|Based on several online dictionaries, a grocery list is defined as a list of heterogenous items that someone wants. Grocery list become popular because it is very rare that somebody can remember many items that he/she has to buy while shopping in a store or a mart. And usually, this list is important while people do their weekly or monthly shopping for their family/household needs.

If you search “grocery list” term in google, you will find many website offering ready made or printable grocery lists. And there are some websites offering you how to create your grocery list online.

This excel template is basically a grocery list maker, where you can put all of your grocery item in one worksheet, select items that you want to buy, and the other worksheet will adjust your selection into a printable grocery list.

Guidance on how to use this grocery list template is as follow :

1. Go into “Item pool” worksheet. I already put many grocery item inside for your reference. You can click the picture below to enlarge it.

2. Put all of your grocery item in Item list column, and if you have category for your item, put your item category in the same column and put word “Cat” next to the item category name in Cat column and all the color in corresponding row will reveal automatically to differentiate between item category name and item name.

3. If you have price references for your item, you can put your item price in five available shopping mart price column, and the unit reference in price reference column. There is extra column at the right side of the item table that will find the cheapest item price and a shopping mart reference where you can shop that item. And also there is a color guidance in those five columns which will mark the cheapest item price.

4. If you have fill all of your item information, you can create your grocery list by selecting your item by filling “v” character in Checklist column. Once you finished filling Checklist column, watch Checklist Item box at the right side, next to the grocery table. You will see number there where you can use as your guidance to print the correct grocery list worksheet. You can see in my example that the number show 174, which mean you should use the worksheet “Grocery List 199” as your grocery list. The number 199, 144 and 84 is the maximum item that will be revealed in the grocery list.

5. I put additional box in those grocery lists worksheet as an empty rows to put your replacement item, if you cannot find your item, or if you want to add other item that you might remember or your kids and husbands wants you to buy something for them while you are already on the way there or already in the store.

You can download the file **here**.

‘would love to use your template but when I inserted lines in item pool to add more items, the information didn’t translate to the list sheets in the workbook. Not sure what I did wrong but the formulas seem to have gone awry. Any suggestions? ‘thanks tons.

@Sue: Thank you for using my template. The worksheet consist of row-related formula, so when you insert a new row, the formula is broken. You can link back the row by revealing the column B and copy the formula in corresponding cell to your inserted rows.

Or, you can create a new list by copy all item in the column to other worksheet, modify the list, and copy it back to the column.

Regards

Great, and simple.

Just copy the row abobe, insert new line and paste, update item easy

Thanks

Formulas, i have no idea what this means because i do not work with excel. looks like a great way to make grocery list, but the learning curve for the jargon is a little tough. can anyone help??????????

I made my own in excel. I listed everything we buy, by the types of categories (refrigeration, Breads, beverages, etc). I have a “coupon” column if you take a coupon. I have an “ok” column once you put the grocery in your basket. Works out great.

This is AWESOME! I can’t wait to try it!

How do you “fill” the checklist field?

Thanks,

Aileen

Thanks. This is very useful. Ok, I guess I’m a little clumsy but I’m having a hard time creating a new category. How is this different from simply adding a new item?

When in the item pool tab, I added the quantity I wanted to buy, this quantity was not updated in the shopping list. Am I doing something wrong? Thanks

heyy, went to enter/change some products to suit my needs in excel but it wont let me saying i need a password to unprotect it as it is read only????

How can I make the quantity listed on “Item Pool” carry over to the quantity column on the shopping list?

I tried to make my own formula but it’s way beyond me! Do you have a formula I can copy/paste into the cell to make it carry over for the right rows?

thanks

I would like to know how to make it so from the item pool you have entered prices for each item at each store and so it knows which store is cheapest and adds the item to a list for that store only. So if you put in milk, egg, cheese you could get 3 different shopping lists if milk is cheaper at store 1, egg is cheapest at store 2 and cheese is cheapest at store 3. See what I mean?

Is anyone monitoring this now? I’m interested in this worksheet but I’m doing something wrong as when I check off items and put quantity in the “item pool”, it does not fill out the grocery list.

What do you mean by “category”? Could you give the options for categories? Is that required in order to cause the grocery list to fill out?

I don’t know if this is still monitored, but this is the best Grocery List maker I’ve seen, and I thank the person who made it!

In case anyone is still having problems writing to the chart, this is what worked for me:

While in the Item Pool…

Go to Tools > Protection > Unlock the sheet.

Then, to add more items, you need to highlight columns A and C. Then go to the top and choose Format > Columns > Unhide. Column B will appear in blue.

Insert a row where you need to add an item.

Now copy the cell (formula) in Column B that is directly above the row you just added and then paste it into the cell in Column B in the row you added.

The numbers in Column B are sequential, but as you add items below your added rows, the numbers in Column B may start to look out of order.

This is how to get them in order:

• After adding a row, type in the item name in the cell in Column C.

• Then type a ‘v’ to select it in Column D.

• Click on the Column B cell that is ABOVE the row you added.

• Copy that cell to the cell below it (in the row you added).

• The number in Column B should be in sequence with the ones above it, but the ones below it will be out of sequence.

• If you click on the first cell in Column B that is out of sequence, a little box with a yellow warning sign pops up to the right.

• Click on the little black arrow in that warning box and select the choice ‘Copy Formula From Above’. Once selected, the numbers in Column B will all re-sequence correctly.

BUT, you’ll need to do these steps EACH time you add something.

This is what I figured out and it worked for me.

I hope this helps someone.

Hey guys…. Adding my own extra item worked fine without any problems, but why doesn’t the Qty & Unit field contents get carried over to the grocery list sheet from Item Pool???

I love the list, but yeah, the only flaw is that the qty and unit fields don’t go into the completed list.

Its a great template to fool around with though.

This has great potential. Its exactly what I need, however I to cannot add extra Cats and items. I find the instruction at step 2 very confusing. I get restricted values error in the cat colume. any help appreciated.

It seems all is not well with the grocery list template, and yet it looks very promising.

On the Item Pool worksheet you have a v in cells under checklist columns. That is cells D4, D26, D42, D56, D62, D78, D90, D98, D107, D113, D120, D138, and D150. I assume that when there is a v in these cells, it transfers the category headings onto the shopping lists. For example, I put a v in Item Pool cell D26 (Fruits), then the Fruits category is transferred to the shopping lists worksheets. I remove the v from this cell and Fruits is removed from the shopping lists worksheets. Not a problem with this, works as I think it should.

Now if I have v’s in any or all of the cells listed above, the respective categories are transferred to the shopping lists worksheets. Now if I remove the v from Item Pool cell D4 (vegetables), all the remaining categories are cleared from the shopping list worksheets.

I fixed the qty issue and added best price and store to the grocery list. It makes it wider, so I removed the second and third colums. I cleared the print area so that an unlimited number of items can be on the list and with everything checked, it’s about 4 pages. Not likely in real world use, but no limitation now. I also added a pivot table on a separate tab that sorts the data by store. This is exactly what I have been looking for and it ROCKS! I don’t know how to make it available to everyone, so I have the formulas I used below. Maybe R. Musadya might consider copy ‘n paste them into his worksheet and post update if his time permits. A big thank you to R. Musadya and all who contributed feedback.

@Cora Seel: You described exactly what Ive been looking for. It inspired me to make this one do that. I wish I could send you what I made.

@Desert Jewel: Invaluable info that got me started. You have to unprotect each sheet to make changes. Your step-by-step bullets were right on. I select the cell formula I want to copy and then grab the lower-right corner (cursor turns to plus sign) and drag it down the entire colum so Excel auto-fills the formula.

Formulas Used (I’m not an expert, but it works).

GROCERY LIST TAB

ITEM Display in left column

1st row:

=IF(ISNA(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,2,FALSE)),””,IF(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,2,FALSE)””,VLOOKUP(A3,’Item Pool’!$B$4:$N$503,2,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,2,FALSE)),””,IF(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,2,FALSE)””,VLOOKUP(A4,’Item Pool’!$B$4:$N$503,2,FALSE),””))

(select first and second cells, drag down column to auto-fill)

QTY Display in left column

1st row:

=IF(ISNA(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(A3,’Item Pool’!$B$4:$N$503,5,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(A4,’Item Pool’!$B$4:$N$503,5,FALSE),””))

BEST PRICE Display in left column

1st row:

=IF(ISNA(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,12,FALSE)),””,IF(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,12,FALSE)””,VLOOKUP(A3,’Item Pool’!$B$4:$N$503,12,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,12,FALSE)),””,IF(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,12,FALSE)””,VLOOKUP(A4,’Item Pool’!$B$4:$N$503,12,FALSE),””))

Hide #VALUE! error if no price entered yet.

=IF(OR(C4=””,E4=””),””,INT(C4*E4))

STORE Display in left column

1st row:

=IF(ISNA(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,13,FALSE)),””,IF(VLOOKUP(A3,’Item Pool’!$B$4:$N$503,13,FALSE)””,VLOOKUP(A3,’Item Pool’!$B$4:$N$503,13,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,13,FALSE)),””,IF(VLOOKUP(A4,’Item Pool’!$B$4:$N$503,13,FALSE)””,VLOOKUP(A4,’Item Pool’!$B$4:$N$503,13,FALSE),””))

Conditional Formatting

=$D3=””

=MATCH($D3,Category,0)

ITEM Display in middle column

1st row:

=IF(ISNA(VLOOKUP(E3,’Item Pool’!$B$4:$N$503,2,FALSE)),””,IF(VLOOKUP(E3,’Item Pool’!$B$4:$N$503,2,FALSE)””,VLOOKUP(E3,’Item Pool’!$B$4:$N$503,2,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(E4,’Item Pool’!$B$4:$N$503,2,FALSE)),””,IF(VLOOKUP(E4,’Item Pool’!$B$4:$N$503,2,FALSE)””,VLOOKUP(E4,’Item Pool’!$B$4:$N$503,2,FALSE),””))

QTY Display in middle column

1st row:

=IF(ISNA(VLOOKUP(E3,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(E3,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(E3,’Item Pool’!$B$4:$N$503,5,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(E4,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(E4,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(E4,’Item Pool’!$B$4:$N$503,5,FALSE),””))

Item Display in right column

1st row:

=IF(ISNA(VLOOKUP(J3,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(J3,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(J3,’Item Pool’!$B$4:$N$503,5,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(J4,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(J4,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(J4,’Item Pool’!$B$4:$N$503,5,FALSE),””))

QTY Display in right column

1st row:

=IF(ISNA(VLOOKUP(J3,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(J3,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(J3,’Item Pool’!$B$4:$N$503,5,FALSE),””))

2nd row:

=IF(ISNA(VLOOKUP(J4,’Item Pool’!$B$4:$N$503,5,FALSE)),””,IF(VLOOKUP(J4,’Item Pool’!$B$4:$N$503,5,FALSE)””,VLOOKUP(J4,’Item Pool’!$B$4:$N$503,5,FALSE),””))

Oops! A mistake I didn’t catch in one of the formulas. Proof I’m not an expert. 🙂

Hide #VALUE! error if no price entered yet.

=IF(OR(C4=””,E4=””),””,INT(C4*E4)) is not correct, as INT rounds down. 🙁

Correct syntax:

=IF(OR(C4=””,E4=””),””,(C4*E4))

The template has potential, even learned something from it. Just modified to go and print up to 3 pages in the event I get a very long list, and for the quantities, I linked them to my recipes so I would know how much of each item I would need to buy.

I do have a question. I am trying to build a grocery list based on the individual store. Explained. If I am going to Market #1 then i select that on the main page and all of my pricing for that store auto fills. If I were to go to Market #2, then that pricing auto fills. Does anyone know of a template that starts this? I can do the final work. of Course, I would add pricing for my own markets.

Thank you

Неllо, Neat poѕt. Thеre is a

ρrοblem tοgether with yоur web site in

ωeb eхplorеr, cοuld check this?

IЕ still іs thе maгket chief

and a laгgе sеctіοn

οf folkѕ wіll pаѕs over your magnifіcent

writіng becаusе οf this pгoblem.

Hello! I knоw thіs is kinԁa οff topіc but

I’d figured I’d ask. Would you be interested in tгading links oг maybe guest writing a blog article or vice-versa?

My blog cоvers a lot of the ѕame subјеcts

as yours and ӏ feеl we could greatly benefit from еасh othеr.

If you happen to be inteгesteԁ feel free to shoot me an e-mail.

I lоok forward to hearing from you! Gгeat blog by the wаy!