A basic Excel table with data is a rectangular set of related records organized under a single header row. In Microsoft Excel for Microsoft 365 on Windows, select the data, choose Insert > Table, confirm the range and that it has headers, then select OK; Excel adds filter controls and table formatting so you can work with the records more easily.
What an Excel table is
An Excel table is a structured data range, not just a group of cells that happens to have borders or colored headings. It has one header for each field, with each row below representing a record. For example, a sales list might have columns for item, region, units, unit price, and revenue. Keeping one kind of information in each column makes the data easier to sort, filter, summarize, and use in formulas.
Tables are useful for everyday lists such as inventory, orders, attendance, expenses, and contact records. They can expand when you add data directly below or beside them, and formulas entered in a table column can be copied to other rows in that column. A table is still part of a worksheet: it does not replace the workbook, and it does not automatically verify that the information you enter is correct.
A small example of data
Here is a compact example you can enter in cells A1:E4. The last column can be calculated from the units and unit price.
| Item | Region | Units | Unit Price | Revenue |
|---|---|---|---|---|
| Desk | North | 3 | 125 | 375 |
| Chair | South | 2 | 80 | 160 |
| Lamp | North | 5 | 24 | 120 |
In a real workbook, keep the raw values in the rows and columns, rather than inserting blank rows to separate categories or adding extra titles within the list. You can put a descriptive title above the table, but leave it outside the selected data range. That keeps each column consistent and helps Excel recognize the full list.
Prepare the data before making a table
Good structure matters more than a particular color or style. Before creating the table, check that each column has a clear, unique header and that there are no completely blank rows or columns inside the data. Avoid merged cells in the range. A header such as “Unit Price” is clearer than “Info,” and duplicate or empty headers can make it harder to identify fields in formulas and other Excel features.
- Put one record on each row and one type of value in each column.
- Use consistent formats within a column, such as dates in a date column and numbers in a quantity column.
- Do not type a currency symbol into some price cells but not others; apply a number format instead.
- Remove leading or trailing spaces from category names if you expect to filter or compare them.
- Keep notes and explanations outside the data range, or store them in a dedicated Notes column.
Excel may interpret entries according to their values and regional settings. For instance, date formats can be ambiguous when imported or typed, and numbers stored as text may not behave like numbers in calculations. If a value is important, check that it is recognized as the intended type before relying on calculations.
Create the table in Excel for Microsoft 365
On Windows, click inside the data or select the full range, then choose Insert > Table. Excel displays the proposed cell range. Check that it includes every record and header, select “My table has headers” when the first row contains column names, and choose OK. You can also use Home > Format as Table, choose a style, confirm the range, and indicate whether it has headers.
- Enter the headers and records in adjacent cells, such as A1:E4 in the example.
- Select a cell in the range, or drag to select the complete range.
- Choose Insert > Table and review the range shown in the dialog.
- Check “My table has headers,” then select OK.
- Click inside the new table and, on the Table Design tab, use the Table Name box to give it a meaningful name if useful.
Excel assigns a default name such as Table1. A descriptive name like SalesData is easier to recognize in formulas, and table names cannot contain spaces. In Excel for Mac, Insert > Table is also available; the dialog and ribbon layout can look different from Windows. In Excel for the web, the commands may be arranged differently, but you can create a table from a selected range using the Insert tab.
Format and identify the table
Excel applies a table style, usually with banded rows and filter arrows in the headers. These visual features help distinguish the table and make longer lists easier to scan. They do not change the underlying values. To change the appearance, select a cell in the table, open Table Design, and choose a style from the Table Styles gallery. Options such as Header Row, Banded Rows, and First Column adjust which table features are emphasized.
For quantities, dates, and prices, use number formatting rather than changing the stored values to include extra characters. Select the relevant cells and use the number-format controls on the Home tab. For the example, format Unit Price and Revenue as currency if appropriate. The choice of currency and decimal places should match the purpose and conventions of the workbook; a displayed currency format does not convert one currency into another.
Table filters are useful, but a color style alone is not evidence that a range is a table. Click a cell and look for the Table Design tab and table-specific controls. If the data is only formatted as a range, use Insert > Table to make it a structured Excel table.
Use formulas with table data
Tables can make formulas easier to read because they support structured references based on table and column names. In the example, after naming the table SalesData, enter this formula in E2, the Revenue column’s first data row: =[@Units]*[@[Unit Price]]. It multiplies that row’s Units by its Unit Price. For the Desk row, 3 × 125 gives 375. Excel generally fills the formula down the calculated column when you enter it in a table.
To calculate total revenue outside the table, with the table named SalesData and a Revenue column containing 375, 160, and 120, enter: =SUM(SalesData[Revenue]). The result is 655. The structured reference refers to the Revenue data in that table column, so adding another table row can be included in the table’s data. If you rename the table or its column, Excel can update structured references it recognizes.
Formulas can also use ordinary cell references, but structured references often communicate intent more clearly. For example, a formula referring to “SalesData[Revenue]” identifies a particular table column, whereas a reference such as E2:E4 identifies cell addresses. Structured references are especially helpful when tables grow or when a workbook contains several similar lists. They do not make a formula immune to errors: check the column names, the calculation, and whether the source values are correct.
Sort and filter records
Each table header has a drop-down control. Select it to sort a text column alphabetically, sort numbers from smallest to largest, or filter the records to show selected values. For example, open the Region filter and select North to show the Desk and Lamp records. Filtering hides rows that do not match the selection; it does not delete those rows. Clear the filter from the same header menu to display the full list again.
Sorting rearranges records, so make sure you sort the table column rather than a single isolated column in a way that separates values from their related records. With a cell in the table selected, use the header’s sort command or the sorting controls on the Data tab. If Excel asks whether to expand the selection, choose the option that keeps the related columns together. A table makes it easier to sort a complete record as one row, but it is still worth checking the result.
Filters can be combined. You might display only North-region records and then apply a number filter to show quantities above a chosen threshold. The visible rows are a view of the data, not a separate copy. If you need a permanent subset, copy the filtered results to another location and verify what was copied.
Add rows and manage the table
To add a record, type in the row immediately below the table or use the table’s last cell and press Tab to move to a new row. When Excel extends the table, it carries the table’s formatting and column formulas into the added row. If the table does not expand as expected, check whether a blank row or column is interrupting the range, then resize the table from Table Design > Resize Table and select the intended range.
To add a column, enter its header in the cell immediately to the right of the table. Excel can extend the table to include it. Use one clear header and make sure the new column’s values follow a consistent pattern. You can also add a Total Row from the Table Design tab; its cells can offer summary calculations such as a sum or average for a column. Confirm which calculation is selected, because a total row is not necessarily a grand total for every field.
To remove the table features but keep the data, select a cell in the table and choose Table Design > Convert to Range, then confirm. The cell values remain, but table-specific behavior such as structured references and automatic expansion no longer applies in the same way. To delete the data itself, select the relevant cells and clear or delete them deliberately; converting to a range is not the same as deleting records.
Common problems and misconceptions
A frequent setup error is treating the first data record as a header. If “My table has headers” is left unchecked when the first row already contains field names, Excel may add generic headers and include the original headings as data. If the table looks wrong, inspect its header row and use Table Design options or recreate it with the correct range and header setting.
- Filters are missing: Check that the range was made into a table and that the Header Row option is enabled.
- A new record is excluded: Add it directly beside or below the table, or use Table Design > Resize Table to set the range.
- A sum seems too small: Some entries may be numbers stored as text, excluded by a filter, or outside the table’s referenced column.
- Sorting gives unexpected results: Look for mixed data types, extra spaces, or values that look alike but are not identical.
- Formatting changes unexpectedly: A table style and number format affect appearance; inspect the cell’s value and number format separately.
Another misconception is that an Excel table is the same as a PivotTable. A table stores and organizes source records; a PivotTable summarizes and rearranges those records for analysis. A well-structured table is often a useful source for a PivotTable, but creating a table does not automatically create a summary report.
When a table is useful compared with a plain range
A plain range may be enough for a small, static block of cells that needs no filtering or structured formulas. A table is usually more convenient when records will be added, filtered, sorted, or referenced by formulas. It provides consistent headers, table-aware features, and a clear boundary around a set of related data. You can still use ordinary worksheet formulas and charts with either a table or a range.
For data analysis, keep the table focused on one dataset and use separate sheets or tables for unrelated information. If you receive data from another system, check its headers, types, and missing values before depending on it. The table structure helps organize information, but it cannot decide whether a sales record is genuine, whether two names refer to the same customer, or whether a date is valid for your work.
Frequently Asked Questions
How do I make a basic table in Excel?
Select the data range, choose Insert > Table, confirm the range, check “My table has headers” if it has a header row, and select OK. You can then choose a table style and rename the table from Table Design.
Can I make an Excel table without headers?
Yes. When creating the table, leave “My table has headers” unchecked if the first row is data rather than field names. Excel supplies header labels, which you can edit. For most lists, adding meaningful headers first makes the data easier to understand and use.
How do I add data to an existing Excel table?
Type in the row immediately below the table or add a column immediately to its right. If the table does not expand, select a table cell and use Table Design > Resize Table to include the new cells.
Does filtering delete rows from an Excel table?
No. A filter temporarily hides rows that do not meet the selected conditions. Clear the filter to show them again. Take care when copying filtered data, because you may intend to copy only the visible records.
What is the difference between a table and a PivotTable?
An Excel table is a structured place to store records. A PivotTable summarizes those records, for example by showing revenue by region. You can use a table as a PivotTable’s data source, but they serve different purposes.
Related
- How to Use Absolute References in Excel for Mac
- How to Name a Print Range in Excel and Reuse It for Printing
- How to Name a Selected Range in Excel and Use It Easily
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Sum a Column in the Google Sheets Mobile AppTo sum a column in Google Sheets on your phone or tablet, tap an empty cell where you…
- Excel Group Rows: How to Add and Use the Drop-DownsTo group rows in Excel for Microsoft 365 on Windows, select the rows using their row numbers, then…
- Can You Do Text to Columns Vertically in Excel?Not directly: Excel’s Text to Columns command splits text across separate columns, not down separate rows. To split…
- Excel Goal Seek Tolerance: Set Precision and Check ResultsExcel Goal Seek has no per-run tolerance box. Its default calculation setting commonly cited for iterative work is…
- Calculate Age in Excel From a Date of BirthTo calculate someone’s age in completed years in Excel, enter their date of birth in A2 and use…
- Google Sheets Checkbox Formula: If Checked, Then What?In Google Sheets, a checked checkbox evaluates to TRUE and an unchecked checkbox evaluates to FALSE, so a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.