.png)
Free ABA Authorization Tracking Spreadsheet: Units, CPT Codes and Renewal Deadlines
Nobody lets an authorization expire on purpose. It happens because the auth lives in an email thread, the units live in the EHR, the renewal date lives in someone's head, and no single view puts all three together. By the time anyone checks, the units are gone or the renewal window has closed, and you're writing off sessions your team already delivered.
An ABA authorization tracking spreadsheet fixes the visibility problem, at least until your caseload outgrows it.
The free template below tracks approved units by CPT code, calculates remaining units and burn rate, flags renewal deadlines against a lead time you set, and tells you every Monday which authorizations need work this week.
Download the free ABA authorization tracking spreadsheet
What an ABA authorization tracking spreadsheet actually needs
Most free templates you'll find are session data sheets wearing a billing label. They track behavior, not coverage. An authorization tracker needs different fields, and it needs them at the line level.
Each row should hold the client, the payer, the auth number, one CPT code, the approved units for that code, the start and end dates, units used to date, units remaining, the renewal due date, an owner, and a next action. If the row can't tell you who owns it and what happens next, it's a record, not a tracker.
The template includes five tabs: a summary view, the authorization tracker itself, a CPT reference, a weekly unit log that feeds the tracker automatically, and a settings block where you set your own thresholds.
Track ABA authorization units by CPT code, not by client
This is the single most common way a homemade authorization spreadsheet goes wrong.
Payers don't approve a block of hours. They approve units per code. A single auth might cover 2,080 units of 97153, 64 units of 97155, and 32 units of 97156, each with its own cap. Track that auth as one row with one total and you'll run out of 97155 units in month two while 97153 looks fine, and you won't see it until the denial comes back.
All ten ABA codes in the 2026 set are timed and billed in 15-minute units. That includes 97151 through 97158 plus the two Category III codes, 0362T and 0373T. One hour of direct therapy is four units. The template gives every CPT code its own row, and the CPT reference tab fills in the descriptor when you pick the code so nobody has to remember whether 97157 is the group caregiver code.
How to calculate authorization burn rate before it burns you
Remaining units on their own don't tell you anything. Nineteen hundred units left sounds comfortable. Spread across fourteen weeks with a schedule that only uses eighty units a week, you'll finish the authorization period with over seven hundred units unused, which is its own problem at renewal when the payer looks at utilization.
The template runs both directions of that math. It calculates how many units per week you'd need to use the authorization fully, compares that to your scheduled units per week, and projects whether you'll finish over or under. Rows flag as "Overbooked vs units" when the schedule will exhaust the auth early, and as "High utilization" once you cross the threshold you set, which defaults to 80 percent.
Underuse matters as much as overuse. A pattern of using half the approved units gives the payer a reason to approve fewer next time, and it usually points at a scheduling or staffing gap rather than a clinical one.
Set ABA authorization renewal deadlines by payer turnaround
Working backward from the end date is where most teams get caught. The renewal deadline isn't the expiration date. It's the expiration date minus the progress report, minus the BCBA's review time, minus the payer's turnaround.
Set that lead time once in the settings tab and every row calculates its own renewal packet due date. Forty-five days is a reasonable starting point, but your slowest payer should set the number, not your fastest one. If one Medicaid MCO routinely takes six weeks, build for six weeks.
The same logic applies upstream. An authorization is only useful if the provider delivering the service is credentialed and billable with that payer, which is why credentialing delays quietly cost clinics revenue even when the auth is perfectly tracked.
The 2027 CPT change your ABA authorization tracker will miss
Here's something a static spreadsheet won't tell you. The adaptive behavior code set changes on January 1, 2027.
Six new Category I codes join the set: 97148, 97149, 97159, 97160, 97173, and 97180. Codes 97151 through 97158 all get revised descriptors, with 97155 seeing the biggest change as it shifts toward direction of technician and analysis. Both Category III codes, 0362T and 0373T, are deleted. The ABA Coding Coalition confirmed the effective date, and the AMA published the 2027 code set in September 2026.
Any authorization that spans January 1 needs a mapping check before the first date of service in 2027. If your practice management system has 0362T hardcoded into an active auth, that's a denial with a date on it. The CPT reference tab in the template lists every code with its 2027 status so you can sort your active auths and see which ones cross the line.
When an ABA authorization tracking spreadsheet stops working
A spreadsheet is the right tool at a certain size. Twenty active authorizations, one person updating it, everyone in the same office. It works.
It stops working in fairly predictable ways. The file gets copied and two versions circulate. The person who built it leaves. Intake adds a client and nobody tells billing. A parent changes insurance and the new plan's units never make it into the tracker, so the intake handoff breaks quietly. Nobody gets an alert, because spreadsheets don't send alerts. They wait to be opened.
The deeper issue is that authorization tracking isn't a standalone job. It sits between the referral pipeline that started the client, the benefits verification that established coverage, and the billing work that happens after the session. When those live in separate files, a change in one never reaches the others. That's the pattern behind most preventable ABA denials, and it's worth understanding how authorization fits into the full revenue cycle before you decide whether a spreadsheet is still enough.
Frequently asked questions
What should an ABA authorization tracking spreadsheet include?
At minimum: client, payer, authorization number, CPT code, approved units, units used, units remaining, start date, end date, renewal due date, owner, and next action. Track one row per CPT code rather than one row per authorization, because payers approve units per code.
How many units is one hour of ABA therapy?
Four units. All ABA CPT codes in the 97151 to 97158 range are billed in 15-minute increments, so one hour equals four units. Payer policy governs rounding on partial units, so check your contracts.
How far in advance should you start an ABA authorization renewal?
Work backward from the payer's turnaround time, not from the expiration date. Forty-five days before the end date is a common starting point, but you need to account for the progress report, clinical review, and the payer's own processing window. Your slowest payer should set the lead time.
Is a spreadsheet enough for ABA authorization tracking?
For a small caseload with one person maintaining it, yes. It stops being enough once multiple people need to update it, once authorization changes have to reach intake and billing, or once you need alerts rather than a file somebody remembers to open.
Are ABA CPT codes changing?
Yes. The adaptive behavior code set changes on January 1, 2027, with six new Category I codes, revised descriptors for 97151 through 97158, and deletion of 0362T and 0373T. Authorizations spanning that date need a code mapping check.
Stop tracking authorizations in a file nobody opens
Download the spreadsheet and use it. It'll give you a clear view of units, codes, and renewal dates, and for a lot of clinics that's a real improvement over what's there now.
When the spreadsheet starts lagging behind your caseload, the problem isn't the spreadsheet. It's that authorization status, ownership, and follow-up are sitting apart from the intake, credentialing, and billing work they depend on.
Explore authorization tracking in SparkzABA and see how unit counts, renewal deadlines, owners, and next actions stay visible across your team before a lapse turns into a denial.
Comments