To turn a rent roll PDF to Excel, build one row per unit with the unit number, tenant, lease start and end, monthly rent, deposit and balance owed. Keep vacant units as rows, fold extra charge lines into their unit, and leave the printed summary rows out of the data so you can check your totals against them.
One row per unit in the rent roll sheet
A rent roll is a list of every unit in a property with its tenant and lease terms, usually printed by property software as of one date. In Excel, the unit is the row. If a unit appears twice, or not at all, every total you build from the sheet is wrong.
The columns each unit needs
Most rent rolls print some version of these fields. Keep them in this order:
- Property, if the report covers more than one building.
- Unit, stored as text, because "0102" and "102" are different units in some buildings, and "2B" is not a number at all.
- Unit type or size, such as "2 bed / 1 bath" or square feet.
- Tenant, the name on the lease.
- Lease start and lease end, as real Excel dates.
- Monthly rent, the contracted amount, not what was paid.
- Market rent, if the report prints it.
- Deposit held, the security deposit on file.
- Balance, what the tenant owes as of the report date. A negative balance is a prepayment or credit.
The report date belongs in the sheet
A rent roll is a snapshot. A tenant who moves out the day after the report is still listed. Add a column, or a cell at the top, holding the "as of" date printed in the report header, and name the file with it, such as "rent-roll-2026-10-01.xlsx". When you compare two months later, the date tells you which snapshot each row came from.
Building rent rolls by property
If the report covers several properties, each one often starts with a property heading row, then its units, then a property subtotal. Copy the property name down into a column on every unit row and drop the heading row. A filter on the property column then gives you each building on its own.
How should vacant units appear in a rent roll export?
A vacant unit should stay in the sheet as a full row, with the unit number, type and market rent filled in and the tenant, lease dates and rent left blank or marked "Vacant". Dropping vacant units makes the property look fully let and breaks the unit count. A vacancy is information, not an empty line.
Converters sometimes lose vacant rows, because a line with only a unit number and the word "VACANT" does not look like the other rows in the table. Count the units in your sheet against the unit count in the report summary. If the sheet is short by exactly the number of vacancies, that is the cause.
Also look for units marked "Notice", "Model", "Down" or "Employee". A unit under notice still has a tenant and rent. A model unit or a unit down for repairs has neither, and should be treated like a vacancy for income but labeled with its own status. Put that word in a "Status" column rather than in the tenant column, so a count of tenants stays honest.
For the rent columns on a vacant row, leave the contracted rent blank and keep the market rent. Writing 0 in the rent column is fine for sums, but blank tells the reader there is no lease, which is the more accurate statement.
Rent roll charge lines that sit under one unit
Many rent rolls print each recurring charge as its own line under the unit: rent on the first line, then parking, pet rent, storage or a utility charge on the lines below, with the unit number printed once. After conversion those lines arrive with no unit number. Fill the unit number down, then decide how to keep them.
You have two good options:
- Keep a charges sheet. Leave every charge as its own row with the unit number filled in and a "Charge code" column. Then a pivot table on unit gives the total monthly charges, and on charge code gives the parking or pet income across the property.
- Widen the unit row. Add a column for each charge type, such as Rent, Parking and Pet, and move each amount into its unit's row. This is easier to read but harder to keep tidy when a new charge type appears.
Whichever you pick, do not leave the charge lines as orphan rows with no unit. A SUM over the rent column will include them, and they will be counted as if they were separate units with tiny rents.
The same fill-down repair applies to any report that prints a key once and details beneath it. A fleet's card statement does it with vehicles, which is why splitting a fuel card statement by vehicle, gallons and cost starts with the same step.
Rent roll summary rows and the occupancy check
The summary rows at the end of a rent roll print the unit count, the occupied count, the total scheduled rent and the total balance due. Those printed numbers are your check. Move them off the data rows, then rebuild each one from the unit rows with a formula. If every rebuilt number matches, the sheet is complete.
A worked example
The six-unit rent roll below is an example, made up for illustration, as of 2026-10-01.
| Unit | Status | Tenant | Lease start | Lease end | Rent | Balance |
|---|---|---|---|---|---|---|
| 101 | Occupied | Tenant A | 2025-11-01 | 2026-10-31 | 1,450.00 | 0.00 |
| 102 | Vacant | |||||
| 103 | Occupied | Tenant C | 2026-03-01 | 2027-02-28 | 1,525.00 | 125.00 |
| 104 | Occupied | Tenant D | Month to month | 1,400.00 | 0.00 | |
| 105 | Occupied | Tenant E | 2026-06-01 | 2027-05-31 | 1,550.00 | -50.00 |
| 106 | Occupied | Tenant F | 2026-01-15 | 2027-01-14 | 1,475.00 | 0.00 |
The printed summary on this example report reads: 6 units, 5 occupied, scheduled rent 7,400.00, balance due 75.00.
Now rebuild those from the rows. COUNTA on the unit column gives 6. COUNTIF on status "Occupied" gives 5, so occupancy is 5 of 6, or 83.3 percent. SUM on the rent column gives 1,450 + 1,525 + 1,400 + 1,550 + 1,475 = 7,400.00. SUM on the balance column gives 125.00 minus the 50.00 prepayment from unit 105 = 75.00. All four match the summary.
What a mismatch tells you
- Unit count short: a vacant row or a down unit was dropped.
- Rent total high: a charge line was counted as a unit, or a property subtotal stayed in the data.
- Balance total off by twice an amount: a credit balance lost its minus sign. A prepayment of 50.00 read as positive pushes the total 100.00 too high.
Unit 101 in the example is worth a second look for a different reason. Its lease ends on 2026-10-31, a month after the report date. A column showing days to lease end, calculated from the report date, puts upcoming renewals at the top when you sort it.
Why do rent roll lease dates break after conversion?
Rent roll lease dates break after conversion when Excel reads them in the wrong order, such as day and month swapped, or keeps them as text because the cell also holds words like "Month to month". Dates that look right but will not sort are text.
The fixes
- Text dates. Select the column and use Data, Text to Columns, choose Date and the order the report uses, such as MDY. Excel converts the text in place.
- Swapped day and month. Check a date with a day above 12, such as the 15th. If it came out as text while dates with small days became real dates, the import used the wrong order. Redo the import with the right one.
- Words in a date column. Move "Month to month", "MTM" or "Holdover" into the Status column or a "Lease type" column, and leave the lease end blank. A date column should hold only dates.
Two-digit years
Some reports print "01/15/27", and Excel has to guess the century. Most short residential leases come out fine. A lease that began decades ago, or a long ground lease that ends far in the future, can land in the wrong century, so sort the lease end column and look at both ends of the list.
Long reports for large portfolios bring a different problem: the PDF itself can run hundreds of pages and choke the browser tab doing the conversion. Ways around a browser crash on a large PDF covers splitting the file and other fixes.
Matching the rent roll to rent deposits on the bank statement
The rent roll says what tenants should pay. The bank statement says what arrived. Matching them means taking each occupied unit's rent and finding the deposit, or the share of a batch deposit, that paid it. Units with rent due and no matching deposit should also show a balance on the next rent roll.
Rent often arrives in mixed ways. Some tenants pay online and the property software sends one batch deposit for many units. Others pay by check, and a deposit slip covers several checks. When that happens, use the deposit detail from the property software, or the check list from the deposit, to split the bank line across units.
Properties that still write checks for repairs and utilities keep a check register beside the rent roll. Turning a check register PDF into Excel with numbers, payees and voids gives the outgoing side of the same bank account.
Getting the statement into rows is the step FastBankConvert is built for: it turns a bank statement PDF into Excel with dates, descriptions and amounts in their own columns, and tells you if the balances do not add up. It is getting ready to open, and the homepage has a sample run. With both sheets in one workbook, sort the deposits by date and the rent roll by rent, and the matches and the gaps both stand out.