Why Turn a Transaction List into a Table
A bank statement exported to a spreadsheet is usually a plain range of cells. Converting it to an Excel Table gives the data a name, named columns, and a built-in total row. Formulas that refer to the table by name keep working as rows are added or removed, instead of you editing cell ranges such as C2:C200 by hand.
Creating the Table
Microsoft's page on creating tables says to select a cell within your data, go to Home > Format as Table, and choose a style. In the Create Table dialog, confirm the cell range and mark whether the table has headers, then select OK. Statement exports normally have a header row (Date, Description, Amount), so tick that option.
The same page notes that the "My table has headers" check box controls this: if it is left unchecked in Excel for the web, default names such as Column1 and Column2 are added above the data, which you can rename by double-clicking. Microsoft's structured references page also gives Ctrl+T as a way to create a table from a selected cell in the data range.
Adding a Total Row
Microsoft's page on totalling data in a table says to click inside the table, go to Table Design and select the Total Row check box. On some versions the path is Table Design > Style Options > Total Row. Excel inserts the total row at the bottom of the table, and the cells are empty the first time it is applied. Select a cell in the total row and choose a function from its drop-down list; More Functions offers further choices.
The page states that the default selections use the SUBTOTAL function, and gives the example that choosing SUM produces =SUBTOTAL(109,[Midwest]). Microsoft says this lets you include or ignore hidden table rows. In practice this means that if you filter the table, the total row reflects the rows still showing.
Date Description Amount
2026-01-03 Sample Cafe -4.50
2026-01-10 Sample Payroll 2000.00
2026-01-15 Sample Market -62.30
Total 1933.20The synthetic rows above add up as 2000.00 - 4.50 - 62.30 = 1933.20, which is what a SUM in the Amount column of the total row would show with no filter applied.
Structured References
Microsoft explains that structured references are names Excel assigns to tables and their columns, used in formulas in place of cell addresses. The basic form is TableName[ColumnName]; the page's example replaces =Sum(C2:C7) with =SUM(DeptSales[Sales Amount]). It also states that the names adjust whenever you add or remove data from the table.
If you rename the table to Transactions in the Table Design tab (Microsoft says each table gets a default name such as Table1 that you can change there), a formula elsewhere in the workbook could be written as:
=SUM(Transactions[Amount])Following the pattern on Microsoft's page, this would return 1933.20 for the three synthetic rows above. The page also notes that [#Totals] points to the total row and [@Column] points to the current row, and that column names with special characters need double brackets.
Microsoft's create-and-format page lists filtering and resizing as separate help topics and does not explain them itself, so this article does not describe those steps.
Practical Checks
After converting, confirm that the Amount column contains numbers and not text, then compare the total against the statement's own figures for the same period. If you build several tables for several statements, give each a distinct name so formulas stay readable.
If your transactions are still locked in a PDF, PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
Microsoft says to select a cell within your data, go to Home > Format as Table, choose a style, confirm the range and the headers option in the Create Table dialog, and select OK. Ctrl+T is also given on Microsoft's structured references page.
Microsoft states the default selections use SUBTOTAL; for example, choosing SUM produces =SUBTOTAL(109,[Midwest]), which lets you include or ignore hidden table rows.
Per Microsoft, it is the combination of table and column names used in a formula in place of cell addresses, such as DeptSales[Sales Amount], and the names adjust when data is added or removed.
Primary Evidence & Official References
- Create and format tables — support.microsoft.com (Verified 2026-10-29). Official source documentation →
- Total the data in an Excel table — support.microsoft.com (Verified 2026-10-29). Official source documentation →
- Using structured references with Excel tables — support.microsoft.com (Verified 2026-10-29). Official source documentation →