Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for property managers

For property managers using Excel to maintain rent rolls, pursue arrears and organise repairs, The Complete Excel Course with AI Integration offers relevant lessons without property data. This guide selects Tables for rent rolls, formulas and date functions for arrears and lease ends, unit lookups, PivotTables for building income, TaskFlow for repairs and FinanceGuard for budget versus actual reporting.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Organise units, tenants, rents and lease dates in a rent roll Table, then filter by building and sort by lease end.
  • Calculate how long arrears have been outstanding using date arithmetic, assign IFS age bands and use SUMIFS to total each tenant's debt.
  • Use TODAY, FILTER and SORT to show leases ending or reaching a rent review within 90 days, with matching rows coloured in the rent roll.
  • Record repairs and contractor work, choose statuses from drop-downs, highlight overdue jobs and calculate remaining working days with NETWORKDAYS.
  • Compare actual service charge costs with the budget, showing variance and percent variance through colour-coded number formats.
  • Summarise occupancy and rental income for each building and month using PivotTables, with slicers and PivotCharts.

Projects

What you will build.

The course has 14 projects. These five matter most here.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Use unit numbers with XLOOKUP to retrieve tenant and unit details on any sheet, calculate month ends with EOMONTH and keep an upcoming lease expiry list current with FILTER.

  • Task tracker in Excel (TaskFlow): start and due dates, status, priority and a progress bar for each task, with working days left, days past due and unfinished high-priority tasks highlighted.

    Project 07

    TaskFlow

    One sheet that shows what is done, what is late and what is urgent.

    Give repair requests and contractor visits a priority, status and target date in a maintenance log. Colour late jobs and count the working days remaining.

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Compare a building's service charge allocation with its spending, colour variances according to direction and use dashboard slicers to select a building or cost heading.

  • Project 02

    Dunder Michael Paper Company employee database

    Practise keeping employee records in a clean Table through sorting, filtering, tidying names and removing duplicate rows. These are the list management skills a rent roll uses.

  • Project 03

    Sales Goal Tracker

    Apply the tracker's formulas to arrears: calculate time unpaid with date functions, group debts into IFS age bands and use SUMIFS to total rent owed by tenant and building.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 63 of them, 5 h 26 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 19 Excel tables

Start with Lesson 19 if entering data and writing a simple formula are familiar: a properly organised Table is the foundation of a rent roll. For a first introduction to Excel, choose Lesson 1. With tidy lists already in place, move to Lesson 28 for arrears calculations or Lesson 79 to begin the maintenance tracker. That first section, Managing data in Excel, is free by email with its practice files: ask for it on the course page.

Get the free section by email

63 of 186 lessons · 5 h 26 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Employee records provide practice for organising a rent roll, another list of the same kind. Build an Excel Table containing units, tenants, rents and lease dates; remove stray spaces and duplicate rows; sort by lease end and filter by building. Freeze the headers and prepare a tidy printout.
  1. 19 Start here Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Calculate days owed with date arithmetic and group arrears using nested IF and IFS. Use SUMIF and SUMIFS to add rent due and received for tenants and buildings, and COUNTIFS to count occupied and empty units. AND and OR support conditions such as a tenant being late and owing more than a limit.
  1. 28 Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
  3. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  4. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  5. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  6. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  7. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  8. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  9. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  10. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  11. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  12. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  13. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min The sales examples demonstrate summaries that also suit a rent ledger. Apply PivotTables to income and occupancy by building and month, group dates, select buildings with slicers and display the results in PivotCharts. Refresh the report after adding the month's receipts.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 12 lessons · 54 min Retrieve records by tenant or unit using VLOOKUP, XLOOKUP, INDEX and MATCH, handling missing units with IFERROR. Calculate lease and month ends with TODAY and EOMONTH, then combine FILTER and SORT to maintain a current list of leases approaching expiry or review.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  6. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  7. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  8. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  9. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  10. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  11. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
  12. 78 Excel Ninja Challenge XLOOKUP, totals and text concatenation lookup challenge 3 min
09 Project: task automation with data validation and forms 9 lessons · 49 min Adapt TaskFlow's task columns to repair requests and contractor work. The maintenance log uses drop-downs for status and priority, highlights overdue jobs and includes a timeline, remaining working days and sheet protection.
  1. 79 TaskFlow project tracker in Excel Overview of the task tracker dashboard 4 min
  2. 80 Designing a task tracker in Excel TaskFlow layout, columns and headers 5 min
  3. 81 Data validation in Excel Status and priority drop-down lists in TaskFlow 8 min
  4. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
  5. 83 Gantt chart in Excel Build a project timeline with conditional formatting 6 min
  6. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  7. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  8. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
  9. 87 Testing an Excel task tracker Stress test TaskFlow with sample data 3 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min A company budget supplies the FinanceGuard example, with a structure that also suits a building or service charge budget. Compare budget and actual tables through variance and percent variance, colour overspending and present the figures with PivotTables, KPI cards, a combo chart and slicers.
  1. 115 FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course can property managers use for their workbooks?

The Complete Excel Course with AI Integration from Coding Liquids teaches skills property managers can apply to their spreadsheets. These include Excel Tables for rent rolls, date arithmetic and IFS for ageing arrears, FILTER and TODAY for upcoming lease expiries, TaskFlow for a maintenance log and FinanceGuard for comparing service charge budgets with actual costs. The examples contain employee, sales and budget data; the steps also work for units and tenants. The selection on this page comprises 63 lessons, taking 5 hours 26 minutes.

Can property managers use this course to create a rent roll in Excel?

Yes. In The Complete Excel Course with AI Integration, the whole of Managing data in Excel is included here and is free by email. It teaches the list skills needed for a rent roll: Tables with structured references and a total row, filters and sorting at several levels, which on a rent roll means building followed by lease end. TRIM and Remove Duplicates help clean untidy exports, while freezing the header keeps it visible. With XLOOKUP, taught in later lessons, you can retrieve tenant details on other sheets through the unit number.

How can property managers find leases expiring within 90 days?

Use the lease dates recorded in the rent roll with the techniques in The Complete Excel Course with AI Integration. TODAY and date arithmetic calculate the days remaining; FILTER applies two conditions to select expiries from today through the next 90 days, and SORT arranges them by date. Conditional formatting highlights the corresponding rent roll rows. The same approach applies to rent reviews, and TODAY keeps the list current when the workbook opens.

Can property managers monitor rent arrears by tenant and age in Excel?

Yes. Although The Complete Excel Course with AI Integration has no lesson using rent data, its formulas support this work. Calculate days overdue by subtracting the due date from TODAY, use IFS to assign your chosen age ranges and sum each tenant's outstanding balance with SUMIFS. A PivotTable can then summarise arrears by building and age band. The IFS lesson assigns sales performance labels using a method that transfers to these bands.

Does the course teach property managers about tenancy law or property management software?

No. The Complete Excel Course with AI Integration teaches Excel, and its datasets do not include a property portfolio. It does not teach property or tenancy law, lease accounting, service charge rules or property management software. Its relevance is to the supporting spreadsheets: rent rolls, arrears records, lease expiry lists, repairs logs and building budgets.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic