Excel for property managers on Mac: rent rolls, lease tracking and owner statements

Most property managers on a Mac use Excel for the same five jobs. The rent roll lists every unit, tenant, rent amount and status. The rent ledger logs each charge and payment. A lease expiration list shows what needs renewing. Owner statements summarize income and costs for each property. A CapEx tracker records repairs and upgrades, with dates and costs. None of these needs an add-in. Excel for Mac handles all five with tables, formulas and a built-in import tool.

The five sheets most property managers keep in Excel

• Rent roll: one row per unit, with tenant, rent, deposit, status and lease end date.
• Rent ledger: one row per charge or payment, with date, type, property and amount.
• Lease expirations: the rent roll filtered to leases that end soon.
• Owner statement: income and expenses for one property or owner, by month.
• CapEx tracker: repairs and upgrades with dates, costs and notes on useful life.

Keep the ledger separate from the rent roll. The roll answers what each unit looks like today. The ledger answers what happened and when. If you mix them, you get one row per unit with a dozen payment columns. Those break every time a tenant pays late or a fee posts twice.

Set up a rent roll that Excel can filter and total

Start with one row per unit. Put the column names in row 1. Leave no blank columns, no merged cells and no notes above the header. Then turn the range into a table. Select the range and press ⌘ T, or go to Insert > Table. Tick 'My table has headers', then click OK.

Name the table as you create it. Microsoft's October 2026 post says that option now works in Excel for Mac, after it was an Insider feature. Use a short name such as tblRentRoll. A formula can then read tblRentRoll[Rent] instead of $F$2:$F$118. The named version is easier to check, and it grows when you add a unit.

Freeze the header row with View > Freeze Panes > Freeze Top Row. Without it, the column names scroll off the screen when you reach the bottom of a long roll. That small step saves a lot of guessing about which column is which.

Flag leases that expire in the next 60 days

This is the check most managers run every week. Add a column called Lease End and type real dates. Excel stores a date as a number, so it can sort and subtract. Text looks similar but does not count. If a date sits on the left of its cell, Excel read it as text. Right alignment usually means a real date.

Next, add a Renewal Flag column. The formula below marks any lease that ends within the next 60 days and is still in the future.

Here is how the flag behaves. A lease that ends 57 days from today gets Renew soon. A lease that ends 70 days out does not. A lease that already ended returns blank, so the flag ignores it. Check expired leases separately, then filter the Renewal Flag column for Renew soon with Data > Filter.

Build owner statements with SUMIFS

An owner statement is a set of SUMIFS formulas over the ledger. Name the ledger table tblLedger. Put the property name in cell B1, the first day of the month in B2 and the last day in B3. The formula in the tip below totals rent for that property and month.

Keep expenses as positive numbers in the ledger. Subtract them on the statement line. Mixed signs cause errors that are hard to spot in a long list. A SUMIFS formula with the wrong criteria returns zero without any warning, so compare each total with the ledger once a month.

To audit a statement, press ⌘ ` to show every formula on the sheet at once. Press it again to go back to values. A quick scan finds a typed number that replaced a formula, which is an easy error to miss.

Import bank and payment exports with Power Query on Mac

Retyping a monthly export wastes time and invites mistakes. Use Data > Get Data > Get Data (Power Query) to pull the file in. Microsoft's Mac article lists Excel Workbook, Text/CSV, XML, JSON, OData and a few other sources. SQL Server import is available in Insiders Beta only.

The support article says shaping in the editor is limited to Microsoft 365 subscribers and needs version 16.69 (23010700) or later. If the editor does not open for you, load the export as a table and clean it by hand. That is slower, but it works on any build that opens the file.

Keep the untouched export on its own sheet named Raw. You can then check any total against the original file when an owner asks a question. Paste each new export over Raw only after you have saved a dated copy of the workbook.

Clean tenant names, duplicates and stray spaces

Tenant lists get messy fast. One tenant can appear as Ana Ruiz, ana ruiz and Ana Ruiz with a trailing space. Excel treats those as three different values. Add a helper column with =TRIM(A2), copy it down, then use Edit > Paste Special > Values to replace the original text. TRIM does not fix capital letters, so pick one format for names and stick to it.

To remove duplicates, select the table and go to Data > Remove Duplicates. Choose the columns that identify a row, such as Unit and Tenant. Make a copy first. Excel deletes the extra rows when you confirm, and undo does not reach back after you save and close the file.

Microsoft's October 2026 post says the Copilot clean data skill finds issues such as extra spaces and duplicate values, then proposes fixes. Microsoft says the skill is rolling out to general availability on all platforms. You can call it with @clean-data or from the Skills menu. Read every proposed change. A fix that looks right on one row can change a name you meant to keep.

Know the limits before the sheet gets slow

Excel's limits are high. Each worksheet holds 1,048,576 rows and 16,384 columns. The limit is the same on Mac and Windows. Most property sheets never come close to the row limit. The limits you feel first are speed, long formulas and file size.

Formulas hit a different ceiling. Long nested IF chains approach the formula limit faster than you expect. If a formula gets that long, split it across helper columns. Each helper column is easier to test and easier to explain to an owner.

Speed usually suffers from formulas that repeat across thousands of rows, not from the row count itself. Keep your ranges inside tables, and delete sheets you no longer use. A smaller file opens faster and sorts faster.

Mistakes that break rent rolls and owner statements

• Unit numbers like 0412 lose the leading zero when typed as numbers. Format the column as Text before you type, or enter them as Unit 0412.
• Dates typed as text do not sort, filter or subtract. Check the alignment of the cell, then retype the value as a real date.
• Merged cells in a header or body break sorting and table creation. Use Format > Cells > Alignment > Center Across Selection for a title that spans columns.
• Typing over one formula in a column breaks the pattern. Press ⌘ ` after large edits to scan for the odd cell.
• SUM includes hidden rows, while SUBTOTAL with 109 does not. Pick the one that matches the total you need.
• One file for every owner and every year makes search hard. Keep one rent roll and one ledger, then build statements from them.

Keep dated copies before you clean or import

Save a copy at the end of each month, such as Rent Roll 2026 11 end. Use File > Save As, or press ⇧ ⌘ S. A dated copy gives you something to compare against when an owner asks why a number changed. If the file lives in OneDrive or SharePoint, check your version history settings before you rely on them.

Where Lazur helps while you work in Excel

Lazur is a voice assistant for the Mac. Hold ⌥ ⌘ to ask about anything on screen, and Lazur answers out loud. Ask where a button is, and Lazur draws a circle and a label around it. Ask how to do a task, and Lazur guides you one click at a time, waiting for each click before it goes on.

Lazur does not click buttons, edit files or operate other apps for you. You keep control of the workbook. That matters for a rent roll, because one wrong click can overwrite a column of tenant data. Use Lazur for questions like where is this button or what does this error mean, then do the clicks yourself.

Hold fn to dictate clean, formatted text into a cell or a note. Lazur can also draft text, such as a short note to a tenant about a late fee. Read the draft, change what needs changing, then send it from your own email.

Lazur listens and looks at the screen only while you hold the shortcut. Audio and the screenshot go to AI providers to answer you. They are not stored, and content is never used to train AI models. Lazur runs on macOS, on Apple silicon and Intel. Windows is planned, not available. It is not an add-in for Excel, and it has no partnership with any app maker.

For more on the Excel side, see AI assistant for Excel on Mac. For the wider property work, see AI assistant for property managers on Mac. Trial details are on the pricing page.

Start from a template, then change it

Stessa publishes free rental property spreadsheet templates for Excel and Google Sheets. Its rent roll tracks tenant details, rent payment status, occupancy, tenancy length, lease expiration, payment history and security deposit refunds. Its guide also lists a rent ledger, two income statement templates, a cash-flow report, a CapEx worksheet with depreciation tracking and a balance sheet.

These are general landlord templates, not Mac-specific ones. Stessa is also a property management software vendor, so its layouts reflect its own view of the work. Check whether the columns match how your firm bills owners before you copy one. Rename the columns to your own terms, then build the formulas on top.

When a spreadsheet stops being enough

A workbook works while one person keeps it current. It gets harder when several staff edit the same rent roll, when owners want their own reports, or when leases live in another system. Know the point where a spreadsheet costs more time than it saves.

A 2025 NAA report, published by AppFolio on December 10, 2025, argues for unified systems and AI. The report says property management professionals devote 42% of their week to routine operational work, such as paperwork, data entry and admin. It says 24% goes to reactive tasks, such as putting out fires.

Treat those figures with care. AppFolio sponsored the research, and the page does not give a sample size or survey dates. Still, the numbers show how much of the week goes to repeat work. That repeat work is the part a spreadsheet can trim, and the part a connected system can trim further.

Next steps this week

1. Turn your rent roll into a table named tblRentRoll, with one row per unit and no merged header cells.
2. Add Lease End as a real date column, then add the Renewal Flag formula from the table above.
3. Save a dated copy of the workbook before you import or remove duplicates.
4. Open Excel > About Excel and note the build number before you try Power Query.
5. Ask Lazur to point at Data > Filter if you cannot find it.