To convert an 835 file to a spreadsheet, split the file into segments, then build one row per service line that carries its claim and payment details, with each CAS adjustment in its own columns or tab. Put PLB provider adjustments on a separate tab so the totals still match the deposit, and limit who can open the file.
Why convert an 835 file to a spreadsheet?
Converting an 835 file to a spreadsheet makes an electronic remittance readable and filterable for people who do not work in a billing system all day. An 835 is the electronic remittance advice a payer sends with a payment. Its raw text is a long run of codes; a spreadsheet turns it into rows to sort and total.
The 835 is a standard, not a payer's own format. HIPAA adopts the ASC X12 835, version 005010X221, as the standard for health care payment and remittance advice in 45 CFR 162.1602 on eCFR. That shared structure is what makes a single conversion layout work for files from different payers.
Billing teams reach for a spreadsheet for three kinds of work.
- Research. A biller chasing one claim wants to see exactly what the payer said about it, line by line, without reading raw segments.
- Reconciliation. When a deposit does not match what posted, a spreadsheet lets someone total the claims, compare them with the payment and find the gap.
- Payer analysis. Filtering a quarter of remittances by payer and adjustment code shows which payers deny which services and how often.
A spreadsheet is a reading copy, not a posting tool. The billing system remains the record of what posted. Converting the file gives a person a second view of the same data, and that copy needs the same care as the original, as the last section explains.
835 spreadsheet columns: one row per service line
An 835 spreadsheet should hold one row per service line, because the service line is where the payer states the charge, the payment and the adjustments for a single procedure. Each row repeats the payment and claim details it belongs to, so any row can be filtered or sorted alone without losing its context.
In an 835, the payment sits at the top, claims sit inside the payment, and service lines sit inside each claim. A flat spreadsheet has to copy the upper levels down onto each line. The column layout below uses element names from CMS's 835 flat file layout for Medicare, which maps each field to the 005010X221A1 version of the 835.
| Column | 835 source | What it holds |
|---|---|---|
| Line ID | Assigned during conversion | A unique number for each row, used to join the adjustment tab |
| Payment amount | BPR02 | Total actual provider payment amount |
| Check or EFT trace | TRN02 | The check or EFT trace number that ties the file to the deposit |
| Patient control number | CLP01 | The number your system sent on the claim |
| Claim charge | CLP03 | Total claim charge amount |
| Claim paid | CLP04 | Claim payment amount |
| Patient responsibility | CLP05 | Patient responsibility amount for the claim |
| Payer claim number | CLP07 | The payer's claim control number |
| Patient last and first name | NM103, NM104 in the patient NM1 | The patient named on the claim |
| Member ID | NM109 in the patient NM1 | The patient identifier |
| Service date | DTM02 in the service date DTM (qualifier 150, 151 or 472) | The date of the service line |
| Procedure and modifiers | SVC01 components | The adjudicated procedure code and up to four modifiers |
| Line charge | SVC02 | Line item charge amount |
| Line paid | SVC03 | Line item provider payment |
| Adjustment columns | CAS in the service loop | Group code, reason code and amount, as the CAS section explains |
Keep amounts as numbers, not text, so totals work. Keep identifiers such as patient control numbers and member IDs as text, so a spreadsheet does not strip leading zeros or turn a long number into scientific notation.
A fictional 835 excerpt, converted
Every value below is invented for the example: the patient, the member ID, the claim numbers, the trace number and the amounts. The excerpt is shortened and leaves out envelope and payer segments. In this example file, an asterisk separates elements, a colon separates components and a tilde ends each segment. A real file declares its own separators in the ISA header; the CMS layout lists the component element separator as ISA16.
BPR*I*186*C*ACH~
TRN*1*EX-EFT-90021*1999999999~
CLP*EX-1001*1*250*96*64*12*EXPAYER5511~
NM1*QC*1*REYES*DANA****MI*XJ4410298~
SVC*HC:99213*250*96~
DTM*472*20260905~
CAS*CO*45*90~
CAS*PR*1*40**2*24~
CLP*EX-1002*1*180*120*15*12*EXPAYER5512~
NM1*QC*1*OKAFOR*SAM****MI*XJ7720114~
SVC*HC:97110*180*120~
DTM*472*20260908~
CAS*CO*45*45~
CAS*PR*3*15~
PLB*1234567893*20261231*WO:EX-OVP-77*30~
The service lines become two rows:
| Line ID | Trace | Patient control no. | Patient | Member ID | Service date | Procedure | Line charge | Line paid |
|---|---|---|---|---|---|---|---|---|
| 1 | EX-EFT-90021 | EX-1001 | Reyes, Dana | XJ4410298 | 2026-09-05 | 99213 | 250.00 | 96.00 |
| 2 | EX-EFT-90021 | EX-1002 | Okafor, Sam | XJ7720114 | 2026-09-08 | 97110 | 180.00 | 120.00 |
Each line balances: $250.00 less $90.00, $40.00 and $24.00 in adjustments leaves $96.00, and $180.00 less $45.00 and $15.00 leaves $120.00. The adjustment rows and the PLB row are shown in the sections that follow.
Converting an 835 file to a spreadsheet with free tools
Converting an 835 file to a spreadsheet with free tools takes three approaches: splitting the text on its separators in a text editor and a spreadsheet program, opening the file in a free 835 viewer, or writing a short script. Splitting by hand works for one small file. A script is the better choice once files arrive every week.
Splitting on separators
Open a copy of the file in a plain text editor. First find the character that ends each segment; in the example above it is the tilde. Replace every terminator with a line break, so each segment sits on its own line. Save the result, open it in a spreadsheet program, and split each line into columns on the element separator.
That gives one row per segment, not one row per service line. A person then has to read down the rows and copy the claim details next to each SVC row. It is slow, and it breaks easily when a claim has many lines, but it needs nothing beyond tools already on a work computer.
Viewers
Free 835 viewers read the file and show it as a formatted remittance, often with options to print or export. Before you load a file with patient information into any tool, check where it runs. A viewer that runs entirely on your own computer is a different question from a website that uploads the file to someone else's server, because uploading shares protected health information with whoever runs that site.
Short scripts
A short script in a common language can read the file segment by segment, remember the current payment and claim, and write a row each time it reaches an SVC segment. It writes the CAS segments that follow into the adjustment tab with the same line ID. Parsing 835 files in code, including loops and edge cases, is covered in the developer integration category.
The meaning of each segment, beyond the ones used for columns here, is explained in the guide to 835 segments a payment poster should recognize.
How do CAS adjustments fit into 835 spreadsheet columns?
CAS adjustments fit into 835 spreadsheet columns either as repeated sets of group, reason and amount columns on the service line row, or as a separate tab with one row per adjustment joined by line ID. A CAS segment explains why a line paid differently from what was billed, and one line can carry several.
The structure comes from the 835 itself. In CMS's 835 flat file layout, CAS01 is the claim adjustment group code, followed by up to six sets of reason code, amount and quantity running through CAS19. A line can also carry more than one CAS segment, one per group code, as both example lines do.
Group codes tell you who carries the amount. The X12 Claim Adjustment Reason Codes list describes group codes as generally assigning "responsibility for the adjustment amounts", with CO for contractual obligation and PR for patient responsibility, and notes that group code CR is not used with version 005010 and up. What each group code means for posting is covered in what CO, PR, OI and PI group codes mean. Reason code 45 means a charge exceeds the fee schedule or contracted arrangement; 1, 2 and 3 are deductible, coinsurance and co-payment amounts.
For the example file, a separate adjustment tab looks like this:
| Line ID | Group | Reason | Amount |
|---|---|---|---|
| 1 | CO | 45 | 90.00 |
| 1 | PR | 1 | 40.00 |
| 1 | PR | 2 | 24.00 |
| 2 | CO | 45 | 45.00 |
| 2 | PR | 3 | 15.00 |
The separate tab is the safer layout. Repeated columns force you to pick a maximum, such as three adjustments per line, and a line with four quietly loses one. A long tab has no limit. It also makes payer analysis simpler, since you can filter one column for reason 45 instead of searching six.
Watch for claim-level CAS segments as well. A CAS that appears after the CLP segment and before the first SVC belongs to the whole claim, not to any line. Give it its own rows in the adjustment tab with the claim's patient control number and a blank line ID, or the claim's lines will not add up to the claim total.
835 spreadsheets that drop PLB adjustments
An 835 spreadsheet that drops PLB adjustments will not match the deposit. A PLB segment is a provider level adjustment: an amount the payer adds to or takes from the whole payment rather than from any claim, such as recovering an earlier overpayment. If the conversion keeps only claims and lines, the claim totals and the check amount disagree.
The rule behind the gap is stated in the Medicare Claims Processing Manual, Chapter 22 on remittance advice: the check amount is the sum of all claim-level payments less any provider level adjustments, and positive adjustment amounts reduce the payment while negative amounts increase it. The same chapter says a compliant 835 must balance at the service, claim and provider levels.
Run the example through that rule. The two claims paid $96.00 and $120.00, for $216.00. The PLB segment carries a positive $30.00 adjustment, which reduces the payment. $216.00 less $30.00 is $186.00, the amount in BPR02. A spreadsheet without the PLB row shows $216.00 in claims against a $186.00 deposit and sends someone looking for a $30.00 posting error that does not exist.
Give PLB its own tab:
| Trace | Provider ID | Fiscal period date | Adjustment code | Reference | Amount |
|---|---|---|---|---|---|
| EX-EFT-90021 | 1234567893 | 2026-12-31 | WO | EX-OVP-77 | 30.00 |
One PLB segment can hold several adjustments. The CMS layout shows PLB03 through later elements as repeating pairs of an adjustment identifier and an amount, so each pair needs its own row. Keep the sign exactly as the file states it; flipping a negative into a positive turns an addition into a deduction.
Finally, add a check row: claim payments minus PLB amounts should equal BPR02. If it does not, the conversion missed a claim, a CAS segment or a PLB pair.
Who should see an 835 spreadsheet?
An 835 spreadsheet should be seen only by the people whose jobs need the remittance details, because it holds protected health information: patient names, member IDs, service dates and procedure codes. Converting the file does not change that. It does make the data easier to copy, email and leave on a shared drive.
The HIPAA Privacy Rule sets the principle. Under 45 CFR 164.502(b) on eCFR, a covered entity must make reasonable efforts to limit protected health information to the minimum necessary for the purpose. 45 CFR 164.514(d) on eCFR adds that a covered entity must identify which people in its workforce need access and to which categories of information.
CMS draws a similar line for banks. Chapter 22 explains that the part of an 835 holding patient information may not go to a bank unless that bank is also a health care data clearinghouse, the provider has authorized it, and it has signed an agreement to safeguard the data.
Practical steps for an 835 spreadsheet follow from that:
- Strip what the task does not need. A payer analysis by reason code needs no patient names. Delete the name and member ID columns before sharing it.
- Store it where access is controlled. Keep it in a folder limited to the billing team, not a general shared drive or an email attachment.
- Delete working copies. A spreadsheet made to research one claim should not live for years.
- Mind free tools. As the free tools section notes, a website that converts a file has received a copy of it.
Not every remittance arrives as an 835. Many payers still mail paper EOBs, and reading those is a different job; the guide on how to read an EOB line by line covers the paper layout, and the guide to turning paper EOBs into 835 files covers conversion in the other direction. Schema Care reads paper EOB pages into fields such as service lines, plan payments and remark codes, and it does not read or produce 835 files; its supported document types are listed on the homepage. For access rules, retention and the rest of handling patient data, see the HIPAA and PHI category.