How to Sort a Rent Roll in Excel

A rent roll can have every column you need and still be hard to use if it's sorted poorly. Units scattered by lease date, floorplans mixed together, no logic connecting one row to the next. Before you can analyze occupancy, spot rent gaps, or build comps, the data needs to be in an order that actually reflects how the property works.

Key Takeaways

Sort rent rolls in this order: unit type, then square footage (smallest to largest), then effective rent (lowest to highest)
For LIHTC or income-restricted properties, add AMI% restriction tier as a level between unit type and square footage
Always use Excel's Sort dialog with multiple levels, never the one-click A-Z button on a single column
Select or format your range as a Table first so sorting doesn't silently leave a column behind

This article covers the sorting hierarchy analysts use and the exact Excel mechanics to get there without breaking your rows.

Why sorting order matters

A rent roll isn't just a list, it's a way of seeing the property's rent structure at a glance. When it's sorted correctly, you can look down a column of similar units and immediately see:

Whether smaller units are renting for proportionally less than larger ones (they should be)
Whether rent increases logically with square footage within a unit type
Where the outliers are, the unit renting well below or above what its neighbors get

None of that is visible if the rows are ordered by lease start date or unit number. Sorting by physical and financial logic, not administrative order, is what turns a rent roll into an analytical tool.

The sorting hierarchy

Unit type (Studio, 1BR/1BA, 2BR/2BA, 2BR/1BA, and so on)
AMI% restriction tier, if the property has income-restricted units (LIHTC, Section 8, etc.), this goes right after unit type
Square footage, smallest to largest within each unit type
Effective rent, lowest to highest within each unit type and size

This groups every unit with its true peers first, then orders those peers from smallest and cheapest to largest and most expensive. It's the same logic whether you're reviewing five units or five hundred.

A note on effective rent versus base rent: sort by effective rent, not base or gross rent. Effective rent already nets out concessions, so it reflects what the unit is actually collecting. If you sort by base rent instead, a unit with a big concession can look like it's outperforming when it isn't.

Excel mechanics, step by step

1. Confirm your data is clean before sorting

Sorting amplifies any structural problems in the sheet. Before you touch the Sort dialog:

Make sure there are no blank rows or blank columns inside the data range. A single blank row will cause Excel to only sort the rows above or below it.
Make sure every row has a value in the columns you're about to sort by. Blank cells sort to the bottom (ascending) or top (descending), which can bury units you need to see.
Convert the range to a Table (Ctrl+T, or Insert > Table). This locks the range boundaries so a sort operation can't accidentally leave a column out, and it keeps your headers anchored as filter buttons instead of getting sorted into the data.

2. Select the full data range

Click any cell inside your data, then go to Data > Sort. If your range is a Table, Excel already knows its boundaries and will offer to expand the selection automatically if it detects adjacent data. If you're not using a Table, select the entire range including headers before opening Sort, and check "My data has headers" in the dialog.

Never use the A-Z or Z-A buttons on the Home or Data tab for this. Those perform a single-column sort and, on an unstructured range, can desynchronize rows from the rest of the record.

3. Build the sort levels

In the Sort dialog:

Level 1: Sort by "Unit Type," Order: Custom List (see note below) or A to Z
Level 2 (LIHTC properties only): Sort by "AMI%," Order: Smallest to Largest
Level 3: Sort by "Square Footage," Order: Smallest to Largest
Level 4: Sort by "Effective Rent," Order: Smallest to Largest

Click OK. Excel will now sort in that exact nested order, unit type first, then within each unit type by AMI tier if applicable, then by size, then by rent.

4. Use a custom list if unit type order matters

By default, an A to Z sort on unit type will alphabetize, which puts "1BR/1BA" before "Studio" and can separate unit types that should logically sit together (like putting all the 2-bedroom variants next to each other regardless of bath count). If you want a specific order, like Studio, 1BR, 2BR, 3BR:

In the Sort dialog, set Level 1's Order dropdown to Custom List
Type your unit types in the order you want, one per line
Click Add, then OK

This custom list will now be available for future sorts on this workbook, so you only need to build it once.

5. Re-check after any edits

If you add units, correct a square footage figure, or update a rent post-sort, the sheet won't automatically re-sort itself. Re-run Data > Sort (Excel remembers your last set of levels, so you can usually just reopen the dialog and click OK) any time the underlying numbers change.

New CRE tools and templates, once a week.

Common mistakes

Sorting one column at a time. Doing separate single-column sorts (sort by unit type, then separately sort by square footage) will undo the first sort. Always build all levels in one pass through the Sort dialog.
Forgetting to select the full range. If a column sits just outside the range Excel auto-detects, that column stays in its original order while everything else moves, breaking the record.
Sorting by base rent instead of effective rent. This misrepresents which units are actually performing well once concessions are factored in.
Ignoring blank AMI% cells on mixed-income properties. Market-rate units in a LIHTC property often have a blank AMI% field. Decide up front whether those should sort to the top or bottom of each unit type block, and confirm Excel is treating them that way rather than scattering them based on how it defaults blank cells.

Skip the manual sort

If you're doing this by hand on every rent roll you touch, it adds up fast, especially on properties with a lot of unit type variety or mixed AMI tiers. DealDuo's Rent Roll Cleaner applies this kind of sorting automatically. Upload a rent roll in Excel or PDF and it standardizes unit types, floorplans, lease status, and charge categories, then exports a clean two-tab workbook with the per-unit rent roll sorted and a unit summary showing occupancy, vacancy, and rent per square foot.