The question of how to merge several departmental spreadsheets into one portfolio record is deceptively simple to state and genuinely difficult to execute well. Most corporate real estate teams operate with lease data in one file, capital expenditure schedules in another, maintenance records somewhere in a shared drive, and headcount forecasts sitting with HR in a format nobody else can read. The gap between those scattered files and a single, authoritative portfolio record is not primarily a technology problem — it is a methodology problem. Solving it requires a disciplined sequence: audit what you have, establish governance before you touch a single cell, design a canonical data schema, run a controlled merge, validate against primary sources, and then embed a process that keeps the record current. What follows is a practitioner's guide to each of those stages.
Why Spreadsheet Fragmentation Happens in the First Place
Commercial real estate data fractures because the stakeholders who generate it have different primary jobs. A facilities manager tracks maintenance costs and square footage allocations in units that make sense for a work-order system. A lease administrator tracks rent escalations, break options and critical dates using the structure an external auditor requested three years ago. A finance team tracks the same leases using a present-value model built for a capital committee that required a different base date.
Each of these versions is locally rational. The problem emerges when anyone tries to answer a portfolio-level question — net rentable area by business unit, total occupancy cost per headcount, weighted average lease expiry — because the answer depends on combining files that were never designed to talk to each other. The fragmentation is not negligence; it is the natural outcome of decentralized ownership without a unifying schema.
Recognising this dynamic matters because it sets the right expectation for the merge project. The goal is not to find the "correct" spreadsheet and copy it into a master file. The goal is to design a canonical record that each departmental file can feed into, and then to create enough governance that future contributors understand which field they own and what format it must take.
Stage One — The Data Audit
Before writing a single formula, every departmental file must be catalogued. This means producing a data inventory that lists each file, the team that owns it, the last date it was meaningfully updated, the properties or leases it covers, and the key fields it contains. A simple inventory table (kept in a separate working document, not in the target master file) lets you identify overlap, spot gaps, and flag conflicts before they contaminate the merged record.
Pay particular attention to the identifier fields. If the facilities file uses a building code, the finance file uses a property address, and the lease admin file uses a landlord reference number, you have no reliable join key — which means the merge will produce phantom duplicates or silently drop records. At the audit stage, the job is to surface this problem, not solve it yet. Document every identifier scheme you find.
Also note the granularity of each file. Some teams track one row per property. Others track one row per lease. Others track one row per cost centre allocated to a property. These are fundamentally different units of analysis, and you cannot merge them without first deciding which granularity the master record will use. That decision belongs in stage two, but the audit is where you gather the evidence to make it.
Finally, assess data quality by sampling ten to fifteen rows from each file and checking whether the values are internally consistent. If a file lists a lease expiry of 2027 but the break option in the same row is dated 2029, that is a source-data quality problem that must be resolved before the merge, not after.
Stage Two — Governance and Schema Design
Schema design is the most consequential decision in the entire project. A schema is simply the agreed list of fields, their names, their data types, and their definitions. If you define "lease commencement" differently from the lease administrator's definition, you will produce a master record that is internally consistent but factually wrong for some leases. Getting the definitions right requires a working session with each departmental owner, not a unilateral decision by whoever is running the merge.
The canonical record should be organized around a stable primary entity. For most corporate real estate portfolios, that entity is the lease — specifically, the lease instrument rather than the property, because a single building may house multiple leases and a single lease may span multiple floors. Each row in the master record should represent one lease, with all property, financial, and operational attributes attached to that row.
The minimum viable field set for a commercial portfolio record typically includes the lease identifier, landlord name, property address, net rentable area, lease commencement and expiration dates, any break options with their exercise deadlines, annual base rent, rent escalation schedule, security deposit, the business unit responsible for the space, and the internal owner accountable for decisions on that lease. That list covers the basics; a more complete record would add effective rent, lease net present value calculated at a specified discount rate, fit-out cost amortization, service charge caps, permitted use clauses, assignment and subletting rights, and renewal option terms.
Governance means assigning a named owner to every field. The lease administrator owns the break option exercise deadline. Finance owns the NPV figure and must document the discount rate used. The facilities team owns the net rentable area as measured, distinguishing it from the contractual area. Without named ownership, the master record will drift back toward fragmentation within one reporting cycle.
Stage Three — Building the Identifier Bridge
Once the audit is complete and the schema is defined, the next task is to create a bridge table that maps every identifier used in every departmental file to a single master identifier. This is unglamorous work, and it is the step most teams skip — which is why their merges fail.
The master identifier can be constructed systematically: a prefix for the portfolio region, a sequential number for the property, and a suffix for each lease at that property. The bridge table then contains one column per departmental identifier scheme and one row per unique lease, with the master identifier as the join key. Every departmental file can then be matched to the master record by looking up its local identifier in the bridge table.
Building this table manually for a large portfolio is time-consuming but not conceptually difficult. Work through it property by property. For each property, verify that you can match the facilities file, the lease admin file, and the finance file to the same master row using their respective identifier columns. Where a property appears in one file but not others, flag it as a coverage gap to be investigated — not silently excluded.
The bridge table should live in a controlled location separate from the master record, treated as a reference document. It is the proof that your merge is reliable. If a future auditor or successor questions how you matched a facilities record to a lease, the bridge table provides the documented answer.
Stage Four — Standardizing Field Formats Before Merging
Merging is not simply copying columns from one file into another. Before any data moves, every field that will be merged must be standardized to the master schema's format. Date fields are the most common source of merge errors: one file stores lease expiry as "31 Dec 2027," another as "12/31/2027," and a third as a plain integer representing a serial date. All three must be converted to a single format — ISO 8601 (YYYY-MM-DD) is a sound choice because it sorts correctly as text.
Currency fields need similar treatment. Some files track rent as an annual total; others track it as a monthly figure; others track it on a per-square-foot basis. Before merging, convert everything to the same basis — annual rent in the lease currency — and document the conversion factor applied to each row. This documentation is not optional; it is the audit trail that lets a reviewer verify the master record against source files two years from now.
Text fields — property names, landlord names, business unit names — require a controlled vocabulary. If the facilities file calls a business unit "Corporate Services" and the finance file calls the same unit "Corp Svcs," the master record will treat them as two different tenants of the building. Build a lookup table for each text field that has a finite set of valid values, and run a substitution pass on each source file before merging.
Area measurements deserve special attention in a commercial portfolio. Confirm whether each source file is reporting gross internal area, net internal area, net usable area, or rentable area under a specific measurement standard. These definitions can differ materially — sometimes by ten to fifteen percent for the same physical space — so label every area field with its measurement basis and do not aggregate across different bases without a conversion step.
Stage Five — The Controlled Merge Process
With the bridge table built and all source fields standardized, the actual merge can proceed. Use a staging environment — a separate working copy of the master template — rather than writing directly into the final record. This allows you to run validation checks before the data becomes the authoritative version.
Work field by field, not file by file. Pull every instance of "lease expiry date" from every source, compare them for each lease using the master identifier as the join key, and resolve conflicts before moving to the next field. This discipline prevents one department's error from silently overwriting another department's correct value. Where two sources disagree on a date or a figure, go back to the primary source document — the executed lease — and treat the lease as the authority.
For fields that exist in only one source file, the merge is straightforward: copy the standardized value into the master row. For fields where multiple sources contribute different pieces — for example, the lease admin file has the base rent and the finance file has the escalated rent — make sure the master record has separate, clearly labelled columns for each rather than collapsing them into a single "rent" field that obscures which version is being reported.
After each field pass, run a row count check. The master record should have the same number of rows at the end of the merge as the number of unique leases identified in the bridge table. If rows have appeared or disappeared, something went wrong in the join logic and must be investigated before proceeding.
Stage Six — Financial Reconciliation and Portfolio-Level Validation
Once the merge is structurally complete, the financial figures need a reconciliation pass. Sum the annual base rent across all rows in the master record and compare it to the finance team's total occupancy cost figure for the same period. If the numbers differ, the gap tells you something: either coverage is incomplete (some leases in finance are not in the master), the rent basis differs (one source is annualizing monthly figures incorrectly), or currency conversion is inconsistent.
A useful reconciliation technique is to build a simple lease NPV column in the master record. For each lease, calculate the present value of remaining base rent payments at a documented discount rate — a hypothetical example might use a seven percent discount rate to reflect a mid-market cost of capital — and sum across the portfolio. This figure should be directionally consistent with what the finance team carries on the books for right-of-use assets under the applicable accounting standard. A material discrepancy is a signal to investigate, not to adjust the formula to make the gap disappear.
Also validate critical dates as a separate check. Produce a list of every break option exercise deadline and lease expiry falling within the next eighteen months. Cross-reference this list against the lease administrator's renewal and expiry tracker. Any lease that appears in one list but not the other requires investigation. Critical dates that are missed because they were in one spreadsheet but not the consolidated record are among the most expensive errors a corporate real estate team can make.
Portfolio-level validation should include a geographic cross-check. Plot the properties in the master record against a map of the organization's operational footprint. If a region has significant headcount but very few leases in the master, that is a signal that some locations were not captured in the source files — perhaps subleases, managed offices, or short-term licences that were tracked informally.
Stage Seven — Source Documentation and Audit Trail
A merged portfolio record without an audit trail is no more trustworthy than the departmental spreadsheets it replaced. For every field in the master record, the documentation should answer three questions: where did this value come from, when was it last verified against the primary source, and who is responsible for keeping it current.
The simplest approach is to add a "source" column alongside every key field. For the lease expiry date, the source column might read "Lease abstract, section 3.1, verified against executed lease." For the annual base rent, it might read "Schedule 1 to the lease, confirmed against latest rent demand." These source annotations transform the master record from a spreadsheet into a documented data asset.
For a large portfolio, consider a companion log that records each update to the master record: the date of the update, the field changed, the previous value, the new value, the source document that justified the change, and the name of the person who made the update. This log is what allows a new team member or external auditor to reconstruct the history of the record without having to track down the original contributors.
Stage Eight — Ongoing Maintenance Without Re-Fragmentation
The merge project is not complete when the master record is first assembled. The harder challenge is ensuring that departmental files do not re-fragment the data over the following months. Departmental teams will continue to maintain their own operational files — that is both inevitable and appropriate — but those files must feed the master record on a defined schedule rather than drifting independently.
Establish a reporting cadence. For fields that change infrequently — lease terms, landlord details, security deposits — a quarterly reconciliation is usually sufficient. For fields that change frequently — cost centre allocations, headcount per location, capital expenditure commitments — a monthly update is more appropriate. Assign the reconciliation task explicitly; if nobody owns the cadence, it will not happen.
Design each departmental file to export a standardized update package rather than requiring manual re-entry into the master record. This means each file has a defined export range — a named region containing exactly the fields the master record needs, in exactly the format the master record expects — that the departmental owner can produce at each reporting cycle. The master record owner then imports that package, runs the same validation checks used in the original merge, and updates the master record with any changes. This approach keeps departmental autonomy intact while maintaining master record integrity.
Applying a Portfolio-Strategy Lens to the Merged Record
Effective rent adjusts the headline rent for landlord contributions, rent-free periods, and fit-out incentives, producing a figure that reflects the true economic cost of occupying a space. Calculating effective rent requires data that typically sits in multiple departmental files — base rent in lease admin, fit-out incentive in the capital file, rent-free period in the legal file — so the merge is what makes the analysis possible in the first place.
Weight each lease's remaining term by its annual base rent contribution to the portfolio total, and sum across all leases. A WALE below two years signals material near-term re-leasing risk; a WALE above five years signals stability but potentially limited flexibility. Neither interpretation is possible without a consolidated view of the portfolio.
Handling Exceptions — Subleases, Licences, and Managed Offices
Most methodology guides for portfolio consolidation focus on direct leases, but a complete record must also cover the exceptions: subleases where the organization is sublandlord, licence agreements for managed offices and co-working arrangements, and any residual occupancies from prior transactions — spaces held over after a lease expiry while negotiations continue.
Subleases as sublandlord create bilateral exposure: the organization still has obligations to the head landlord, while simultaneously holding receivables from the subtenant. The master record should capture both sides — the head lease terms in the standard fields and the sublease income in a separate section — so that net occupancy cost per location reflects the actual economics after subletting income.
Licence agreements for managed offices are often treated as operating expenses and excluded from the lease register, but they represent real occupancy commitments and real per-headcount costs. Including them in the master record, even with a flag distinguishing them from formal leases, gives leadership an accurate picture of total space costs. A common approach is to convert the monthly licence fee to an annualized figure and assign it a notional lease expiry based on the minimum notice period in the licence.
Holdover occupancies are legally distinct from leases in most jurisdictions, but they carry real financial and operational risk. A space held over after expiry may be subject to a higher occupancy cost (often double or treble rent under common law provisions, though the specific terms will depend on the jurisdiction and the lease — verify with counsel for any specific situation). Capturing these in the master record with a clear status flag ensures they are not forgotten during portfolio planning cycles.
Connecting the Merged Record to Decisions
A portfolio record that is consulted only at lease expiry is an underused asset. The disciplines built during the merge project — clean identifiers, standardized formats, documented sources, named owners — create the infrastructure for ongoing, evidence-based occupancy decisions.
Scenario modelling at lease events is another downstream benefit.
Fit-out cycle projections require knowing when each lease was last refurbished and when the next expiry or break creates a natural refresh trigger. If fit-out history lives only in a facilities spreadsheet that has never been reconciled with the lease admin file, the capital plan will inevitably miss some locations and double-count others.
About Advantai
Advantai is a commercial real estate intelligence and operations platform operated by ADVANTAGE AI LLC, a Delaware limited liability company. It connects client relationships, property research, documents and financial decisions in one workspace for commercial real estate teams — advisers and brokerage teams, occupier and facility teams, and portfolio teams. The platform covers CRM and origination, requirements and site selection, Property X-Ray (an interactive 3D building workspace), financial modeling and comparison, document intelligence, transactions and diligence, client collaboration, and portfolio strategy with critical dates. The optional Super Agent upgrade adds specialist, source-backed research and automated scenario analysis.
Get Started with Advantai
Ready to see your next move clearly? Go to advantaico.com, click Request a demo and tell us about your next project. Prefer to start with a single project? Visit advantaico.com/getting-started to plan your first one.