↓Skip to main content

Excel Pivot Table for RELIEF: Turn an Invoice Register Into Monthly Per-Customer Totals for Your DAT File

To prepare a RELIEF Summary List of Sales from a long invoice register, build an Excel pivot table with customer on the rows and month beside it, sum the net sales, sort the customers A to Z, and paste the result into your DAT conversion sheet. That matches RMO No. 4-2003’s description of one consolidated figure per customer per month.

Skip the Manual Rollup: Convert Your Sales List to a RELIEF DAT FREE →

Paano gawing monthly total per customer ang daan-daang invoice? RELIEF is the BIR system fed by the quarterly Summary List of Sales and Purchases (SLSP), a DAT (delimiter-separated data) file. A register with one line per invoice is not the format of the list, and this guide closes that gap.

What does the BIR require the sales list to look like? #

The Summary List of Sales shows consolidated monthly sales per customer, not one line per invoice. RMO No. 4-2003 Policy IV.6 sets that shape and the alphabetical order. The pivot table in this guide produces exactly that structure from an invoice-level register exported from a POS, spreadsheet, or accounting system.

Policy IV.6 of RMO No. 4-2003, as reproduced on Lawyerly, reads:

“The Quarterly SLSP shall contain the consolidated monthly sales/purchases per customer/seller which shall be alphabetically arranged while the Quarterly Summary List of Importation must be chronologically arranged on a per individual transaction for each month of the taxable quarter.”

Source note: the Lawyerly copy is a secondary reproduction of the BIR order, with the quote shown here as it appeared there (the passage was read in short pieces and joined). The order is also the source of the regular and casual buyer tests in RELIEF SLSP Regular vs. Casual Buyers, and the filing duty for all VAT-liable persons comes from RR No. 1-2012, covered in Who Must File RELIEF SLSP?.

How do you prepare the invoice register before pivoting? #

Clean the register first, because a pivot table groups exactly what is typed. A pivot table counts “Greenfield Realty Corp.” and “Greenfield Realty Corporation” as two customers, and a TIN that lost its leading zero as a different TIN. Five minutes of cleaning prevents duplicate rows in the DAT file.

  1. Keep one row per invoice with these columns: invoice date, invoice number, customer name, customer TIN, net sale, and output VAT.
  2. Format the TIN column as Text before pasting; see Why a TIN Loses Its Leading Zero.
  3. Run Data > Remove Duplicates on invoice number to catch a pasted line twice.
  4. Add a Month column with =TEXT(A2,"yyyy-mm"), where A2 holds the invoice date, and fill it down.
  5. Trim stray spaces with =TRIM(C2) in a helper column; hidden characters are covered in Trailing Spaces and Invisible Characters.
  6. Sort by customer name once and scan for two spellings of one customer.

How do you build the pivot table? #

Insert a pivot table on the cleaned register, put the customer name and TIN on rows, the Month field beside them, and sum net sale and output VAT. The result is one line per customer per month. Set the report layout to tabular and repeat item labels so every row carries the name, because the DAT conversion needs a value on each line.

  1. Select the register and choose Insert > PivotTable, on a new sheet.
  2. Drag Customer Name, then TIN, then Month to Rows.
  3. Drag Net Sale and Output VAT to Values; confirm each reads “Sum of”.
  4. Under Design > Report Layout, choose Show in Tabular Form and Repeat All Item Labels.
  5. Under Design > Subtotals, choose Do Not Show Subtotals, and under Grand Totals, turn the column total on for checking.
  6. Sort Customer Name A to Z.
  7. Copy the pivot as values onto a new sheet before you add any DAT-specific columns.

Worked example: twelve invoices become six rows #

Twelve invoices for two customers collapse into six monthly rows, and the net and VAT totals stay identical. The numbers are fictional, for a VAT-registered printer in July to September 2026 at 12% output VAT.

CustomerJulyAugustSeptemberQuarter netOutput VAT
Bayanihan Events₱30,750₱34,000₱28,000₱92,750₱11,130
Greenfield Realty Corp.₱75,000₱52,000₱59,400₱186,400₱22,368
Grand total₱105,750₱86,000₱87,400₱279,150₱33,498

The register holds seven Bayanihan Events invoices (two in July, three in August, two in September) and five Greenfield Realty Corp. invoices, twelve in all. After pivoting, the list has six rows, each with a customer, month, net sale, and VAT. Grand total net must read ₱279,150 and VAT ₱33,498, matching =SUM() on the register. If they differ, a customer is being dropped by a blank name or filter.

Customer (A–Z)MonthNet saleOutput VAT
Bayanihan Events2026-07₱30,750₱3,690
Bayanihan Events2026-08₱34,000₱4,080
Bayanihan Events2026-09₱28,000₱3,360
Greenfield Realty Corp.2026-07₱75,000₱9,000
Greenfield Realty Corp.2026-08₱52,000₱6,240
Greenfield Realty Corp.2026-09₱59,400₱7,128

What should you check before you convert to a DAT file? #

Check three things: the pivot total equals the register total, no customer appears under two spellings, and every itemized buyer qualifies. Then reconcile to the VAT return and convert. Centavo differences are the next most common cause of a rejected upload; see Rounding and Centavo Mismatches.

Summary #

  • RMO No. 4-2003 Policy IV.6: sales are consolidated monthly per customer, alphabetically; importations are chronological per transaction.
  • Clean names and TINs first; a pivot groups exactly what is typed.
  • Pivot customer, TIN, and month; sum net sale and VAT; sort A to Z; paste as values.
  • Match the pivot total to the register and to the VAT return before conversion.

Frequently asked questions #

Does the RELIEF Summary List of Sales list every invoice separately? #

No for sales. RMO No. 4-2003 Policy IV.6 says the Quarterly Summary List of Sales and Purchases contains the consolidated monthly sales or purchases per customer or seller, arranged alphabetically. Only the Quarterly Summary List of Importation is arranged chronologically on a per individual transaction basis.

Why does my pivot table show the same customer twice? #

The customer name is spelled two ways in the register, for example with and without a period or with extra spaces. A pivot table treats each spelling as a different customer. Clean the names, or group by TIN stored as text, before building the pivot.

Should the pivot table total match my VAT return? #

The pivot grand total of net sales should equal the register total, and the register total should reconcile to the sales reported on the quarter’s VAT return after allowing for sales that fall outside the list. Reconcile before conversion, not after the BIR flags a mismatch.

How do I keep the TIN from losing its leading zero in the pivot table? #

Format the TIN column as Text before typing or pasting TINs, and keep the TIN as a pivot row field next to the name. A TIN stored as a number loses leading zeros and dashes, which breaks the DAT record.

Can I use a pivot table for the importations schedule? #

No. RMO No. 4-2003 Policy IV.6 says the Quarterly Summary List of Importation must be arranged chronologically on a per individual transaction basis for each month of the taxable quarter, so each import entry stays its own row.