About this article
This article was created using an automated generation workflow powered by generative AI. It reviews Microsoft official support explanations regarding Excel tables, structured references, and table expansion via row and column additions, organizing them as a management method using a fictional equipment ledger.
Verification Status: 📘 Microsoft Official Specifications Confirmed / Excel Actual Device Unconfirmed
Information Verification Date: October 11, 2026. Access to the official page and specification explanations have been verified. The calculation results and screen operations presented are expected examples for explanatory purposes and have not been verified on an actual Excel device.
- Supported Environments, Capabilities, and Limitations (Verified on October 15, 2026)
- The difference between SUM(D2:D4) and SUM(EquipmentLedger[Amount])
- Do not misunderstand the relationship with Copilot
- Safe reproduction and verification
- Try this first: Convert a dummy equipment ledger into a table
- Summing amounts using column names
- Aggregating by department
- When automatic table expansion does not work
- Change one location and verify
- Important considerations for practical use
- Official and Primary Sources
Supported Environments, Capabilities, and Limitations (Verified on October 15, 2026)
| Item | Target and Conditions |
|---|---|
| Target Feature | Excel tables and structured referencesDistinct from Copilot Excel analysis |
| Available Excel Versions | Microsoft 365 / Excel 2024 / 2021 / 2019 / 2016 supported desktop environments, Excel for the Web, etc. |
| Required Subscription | Supported Excel product. Copilot paid add-on license is not required. |
| Data Location | Cell range of the currently open Excel workbook. Does not automatically enumerate other folders on the PC. |
| Capabilities | Naming table columns and creating references and aggregation formulas that follow row additions |
| What cannot be done | Automatic reloading of the original CSV, automatic searching of other workbooks within OneDrive, and file relocation and deletion |
| Limits | Excel sheet row and column limits, as well as performance limits, apply. This is separate from the attachment limit of Copilot Chat. |
| Caution | Verify duplicate table names in existing workbooks, column name changes, blank cells, and total row reference ranges. |
Official:Structured references、Create an Excel table。
The difference between SUM(D2:D4) and SUM(EquipmentLedger[Amount])
=SUM(D2:D4)sums a specific cell range. By creating a table備品台帳and writing it for the amount column as=SUM(備品台帳[金額])it can be referenced by the column name[金額], making formulas easier to update automatically when new data rows are added to the table. However, simply adding regular rows outside the table may not include them in the target range.Determining the table boundaryCheck the table range rather than its visual appearance.
For example, if the first three entries are 1,800 yen, 3,200 yen, and 2,500 yen, the total is 7,500 yen. Adding a fourth entry of 1,000 yen to the table would yield an expected total of 8,500 yen. If the calculation result does not change, first check whether the added row is outside the table or if the amounts are stored as text. "Not analyzing with AI" is distinct from "not being able to use Excel aggregation features."
Do not misunderstand the relationship with Copilot
When analyzing tables using Copilot in Excel, verify the available subscription, table structure, data storage location, permissions, and feature availability status. However, theSUM(テーブル名[列名])in this article refers to standard Excel formulas and does not inherently require sending data to Copilot. For confidential data, there is no need to assign tasks to AI that do not require AI processing.
Furthermore, structured references in tables are a mechanism for easily managing data within the same workbook. Saving a file to OneDrive does not mean that Copilot can edit all folders on your PC. The processing logic also differs from Copilot in OneDrive, which compares multiple files.
Safe reproduction and verification
In a practice workbookCtrl+T, press to create a table and check the table name, column names, and formulas. Add a single row and observe whether the total changes as expected. Next, write data to a cell outside the table and compare the impact on the results. Finally, record the formulas, row counts, and totals before and after the modification. It is safer to make a copy and test rather than directly modifying the original business ledger. The figures in this article are sample calculations and are not test results from actual Excel software.
If you add rows every month to an Excel list, converting the cell range into a "Table" makes it easier for aggregation formulas to track new rows. Especially in tasks where the number of items increases, such as equipment ledgers or application lists, understanding "structured references" that use column names will make formula maintenance easier.
Try this first: Convert a dummy equipment ledger into a table
Enter the following dummy data into cells A1:D4 in Excel.
| Control Number | Item Name | Department | Amount |
|---|---|---|---|
| A001 | Mouse | General Affairs | 1800 |
| A002 | Keyboard | Sales | 3200 |
| A003 | USB Hub | General Affairs | 2500 |
Select the range including the headers, and select Insert -> Table. Ensure that My table has headers is enabled. Change the table name to Equipment Ledger from Table Design.
This operation not only improves the layout, but also enables Excel to manage the headers and data rows as a single entity. The difference from a simple cell range becomes apparent when adding data, sorting, or filtering.
Summing amounts using column names
Enter the following formula in another cell.
=SUM(備品台帳[金額])
The expected total is 7,500 yen. Here, Equipment Ledger is the table name, and Amount is the column header. Unlike a standard =SUM(D2:D4), the formula makes it clear what amount is being summed.
Next, add A004 / Web Camera / Sales / 4800 below the last row of the table. If the table expands automatically, the expected result for the same SUM formula will be 12,300 yen.
The important point is not that the SUM function itself has changed, but thatthe reference is now a table column rather than a fixed cell address. Verify whether the range was actually expanded by checking both the table range and the formula result.
Aggregating by department
If you want to know the purchase amount for the General Affairs department, you can combine the SUMIFS function with structured references.
=SUMIFS(備品台帳[金額],備品台帳[部署],"総務")
For the first 3 items only, the expected result is 4,300 yen. Because the condition column and sum column are specified by name, the intent of the reference remains easy to follow even if columns are rearranged later.
However, if General Affairs and General Affairs Department are mixed in the department names, they will be treated as separate values. If the aggregated total does not match, check not only the formula but also input inconsistencies and extra spaces.
When automatic table expansion does not work
If the total does not increase after adding a new row, first check whether that row is included in the table range. Inserting blank rows or entering data outside the table may result in it not being included in the expected range.
Next, check if the amount column is recognized as numbers. Even if it looks like 4800, if it is text, it may not be handled as expected in SUM calculations. Numbers stored as text may not be fixed simply by changing the cell display format.
Furthermore, if the total row is enabled, verify whether the table aggregation method is SUM or SUBTOTAL linked to filters. Aggregating only the displayed rows versus all rows serves different purposes.
Change one location and verify
Let us try changing the department for "A003" from "General Affairs" to "Sales". The SUMIFS result for General Affairs should change from 4,300 yen to 1,800 yen. This confirms that the formula conditions are based on the department column values rather than row numbers.
Next, change the header of the amount column and observe how Excel updates the structured reference. Actual behavior should be verified in your Excel environment.
Important considerations for practical use
While tables make data entry and aggregation convenient, they do not automatically provide data validation or permission management features. If you want department names to be selectable, combine this with data validation to prevent incorrect entries.
Furthermore, in workflows where multiple users edit the same file, the storage location and co-authoring mechanism are also important. Simply converting data into a table does not eliminate issues related to update conflicts or version history management.
For small ledgers, tables and structured references alone make management easier. However, if the number of records increases and you need to combine multiple ledgers, you can also consider migrating to Power Query or a database.
Official and Primary Sources
Microsoft Support: Using structured references with Excel tables
Microsoft Support: Resize a table by adding or removing rows and columns in Excel
Official Microsoft support refers to references using table and column names as structured references and explains that reference names are adjusted when data is added to or removed from a table. It also guides users that typing directly below the last row of a table expands it to include the new row.
