Nonprofit Accounting Basics

Microsoft Excel Table Tips for Managing Lists

Topics: 

If your work involves managing data in lists, then the new Excel Table is a “need to know” feature. The Excel Table will enhance accuracy of subtotals in a list, automatically filter the subtotal calculations to the visible filtered rows, and copy formulas into new rows when new rows are inserted. This article will show you how to use the Table feature as a best practice using Excel. The Excel Table is available in Excel versions 2007, 2010, and 2013.

You begin by creating a list of data or importing a list of data. The organization of the data must be in consecutive rows with the top row containing the unique column headings. Place the cell pointer on one of the column heading cells and select the Insert tab on the ribbon. In the Tables group, select the Table tool. If your list does not have any skipped columns or rows, then Excel will select the entire list and display the coordinates in the Create Table dialog box. Click OK.

The resulting table offers color-banded rows and the AutoFilter feature is active. When inserting or deleting rows, the color banding on the rows automatically adjusts so the list is easier to read. Also, if there are formulas in a column of the Table, as there are for the amounts in column H, the formulas are automatically copied when inserting new rows and are placed into the appropriate cell(s) on the new row.

The Table Tools Design contextual tab appears on the ribbon when your cell pointer is within the table. You can change the style of the color banding in the Table Styles group.

When you place a check mark next to Total Row, the Table adds a new total row at the bottom of the list. The total row provides access to summary functions such as AVERAGE, COUNT, or SUM. A drop-down list appears in each total row cell so you can quickly calculate the totals you want. Excel builds the formula for you.

And these totals calculate to the visible rows when filtering the data. Change the filter choices, and the subtotals update on the total row.

The sizing handle in the lower right corner cell in the Table automatically remains on the last cell of the Table.

If you add more rows and columns, the sizing handle moves automatically to the last cell. If needed, you can drag the sizing handle.

The Table Name is found on the Design tab on the ribbon in the Properties group. You can use the table name as a reference for formulas and functions or for a pivot table range. You can also change the table name from Table1 to a more meaningful name by typing in the table name box.

The Table is a popular feature because Excel is performing work for you. I have met many Excel users who have benefitted from using the table name as a pivot table range. The table name tracks the size of the list. If someone types additional rows at the bottom of the list, the table name automatically expands to include those rows.

If you enjoyed these tips, watch for upcoming articles that will discuss more “need to know” Excel features.