Excel Reconciliation13 min read

TDS Reconciliation in Excel: Ledger, Challans and 26AS

Q: How do I reconcile TDS in Excel?

Start by deciding which side you are reconciling. As a deductor, you reconcile the TDS your books say you deducted against the challans you deposited and the quarterly return you filed, section by section and PAN by PAN. As a deductee, you reconcile the TDS shown in your Form 26AS against the TDS receivable sitting in your books. Both are done the same way in Excel — build a key, match, and explain every difference in a category.

Q: Why does the TDS in my books not match Form 26AS?

Four reasons cover most of it: the deductor has not yet filed the return for that quarter, so the entry has not reached your 26AS; your PAN was recorded incorrectly by the deductor; the amount was deducted but never deposited, or deposited without the deductee details; or a timing difference where the payment and the deduction fall in different financial years. Check the filing status for the quarter before treating anything as a real mismatch.

Q: What is the difference between Form 26AS and AIS?

Both are income tax department statements, and 26AS is generated from the information reported by deductors. The Annual Information Statement presents a wider set of reported information, including entries that are not TDS. For a TDS reconciliation, Part A of 26AS is the section you work with, because it carries the deductor, the section, the amount paid and the TDS deducted.

Q: How do I reconcile one challan that covers many deductees?

Match at the challan level, not the deductee level. Tag each deduction in your books with the challan it was deposited under, use SUMIFS to total the deductions per challan, and compare that total to the challan amount. Only the challans that fail to balance need deductee-by-deductee investigation.

Q: What causes a short deduction or short payment notice?

The usual causes are a wrong or missing PAN — which results in deduction at a higher rate — a section applied incorrectly to a payment, a threshold treated as having been crossed when it was not, and a total in the return that does not agree with the challans. All four are exactly the things a monthly reconciliation catches before the return is filed, rather than after a notice arrives.

Q: How often should TDS be reconciled?

Monthly, before the deposit, and again before each quarterly return is filed. The monthly pass is about catching a PAN error or a wrong section while it can still be corrected in the ledger. The quarterly pass is the control that confirms the return you are about to file agrees with your books and your challans.

TDS reconciliation fails in two places: the deductor who cannot tie the return back to the challans, and the deductee whose 26AS does not agree with the receivable in their books. Both are Excel problems, and both have the same shape.

Short answer

TDS reconciliation in Excel means matching two sets of records so that every rupee deducted is accounted for. A deductor matches the TDS ledger in their books against the challans deposited and the quarterly return filed, section by section. A deductee matches the entries in their Form 26AS Part A against the TDS receivable recorded in their books. In both cases you build a key, match on it, and explain every difference by category rather than by hunting for one missing row.

This is a technique guide, not tax advice

Rates, thresholds, due dates and the contents of the department's statements change from time to time. Everything below describes how to carry out the reconciliation in Excel. For what applies to a specific payment in a specific period, check the current provisions and the portal.

Two reconciliations, one method

	Deductor side	Deductee side
Who	You deducted tax on payments you made	Someone deducted tax from what they paid you
Your record	TDS payable ledger by party, section and month	TDS receivable ledger by customer, section and month
Outside record	Challans deposited, and the quarterly return filed	Form 26AS Part A, and the TDS certificates received
The question	Has everything I deducted actually been deposited and reported?	Has everything deducted from me been credited against my PAN?
The key	Party PAN plus section plus month	Deductor TAN plus section plus amount plus date

This article covers both, because a CA firm usually sees both in the same week and the spreadsheet work is nearly identical.

Deductor side: the three files

File	Where it comes from	What it is used for
TDS ledger extract	Tally or your ERP — TDS payable grouped by party and section	What your books say you deducted
Challan download	The department's challan status view, or the bank's challan counterfoil data	What you actually deposited, with BSR code, challan serial number, date and amount
Return data	The return preparation file for the quarter, or the filed return's deductee annexure	What you reported — deductee PAN, section, amount paid and TDS

Get all three for the same quarter. Reconciling a ledger to 31 March against a return for the March quarter, when the ledger is extracted in April, produces differences that are purely cut-off.

Step 1: build the deductee key

The key on the books side is the party PAN and the section, joined. Both sides must use the identical formula.

1Normalise the PAN — force upper case and strip spaces: =UPPER(TRIM(B2)). A PAN entered with a trailing space is one of the most common causes of a phantom mismatch.
2Normalise the section: vendors write 194C, 194-C, 194c. Standardise to 194C.
3Join them: =UPPER(TRIM(B2))&"|"&UPPER(TRIM(C2))

PAN is where most TDS problems start

A deduction made without a valid PAN, or with a PAN that does not belong to the deductee, is not credited to anyone. It will not appear in the deductee's 26AS, and it can affect the rate applied to the payment. Validate the PAN format before the payment is made rather than at the end of the quarter, when the correction is far more expensive.

Step 2: match the ledger to the return deductee-wise

Column	Formula shape	What it gives you
TDS in books for this key	`=SUMIFS(Books!$F:$F, Books!$H:$H, $A2)`	Total TDS the ledger shows for this PAN and section
Difference	`=C2-D2`	Books minus return, so the direction is visible
Status	`=IF(COUNTIF(Return!$H:$H,$A2)=0,"Missing in return",IF(ABS(E2)<=1,"Matched","Amount difference"))`	A provisional category per row

Key column H on both sheets. Adjust column letters to match your files.

Step 3: match the challans — and this is where a lookup fails

A single challan usually covers many deductions, and a single deduction never equals a challan amount. This is the one-to-many case, and it is the reason a plain lookup produces a list of “mismatches” that are in fact perfectly correct payments.

Tag each deduction in your ledger with the challan reference it was deposited under, then total the ledger by challan and compare that total to the challan itself.

Challan	BSR code	Date	Challan amount (₹)	Sum of deductions tagged (₹)	Difference (₹)
00041	0510308	06-08-2025	84,340	84,340	0
00042	0510308	06-09-2025	61,205	61,205	0
00043	0510308	07-10-2025	92,760	92,760	0
00044	0510308	07-11-2025	1,14,090	96,730	17,360

Only the last challan needs deductee-level investigation — three out of four are proven correct in one pass.

Group matching removes most of the noise

Reconciling challan totals before looking at individual deductees turns a sheet of hundreds of apparent exceptions into a short list. Your ledger is almost always right at the challan level and wrong at the deductee level, or the other way round — doing the total first tells you which.

Step 4: categorise the exceptions

Category	What it means	Where the fix goes
Missing in return	In your ledger, not reported in the quarterly return	Amend the return, or the deduction was reported in a different quarter
Missing in books	In the return, not in your ledger	A deduction booked to the wrong party or the wrong section
Amount difference	Same PAN and section, different TDS	Usually a rate difference or a threshold treated differently
PAN error	A record that matches on name but not on the key	Correct the PAN in the ledger and in the next return
Challan not fully allocated	The challan total does not equal the tagged deductions	An unallocated balance, or a deduction tagged to the wrong challan
Lower or nil deduction certificate	A payment where the rate was reduced by certificate	Attach the certificate reference to the row, or the amount will look wrong forever

Step 5: reconcile the return to the challans

The final control is a simple one: the total TDS reported in the return must equal the total of the challans you have set off against it. This is a two-line check, and it catches the class of error where the return is internally consistent but the tax was never actually deposited.

Control	Books (₹)	Return (₹)	Challans (₹)	Variance (₹)
194C — contract payments	2,41,860	2,41,860	2,41,860	0
194J — professional fees	1,18,400	1,18,400	1,18,400	0
194I — rent	96,000	96,000	79,000	17,000
194Q — purchase of goods	42,300	42,300	42,300	0
Total	4,98,560	4,98,560	4,81,560	17,000

A section-wise control like this is what a reviewer or an auditor asks for first.

Deductee side: 26AS against TDS receivable

If you are the one whose tax was deducted, the reconciliation is between Part A of your Form 26AS and the TDS receivable in your books. Here the key cannot be an invoice number, because 26AS does not carry one.

26AS Part A carries	Your books carry	How to match
Deductor name and TAN	Customer name and their TAN	Normalise the TAN exactly, then fall back to the name
Section	Section, if you track it	Match where available; otherwise match on amount and party
Amount paid or credited	Invoice value or receipt value	Match on TDS amount first, then on the paid amount
TDS deducted	TDS receivable booked	The primary amount to compare
Date of payment or credit	Invoice date or receipt date	Allow a date window rather than exact equality

Match on the TDS amount, not the invoice

Because 26AS has no invoice reference, matching invoice to entry will never work. Match on the customer plus the TDS amount, and accept that a customer who deducts on several invoices in one month will appear as one entry on their side and several on yours. Total your side by customer and month before comparing.

The timing issue you cannot avoid

26AS updates when the deductor files. If they file their quarterly return in the last week allowed, your 26AS shows nothing until then. An entry missing from 26AS in the first week after quarter end is not a mismatch.
The financial year may not agree. A payment in March deducted and deposited in April can appear in a different year on the two sides. Track these separately rather than forcing them to match.
Certificates arrive late. Record the expected TDS when you book the invoice so the receivable exists to reconcile against. If you book receivables only when the certificate arrives, your reconciliation is always missing a period.

A month-end TDS control sheet

Extract the deduction ledger for the month

Party, PAN, section, amount paid, TDS deducted, date. One row per deduction event.

Extract the challans deposited for the month

BSR code, challan serial, date, amount, section. Keep the section mapping from the challan.

Tag every deduction to its challan

This is the only manual step that genuinely matters. Without the tag, the rest of the reconciliation cannot be done in a group.

Total by challan and compare

Any challan where the tag total does not equal the deposited amount is the small list you actually investigate.

Check PAN validity on every new deductee

Format check plus a look at whether the PAN already exists elsewhere in the ledger under a different name.

Carry the control into the quarterly return

Before filing, confirm the section-wise totals in the return agree with the ledger and with the challans set off. That single comparison is the control.

What breaks in Excel

The challan-to-deductee tag is manual. Nothing in the export gives it to you, and if the tagging is done by a different person from the one who filed the return, the two halves do not agree.
PAN errors look like missing data. A single wrong character produces a record that matches nothing on either side, which is far harder to spot than a missing row.
Section changes break the key. When a payment moves from one section to another between quarters, the same party appears under two keys and neither totals correctly.
Nothing records why an exception was accepted. The next quarter, somebody re-investigates a difference that was resolved and documented only in a conversation.
Multiple financial years in one file. A single sheet holding two years of deductions, without a year column in the key, silently double-counts.

Where automation genuinely helps, and where it does not

The judgement in TDS reconciliation — whether a payment falls in a section, whether a threshold has been crossed, what a lower deduction certificate permits — is not something a tool should decide for you. What a tool does well is the mechanical part: normalising PANs, grouping deductions under their challan, and applying a tolerance so that the ninety correct rows do not hide the four that are wrong.

If your reconciliation is one company and one quarter at a time, a well-built control sheet like the one above is the right answer. The Piloteq Automate reconciliation workflow is for when the volume of records makes the manual tagging the bottleneck — it applies the same group and tolerance rules as a saved set, and returns matched, unmatched and amount-difference records as separate result sets with the reason written next to each one.

If you would rather not build this by hand

Tie the TDS ledger to the challans without rebuilding the sheet

Piloteq Automate matches your deduction ledger against the challan file and the return data on the rules you set, and writes the reason for every record. One challan covering many deductees is handled as a group, so a correctly deposited amount is never reported as a mismatch.

✓Group matching for one challan against many deductions
✓Exact and tolerance matching on amounts
✓Duplicate detection for repeat challan entries
✓Difference analysis with the delta per record

₹1,499 · Single PC License · 12-month license

See how Piloteq Automate handles reconciliation →

Frequently asked questions

How do I reconcile TDS in Excel?+