How to Create a Property Management Dashboard in Excel with ChatGPT
Juggling rent payments, maintenance requests, and tenant contracts can feel like a full-time data entry job. Instead of focusing on growing your portfolio, you're buried in spreadsheets and sticky notes. This article will show you how to build a dynamic property management dashboard in Microsoft Excel, and the best part is, you'll use ChatGPT to do all the heavy lifting - no PhD in Excel formulas required.
Why an Excel Dashboard is Perfect for Property Management
An Excel dashboard pulls all your critical property information into a single, at-a-glance view. Think of it as a financial cockpit for your real estate investments. Instead of digging through different files to see which units are vacant or how much you spent on repairs last month, you get all the answers on one screen. It’s a cost-effective and highly customizable way to make smarter, data-driven decisions on the fly.
The main hurdle for many property managers is the technical side of Excel. Crafting complex formulas, setting up PivotTables, and designing charts can be intimidating. This is where ChatGPT changes the game. By simply describing what you want in plain English, you can get the exact formulas and steps you need, turning hours of work into a few minutes of conversation.
Step 1: Get Your Data Organized
Before you build anything, you need a solid foundation. A great dashboard runs on clean, well-structured data. The best way to organize this in Excel is by creating separate tabs for each type of information and formatting each one as an Excel Table (select your data, go to the Insert tab, and click Table). This makes your data dynamic, so your dashboard updates automatically as you add new rows.
Set up your Excel workbook with these four essential tabs:
Properties List
This is a master list of your real estate assets. Keep it simple and clear.
- Property Name/Address: e.g., "123 Main St"
- Unit Number: e.g., "Apt 2B" or leave blank for single-family homes
- Property Type: e.g., "SFH," "Condo," "Duplex"
- Status: A simple "Occupied" or "Vacant"
- Bedrooms / Bathrooms: e.g., "2" / "1.5"
- Square Footage: e.g., "950"
Tenant Information
This tab tracks who is in your properties and the terms of their leases.
- Tenant Name: e.g., "Jane Smith"
- Property/Unit: Match this to the entry in your Properties list (e.g., "123 Main St - Apt 2B")
- Lease Start Date: e.g., "01/01/2024"
- Lease End Date: e.g., "12/31/2024"
- Monthly Rent: e.g., "$1500"
- Security Deposit: e.g., "$1500"
- Contact Info: Phone number and email
Financial Ledger
This is where you'll log every transaction. Diligent tracking here makes your dashboard incredibly powerful.
- Date: The date of the transaction
- Property/Unit: Which property the transaction belongs to
- Category: Use consistent terms like "Rent Payment," "Late Fee," "Plumbing Repair," "Management Fee," "Utilities," etc.
- Description: A brief note for context (e.g., "July Rent" or "Leaky Faucet Fix")
- Income: Log all incoming money in this column
- Expense: Log all outgoing money in this column
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
Maintenance Requests
Keep a running log of all repairs to spot trends and track costs.
- Request Date: When the issue was reported
- Property/Unit: The property where the issue occurred
- Issue Type: Be consistent (e.g., "Plumbing," "Electrical," "Appliance")
- Description: Details about the problem
- Status: "Open," "In Progress," or "Closed"
- Date Closed: The date the repair was completed
- Cost: The final cost of the repair. Link this to an entry in your Financial Ledger.
Step 2: Let ChatGPT Be Your Excel Co-Pilot
Now for the fun part. You don't need to be an Excel wizard, you just need to know how to ask questions. Instead of uploading your private financial and tenant data to ChatGPT (which you should never do), you’ll describe your table structure and ask it to generate formulas for you. Treat it like your personal data analyst.
A good prompt describes your goal, mentions the sheet name, and lists the column headers. Here's a model you can adapt:
"I have an Excel sheet named '[Sheet Name]'. It has these columns: '[Column A Header]', '[Column B Header]', and '[Column C Header]'. I need a formula that '[your goal]'."
Step 3: Calculating Your Key Performance Indicators (KPIs)
Create a new tab in your spreadsheet and name it "Calculations." This is where you'll keep all the behind-the-scenes math tidy. We'll ask ChatGPT to help us build the formulas for your most important metrics.
KPI 1: Occupancy Rate
This is a fundamental health metric for your portfolio. We want to calculate the percentage of properties that are currently occupied.
Your Prompt for ChatGPT:
"I have an Excel sheet named "Properties". In column D ("Status"), I have either "Occupied" or "Vacant" for each property. Give me an Excel formula to calculate the Occupancy Rate."
The Likely Formula from ChatGPT:
=COUNTIF(Properties[Status], "Occupied") / COUNTA(Properties[Status])
Format the cell as a percentage, and you'll have your live occupancy rate.
KPI 2: Income vs. Expense for an At-a-Glance P&L
Knowing your cash flow is critical. You’ll want to see total income and an expense summary right on your dashboard.
Your Prompt for ChatGPT:
"In my "Financial_Ledger" Excel sheet, which is formatted as a table, I have columns for "Income" (E) and "Expense" (F). Give me a formula to sum all the income in the table. Then, provide a separate formula for all expenses."
The Likely Formulas from ChatGPT:
Total Income:
=SUM(Financial_Ledger[Income])
Total Expense:
=SUM(Financial_Ledger[Expense])
You can also ask ChatGPT for a simple formula to calculate Net Profit: (Total Income Cell) - (Total Expense Cell).
KPI 3: Outstanding Maintenance Tickets and Average Resolution Time
This KPI helps you stay on top of repairs and tenant satisfaction.
Your Prompt for ChatGPT:
"I have a "Maintenance" table in Excel. Column E is "Status" ("Open", "In Progress", "Closed"). Column A is "Request Date" and Column F is "Date Closed". First, give me a formula to count how many tickets are "Open". Second, give me a formula to calculate the average number of days it takes to close a ticket. This should only count tickets that have a "Closed" status and subtract the Request Date from the Date Closed."
The Likely Formulas from ChatGPT:
Number of Open Tickets:
=COUNTIF(Maintenance[Status],"Open")
Average Resolution Time (in days):
=AVERAGEIFS(Maintenance[Date Closed], Maintenance[Status],"Closed") - AVERAGEIFS(Maintenance[Request Date], Maintenance[Status],"Closed")
This looks intimidating, but you just copy and paste! It calculates the average of all closed dates and subtracts the average of all corresponding request dates.
KPI 4: Profit & Loss by Property
Understanding which properties are most profitable is essential. The best tool for this is a PivotTable, and ChatGPT can walk you through it.
Your Prompt for ChatGPT:
"My "Financial_Ledger" table has columns for "Property/Unit", "Income", and "Expense". Explain step-by-step how to create a PivotTable in Excel that shows the total Income, total Expense, and a calculated Net Profit column for each individual property."
ChatGPT will give you a walkthrough that looks something like this:
- Select any cell inside your Financial_Ledger table.
- Go to the Insert tab and click PivotTable.
- Place it in a new worksheet.
- In the PivotTable Fields pane on the right:
- To add Net Profit, go to the PivotTable Analyze tab >, Fields, Items, & Sets >, Calculated Field.
You now have a clean summary table showing the financial performance of each property, which will auto-update as you add new transactions.
Step 4: Designing Your Dashboard Layout
With all your KPIs calculated, it's time to build the visual dashboard. This is where you combine everything into a functional and easy-to-read report.
Creating Your Dashboard Sheet
Create one last tab and name it "Dashboard". You can format it with a simple grey background to make your charts and KPI cards pop.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
Adding KPI Cards
These are the big numbers at the top of your dashboard. Instead of trying to put complex formulas here, we'll simply link to our cells in the "Calculations" tab.
Steps:
- Go to the Insert tab >, Text >, Text Box. Draw a box on your dashboard for your first KPI, e.g., "Occupancy Rate."
- Click on the border of the text box (not inside where the text cursor blinks).
- Now, click into the Formula Bar at the top of Excel.
- Type = and then navigate to your "Calculations" tab and click the cell that contains your Occupancy Rate formula. Press Enter.
- Style the text box to make it look nice (larger font, center alignment). Below it, add another text box with the label "Occupancy Rate."
Repeat this process for your other KPIs like Total Income, Net Profit, and Open Maintenance Tickets.
Visualizing Trends with Charts
Let's add some visuals. Charts are much easier to interpret than tables.
Your Prompt for ChatGPT:
"I have a PivotTable showing total expenses broken down by "Category". What is the best Excel chart to show which categories make up the biggest portion of my overall expenses?"
ChatGPT will almost certainly recommend a Pie Chart or a Bar Chart. To create it:
- Click on your PivotTable that summarizes expenses by category.
- Go to the Insert tab and choose a chart from the charts section (e.g., Pie Chart).
- Cut and paste this chart onto your Dashboard tab. Clean it up by giving it a title and maybe removing unnecessary labels.
Making it Interactive with Slicers
Slicers are user-friendly buttons that filter your PivotTable data, and by extension, your charts. This is what truly makes a dashboard feel dynamic.
Steps:
- Click on any of your PivotTable-based charts on your dashboard.
- Go to the PivotTable Analyze tab and click Insert Slicer.
- A box will appear with your data columns. Check the box for "Property/Unit."
- A slicer with buttons for each of your properties will appear. Move it to a convenient spot on your dashboard.
Now, you can click on any property name, and your charts and PivotTable data will instantly filter to show you information for just that property!
Final Thoughts
You’ve just set up a powerful property management dashboard by organizing your data, asking simple questions, and assembling the pieces in Excel. This turns a cluttered collection of files into a centralized source of truth, helping you spot problems before they escalate and identify opportunities to increase profitability.
While an Excel dashboard is a massive step up from manual tracking, there's a limit to how much you can automate without it getting clunky. For instance, linking bank accounts or software like QuickBooks still requires manual data entry or messy CSV exports. At Graphed, we built an AI data analyst to solve exactly this problem. You connect your data sources (like Google Sheets, accounting platforms, etc.) one time, and from there you can simply use natural language to ask for dashboards and reports, like “Create a dashboard showing profit and loss by property from my QuickBooks data.” It generates real-time, interactive dashboards without you ever having to set up a table or write a formula yourself.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.