Use an Excel workbook with one row per physical keg, a current-status field, and a summary sheet to track stock, locations, and returns. For example, a summary can count kegs marked “Full” and total their capacity; keep a separate transaction log if you also need a history of movements.
What a Keg Inventory Spreadsheet Tracks
A keg inventory spreadsheet is a practical record of the reusable containers a brewery, bar, distributor, or event business owns or manages. It answers questions such as how many kegs are full, which are empty, which have gone to customers, and where a particular keg was last recorded. It can also help flag overdue returns and show the deposits associated with kegs currently out.
There are two related kinds of stock to distinguish. Physical keg inventory counts the containers themselves. Beverage inventory counts the beer or other product inside them. A keg list can record a product name and full or empty status, but it does not automatically establish how much saleable beverage remains; that depends on reliable fill, sale, and dispense records. Businesses that need both views should keep them linked but treat them as separate quantities.
The spreadsheet is most useful when its status names and update routine are consistent. It is not a substitute for a physical count, a point-of-sale system, or accounting records. Rather, it gives staff a shared, searchable record and helps make discrepancies visible sooner.
Choose a Record-Keeping Structure
For a small operation, one worksheet with one row per keg may be enough. Give each physical keg a unique internal ID, such as K-001, and do not use the beer style or customer name as the ID: those details can change, while the asset identity should remain stable. If a keg has a manufacturer’s serial number, record it too, but keep an internal ID if the serial is missing, hard to read, or unsuitable for everyday scanning.
As the operation grows, use a workbook with three worksheets:
- Kegs: the current record for each physical keg, including its status and location.
- Movements: a dated log of deliveries, returns, transfers, fills, and other changes.
- Summary: counts and capacity totals by status, plus follow-up measures such as overdue returns.
The Kegs sheet answers “what is the latest recorded state?” The Movements sheet answers “what happened, and when?” Keeping both can make corrections easier to investigate. If a keg changes hands, update its current record and add a movement entry rather than relying on an old note or an email thread.
Useful Columns for the Kegs Sheet
Set up a header row in row 1 and enter one keg per row, starting in row 2. A useful layout is:
- A — Asset ID: a unique, stable identifier for the keg.
- B — Capacity (L): the keg’s nominal volume, using one consistent unit.
- C — Product: the current product or a clear value such as “Empty” where appropriate.
- D — Current status: a controlled value such as Full, Empty, With customer, In transit, Repair, or Retired.
- E — Current location: a brewery, warehouse, venue, vehicle, or other agreed location name.
- F — Customer or venue: the recipient when a keg is out; otherwise leave it blank or use your agreed convention.
- G — Date out and H — Due date: useful for tracking returns, where applicable.
- I — Deposit amount: the amount associated with that keg under your business’s process, if you track it here.
- J — Last checked and K — Notes: a dated physical check and concise details about damage, repairs, or exceptions.
Use dates as actual Excel dates, not text that merely looks like a date, so sorting and date comparisons work. Keep capacity units consistent: if some records are in liters and others in gallons, totals will be misleading unless you convert them or add a unit column and report them separately. Do not delete a retired keg’s row if you need an asset history; mark it Retired instead.
Build the Summary and Calculate Counts
On the Summary sheet, put status labels in column A and use columns B and C for keg count and total capacity. For instance, A2 can contain Full, A3 Empty, and A4 With customer. The formulas below use the Kegs layout described above and check rows 2 through 501; enlarge the ranges if the inventory exceeds that capacity.
With current statuses in Kegs!D2:D501 and the status to count in Summary!A2, enter this in Summary!B2. If A2 is Full and 12 rows are marked Full, it returns 12.
=COUNTIF(Kegs!$D$2:$D$501,A2)
With statuses in Kegs!D2:D501, capacities in Kegs!B2:B501, and the chosen status in Summary!A2, enter this in Summary!C2. If 12 full kegs each have a capacity of 30 liters, it returns 360 liters.
=SUMIF(Kegs!$D$2:$D$501,A2,Kegs!$B$2:$B$501)
Copy both formulas down alongside the other status labels. The count and capacity will update when the Kegs records change. These formulas describe the spreadsheet’s recorded state, not a verified physical count; staff still need to update records promptly and investigate mismatches.
Track Returns and Deposits Carefully
A customer-out record is only useful for follow-up if it includes enough information to identify the recipient and expected return date. Agree on what “With customer” means: for example, it might include kegs at a venue but exclude kegs already returned to your loading area. Use the same definition across staff, and change the status when the keg is returned, transferred, or found to need repair.
With statuses in Kegs!D2:D501 and due dates in Kegs!H2:H501, this formula counts kegs marked With customer whose due dates are before today. The final condition excludes blank or zero due-date cells. If three qualifying records are overdue on the day the formula is evaluated, it returns 3; the result changes as the date changes.
=COUNTIFS(Kegs!$D$2:$D$501,"With customer",Kegs!$H$2:$H$501,"<"&TODAY(),Kegs!$H$2:$H$501,">0")
Due dates are operational reminders, not proof that a customer is legally late or that a particular fee applies. Policies, contracts, and local requirements vary, so keep the spreadsheet aligned with the actual terms you use. Likewise, a deposit column can show amounts associated with tracked kegs, but it should not be treated as a complete accounting balance unless it is reconciled with your accounting process.
With statuses in Kegs!D2:D501 and deposit amounts in Kegs!I2:I501, this formula totals the recorded deposit amounts for kegs marked With customer. If six such kegs each have an illustrative amount of $40 recorded, it returns $240. That is a spreadsheet subtotal, not a recommendation about deposit levels or accounting treatment.
=SUMIFS(Kegs!$I$2:$I$501,Kegs!$D$2:$D$501,"With customer")
Log Movements and Control Data Entry
In a Movements sheet, use one row per event, with columns such as date and time, keg ID, action, from location, to location, customer or venue, reference number, and staff initials. A delivery and a return should be separate entries. A movement log makes it possible to trace when the record changed, while a single current-status row is quicker to use for everyday stock checks.
Excel’s Data Validation can create a dropdown for the status field. On Excel for Microsoft 365 on Windows, select the status cells and choose Data > Data Validation, then set Allow to List and provide the approved status values as the source. The Data Validation command is also available on the Data tab in Excel for Mac, though the dialog layout can differ. A dropdown reduces spelling variations such as “With customer,” “Customer,” and “out,” which would otherwise split formula counts.
Turn the inventory range into an Excel table using Insert > Table and confirm that the table has headers. Tables make it easier to filter and sort records and extend formatting as rows are added. Always filter the whole table rather than sorting a single column, which could separate a keg ID from its status or location. If you use fixed-range summary formulas, remember to extend their ranges as the list grows.
Daily Workflow and Physical Reconciliation
A spreadsheet works best when updating it is part of the movement process, not an occasional cleanup task. A simple routine can be:
- Before dispatch, confirm the keg ID, product, and status, then record its destination and due date if one applies.
- At delivery or transfer, add a movement entry and update the current location and status.
- At return, check the ID and condition, update the status and location, and clear customer and due-date fields that no longer apply.
- During a scheduled stock check, compare the physical kegs with the sheet and record exceptions for investigation.
Reconciliation matters because a formula cannot detect an unrecorded delivery, a duplicate ID, or a keg placed in the wrong location. Count by location and status where possible. If the sheet says a keg is in the warehouse but staff cannot find it, record the discrepancy and investigate the last movement rather than silently changing the status to make the totals look right.
For a small inventory, checking every asset may be manageable. A larger operation may schedule counts by area or rotate through sections, provided every keg is checked on a defined cycle. The appropriate frequency depends on movement volume, loss risk, and staffing; there is no universal count interval that suits every business.
Common Spreadsheet Problems to Avoid
- Duplicate or missing IDs: two rows for one keg inflate counts, while an unrecorded keg disappears from reports. Check IDs when adding records and investigate duplicates.
- Inconsistent statuses: free-typed labels can make a status total incomplete. Use a dropdown and maintain a short, documented list.
- Confusing empty with unavailable: an empty keg may be available for filling, awaiting cleaning, or under repair. If those states require different actions, track them separately.
- Stale location data: a last known location is not necessarily a live location. Update it at handoffs and label the field as current only when staff follow that process.
- Mixed units or misunderstood totals: capacity totals add nominal keg volumes, not necessarily the volume of beverage actually present. Do not present a count of containers as a product sales or fill report.
- Overwriting history: replacing old values makes it hard to explain discrepancies. Keep a dated movement log or retain dated backups according to the business’s record-keeping needs.
When a Spreadsheet Is Enough—and When It Is Not
A spreadsheet can suit a small team with a manageable number of kegs, few locations, and a clear person responsible for updates. It is inexpensive to start, easy to adapt, and can be shared through an organization’s approved Microsoft 365 storage. Protect access appropriately and avoid circulating uncontrolled copies: two people editing different versions can quickly create conflicting records.
A spreadsheet may become cumbersome when many staff need simultaneous mobile updates, barcode scanning, automated customer statements, integrations with sales or brewery systems, or a reliable audit trail of every change. At that point, compare inventory software or a database-based system against the actual workflow. A more specialized tool may reduce manual entry, but it still needs accurate IDs, clear status definitions, and physical checks. Moving to software does not by itself solve poor data practices.
Frequently Asked Questions
What is the best way to identify each keg?
Assign every physical keg a unique, durable internal ID and record it consistently. Add a readable label or barcode if useful, and store a manufacturer serial number separately when available.
Should full and empty kegs be separate inventory items?
They are usually separate statuses for the same physical asset. That lets the sheet count containers while distinguishing which ones are ready to dispatch, need filling, or need cleaning.
Can Excel automatically tell me which kegs are overdue?
It can count or highlight records based on a due-date column and the current date, provided statuses and dates are maintained correctly. It cannot know whether a keg was actually returned unless someone records the return.
Does keg capacity show how much beer is left?
No. Capacity describes the container’s nominal size. To estimate or record beverage remaining, maintain separate fill, dispense, or measured-volume data and define how that quantity is verified.
How often should I reconcile the spreadsheet with the physical kegs?
Set a schedule that fits movement volume and the consequences of missing equipment. Count more often where turnover or discrepancies are high, and investigate differences rather than treating the spreadsheet as proof of physical stock.
Related
- Free Habit Tracking Spreadsheet: Build One in Excel
- How to Enter Slide Master View in PowerPoint
- Excel Not Responding? Troubleshoot Common Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Filter by Date: A Practical GuideYes. In Google Sheets, select your data, choose Data > Create a filter, open the filter dropdown on…
- Move a Row Up or Down in Excel Without Losing DataTo move a row in Excel without overwriting another one, click its row number, press Ctrl+X, then right-click…
- How to Make Columns in Microsoft Word, Step by StepTo make columns in Microsoft Word, place your cursor where the layout should begin, then choose Layout >…
- Excel Solver Answer Report: Create and Read ItTo create a Solver Answer Report in Excel, set up and solve your model, then select Answer in…
- Microsoft Excel Project Timeline Template: How to Build OneA Microsoft Excel project timeline template is a worksheet that lays out tasks against calendar dates, often using…
- How to Run Apps Script in Google Sheets: A Practical GuideTo run Apps Script in Google Sheets, open your spreadsheet, select Extensions > Apps Script, add and save…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.