Working with Tables

What is a Table

While formulas and functions are great at analyzing data within your workbook, they are more apt at analyzing entire workbooks rather than specific sets of data. If you need to analyze smaller sets of data within a large workbook or break down large sets of data into smaller parts, then converting your data into tables is often the best solution. Tables allow you to use Excel’s powerful organizational capabilities without modifying the data itself. Using tables you can narrow down specific data, focus on only the important information, and more.

Table

A table is a specially designated range of information that has added functionality. You can have multiple tables per worksheet, and tables can be as large or small as the amount of data you want to work with. Tables can be created from existing data ranges or from empty ranges and then populated afterward.

Once a table has been created, it will automatically be given a generic name such as “Table1” or “Table2” depending upon the number of tables present in the current worksheet. However, these names can be changed at any time.

Additionally, tables are flexible in that you can convert a table back to a normal range at any point without affecting the contents. 

A table is made from adjacent columns of data, with a unique label or heading for each column. Columns and rows may be added to a table just as you would when working with a normal range.

Remember that each worksheet has a lot more rows than columns. This design is well suited for data organized in long, adjacent, list-like columns:

Table Components

There are a number of components that come together to create a table. Some of these components, like the header row and banded rows, are required, but other components have the ability to be toggled on or off. Below you can see a table with many of the components toggled on:

Here is an overview of each element.

• The Header row (1) will display the column headers for each column in the table. For each header within the Header row, you will see the Header row drop-down arrow (2). This is used to access a variety of organizational commands. 
• Banded rows (6) allow you to easily differentiate between each row that appears within the table, while the Total row (3) is used to display the results of calculations that are done on a column-by-column basis. The Total row also includes the Total row drop-down arrow when selected. This gives you quick access to functions that can perform calculations on the table data. Note that this component will only be displayed if a cell in the Total row is selected. 
• Finally, the First column (4) option will apply a bold effect to the first column to the table to differentiate it from other columns. 
• The Last column (5) option, when applied, will do the same thing to the last column of data.

Creating Tables

The Create Table dialog box is used to convert existing ranges of data into a table. Additionally, this dialog box also allows you to choose if you want to include the top row of the selected range in the new table as a header row.

• To access this dialog, select the cell range that you would like to convert into a table and then click Insert → Table:

• The Create Table dialog will open. You will see the selected range (using absolute cell references) in the “Where is the data for your table?” text box:

• Clicking the OK button will convert the selected cell range into a table.

Modifying Tables

Similar to cell styles, table styles are preconfigured formatting options that can be applied to tables. They allow you to quickly apply a splash of color to your tables and in some cases enhance their readability. While you do have the option of configuring your own table style, you can also select from a variety of pre-configured quick styles. 

• You can find all of these quick styles within the Table Styles group of the Table Tools – Design tab:

• Clicking the More arrow within the Table Styles gallery will expand it to show more options:

• Clicking any style option inside the Table Styles gallery will apply it to the current table:

• To clear an applied style, click the More arrow within the Table Styles gallery, and then click Clear.
  
• The table will then be displayed with no style at all:

Using the Table Tools – Design Tab

Whenever you are working with a table, the Table Tools – Design contextual tab will appear on the ribbon. Using the controls on this tab, you can modify just about any aspect of your table:

Here is a brief description of the various groups that on this tab and the commands and options that they contain.

• Properties - This group allows you to view and edit the current table’s name. You can also redefine the table size using the Resize Table command. 
• Tools - Within this group, the Remove Duplicates command allows you to remove duplicate values from the current table. The “Summarize with PivotTable” command will create a PivotTable out of the current table, and the “Convert to Range” command will convert it back to a regular range. You can also insert a slicer into the table using the Insert Slicer command. 
• External Table Data - The commands in this group are used to export table data to other applications, as well as manage data links to external resources. 
• Table Style Options - The checkbox controls in this group allow you to toggle available table components on or off. 
• Table Style - This group displays a gallery of styles that you can apply to the current table.

Adding a Total Row

To insert a new row, select the table row below where you want to insert one or more blank table rows. Next, click Home → Insert → Insert Table Rows Above:

A new blank row will then be added above the selected row, shifting everything below this row downwards:

Similarly, you can add new columns by selecting the table column to the right of where you want to insert a new column. Next, click Home → Insert → Insert Table Columns to Left.