When working in Microsoft Excel, we can easily turn a range of data into an Excel table. An Excel table can make managing and analyzing data easier. Within the table, data can be filtered, sorted, summarized, and formatted.
Excel tables offer several benefits, including:
Each column header contains a drop-down list, which can be used to sort or filter data.
When scrolling, the table headers replace the column headers in the worksheet.
As rows or columns are added or deleted, formatting, conditional formatting, data validation, and other operations are automatically carried over to the new cells.
A Total Row can quickly be added to a table to calculate any column.
A single formula is automatically propagated to all cells in the column.
Rather than using cell references, you can use table names and column headers in formulas.
Duplicate rows can be removed automatically.
As rows or columns are added to or deleted from the table, the table range will adjust accordingly.
Let's turn the Sales by Location data into an Excel table.
The Table tool is on the Insert tab on the ribbon.
To move to the Insert tab, on the ribbon,
Click the Insert tab
To select a cell containing data, in the Sales by Location worksheet,
Click any cell that contains data
To begin creating an Excel table, on the right side of the Insert tab,
Click
The Create Table dialog box opens:
Excel has selected the range of cells adjacent to the selected cell as the data for the table. There is also an option to indicate whether or not the table has headers. Our table has headers in row 1, so we will leave this checkbox checked.
To create the table, in the dialog box,
Click
The data in the Sales by Location worksheet is now an Excel table.
The table has been formatted with banded rows of blue and white. Additionally, the column headings have drop-down menus. These drop-down menus can be used to quickly sort or filter the data.
Working with the Total Row
One of the features of an Excel table is the Total Row. When the Total Row has been toggled on, it can calculate any column of the table.
Let's turn on the Total Row. This option is located on the Table Design (or Table) contextual tab on the ribbon.
To move to the Table Design (or Table) contextual tab, on the right side of the ribbon,
Click the Table Design (or Table) tab
To toggle on the Total Row, in the middle of the ribbon,
Click the Total Row checkbox
The Total Row appears at the bottom of the Excel table.
By default, the Sum function has been applied to column D. We can change the calculation shown in the Total Row. Let's find the average of the sales.
To select the Total Row calculation,
Click in cell D30
To change the function,
Click , Click Average
To save the workbook, on the keyboard, press:
Control key + S
The Total Row now shows the average sales, $1088.95.
,