Build a Prior Authorization Tracker with Smart Alerts in Google Sheets

What This Does

Creates a live prior authorization tracking spreadsheet with automatic overdue flags — when a PA is taking longer than expected, the row turns red so you never miss a follow-up. Prevents authorization-related care delays and revenue holds.

Before You Start

• Google account (free)
• List of current open PA requests (can type them in manually to start)
• 15 minutes to set up the template once — then it's ongoing maintenance

Steps

1. Create a new Google Sheet

Go to sheets.google.com → click the + to create a new blank spreadsheet. Name it "PA Tracker" or similar.

2. Set up your columns

In Row 1, type these headers in columns A through J:

• A: Patient Name (or ID)
• B: Provider
• C: Payer
• D: Procedure Requested
• E: Diagnosis
• F: Date Submitted
• G: Expected Response Date (typically F + 14 days)
• H: Status (Pending / Approved / Denied / Info Requested)
• I: Days Pending
• J: Notes

3. Add the "Expected Response Date" formula

Click cell G2 and type:

=IF(F2="","",F2+14)

This automatically calculates the expected response date as 14 days from submission. Drag this formula down for all rows.

Tweak: Change +14 to match your payer's typical turnaround. Medicare is often 72 hours; commercial payers vary 5-14 days.

4. Add the "Days Pending" formula

Click cell I2 and type:

=IF(F2="","",IF(H2="Pending",TODAY()-F2,""))

This counts how many days the PA has been pending. Drag down for all rows.

What you should see: Pending PAs show their current age in days. Approved/Denied PAs show blank.

5. Add overdue conditional formatting

Select your entire I column → click Format → Conditional Formatting:

• Condition: "Greater than" → enter 10 (or your threshold)
• Formatting style: Choose red background

Now any PA pending more than 10 days turns red automatically.

Add a second rule for yellow at 7 days for a warning stage.

6. Freeze the header row

Go to View → Freeze → 1 row so column headers stay visible when you scroll down.

Real Example

Scenario: You're managing 45 open PA requests. Without tracking, three slipped past their response window — the procedures got scheduled but authorization was never confirmed, leading to claim denials.

What happens with this tracker: You open the sheet every morning and immediately see 4 PAs highlighted red (over 10 days pending). You call those 4 payers first, get 3 approved and discover 1 needs additional documentation. Zero missed authorizations for the week.

Tips

• Add a "Priority" column and use a dropdown (1/2/3) to manually flag urgent PAs
• Filter by "Status = Pending" to show only open items — go to Data → Create a filter
• Color-code the Status column too: green for Approved, red for Denied, yellow for Pending
• Share the sheet with the office manager or scheduling staff so they can see approval status without asking you

Tool interfaces change — if Conditional Formatting has moved, look for it in the Format menu.