The Gas Cylinder Sales Data Entry System in Excel is a macro-enabled workbook with a 7-field entry form, 4 VBA buttons, 4 live stat cards and 3 dropdown lists holding 28 values – 12 cylinder types, 8 payment statuses and 8 delivery statuses. Every saved order gets an automatic Record ID in the GCS-0001 series and an Entry TimeStamp, and the records table has formatted, dropdown-ready rows for 200 orders.
Plenty of small gas suppliers still keep orders in a paper book by the phone. It works until someone asks how many 19kg cylinders went out last week, or which delivered order is still unpaid, and the answer means turning pages. This walkthrough covers every sheet, exactly what each stat card counts – including one formula you will want to change on day one – and the limits worth knowing before you set it up.
What the Gas Cylinder Sales Data Entry System in Excel Does
It is an .xlsm workbook with four sheets. You type an order into a form, click Add, and the order drops into a records table with its Record ID and timestamp. There is nothing to install, nothing to sign into and no subscription. Four cards beside the form recount themselves as records are added, updated or deleted.
It is deliberately small, and it is a sales record only. It is not a safety register, not a cylinder-testing, filling or inspection log, and not a licensing or regulatory record. It does not track cylinder serial numbers, refills, empty returns, deposits or stock, and it is not billing or accounting software. The six sample customers, cylinder types and prices are fictional.
Key Features
- Seven-field form: Order Date, Customer Name, Cylinder Type, Quantity, Unit Price, Payment Status and Delivery Status.
- Four VBA buttons: Add, Update, Delete (with a confirmation that shows the Record ID) and Reset.
- Stable Record IDs: the next ID is one more than the highest in the table, so a deleted number is never reused.
- Double-click to edit: the row loads into the form and the macro remembers its ID, so Update finds it wherever it sits.
- Two status columns: payment and delivery are tracked separately, so an order can be Delivered but still Unpaid.
- Dropdowns in two places: on the form and on table rows 15-214, all reading the Setting sheet.
- Linked-picture stat cards: the cards on Data Entry are pictures of formula cards on Setting, so restyling one restyles both.
- Unlocked: no sheet protection and no VBA password.
Sheets Explanation
Data Entry sheet
The form, the four buttons and the four stat cards sit across the top. Below them the table holds S.No., Record ID, Order Date, Customer Name, Cylinder Type, Quantity, Unit Price, Payment Status, Delivery Status and Entry TimeStamp. With the samples loaded the cards read 6 orders, $529 revenue, 3 paid orders and 3 delivered orders.
Setting sheet
Three lists drive every dropdown: Cylinder Type List (5kg LPG Domestic, 14.2kg LPG Domestic, 19kg LPG Commercial, 47.5kg LPG Commercial, Oxygen Medical, Oxygen Industrial, Nitrogen Industrial, Argon Welding, CO2 Beverage, Acetylene Welding, Helium Balloon, Propane 45kg), Payment Status List (Paid, Unpaid, Partial, Pending, Overdue, Refunded, On Hold, Cancelled) and Delivery Status List (Scheduled, In Transit, Out for Delivery, Delivered, Pending, Delayed, Returned, Cancelled). The cylinder names are only sample labels – rename them to match what you sell. The four formula cards live here too.
How To Use sheet
Short instructions for entering, updating and deleting records, how the stat cards update, how to edit the lists and how to enable the macros on first open.
What the Four Stat Cards Count
These are the real formulas on the Setting sheet, read from the workbook itself:
| Card | Formula | What it means |
|---|---|---|
| Total Orders | COUNTA('Data Entry'!$C$15:$C$1048576) | Counts the Order Date column. Add refuses a blank Order Date, so this matches the orders added through the form. |
| Total Revenue | SUM('Data Entry'!$G$15:$G$1048576) | Adds the Unit Price column only – Quantity is ignored – for every order whatever its status. |
| Paid Orders | COUNTIF('Data Entry'!$H$15:$H$1048576,"Paid") | Rows whose Payment Status is exactly Paid. |
| Delivered Orders | COUNTIF('Data Entry'!$I$15:$I$1048576,"Delivered") | Rows whose Delivery Status is exactly Delivered. |
Total Revenue ignores Quantity. Column G is Unit Price, so the sample order for 10 Oxygen Medical cylinders at $120.00 counts as $120, not $1,200. The card shows $529 (exactly $529.25) while the six orders are worth $2,572.75. Replace Setting!I13 with this SUMPRODUCT formula for true order value:
=SUMPRODUCT('Data Entry'!$F$15:$F$5000,'Data Entry'!$G$15:$G$5000)It is written-up value, not cash collected. There is no status filter, so Unpaid, Partial, Pending, Refunded and Cancelled orders all count. On the samples only $512.75 of the $2,572.75 sits on Paid rows. For paid revenue only, use:
=SUMPRODUCT(('Data Entry'!$H$15:$H$5000="Paid")*'Data Entry'!$F$15:$F$5000*'Data Entry'!$G$15:$G$5000)One status per list is carded. Paid Orders covers 1 of 8 payment statuses and Delivered Orders 1 of 8 delivery statuses. Add =COUNTIF('Data Entry'!$H$15:$H$1048576,"Unpaid") or =COUNTIF('Data Entry'!$I$15:$I$1048576,"In Transit") as extra cards if those matter to you.
The lists do not grow on their own. How To Use says every dropdown updates when you add rows, but the named ranges are fixed – Cylinder_TypeList is Setting!$A$3:$A$14, Payment_StatusList $C$3:$C$10, Delivery_StatusList $E$3:$E$10 – and all three are full. Insert a row inside a list to add a value, or redefine the name as =OFFSET(Setting!$A$3,0,0,COUNTA(Setting!$A$3:$A$100),1).
Two input quirks. If the Order Date box holds text that is not a date, the macro quietly saves today’s date. Quantity and Unit Price are not checked for numbers, so a typo there flows straight into the cards.
Excel Workbook vs. Google Sheets Log vs. Gas Distribution Software – Feature Comparison
| Feature | Gas Cylinder Sales workbook (Excel) | Home-made Google Sheets log | Gas agency / distribution software |
|---|---|---|---|
| Cost | $6.99 one-time (regular $11.99) | Free, but you build it | Recurring monthly subscription |
| Platform | Excel for Windows desktop | Any browser | Vendor app or browser |
| Setup time | About 10 minutes | Hours of design work | Days of onboarding |
| Entry form with Add / Update / Delete | Yes, VBA | No | Yes |
| Separate payment and delivery status | Yes | Only if you build it | Yes |
| Real-time team collaboration | No | Yes | Yes |
| Mobile access | No | Yes | Usually |
| Serial, refill, deposit and stock tracking | No | No | Usually |
| Year-1 cost at 5 users | One purchase per user, no renewal | Free | Subscription x 12 months |
For a small gas supplier that wants a searchable order register without paying for distribution software it will not fully use, this workbook sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Small LPG and industrial gas resellers recording each order, the cylinder type, the quantity and whether it has been paid
- Suppliers delivering cylinders who need to see which orders are scheduled, in transit or delivered
- Owners comfortable in Excel who want a file they fully control
Not a fit if:
- You need a safety, cylinder-testing, inspection, filling, licensing or regulatory register – this workbook makes no such claim
- You need cylinder serial numbers, refills, empty returns, deposits, stock levels, invoices or accounting
- Your team works on Mac, in a browser or in Google Sheets, or needs several people editing at once
Real-World Use Cases
Ravi runs a small LPG reseller. He logs each doorstep order for 14.2kg domestic cylinders as Scheduled, moves it to Delivered when the van returns, and filters Payment Status for Unpaid before the weekly collection round.
A welding gas counter keeps Argon Welding and Acetylene Welding at the top of the Cylinder Type list, filters the table by type at month end, and adds a Partial card so part-paid workshop accounts are never missed.
A party supply shop uses the register for Helium Balloon cylinder orders, switches Total Revenue to the SUMPRODUCT formula so the card reflects quantity, and adds an Out for Delivery card for busy weekends.
Advantages
- One purchase, no subscription and no per-user fee.
- Ten minutes from download to the first real order.
- Update and Delete work by Record ID, so sorting or filtering the table does not break them.
- Payment and delivery are separate, which matches how cylinder orders really move.
- Everything is visible and editable – formulas, lists and macro.
Opportunities for Improvement
- A quantity-weighted Total Revenue card, and a paid-only revenue card beside it.
- A line-total column (Quantity x Unit Price) in the table itself.
- Cards for Unpaid, Partial and In Transit orders.
- Self-extending dropdown lists to match what How To Use promises, and number checks on Quantity and Unit Price.
Best Practices
- Always add orders through the form so every row gets a Record ID; rows typed straight into the table cannot be updated or deleted by the buttons.
- Rewrite the Cylinder Type list before you enter real orders, inserting rows inside the block.
- Type Order Date as a real date – text is replaced by today’s date.
- Keep a dated backup and store the file on a secured PC, because it holds customer names; the in-table dropdowns cover rows 15-214 and Delete removes the whole worksheet row.
Macros and Compatibility
Macros must be enabled and Windows desktop Excel is required. On first open click Enable Content; Microsoft’s guide to enabling macros in Microsoft 365 files covers the security bar, and its note on macros from the internet being blocked explains the Unblock checkbox.
Explore Relevant Templates
If you like this format, the Optical Shop Sales Data Entry System in Excel and the Bakery Sales Register Data Entry System in Excel apply the same form-and-cards build to other counters, while the Furniture Sales Register and Electronics Sales Log walkthroughs cover higher-value retail orders.
In the store, the Oil and Gas Dashboard in Google Sheets and the Petroleum Industry KPI Scorecard in Google Sheets suit businesses that have outgrown a simple order register.
Frequently Asked Questions
What does the Gas Cylinder Sales Data Entry System in Excel record?
One row per cylinder order: Order Date, Customer Name, Cylinder Type, Quantity, Unit Price, Payment Status and Delivery Status, plus an automatic Record ID and Entry TimeStamp. Four cards show total orders, total revenue, paid orders and delivered orders.
How long does setup take?
About ten minutes. Enable macros, replace the sample cylinder types on the Setting sheet, delete the six sample rows and start adding orders. Nothing needs installing.
Do I need to know VBA?
No. The Add, Update, Delete and Reset buttons are already wired. VBA only matters if you want to change what a button writes, such as adding a line-total column.
Why does Total Revenue look too low?
The card adds Unit Price without multiplying by Quantity. On the samples it shows $529 against $2,572.75 of real order value. Swap in the SUMPRODUCT formula above.
Is it a cylinder safety or compliance register?
No. It is a sales record only, with no safety, cylinder-testing, inspection, licensing or regulatory function. Keep any records your local rules require in a system built for that purpose.
How does it compare to gas distribution software?
Distribution software handles serials, refills, deposits, stock and invoices for a monthly fee. This is a one-time $6.99 Excel file that records what was ordered, by whom, for how much, and where payment and delivery stand.
About the Author
Built by PK – Microsoft Certified Professional with 15+ years of Excel, Google Sheets and Power BI experience. Founder of NextGenTemplates, reaching 300K+ subscribers across YouTube channels. Every template is hand-built and tested before release.
Conclusion
This workbook turns a paper order book into a table you can sort and filter, with four numbers on top that keep themselves current. Change the Total Revenue formula to the SUMPRODUCT version, know that each status card counts one value out of eight, and it will not surprise you.
Click here to Purchase the Gas Cylinder Sales Data Entry System in Excel – $11.99, currently $6.99.
✅ Instant download · One-time payment · No subscription
Last updated: September 2026
Watch more Excel tutorials at Youtube.com/@PKAnExcelExpert
