The Family Quartermaster’s Excel Sheet

Weekend shopping was an easy thing before the lockdown. We decided which grocery stores we fancy that weekend and went for it. Just like that!

Since the lockdown we scramble to place orders with companies that deliver the goods, preferably just in time to allow us to follow our weekly cooking schedule. Going out to stores in person is a last resort solution because two out of three in our household have underlying conditions…

The shopping process starts with the menu we try to prepare for the week, the list of ingredients needed plus the other weekly necessities. This sounds easy but now we also have to factor in what comes from where, with the added twist of timing.

Personally, when I feel overwhelmed and confused, I write a list (or several lists), or create an Excel spreadsheet. Click on the link Weekly Shopping List to download my new shopping list spreadsheet. This met my family’s approval, so much so, that they unanimously voted me Family Quartermaster.

I am sharing this spreadsheet in the hope that others will find it useful as well. At first I considered adding a few macros to automate some tasks, but then I decided against, because some computers might not want to download or open a spreadsheet containing a code for suspicion of a virus.

My spreadsheet consists of two worksheets: KEYWORDS and LIST ITEMS.

KEYWORDS contains lists (columns) with categories of (a) shopping list item types, (b) units and (c) sources. The contents of these three lists are used to create dropdown menus in the cells of LIST ITEMS worksheet, which is the actual shopping list.

For those less versed in Excel, I will include a few explanatory notes below. Those familiar with Excel please ignore the next few paragraphs or email me if you can think of a better way to explain.

Dropdown menus  – at the level of the cells to be filled (not the header cells) – are meant to save typing time. Instead of keying in repetitive information, keywords for types, units and sources can be selected from a list by clicking the small arrow right near the cell in question.

The operation in Excel lingo is called data validation and besides the convenience of pulling the text from a list, it also means that the user is only allowed to type in the keywords used in the KEYWORDS sheet. Typing something different will result in an error message. Click cancel and type a correct keyword or select from the options in the drop-down list.

The keywords in the KEYWORDS sheet can be changed. More keywords can be added in the cells with texts like “custom # 01”, “unit # 01”, “other # 01”, etc. simply by overwriting them (just in case I didn’t think of everything someone else might). If change is needed, the adjustment of the keywords has to be done first, before filling the LIST ITEMS sheet. That is because the keywords on the LIST ITEMS sheet will not change automatically, in order for the changes to take effect the keywords have to be re-selected from the pull-down menus.

I filled the LIST ITEMS sheet with goods that I would buy one time or another depending on the menu planned and the situation of stocks in the house. I also listed most of the regular suppliers for the different items.

The header of the worksheet features another set of arrows, different from the ones used to fill the regular cells, these represent the means to filter the list by different criteria.

For example, using an “x” to mark those items that I want to order on a particular day, I can filter the list by clicking the arrow near the header item “TO GET”, deselect all and then just select “x”. Only the items marked with “x” will be listed. Clicking on the arrow near TYPE header will allow filtering by type and clicking on the arrow near SUPPLIER header will do the same for suppliers. The filters can be combined, so we can see as a result all things we want to buy from a certain supplier.

Every time we filter by a header item, the adjacent arrow is replaced with a filter icon. Going back to the full list is just as simple as filtering: click on the filter icon and then click “select all”. When we remove the filter (by this reverse procedure of “select all”) the filter icon is replaced by the arrow.

The TO GET column has a conditional formatting applied, so when there is any text in the cell, than the cell will have it’s rectangular borders revealed. When the text is erased, the rectangle disappears.

The spreadsheet can be used on cell-phones with the correct app – I am using OfficeSuite Pro for Android – so filtering can be done on the go, not that we want to go anywhere these days, but there is always the prospect of life re-commencing after the lockdown.

A final note: the spreadsheet was created in Excel 2007 Student, so it should work in all newer versions of the software.

I hope this is useful for some of you. Good luck All and keep safe!