Excel Tables and Formulas: How to Use Them Together

Formulas & functions · Updated November 20, 2026

Yes. In Excel for Microsoft 365 on Windows, select your data and choose Insert > Table, then enter a formula in a table column; Excel can fill it down and extend it to new rows. If you mean a multiplication grid rather than an Excel Table, you can also build one with a formula and fill it across and down.

What an Excel Table Is

An Excel Table is a structured range of data, not just a group of cells with borders or shading. Tables have named columns, built-in sorting and filtering, and formulas that can refer to those columns by name. When you add data directly below a table, Excel can expand the table to include it, so its formatting, filters, and calculated columns continue to work.

For example, a sales table might have the headers Product, Qty, Unit Price, Revenue, and Region. Each row describes one sale. Turning that range into a Table does not change the values or automatically invent calculations; it gives Excel a structure that makes managing the data and writing formulas easier. A formatted range can look like a table without having these structured features.

Excel assigns a default name such as Table1. You can rename it to something meaningful, such as Sales, using the Table Design tab’s Table Name box on Windows. A clear name makes formulas easier to read, especially when a workbook contains several datasets.

Create a Table Before Adding Formulas

Put one set of column headings in the first row, with one record per row and no completely blank rows or columns splitting the data. Select a cell in the range, then choose Insert > Table. Check that the proposed range is correct, select My table has headers if the first row contains labels, and choose OK. Excel for Mac also offers table creation from the Insert tab; the location and appearance of contextual controls can differ slightly by platform.

  1. Use one distinct heading per column. Avoid blank or duplicate headings, since Excel needs column names for structured references.
  2. Keep each column’s contents consistent where possible—for example, quantities in Qty and dates in Order Date—so sorting, filtering, and calculations behave predictably.
  3. After creating the Table, use its filter arrows to narrow the records, and rename it if a descriptive name will help with formulas.

On Windows, Ctrl+T is a shortcut for creating a Table after selecting a cell or range. On a Mac, use Insert > Table if the shortcut differs with your keyboard settings or Excel version. A Table can also be created from an existing range without first applying a visual table style.

Enter a Formula in a Table Column

To calculate a value for every record, add a column heading such as Revenue and type a formula in the first data cell beneath it. In a table named Sales with columns Qty and Unit Price, the row formula is:

Assume the formula is being entered in the Revenue column of the Sales table, in the same row as a Qty of 3 and a Unit Price of 12.50. The formula multiplies those two values and returns 37.5:

=[@Qty]*[@[Unit Price]]

The at sign means “this row.” The brackets identify the table columns, and the extra brackets around Unit Price accommodate the space in its heading. Excel generally recognizes a formula entered in one cell of a table column as a calculated column and fills it into the other rows. If it does not, check Excel’s formula and table settings or copy the formula down the column.

Calculated columns are useful because each row follows the same rule. If a new sales row is added to the table, Excel can extend the formula into it. This reduces the chance that a new record will be left out of a manually selected formula range. It is still worth checking that the new row contains the expected calculation, especially in a workbook with unusual formatting or pasted data.

Use Structured References to Summarize Data

Structured references use table and column names instead of fixed cell addresses. A reference to Sales[Revenue], for example, means the Revenue values in the Sales table. Unlike a range such as D2:D100, the table reference adjusts as the table changes, and the name tells a reader what the calculation is summarizing.

Assume the Sales table has three Revenue values: 37.5, 20, and 45. This formula adds the Revenue column and returns 102.5:

=SUM(Sales[Revenue])

Structured references can also be used with criteria. Assume Sales[Region] contains West, East, and West, and cell H2 contains West. This formula counts the matching rows and returns 2:

=COUNTIF(Sales[Region],H2)

Inside a table row, an at-sign reference targets the current record; outside a table, a reference such as Sales[Revenue] usually refers to the column’s data. Excel also supports special table references for parts such as headers and totals, but the basic table-and-column form is enough for many summaries. If a heading is renamed, Excel normally updates references it tracks within the workbook.

Choose Between a Table Formula and a Totals Row

Use a calculated column when you need a result for each record, such as revenue per sale, days between two dates, or a status derived from other fields. Use a summary formula when you want one result for the whole dataset, such as total revenue or the number of sales in a region. A Table’s Total Row is a convenient built-in option for common summaries.

To show it, select a cell in the Table and use Table Design > Total Row on Windows. A row appears below the records, and its cells can offer summary choices such as Sum, Average, and Count. The available choice depends on the column’s contents. For a filtered Table, Excel’s Total Row commonly uses a subtotal calculation that responds to filtering, unlike a basic sum that includes hidden rows. Check the displayed result if the workbook has additional hidden rows or special filtering needs.

A Total Row is convenient for an on-screen summary, but it is not a substitute for a separate report when you need several summaries, a chart, or a layout designed for printing. In those cases, put summary formulas elsewhere or use a PivotTable. A table reference can make a separate summary formula easier to maintain when records are added.

Build a Multiplication Table with a Formula

Some people asking about a “table using Excel formulas” mean a grid of multiplication results, rather than the formal Excel Table feature. For that layout, put the first factor of each row in A3:A12 and the second factor of each column in B2:K2. Enter the following in B3; if A3 is 2 and B2 is 1, it returns 2:

=$A3*B$2

Fill the formula across and down the result area. The dollar signs lock the factor column A and the factor row 2 while allowing the other reference to change as the formula moves. This creates a grid of calculated products, but it is not itself an Excel Table with named columns and filter controls. Use this approach for a calculation matrix; use Insert > Table for a dataset you want to sort, filter, and extend.

Common Formula Problems and Fixes

  • The formula did not fill the column: Confirm that you entered it inside the Table, under the intended heading. A blank row, inconsistent pasted data, or a setting that prevents automatic calculated-column filling can affect the result. You can copy the formula into the remaining table cells if needed.
  • A structured reference shows an error: Check the table name and spelling of the column heading. Spaces and punctuation in headings must be included in the reference’s bracket syntax; typing a reference by selecting the table cells can help Excel insert the correct form.
  • New records are missing from a result: Check whether the records were added inside the Table or immediately below it so Excel expanded the range. Data pasted several rows away may remain outside the Table, and a summary of the Table will not include it until the Table is expanded.
  • The result is wrong after sorting: A table row formula should calculate from that row’s values, so sorting the Table normally keeps each record together. Check that the formula uses current-row references and that the source values are numbers rather than text that only looks numeric.
  • A total changes when you filter: That can be expected for the Table Total Row. Decide whether the goal is to summarize visible records or all records, then choose a suitable summary method and verify it against the filtering behavior you need.

Frequently Asked Questions

Do formulas automatically fill down in an Excel Table?
Usually, yes. Enter a formula in a table column and Excel normally treats it as a calculated column, applying the same pattern to other rows and new rows. If it does not, check the formula, the table boundaries, and Excel’s calculated-column behavior.

Can an Excel Table contain different formulas in the same column?
It can, but a calculated column is designed to use one consistent formula pattern. Different formulas in different rows can make the column harder to understand and may stop Excel from treating it as a uniform calculated column. Consider adding a separate column or using a clear condition in one consistent formula.

How do I add a new row to a Table?
Type in the row immediately below the Table or use its last cell and press Tab to move to a new row. Excel usually expands the Table and carries down calculated-column formulas. If the range does not expand, check the Table boundary and add the row through the Table’s cells.

Are Excel Tables the same as PivotTables?
No. An Excel Table organizes source records and provides structured references, filters, and calculated columns. A PivotTable summarizes and rearranges data to show grouped results. A PivotTable can use a Table as its source, but the two features serve different purposes.

Can I use normal cell references in an Excel Table?
Yes. Formulas can use ordinary cell addresses as well as structured references. Structured references are often easier to read and adjust when the Table grows, while cell references may be convenient for calculations tied to a particular worksheet layout.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.