In the previous session we built a four-tab management reporting workbook — a P&L, a Balance Sheet, an AR Aged Debt report, and an AP Aged Debt report. The workbook refreshes with a single click (Run All Reports) and keeps filters consistent across tabs automatically.
In this session we are going to add a fifth tab — an Executive Summary — that pulls the key headline figures from across the pack into a single, clean one-pager. The kind of page a CFO or board member would look at first.
To do that reliably, we need to understand Excel Tables — and by the end of this session you will see exactly why they make this kind of summary so much more robust than a standard approach.
Here is a video, or read below — don't forget the exercises at the bottom.
It's a common point of confusion. To most people, anything with rows and columns in Excel looks like a "table." In Excel terminology though, there is a significant difference between a Standard Data Range and an official Excel Table. Understanding that difference will change how you work with FastClose data in Excel.
Think of a normal data range like a pile of loose papers, and an Excel Table like a neatly bound, smart notebook. Here's how the two compare:
1. Dynamic vs. Static Ranges
With a normal data range, if you have a formula like =SUM(A1:A10) and a new row of data appears in row 11 after a refresh, the formula won't pick it up — you'll need to update the range manually.
Excel Tables are dynamic. Add a new row or column and the table expands automatically, taking any connected formulas, charts, or Pivot Tables with it.
2. Formula References
Normal data ranges use fixed cell coordinates. To calculate profit, you might write =B2-C2 and drag it down the column.
Excel Tables use Structured References, which use column names instead of cell letters. The same formula inside a table looks like =[@Revenue]-[@Expenses]. Type it once, press Enter, and Excel fills it down the entire column automatically.
3. Formatting
Banded row colours on a normal data range need to be applied manually and will break when rows are inserted or deleted.
Excel Tables apply professional alternating row formatting automatically, and it adjusts itself whenever the data changes.
4. Header Behaviour When Scrolling
With a normal data range, column headers disappear off screen as you scroll down unless you manually apply Freeze Panes.
With an Excel Table, as you scroll down, the standard column letters (A, B, C...) are automatically replaced by the table's header names, so you always know what you're looking at.
Open the workbook from the previous session and switch to the P&L tab. The report is sitting on the sheet as a standard data range — just cells, with no particular structure beyond what FastClose has put there.
Now imagine you want to pull the Revenue figure from this sheet onto a new summary tab. You could click on the Revenue cell, note that it is in — say — cell E12, and write a formula on the summary tab that references ='P&L'!E12.
That works. Until it doesn't.
The next time the report is refreshed with a different filter — a different entity, or a period where there are more or fewer rows above Revenue — cell E12 might no longer contain Revenue. The row has shifted. Your formula is now pointing at the wrong number, and it will do so silently, with no error to warn you.
Now you could seek to avoid this problem using a VLOOKUP or XLOOKUP in your formulae, but it's fiddly and again can break if the size of the data returned varies dramatically.
For reports that use a hierarchy such as a P&L or Balance Sheet, there is a further option using a control that goes a long way to producing a fixed size output. Click on Worksheet Options and use the "Rows down to depth" setting to only display rows to a fixed level in the hierarchy.


Then, as long as the nominals are all at the level below the restriction set above, you get a fixed row layout and can use simple formulae.
However if you're not working with hierarchies, or you want to be able to see detail all the way down to the account nominal then using simple formulae to reference cells, is likely to break.
And this is the core problem with referencing a standard data range when the data can change shape. Excel Tables solve it by letting you reference data by column name rather than cell position. The reference stays accurate however the data shifts.
We are going to switch all four report tabs to Table mode before building the summary. Let's start with the P&L.
Before switching, make sure the P&L worksheet tab is named clearly — P&L works fine. As we mentioned in the previous session, the worksheet name becomes part of the table name, so a clear name here makes your summary formulas much easier to read.
With the P&L tab active, open Worksheet Options from the FastClose ribbon.


One change to make here:
Switch on Display Results as Table
Click OK and re-run the report.

You will notice several things have changed:
AutoFilter dropdowns have appeared on every column header
The header row is now frozen as you scroll
The Table Design tab has appeared in the ribbon
The table has been given a name, prefixed with the worksheet name
Switch to the Balance Sheet tab, open Worksheet Options, switch on Display Results as Table, and re-run.
Do the same for AR Aging and AP Aging, but this time also switch off Show Subtotals and Grand Totals.

All four tabs are now running as Excel Tables. Every column in every report can now be referenced by name, from any sheet in the workbook.
Click the + button to add a new worksheet. Rename it Summary and drag it to the leftmost position so it is the first tab the reader sees when they open the workbook.

This tab is going to be a clean, simple one-pager. No FastClose report here — just a summary table we build ourselves, pulling figures from the four report tabs using structured references.
Add a logo and title if you like, and then set up a simple layout with two columns — a label column on the left and a value column on the right. Something like this:
KPI | Value |
Profit & Loss | |
Revenue | |
Gross Profit | |
Operating Profit | |
Balance Sheet | |
Total Assets | |
Total Liabilities | |
Net Assets | |
Receivables & Payables | |
Total AR Outstanding | |
Total AP Outstanding |

Format the section headers in bold.
Click in the Revenue value cell. Now let's write a formula that references the P&L table by name.
The period columns in the P&L (Cve01 to Cve12) hold cumulative values, so each month already includes everything before it. Adding them together would double-count. What we actually want is the single value from the current month's column. To get it, we combine INDEX and MATCH:
=INDEX(PLTable,MATCH("Sales",PLTable[Account],0),MATCH("Cve"&TEXT(MONTH(TODAY()),"00"),PLTable[#Headers],0))
Here's how it works:
MATCH("Sales",PLTable[Account],0) finds which row of the table holds the Sales account.
"Cve"&TEXT(MONTH(TODAY()),"00") builds the column name for the current month. MONTH(TODAY()) returns the month number (for example, 3 for March), and TEXT(...,"00") pads it to two digits, giving Cve03.
MATCH(...,PLTable[#Headers],0) finds which column of the table has that header.
INDEX(PLTable, row, column) returns the value where that row and column meet.
In plain terms: find the Sales row, find this month's column, and return the figure where they cross.
Format the cell as currency and the result should look something like this:

The figure appears. Crucially, this formula references the table by name and column, not by cell address. If the P&L report is refreshed and Revenue moves to a different row — for example, because an additional account has been added — the formula will still find it correctly. And because the month is worked out from today's date, it moves on to the next column automatically as the year progresses.
Build out the remaining P&L rows in the same way, changing only the account label to match the account name as it appears in the report:
=INDEX(PLTable,MATCH("Cost of Sales",PLTable[Account],0),MATCH("Cve"&TEXT(MONTH(TODAY()),"00"),PLTable[#Headers],0))=INDEX(PLTable,MATCH("GP",PLTable[Account],0),MATCH("Cve"&TEXT(MONTH(TODAY()),"00"),PLTable[#Headers],0))
The label in your MATCH formula needs to match exactly how the account or subtotal row appears in the FastClose report. If the formula returns #N/A, check the spelling against the actual report data.
This formula assumes period 1 is January. If your financial year starts in a different month, the current period number won't match the calendar month, so you'll need to adjust how the column name is built.
The Balance Sheet figures work in a similar way, but because we are only interested in one total rather than the sum of several periods, we can use SUMIF instead.
=SUMIF(BalanceSheetTable[Account], "Total Assets", BalanceSheetTable[This Year YTD Actual])

And for the remaining rows:
=SUMIF(BalanceSheetTable[Account], "Total Liabilities", BalanceSheetTable[This Year YTD Actual])=SUMIF(BalanceSheetTable[Account], "Net Current Assets", BalanceSheetTable[This Year YTD Actual])

For the AR and AP totals we want the sum of the Total column. Because we switched off Show Subtotals and Grand Totals when setting up the tables, there is no total row in the report itself, but we can calculate it cleanly using SUM with a structured reference:
=SUM(ARAgingTable[Total])
=SUM(APAgingTable[Total])

These add up every value in the Total column of each table — however many rows there are — and will update automatically when the reports are refreshed.
With all the formulas in place, the summary should now be showing live figures pulled from across the four report tabs.
Let's test it properly. Switch to the P&L tab and change the Company filter to a different company. Click Run All Reports to refresh the whole workbook.

Switch back to the Summary tab. Every figure should have updated to reflect the new company without touching a single formula on the summary page.

This is the key advantage of building the summary on top of named Excel Tables. The summary is completely insulated from changes in data layout. It will keep working correctly through filter changes, period rolls, and report refreshes.
A summary page is a natural home for a simple chart. Let's add a visual showing revenue over the 12 periods.
Go to the P&L worksheet and select the 12 Sales value cells.

Navigate to the Insert ribbon and select Recommended Charts.

Excel offers a range of suitable charts based on the selected data.

Select Line Chart and click OK.
The chart will appear over the grid.
Edit the Report Title as you wish, then cut and paste the chart onto your Summary page.

The workbook now has five tabs and a summary page that:
Pulls live figures from four FastClose reports using named table references
Updates automatically whenever the workbook is refreshed — no formula maintenance needed
Stays accurate even when the underlying data changes shape after a refresh
Includes a chart that updates alongside the data
The Executive Summary tab is the kind of page you can hand to a senior stakeholder with confidence, knowing that every figure on it is coming directly from the FastClose data with no manual intervention in between.
In the next session we will look at Dynamic Array Functions — a set of powerful Excel functions that work particularly well with the named tables we have just set up, and open up further possibilities for slicing and analysing the data in your workbook.
Now try the downloadable exercises below:
07.02 Building an Executive Summary with Excel Tables - Exercises.pdf
Your feedback helps us do better, to fill in a short survey on this tutorial, click here.
Move on to 07.03 Dynamic Array Functions