Multi-entity financial consolidation is one of the more complex things you can do in a spreadsheet. If you have two or more business entities and need to produce a combined financial report, you're dealing with intercompany transactions to eliminate, currency differences (sometimes), and a chart of accounts that may not perfectly align across entities. This guide shows you how to approach it in Google Sheets.
Structure: One Tab Per Entity, One Consolidated Tab
The cleanest structure for multi-entity consolidation is one tab per entity containing that entity's trial balance or P&L, plus a Consolidated tab that pulls from all entity tabs.
Each entity tab should have the same structure: account name in column A, account code in column B, and the balance or amount in column C. Using the same chart of accounts across entities makes consolidation much easier — if accounts don't align, you'll need a mapping table.
Pulling Entity Data into the Consolidated View
On the Consolidated tab, list all accounts in column A. For each entity, add a column that pulls the corresponding balance using VLOOKUP or SUMIF:
=IFERROR(VLOOKUP(A2, Entity1!A:C, 3, FALSE), 0)
Do this for each entity. The last column is the total:
=B2+C2+D2
Or SUM across all entity columns. This gives you a side-by-side view of each entity's financials with a combined total.
Eliminating Intercompany Transactions
If Entity A sells services to Entity B, that revenue appears in Entity A's P&L and that expense appears in Entity B's P&L. On a consolidated basis, neither should appear — they cancel out. Add an Eliminations column where you manually enter the intercompany amounts as negatives. Include this in your total formula.
Keep a log of all elimination entries with a description so auditors can follow the logic.
Handling Different Chart of Accounts
If entities use different account names for the same thing, build a mapping table: Entity Account Name in column A, Consolidated Account Name in column B. Use VLOOKUP in your entity tabs to map each account to the consolidated name before pulling into the Consolidated tab.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have each entity's trial balance already in the spreadsheet.
"I have three entity P&Ls on separate tabs (Entity1, Entity2, Entity3) with the same account structure. Build a consolidated P&L on a new tab showing each entity in a column with a combined total, and a separate Eliminations column for intercompany entries."
SheetXAI reads all three tabs, builds the consolidated view with VLOOKUPs for each entity, and adds the eliminations column.
Example 2: Your entity data lives in separate accounting systems.
"Pull P&L data for our three entities from QuickBooks and build a consolidated financial report with elimination entries for our intercompany management fees."
SheetXAI connects to all three entities and builds the full consolidated report.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Profit and Loss Report in Google Sheets, How to Build a Budget vs. Actuals Report in Excel, and Google Sheets AI Guide.
