Pantry Inventory That Matches the Shelf: How the Pantry Ledger Works
A pantry inventory that matches the shelf: how the Pantry Ledger logs Added, Used and Counted from a phone form, with use-by dates and a shopping list
A pantry inventory is worth keeping for exactly as long as its numbers match the shelf, and the Pantry Ledger is built around that one idea. It is a Google Sheet with a phone form: each time something goes onto the shelf, comes off it, or gets counted, the entry travels form to sheet and the list updates from it. Logged in-the-moment, from wherever the item happens to be in your hand, the sheet keeps a pantry list that is worth trusting before a shopping trip.
The form that writes to the list
The form is called Log Pantry, and a link to it sits on the Dashboard under the heading "In-the-moment entry." It asks for six things. Date, which falls back to today when it is left blank. The Pantry item, chosen from a dropdown of every item on the list, A to Z. What happened: Added, Used or Counted. Amount, a number in the item's own unit. Use by, an optional date for when something is added. And a Note.
Everything the form collects lands as one row on the Pantry Log tab, so the whole history of the shelf sits in one place, in order. Dates, items and amounts on the log are validated as they are entered, which keeps the list free of stray text where a number belongs.
The Items tab is the list
Every item the Pantry Ledger knows about has one row on the Items tab, with room for 250 of them. The owner's columns are Item, Category, Where it lives, Unit, Low at, Use by and Notes. The sheet fills in the rest: Added, Used, On Hand, Status and Last Counted.
An item is added on the Items tab, not on the form, which keeps the form to one dropdown. As a name goes into the Item column, it appears in the form's Pantry item choices. Each item carries one unit, in the owner's own word: lb, cans, jars, packets. The sheet never converts between units, so "2 lb" and "32 oz" are two different things on purpose, and a number always means what the unit beside it says.
How the On Hand number is worked out
On Hand is the most recent Counted amount, plus everything Added since that count, minus everything Used since it. An item that has never been counted uses the whole log: all Added, minus all Used. It is a live formula over the log, so correcting a log row corrects the number.
That makes Counted the action that keeps the list honest. Counting a shelf and logging the result sets On Hand to what is physically there, and the Last Counted column shows the date, so each item shows how recently its number was checked against the shelf. A count is also how a number gets fixed: log a Counted entry with the amount on the shelf and the list agrees with the shelf again, with the history intact.
Use by: one date per item, the soonest on the shelf
When an Added entry carries a use-by date, the form hands that date to the item. If the item was at zero, the new date takes over, because the shelf now holds only the new package. If the item still had some on hand, the earlier of the two dates is kept, because the older package is the one that needs attention first. An Added entry with no date and an empty shelf clears the old date, so fresh stock never carries the previous batch's date. The Use by cell on the Items tab stays editable, so any date can be typed over directly.
These are the dates printed on the packages, copied in by the owner. The sheet compares each one with today and with a window set on the Setup tab, "Warn me this many days before a use-by date," which starts at 14. Inside the window, the item's Status reads Use soon. Past the date, it reads Past date. The Pantry Ledger reports where a date sits on the calendar and nothing more; the date itself is always the owner's own.
Low at, and the Shopping List
Each item has a Low at number: the amount at which it is time to buy more. When On Hand reaches that number or falls below it, the Status reads Low, and at zero it reads Out. Those two statuses feed the Shopping List band on the Dashboard, which lists every Low or Out item by category and then by name, with its On Hand, its Low at, its unit, its category and where it lives. The list arrives already sorted, so a trip to the store can begin with a look at the Dashboard.
The row of counts across the top of the Dashboard shows the whole pantry at once: items on the list, Low, Out, Use soon and Past date. Below the Shopping List sit two more bands. Use Soon shows the items still on the shelf whose use-by date falls inside the window, or has passed, soonest first, with the days left. Recent Activity shows the latest entries from the log, newest first, so the last few Added, Used and Counted entries are always in view.
Places and categories, in the owner's words
The Customize Forms tab holds two short lists: Places and Categories. They start with Pantry, Fridge, Freezer and Basement shelf, and Canned, Dry goods, Baking, Spices, Frozen, Snacks, Drinks and Household. They fill the dropdowns on the Items tab, and any of them can be renamed, added to or removed, so the sheet describes the owner's own kitchen rather than a template's.
Starting with a sample kitchen, then making it your own
The Pantry Ledger arrives filled with a sample kitchen: 60 items across the four places and eight categories, with about three months of log entries, so every status and every band on the Dashboard shows something real. That makes the whole system visible before any of the owner's own data goes in. Clear Sample Data, on the custom Ledger & Light menu, empties the log and the amounts, keeps the places and categories, and leaves the Items tab ready for the owner's own list. Entering that list is real work, done once. After that, each entry is a short form on a phone, and the sheet does the totalling, the sorting and the dating.
The menu also carries "Show my Log Pantry form link," so the form's address is always on the menu when it needs to be bookmarked on a phone.
Owned, not rented
The Pantry Ledger is a Google Sheet bought once and kept, living in the owner's own Google Drive with no subscription to renew. Because the log is a list of rows, a pantry that grows from 60 items to 200 simply adds rows to the same sheet, and the history stays in one file.
This article describes how the Pantry Ledger works. It is a list-keeping sheet, and the use-by dates and amounts in it are the owner's own entries.