Excel HVAC Refrigerant Usage Log for Cylinder Balances

How this template can help

• Follow each cylinder from its opening quantity through non-voided issues and returns.
• Connect refrigerant usage to a technician, customer, job number, equipment record, and responsibility.
• Calculate usage cost from pounds used and the editable unit cost assigned to the cylinder.
• Focus office review on draft records, entry checks, negative balances, and low-balance cylinders.

What the workbook contains

How to get started

1. Set the reporting controls and lists

Replace demo data on the Assumptions sheet with the report dates, low-balance threshold, quantity variance tolerance, entry limits, refrigerants, technicians, and status choices used by your company. The supplied names and records are fictional examples.

2. Create the cylinder records

Enter cylinders first on the Cylinders sheet. Complete Cylinder ID, Refrigerant, Description, Opening Qty, Unit Cost, and Cylinder Status. Current Qty, Remaining, Unit, and Check are calculated fields, so preserve those formulas.

3. Record each movement

Enter transactions in order on Usage Log rows 6 through 105. Select the cylinder, then enter the date, technician, job details, Qty Issued, Qty Used, Qty Returned, responsibility, job status, and record status. Quantities are recorded in pounds. Enter 0 rather than leaving Qty Returned blank when nothing came back.

4. Review checks and totals

Review before relying on totals. Resolve messages in the Usage Log Check column and the Cylinders Check column, then set the desired report period on Assumptions and inspect Summary. A calculated check is a prompt for staff investigation, not confirmation of the physical cylinder quantity.

How cylinder quantities move through the log

Qty Issued drives the balance. For each Cylinder ID, the workbook calculates Current Qty as Opening Qty minus all non-voided quantities issued, plus all non-voided quantities returned. Qty Used is not subtracted a second time.

The quantity check compares Qty Issued with Qty Used plus Qty Returned, allowing for the Quantity Variance Tolerance on Assumptions. Entering records in transaction order matters because Balance After Entry is cumulative through the current row. If an older transaction is entered below newer activity, the final cylinder total may still calculate, but the row-by-row balance will not represent the actual sequence.

Using cost, responsibility, and record status

The selected Cylinder ID brings its Refrigerant and Unit Cost into Usage Log. Usage Cost equals Qty Used multiplied by Unit Cost. For example, 4.00 lb used at $18.25 per lb produces a $73.00 usage cost.

A blank Usage Cost can mean the row is voided or one of the required numeric inputs is unavailable. A $0.00 result can occur when Qty Used or Unit Cost is zero and should be reviewed rather than assumed to mean no cost.

Draft is not excluded from balance and period calculations. The formulas exclude only rows marked Voided. Responsibility and Job Status provide operational context, but they do not change the cost formula.

Worked refrigerant usage scenario

Fictional example: Add cylinder FICTION-CYL-21 as R-410A with a 24.00 lb opening quantity, a $15.20 unit cost, and an In Service status. On 09/03/2026, fictional technician Jamie Reed issues 6.00 lb for job DEMO-SVC-210, uses 5.50 lb, returns 0.50 lb, and marks the record Reviewed.

The quantities reconcile because 6.00 lb issued equals 5.50 lb used plus 0.50 lb returned. Usage Cost is $83.60. Balance After Entry is 18.50 lb: 24.00 − 6.00 + 0.50. The Cylinders sheet reports about 77.1% remaining.

With the Low Balance Threshold set to 20.0%, this calculated balance is above the threshold. The next decision is to reconcile the entry with the job documentation and decide whether 18.50 lb is adequate for upcoming dispatch needs. If 09/03/2026 falls within the selected report period, Summary adds one transaction, 5.50 lb used, and $83.60 to the applicable totals.

Reading the Summary indicators correctly

Transactions in Period, Refrigerant Used, Usage Cost, and Average Used per Transaction use the Report Start and Report End dates and exclude Voided rows. The refrigerant totals feeding the chart follow the same date range and listed refrigerants.

Records Needing Check, Draft Records, Active Cylinders, and Low-Balance Cylinders are not restricted to the report dates. They scan their applicable prepared ranges, so these counts can include records outside the displayed period.

If there are no qualifying transactions, the transaction, usage, and cost indicators can show zero. Average Used per Transaction remains blank instead of dividing by zero. Confirm the report dates, Cylinder ID, and Record Status before treating a blank average as missing workbook logic.

Common entry errors and practical corrections

Resolve the cause of each message rather than overwriting a formula cell.

• CHECK REQUIRED INPUTS: Add the missing date, technician, or record status.
• CHECK CYLINDER: Select a Cylinder ID that exists on Cylinders. Check spelling and avoid duplicate IDs.
• CHECK QUANTITIES: Complete all three quantity fields and confirm that issued equals used plus returned within the configured tolerance.
• CHECK NEGATIVE BALANCE: Review the opening quantity, transaction order, issued amount, returns, duplicate entries, and voided status.
• CHECK LOW BALANCE: Compare the calculated remaining percentage with the threshold and decide whether the cylinder needs operational attention.
• CHECK OPENING QTY: Enter a valid positive opening quantity for the cylinder.

These messages identify data conditions inside the XLSX. They do not verify cylinder weight, refrigerant handling, job scope, or technician work.

Workbook limits and record protection

The prepared calculation ranges run from row 6 through row 105, providing 100 usage rows and 100 cylinder rows. The sheets are not configured as Excel tables. Entries added below row 105 will not automatically join the verified formulas, filters, validations, or summary ranges.

Cylinder IDs do not have a verified uniqueness check. A duplicate ID can cause lookups to return the first matching cylinder while transaction totals combine activity under the shared ID. Assign one distinct ID to each cylinder and review duplicates before use.

This workbook does not provide live inventory monitoring, automatic messages, payment processing, regulatory filing, or version history. Save identified copies when preserving a reporting period or approved record set.

Customer names, employee names, job details, equipment information, pricing, and cylinder identifiers can be sensitive. Store the file in an access-controlled location, limit distribution, and remove fictional examples before entering operational records.

Comparison with paper and field-service systems

Choose this workbook when one controlled Excel file and a 100-entry prepared range fit the process. Keep a paper log only when manual recording and reconciliation are acceptable. Evaluate a field-service system when the company needs configured multiuser workflows, mobile access, integrations, or automated communication that this XLSX does not provide.

Frequently asked questions