A table in Excel is a block of cells containing related data that has been formatted like a table. Formatting a block of data as a table makes it easy to perform various tasks on the data in the table without affecting other data in the worksheet. These tasks include sorting data, filtering data, doing quick calculations, adding column totals, and formatting tables visually.
The information in this article applies to versions of Excel 2019, 2016, 2013, 2010, and Excel for Mac.
Insert a table
Before creating a table, enter data in the worksheet. When entering the data, do not leave empty rows, columns, or cells in the data frame that makes up the table.
To create a table in Excel:
- Select a single cell within the data frame.
- Choose insertion.
- Choose panel. Excel selects the entire block of contiguous data and opens the Create a table dialog box.
If Excel is having trouble formatting the table correctly, highlight the data before selecting it insertion panel Possibility.
- If your data has a caption, check it My table has headings Possibility.
- Choose OK to create the table.
table properties
The most notable features that Excel adds to the data frame are:
- Column header drop-down menus containing sort, filter and search options.
- Alternating shaded lines that make the data easier to read.
- The size handles are in the corners of the table.
- Quick Analysis (in Excel 2013 and newer) that appears in the lower-right corner of the active cell when two or more table cells are selected. This makes it easy to analyze data using charts, pivot tables, running totals, and conditional formatting.
Manage table data
Sorting and filtering options
Sorting and filtering drop-down menus added to the header make it easy to sort tables in ascending or descending order, by font or by cell background. You can also define a custom sort order. In addition, you can use menu filter options
- Only show data that meets the specified criteria.
- Filter by font or cell background color.
- Find specific records by matching individual data fields.
Adding and deleting fields and records
The sizing handle makes it easy to add or remove entire rows (records) or columns (fields) of data in the table. To resize the table, drag the resize handle up, down, left, or right.
Data that is deleted from the table is not removed from the worksheet, but it is no longer included in table operations such as sorting and filtering.
Calculated Columns
A calculated column allows you to enter a single formula in a cell in a column and automatically apply that formula to all cells in the column. If you don’t want the calculation to include all cells, remove the formula from those cells.
If you only want the formula in the starting cell, use the undo function to remove it from all other cells.
row overall
The number of records in a table can be totaled by adding a total row at the end of the table. The total row uses the SUBTOTAL function to count the number of records.
Additionally, other Excel calculations such as SUM, AVERAGE, MAX, and MIN can be added via a drop-down menu of options. These additional calculations also use the SUBTOTAL function.
- Select a cell in the table.
- Choose table tool design Tab.
- In which Table style options group, put a tick in the total row check box.
The total row appears as the last row of the table and displays the word In total in the leftmost cell and the total number of records in the rightmost cell.
To add more calculations to the total row:
- In the Total row, select the cells where the calculation should appear. A drop-down arrow appears on the right side of the cell.
- Choose drop down arrow to open the options menu.
- Select the desired calculation in the menu to add it to the cell.
The formulas that can be added to the total row are not limited to menu calculations. Formulas can be manually added to any cell in the total row.
Drop a table but save the data
If you decide you don’t need the table for your data, you can delete it without damaging its content by following the steps below:
- Select a cell in the table.
- Choose table tool design Tab.
- In which Tool group, select Convert to area to open a confirmation box to remove the table.
- Choose Oui confirm.
Table features such as drop-down menus and the sizing handle are removed, but dates, row shading, and other formatting features remain.