Excel to BIR DAT File: Why Merged Cells and Hidden Rows Break Your Conversion

A merged cell in a RELIEF, SAWT, or QAP workbook only holds its value in the merge’s top-left cell — every other cell in that range reads as blank to a BIR DAT converter, which either drops a field or shifts every column after it out of position. A hidden row or column causes a related but different problem: hiding it only changes what you see on screen, not what’s physically in the worksheet, so it’s still exported — and still converted into a DAT record — unless it’s actually deleted or filtered out before conversion.

This guide covers why both mistakes happen, how to spot them before they reach the BIR’s Alphalist Data Entry and Validation Module, and a worked example of a hidden duplicate row that made it into a filing.

Why does a merged cell break the column alignment? #

A merge is a display feature, not a data feature — Excel stores the merged range’s value in only its top-left cell, and every other cell the merge visually spans is empty underneath. This is easy to miss because the merged block looks like one wide, filled cell on screen. A converter or script reading the worksheet row by row, column by column, doesn’t see what you see — it reads each column position independently, finds nothing in the cells the merge is hiding, and either leaves that field blank in the output record or, more disruptively, shifts every subsequent column one or more positions out of place for that row.

This most often happens in two places in a BIR workbook:

• A merged header row carrying a title like “RELIEF SLSP — Sales, Q2 2026” spanning several columns at the top of the sheet, left in place above the actual data headers instead of being removed before conversion.
• A merged “TOTAL” or subtotal row inserted partway down the data range — common when a workbook is adapted from a printed report layout rather than built as flat, one-row-per-transaction data from the start.

A BIR DAT record has a fixed field position for TIN, name, ATC, amount, and the other columns a given filing type requires. Once a merge shifts even one row’s fields out of position, that row’s data no longer lines up with what the Validation Module expects for that position — which is a different failure mode from a wrong value, and one that doesn’t show up just by looking at the spreadsheet.

Why doesn’t Excel just skip hidden rows automatically? #

Hiding a row or column in Excel is purely a display setting — the underlying data stays exactly where it was, and most save and export paths carry it straight through into the converted file. A row only drops out of a conversion if it was actually deleted, or if the export step specifically honors an active filter (not just a manually hidden row) at the moment of export. A manually hidden row — right-click, Hide Rows — survives a plain Save, most CSV exports, and most column-mapped DAT conversions, because none of those operations ask whether a row is currently visible before reading it.

This causes real problems in practice because hidden rows tend to accumulate for reasons that have nothing to do with the final filing:

• A draft row hidden instead of deleted after the preparer decided it was wrong, meaning to clean it up later and forgetting to.
• A row hidden to shorten a printed or on-screen view, with no intention of ever filing it, left in the same workbook that later gets converted.
• A duplicate entry hidden after being corrected elsewhere in the sheet, rather than removed — which is exactly the scenario in the worked example below.

Worked example: a hidden duplicate row in a RELIEF SLSP filing #

A bookkeeper preparing a Q3 2026 RELIEF SLSP Sales workbook spots that row 14 — a ₱85,000 sale to a repeat customer — was entered with the wrong invoice number, so she re-enters the corrected line as row 50 at the bottom of the sheet and hides row 14 rather than deleting it, planning to clean it up before the final save. The cleanup never happens. When the workbook is converted to a DAT file, both row 14 (with the wrong invoice number) and row 50 (the correction) come through as separate detail records, because conversion reads every row in the data range regardless of its visibility.

The resulting RELIEF SLSP file reports the same sale twice under two different invoice numbers, inflating the quarter’s declared sales by ₱85,000 and creating a mismatch against the sales figure on the taxpayer’s BIR Form 2550Q — exactly the kind of discrepancy that can trigger a BIR Letter Notice for a RELIEF-to-VAT-return mismatch. The fix isn’t re-hiding row 14 more carefully; it’s deleting row 14 outright once row 50 replaces it, then regenerating the DAT file from the corrected, 49-row dataset.

How to check your workbook before converting #

1. Select the full data range and unmerge everything. Home > Merge & Center (toggle off for any range still merged), or check each header and subtotal row individually if the sheet is large.
2. Use Go To Special > Blanks to surface cells that read as empty — a merged range’s hidden cells will show up here, which often reveals a merge the preparer had forgotten about.
3. Unhide all rows and columns before export: select the whole sheet, then Home > Format > Hide & Unhide > Unhide Rows, and again for Unhide Columns.
4. Review what becomes visible. Anything that shouldn’t be in the filing — a duplicate, a draft, test data — gets deleted, not re-hidden.
5. Re-save and convert from the cleaned sheet, rather than patching the already-generated DAT file by hand.

This is the same discipline the BIR’s own electronic alphalist framework is built around: submissions must come from a validated, structurally clean file, not a hand-adjusted one.

“…Summary Alphalists of Withholding Tax (SAWT), Monthly Alphalists of Payees (MAP) required under BIR Form Nos. 1600, 1601E, 1601F… shall be prepared using the Data Entry Module… and submitted via email to esubmission@bir.gov.ph.”

— Revenue Memorandum Circular No. 19-2015, FAQs on the electronic platform for filing tax returns

The requirement traces back to Revenue Regulations No. 1-2014, as clarified by Revenue Memorandum Circular No. 5-2014, which set mandatory electronic submission of the alphalist of employees and payees through the BIR’s Data Entry and Validation Module. A merged cell or a hidden row doesn’t just look messy in Excel — it produces a DAT record the module reads differently from what the preparer intended, which is precisely the gap that module is designed to catch before a filing reaches the BIR.

Frequently asked questions #

Why does a merged cell break a BIR DAT file conversion? #

A merged cell only stores its value in the top-left cell of the merged range — every other cell inside that range reads as blank to a program reading the spreadsheet row by row. If a header label or a totals row was merged across several columns, the converter sees blanks where it expects data, which either drops that field or shifts every column after it one or more positions to the left in the resulting DAT record.

Does Excel skip hidden rows when I save or export my workbook? #

No. Hiding a row only changes whether you can see it on screen — the data is still physically present in the worksheet and is included in a plain Save, in most CSV exports, and in most column-mapped DAT conversions. Only an actual filter applied at export time, or deleting the row outright, removes it from what gets converted.

How do I find merged cells in a large RELIEF, SAWT, or QAP workbook before converting it? #

In Excel, select the data range and use Home > Find & Select > Go To Special > Blanks — a merged cell’s hidden cells show up as blanks in that selection, which is usually how a stray merge first becomes visible in a large sheet. You can also select the whole sheet and check whether Home > Merge & Center is highlighted as active for any selected range, or step through columns looking for cells that don’t highlight individually when clicked.

How do I make sure no hidden rows or columns sneak into my DAT file? #

Before exporting, select the entire sheet and unhide everything: Home > Format > Hide & Unhide > Unhide Rows, and the same for Unhide Columns. Review what becomes visible. If a row shouldn’t be in the filing at all — a duplicate, a draft, or test data — delete it outright rather than hiding it again, then re-save before converting.

Can a hidden row cause a trailer record count mismatch, not just a data error? #

Yes. If a hidden row is picked up as a detail record during conversion, the file ends up with one more record than the preparer believes it has, which can desynchronize the trailer’s declared row count from what a manual process assumes — the same class of mechanical failure covered in this site’s guide to header, detail, and trailer record mistakes, just triggered by a hidden row instead of a stale manual edit.

Summary #

Merged cells and hidden rows are two of the quietest ways an otherwise correct-looking Excel workbook produces a wrong BIR DAT file: a merge shifts column positions because only its top-left cell actually holds data, and a hidden row still exports because hiding is a display setting, not a deletion. Catch both before converting — unmerge everything, unhide everything, and delete rather than hide anything that shouldn’t be filed — rather than discovering the mismatch only after the BIR’s Validation Module, or a RELIEF-to-2550Q reconciliation, flags it. For the structural failures this connects to, see Excel-to-DAT File Conversion Mistakes: Header, Detail, and Trailer Records, and for the format basics behind every conversion, see What Is a BIR DAT File?.