Introduction to tables
A table in Excel is a method for storing data in an organized way. Each row in the table corresponds to a single entity.
For example, each row may contain information about customers, employees or products, and each column contains a specific piece of information, such as first name, last name, date of birth, salary, and so on. Usually, at the top of the table, there is a header with a description for each column.
Excel tables have the following useful features:
Dynamic size-change
Tables in Excel can expand automatically. When the table changes its size, each formula that uses this table automatically adjusts to it.
Suppose that you have a function that sums rows in the table. Each time you add or delete any of those rows, the scope of the SUM function will be changed accordingly.
Can connect to databases
Although Excel tables can be used in stand-alone sheets, they can also be a great tool for viewing the information from the database.
Data can be sorted
When you work with tables you may want to sort the data in alphabetical order or by date of birth, in ascending or descending order.
Data can be filtered
If there is a lot of data in your table, you may want to show only those, that you are currently interested in. You can do it by using the filtering feature.
Lots of formatting styles
Thanks to a large number of formatting styles, you can quickly format the table by setting the appropriate colors or adding gridlines.
Removing duplicates
This simple command allows you to select and (optionally) remove duplicate rows from the table.
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.
After you create the table, you will notice that Excel will add a new tab to the ribbon called DESIGN. This tab is divided into five areas:
Properties
Here, you can change the name and the size of the table.
Tools
This is the place where you can find tools that are used in PivotTables: tools to remove duplicates or tools to convert a table to normal cells.
External Table Data
It allows you to work with external data, such as records from a database.
Table Style Options
Here, you can control various elements of the table.
Table Styles
In this area, you can choose one of the predefined styles.
Example 1:
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:
- Use the keyboard shortcuts, such as Ctrl + T or Ctrl + L,
- Go to INSERT >> Tables >> Table,
- 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.
TIP
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.
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.
Example 1:
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:
- HOME >> Edit >> Sort & Filter,
- 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.
