Tables

Excel tables are a powerful tool for organizing, analyzing, and managing your data in spreadsheets. They offer several advantages over traditional data ranges:

Creating a table

A table in Excel is usually created from the data that already exists in the worksheet. However, Excel will also allow you to create tables that don’t have any values, so you will be able to take care of the details later.

Create the following table. Here, you will find information about employees, such as name, surname, year of birth and city.

If you want to create a new table from the existing data, click any cell that you want to be a part of the future table and then perform one of the following actions:

  1. Use the keyboard shortcuts, such as Ctrl + T or Ctrl + L,
  2. Go to INSERT >> Tables >> Table,
  3. Go to HOME >> Styles >> Format as Table and select one of the available formatting styles.

After you select one of these options, the new window called Create Table will appear. Here, you can select a range of data and choose whether your table already has headers.

In this case, the table has headers, so leave this checkbox checked.

If the table doesn’t have headers, you can deselect this option and then Excel will insert headers with the default column names.

Excel tries to guess the range of data, from which to create a table. If Excel selected the data you wanted, click OK.

Undo

You can convert your table to normal cells once again. To do this, click any cell in the table and choose DESIGN >> Tools >> Convert to Range.

Formatting a table

After you create a table in Excel, you will be able to adjust it, according to your needs.

Renaming the table

Tables in Excel are normally called Table1, Table2, Table3, …. If you want to change their names, go to DESIGN >> Properties and in the Table Name text box type a new name.

Filter buttons

After you create the table, Excel will automatically insert buttons that can be used to filter data.

If you want to get rid of them, click the table, go to DESIGN >> Table Style Options and uncheck the Filter Button checkbox.

Total Row

The Total Row contains formulas that summarize information in the columns. When you create a table, Total Row is disabled by default. If you want to change its display, go to DESIGN >> Table Style Options and select the Total Row check box.

At the bottom of the table, there is a row that sums up the last column. If the data in the last column isn’t numerical, Excel will use a counter instead. If you click any item in that row, the drop-down button will appear. You can click it and choose one of the several available options.

Selecting styles

A table style is a set of formatting settings that are applied to the entire table. You will find them in DESIGN >> Table Styles. Click the button at the bottom right corner to expand the full list of styles.

Simply hover the mouse over one of them and Excel will automatically change the appearance of your table. If you like how your table looks, confirm your choice by clicking this style.

Table styles only change the background color and font color. If you want to use the styles that also change font type, go to PAGE LAYOUT >> Themes >> Themes and select one of the available themes.

TIP

Sorting a table

When you have a lot of data in your worksheet, finding a specific position can be very time-consuming. A good idea would probably be to sort the table first, so you can find your data faster.

In Excel, you can sort a table alphabetically or numerically, depending on the type of data in the column. You can also determine whether you want to sort your data in ascending or descending order.

The following example will illustrate the sorting feature.

This example contains a list of employees which are identified by the information in the columns.

In order to sort this table, click any cell in the First Name column and then use the sorting option, which you can find in two places:

  1. HOME >> Edit >> Sort & Filter,
  2. DATA >> Sort & Filter.

When you use sorting, Excel won’t sort the first row because it will treat it as a header.

Custom Sort

When you have a very large amount of data, sorting it by one column may not be sufficient, so I will show you how to use the Custom Sort feature, which will let you sort by more than one column.

You’ll find it in HOME >> Edit >> Sort & Filter >> Custom Sort….

In the following example, three levels of sorting are created. First, the table will be sorted by the First name, next to the Last name, and finally by the Year of birth.

Columns that contain text values will be sorted from A to Z and the ones with numbers will be sorted from the smallest to the largest value.

In the upper right corner, you can find the My data has headers check box. Uncheck it if you don’t have headers in your table.

Filtering a table

Filtering tables allow you to reduce the amount of data that is currently displayed in a table, so you can view only those positions that you want to see. It is useful, especially when you deal with a large amount of data.

Filter controls

When you create a table, in each header at the top of the column you will find the filter controls.

Uncheck the position you don’t want to see in the table and click OK. Now, the table contains only those employees that work in the selected cities. Please note that the icon in the “City” header has changed. That means that Excel filters data by this column.

In conclusion, Excel tables offer a versatile and efficient way to manage data within spreadsheets. From their ease of creation to their customizable formatting and powerful sorting and filtering capabilities, Excel tables provide users with a robust toolset for organizing and analyzing information

Excel 365 Update

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.