Expert guide

Tax Season Client Document Tracker in Excel

Expected, received, missing: one view of every return, and one list per client to chase

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: build an Expected Documents table (one row per client per document, seeded from last year's return), mark each row Received with a date as documents arrive, calculate each client's completeness percentage, generate a missing-items list per client with TEXTJOIN for reminder emails, and route complete clients to a preparer queue. Log every reminder. We automate the intake and reminders as AI workflow automation for accounting firms.

Every tax season, accounting firms lose hundreds of hours not to preparing returns but to waiting for documents and working out what is still missing. A client sends four PDFs, then two more a week later, and the preparer opens the file three times to check. Clients get generic reminders and reply "I sent everything". A document tracker answers one question precisely for every client: what exactly do we still need?

The Structure

TableColumnsPurpose
tblClientsClientID, Name, Email, Preparer, ReturnType, DueDate, Extension (Y/N)Who and when
tblExpectedClientID, DocType, Issuer, Required (Y/N), Received (Y/N), ReceivedOn, NotesThe checklist, one row per document
tblRemindersClientID, SentOn, Channel, ItemsListedChase history
tblDocTypesDocType, Description for clientsPlain-language names in reminders

Seeding the Checklist From Last Year

Export last year's list of received documents (from your tax software or last year's tracker) and copy it into tblExpected for the new year with Received cleared. Then add items from client organisers: new employer, sold property, new brokerage account. Mark items that are optional or "if applicable" as Required = N so they do not block completeness.

Completeness and Status

On tblClients:
Required docs:   =COUNTIFS(tblExpected[ClientID], [@ClientID], tblExpected[Required], "Y")
Received docs:   =COUNTIFS(tblExpected[ClientID], [@ClientID], tblExpected[Required], "Y",
                           tblExpected[Received], "Y")
Complete %:      =IF([@[Required docs]] = 0, 0, [@[Received docs]] / [@[Required docs]])

Status:
=IFS([@[Complete %]] = 1, "Ready to prepare",
     [@[Complete %]] >= 0.75, "Nearly there",
     [@[Received docs]] > 0, "In progress",
     TRUE, "Nothing received")

Days to due date:  =[@DueDate] - TODAY()

The Missing-Items List per Client

Missing items as a bullet list for an email:
=TEXTJOIN(CHAR(10), TRUE,
  "• " & FILTER(
     XLOOKUP(tblExpected[DocType], tblDocTypes[DocType], tblDocTypes[ClientDescription])
       & IF(tblExpected[Issuer] <> "", " from " & tblExpected[Issuer], ""),
     (tblExpected[ClientID] = [@ClientID]) *
     (tblExpected[Required] = "Y") * (tblExpected[Received] <> "Y"),
     "Nothing outstanding"))

This produces lines such as "• Year-end wage statement from Riverside Health" and "• Mortgage interest statement from First County Bank". Client-friendly descriptions in tblDocTypes matter: clients do not know form numbers, but they recognise "the statement from your bank showing mortgage interest".

Reminder Cadence

StageTriggerAction
Kick-offSeason opensSend each client their full expected list
First chase10 days after kick-off, items missingEmail with only the missing items
Second chase10 days laterEmail plus text, mention the deadline
CallAfter two emails, or 30 days before due datePreparer or admin phones
Extension decisionAgreed date before due dateFile extension, set new target
Last reminder date:
=MAXIFS(tblReminders[SentOn], tblReminders[ClientID], [@ClientID])

Reminders sent:
=COUNTIFS(tblReminders[ClientID], [@ClientID])

Chase today:
=AND([@Status] <> "Ready to prepare",
     OR([@[Reminders sent]] = 0, TODAY() - [@[Last reminder date]] >= 10))

A VBA macro can create one Outlook email per client with the missing-items list in the body, saved as drafts for review, and log each one in tblReminders. The code pattern is in personalized emails from Excel.

Preparer Queues and Capacity

  • Each preparer's queue: clients Ready to prepare, sorted by due date.

  • Capacity view: ready returns per preparer versus hours available this week.

  • Bottleneck view: clients Nearly there for more than 14 days, often waiting on one item.

  • Extension pipeline: clients unlikely to be complete before the deadline, flagged early.

Where AI Speeds Up Intake

  • Classify uploads

    Identify the document type, issuer and tax year of each uploaded PDF and suggest which checklist row it satisfies.

  • Spot surprises

    Flag documents that were not expected, such as a new brokerage account, so the checklist and engagement are updated.

  • Read client emails

    Summarise messages like 'I don't have the HSA form, the account closed' into a note on the right row.

Client tax documents are confidential; use only approved, secure tools, and keep a preparer review on every classification. The extraction technique is covered in extracting PDF data with AI and the agent pattern for chasing in AI agents and Excel.

After the Season

Keep the year's tracker as next year's seed, and review which clients needed the most chasing: they may need an earlier start or a different engagement. Firms that also run monthly bookkeeping for clients can use the same expected-versus-received approach for month-end; see the month-end close checklist. Consolidating trackers from several preparers or offices is what Excel data consolidation handles.

FAQ

Tax document tracker FAQ

Checklists, chasing and AI intake for firms

The practical approach is a checklist per client: which documents are expected this year (based on last year's return and any changes), which have arrived, which are missing, and when the client was last chased. A tracker built from that checklist drives reminder emails and shows which returns are ready to prepare.

Start from last year's return: every income form, account statement and deduction schedule received last year is likely needed again. Add items from the client's organiser or questionnaire for life changes such as a new job, property sale or new dependant.

Send each client one message listing exactly what is still missing, not a generic reminder. Repeat on a fixed cadence, such as every 7 to 10 days, and escalate to a phone call after two or three attempts. The tracker records every reminder so nobody is chased twice in a day or forgotten.

AI document models can classify common document types such as wage statements, interest and dividend statements and brokerage summaries, and extract the issuer and tax year, which speeds up checking items off. A preparer still reviews every document, and client tax documents should only be processed with tools approved for confidential data.

Use practice management software if it already handles document requests well. Many smaller firms use Excel because their software's checklists are clumsy, or they need a single view across preparers. Keep client documents themselves in the firm's secure portal or document system; the tracker only records status.