Tables in Excel

What Are Excel Tables?

Tables in Excel are named objects that assist in managing inter-related data in a series of rows and columns, independently, from the remaining spreadsheet data. Moreover, Excel tables offer features that help users manipulate and format their data quickly, thus making it easy for them to work with massive data sets.

For instance, the below cell range shows the revenues of US tech companies from 2018 to 2021.

Using tables in Excel option, we can make the above data range more organized and easy to analyze.

Remember, when we create an Excel table, it automatically assigns names to the table and its columns. As a result, we can see structured references to the cells with AVERAGE() function in the range F2:F6, thus making the formula and the table more readable and easy to grasp.

How To Make Tables In Excel?

We can create tables in Excel using the below methods.

1. Using Table Option From Insert tab

To begin with, let us consider the following example with the list of grocery items (in column A), along with the units (in column B) and their prices (in column C). Now, we need to use the below steps to create tables in excel.

Please Note: We must ensure to have data without any blank rows or columns. Also, each column must have a different heading. Otherwise, while creating the table, Excel will automatically change one of the headers to make all column headers unique.

The steps to create tables using the table option from Insert tab method are as follows:

Step 1: First, click on a cell in the table.

Next, go to the Insert Tab; choose the Table option from the Tables group.

Step 2: A window named Create Table pops up.

Firstly, ensure the cell range shown in the Where is the data for your table? dialog box is correct, and the entire table is selected in the cell range.

Step 3: Then, check the box against My table has headers option.

Please Note: Select My table has headers option when the data has headers. It is because excel inserts a row in the table to enter the column headers. So, we need not select the option if the data does not have headers.

Click OK.

Clearly, we can see that the data has been converted into a table.

• Similarly, we can create tables using the table option from Insert tab.

2. Using Tables In Excel Shortcut

Consider the following cell range showing the marks obtained by students. We need to follow the below steps to create tables in excel using the shortcut method.

In the table,

• Column A displays the names of students.
• Column B shows the marks obtained by the students.

The steps to create tables in Excel shortcut method are as follows:

Step 1: Choose a cell from the cell range A1:B6; then, press the shortcut keys Ctrl + T to create tables in Excel.

Step 2: We can see the Create Table window on the screen.

Also, ensure that the cell range shown in the Where is the data for your table? dialog box is correct, and the entire table is selected in the cell range.

Step 3: Then, check the box against My table has headers option.

Please Note: Select My table has headers option only when the data has headers. Excel inserts a row in the table to enter the column headers. So, if the data does not have headers, we need not select the option.

Step 4: Click OK.

At last, we can see that the data has been converted into a table.

• Likewise, we can create tables using the tables in Excel shortcut.

3. Using Format As Table Option From Home tab

The below cell range shows the marks scored by Andy in various subjects. Now, we need to follow the steps to create tables in excel using format as Table option from Home tab.

In the table,

• Column A indicates the subjects.
• Column B displays the marks obtained by Andy.

The steps to create tables using format as Table option from Home tab method are as follows:

Step 1: We need to select a cell from the cell range. In our example, let us choose A1:B5.

Now, click on the Format as Table option from the Styles group under the Home tab.

Step 2: Excel has clearly categorized the formatting options according to colour scale. We can select any style from the available table styles. In this example, let us choose the seventh style from the Light section.

Step 3: After selecting the table styles, the Format as Table window pops up.

Meanwhile, ensure the cell range shown in the Where is the data for your table? dialog box is correct, and the entire table is selected in the cell range.

Step 4: Check the box against My table has headers option.

Please Note: Select My table has headers only when the data has headers. It is because excel inserts a row in the table to enter the column headers. If in case the data does not have headers, we need not select the option.

Step 6: Click OK.

We can see that the data has been converted into a table.

• Therefore, we can create tables using the Format as Table option from Home tab

Similarly, we can create individual or multiple tables in Excel using the above mentioned methods.

How To Customize Tables In Excel?

Generally, Formatting tables in Excel is the method used to customize tables in excel. We can change the table’s name or color in excel.

1. Change Excel Table Name

Naming tables in Excel

When we create tables, Excel automatically assigns default names such as Table1, Table2, etc., depending on the number of tables in our workbook.

However, naming tables in Excel with unique labels can make our tables appear more resourceful.

Let us understand the method to change the name of the table with an example.

Consider the table with the names of employees, their designation, and annual salaries in columns A, B, and C, respectively. So, we need to use the following methods to change the name of the excel table.

The steps to change the table’s name are as follows:

Step 1: First, we should choose a cell in the above table.

Step 2: Then, under the Table Design tab, look at the Table Name: dialog box.

We can see Table14 as highlighted in the image below.

Step 3: Now, type the required name in the Table Name box.

Step 4: Press Enter.

• Thus, we can customize tables in Excel by changing the name of the tables.

Please Note: The table’s name should always start with a letter or underscore; and it should not have any blank spaces or characters. Also, no table can be named with another existing table name in the worksheet. Otherwise, we get an Excel error, as shown in the below image, that indicates us to enter the correct name.

Change Excel Table Color

We can change the Excel table color using the Table Styles group in the Design tab.

Let us use the same example with the names of employees, their designation, and annual salaries in columns A, B, and C, respectively. We need to use the following methods to change the color of table.

The steps to change the table color are as follows:

Step 1: Go to the Design tab and select the required table color from the Table Styles group.

Step 2: Meanwhile to explore other options, click on the More option from the right-bottom of the group.

We can select any of the available table styles options. In this example, let us choose the seventh style from the Medium section.

Clearly, we can see that the color of the table has been changed.

So, we can customize tables by changing the color of the tables in excel.

• Thus, formatting tables in Excel is made easier with the in-built options.

Please Note: To customize table style, click on New Table Style, available below the table style templates. To remove a chosen table style, click Clear.

Useful Features Of Excel Tables

Looking at the examples of tables in Excel, we might perceive them as regular data sets in a series of rows and columns.

However, they come with powerful features that make tables in Excel more practical and useful.

Let us look at all the features using an example.

The below table shows the annual sales of branded products in 4 quarters. So, we need to use the following steps to understand the various features available in excel tables.

1. Sorting And Filtering Options

Generally, when we have to sort and filter data in a required cell range, we insert filters using the Filter option under the Data tab or use shortcut keys.

But, in the above-explained examples of tables in Excel, or even in this table, we can see that the filters are already available.

Thus, the tables in Excel allow users to sort and filter data directly and quickly.

However, if we do not require the filter option, we can remove the filter options using the following steps:

Step 1: First, go to the Design tab

Step 2: Then in the Table Style Options, uncheck the Filter Button box.

Now, we can see that the filter buttons are hidden.

Alternatively, we can use the shortcut keys Shift + Ctrl + L to hide or unhide the filters.

2. Column Headers Visible During Scrolling

Let us assume that we have a huge dataset in Excel. When we scroll down, we cannot see the headers until we use the Freeze Panes option.

But, a table in Excel with huge data automatically freezes the headers. When we scroll down, the column headers remain visible by default, thus helping us analyze data easily.

For example, in the above table, we can see that the data is distributed till row 31. Also, the column headers remain static when we scroll down, as highlighted in the above image.

Please Note: When scrolling down the table, we must remember to choose a cell in the Excel table to view the column headers.

3. Simple Formatting Steps

After converting our data into tables in Excel, we can easily format them according to our requirements using the Design tab.

We can change the color of the table using the below steps:

• Go to the Design tab
• Choose the required style from Table Styles group

In addition, we can format the visibility of the table with the following steps:

• Go to the Design
• Select or unselect the options available in the Table Style Options

Remember that the Header Row ensures that the column headers remain visible when we scroll down the data.

Similarly, the First Column and Last Column options add special formatting features for the respective columns in the table.

Also, the Banded Rows and Banded Columns show alternative rows and columns in different shades.

Along with the specified options, we can also access options such as Resize Table, PivotTable for pivot tables in Excel, and Insert Slicer options to format an Excel table quickly.

4. Automatic Table Expansion

Whenever a new row or column is added to an existing data range, we need to use the formatting steps for each new entry. But, tables makes it easy by automatically applying the necessary formatting steps and formulas to the latest data entries.

For instance, in the above Excel table, we have added column F. The data in column F is automatically added with the format of the adjacent columns in the existing table.

5. Quick Access To Functions

The features in Excel tables help users access the inbuilt functions quickly.

We can access the inbuilt functions by checking the Total Row box in the Design tab.

As soon as we enable the Total Row option, we can see a new row that is automatically added to the table. The newly added row shows the sum of the values in the last cell.

Here, we can use the in-built Excel functions available in the drop-down list. Or, we can also use the functions to enter data in the adjacent cells.

Also, we can click on the More Functions… to use other predefined Excel functions on the table.

6. Quick Calculations

When we apply a formula to a cell in an Excel table, the function gets copied to the subsequent cells.

For instance, let us enter the SUM() formula in cell F2 as

=SUM(Table16[@[Q1 2021]:[Q4 2021]])

After we press Enter, the formula gets adjusted and automatically gets copied to all the cells in the range F3:F6.

Alternatively, we can use the AutoCorrect option to manage the autocorrect and automatic calculations according to our needs.

7. Structured References In Excel Table Functions And Formulas

The Quick Calculations feature automatically copies the SUM() results to cells in the range F2:F6. It is because the Excel table uses structured references.

The formula is =SUM(Table16[@[Q1 2021]:[Q4 2021]])

The function automatically uses the Excel table (Table16) and its specific column names (Q1 2021 and Q4 2021), thus making the expression more straightforward.

How To Manage Table Data?

Let us learn the method to manage the data in the table with an example.

The following table shows the personal details of students. We need to use the below steps to manage data in table.

In the table,

• Column A displays the names of students.
• Column B shows the students’ grade
• Column C indicates the students’ residence
• Column D highlights the fee payment status

1. Converting An Excel Table To A Range

We can retain the data along with the formatting when we delete a table using the below steps.

Step 1: To begin with, choose a cell on the table.

Step 2: Next, select the Convert to Range option under the Tools group from the Design tab.

Alternatively, we can right-click anywhere on the Excel table and choose Table -> Convert to Range from the context menu.

Step 3: The Microsoft Excel confirmation box pops up. Click Yes to convert the table into a normal cell range.

Clearly, we can see that the table has been converted into a normal cell range.

Therefore, we can delete tables in excel without losing the table’s format and content.

In addition, the formulas in the cell range will include normal cell references instead of structured references.

2. Inserting and Deleting Table Rows and Columns

Inserting table rows and columns

The steps to insert rows and columns in tables in Excel are as follows:

Step 1: First, we need to choose the location where we wish to insert a row or column in excel table.

In this example, let us select cell D9.

Step 2: Next, in the Home tab, click on the Insert option from the Cells group.

Step 3: Here, we can select any option from the drop-down list to insert any number of rows or columns in the table.

The Insert Table Rows Above option adds a row above the selected cell in the table, as shown in the image below.

The Insert Table Columns to the Left option creates a column to the left of the selected cell, as highlighted in the image below.

Alternatively, we can right-click on the cell where we want to insert a row or column. And then, choose Insert option from the context menu. Then, we can select the required insert option.

Deleting Table Rows And Columns

The steps to delete rows and columns in Excel tables are:

Step 1: First, we need to choose where we wish to delete a row or column.

In this example, let us select cell D9.

Step 2: Next, from the Home tab, select the Delete option under the Cells group.

Step 3: By default, excel has a number of deleting options. We can select options to delete as many rows or columns we want to delete.

The Delete Table Rows deletes a row above the selected cell in the table.

Then, the Delete Table Columns option deletes a column in the table.

Alternatively, we can right-click on the cell where we want to delete a row or column and choose Delete option from the context menu. Now, we can select the required options.

3. Resizing Excel Table

We can resize tables in Excel to include new rows and columns or exclude existing ones.

The steps to resize table are as follows;

Step 1: First, choose a cell.

Then, click on the Resize Table option under the Properties group in the Design tab.

Step 2: As soon as we click the Resize Table option, we can see the Resize Table window.

Step 3:  To resize the table, we need to enter the new cell range in the Select the new data range for your table: box.

Therefore, let us change the cell range as =$A$1:$D$11

Step 4:  At last, click OK.

We can see that the table is resized with a newly added row.

Alternatively, we can use the Resize handle option. We can see a double-sided pointed arrow at the table’s bottom-right corner

We can drag the arrow in the required direction to insert or exclude rows or columns.

4. Selecting an Excel Table Row or Column

Selecting table columns

We can move the cursor to the specific column header to select all the cells in a particular column.

Step 1: Alternatively, we can use the black arrow pointing downwards. Just click the arrow to select the cells in that column.

Step 2: Or, we can simply double-click on the black arrow pointing downwards to select the cells along with the column header.

Alternatively, we can select a cell in the required column and use the shortcut keys, Ctrl + Space.

Remember to select the cells and the column header, press Ctrl + Space twice.

Selecting table rows

Similarly, to select an entire row, we should move the cursor to the specific row header. This way, all the cells in the particular row gets selected.

Step 1: Instead, we can click on the black arrow pointing right to select the cells in that particular row.

Alternatively, we can select the first cell in the specific row and use the shortcut keys Ctrl + Shift + Right Arrow to select all the cells in that row.

Selecting the entire table

Step 1: To select the entire table –

• Move the cursor to the top-left corner of the table
• A southeast pointing arrow appears. Click the arrow to select all the cells (entire table)
• Double-click on the arrow to select the cells along with the headers.

Instead, we can select any cell in the table and use the shortcut keys Ctrl + A to select all the cells in the table and twice to select the cells along with the headers.

5. Inserting a Slicer for Filtering Table Data

The steps used to insert a slicer in Excel are:

Step 1: First, we must select a cell to insert the slicer.

Step 2: Next, select the Insert Slicer option from the Tools group under Design tab.

The Insert Slicers window pops up

Step 3: Then, choose the columns where we want to apply the filters.

In this example, let us select the Student column. Click OK.

Step 4: The Student window with the list of entries in that Student column appears.

Here, we can select one or multiple entries (names).

Otherwise, we can press the Ctrl key to select multiple entries to filter using the slicer option.

6. Removing Duplicates from Excel Table

We may have tables in Excel with duplicate entries. With simple steps, we can remove the duplicates in Excel easily.

Consider the table with duplicate entries in rows 9 and 10.

The steps to remove the duplicate data are:

Step 1: To begin with, select a cell and click on the inbuilt Remove Duplicates option from the Tools group under the Design tab.

Step 2: We will be able to see the Remove Duplicates window.

Here, we need to select the columns where we suspect duplicate entries.

Click OK.

Step 3: As soon as we click OK, the Microsoft Excel confirmation box pops up. It shows the number of duplicates and original entries in the selected columns, as shown in the image below.

Step 4: Click OK.

Clearly, we can see that all the duplicate entries in the table gets removed.

Likewise, we can manage table data using the features in excel.

Important Things To Note

• Tables in Excel allow us to manage data efficiently; using the various features, we can format the content as required.
• Remember to ensure that the cell range we wish to convert into an Excel table does not have any blank rows or columns.
• It is important to name tables in excel starting with a letter or underscore since Excel does not accept spaces, characters, or names that are already assigned to other tables.
• We should select a cell on the table to use the options from the Design tab in the Excel ribbon.

Frequently Asked Questions

Download Template

This article must be helpful to understand the Tables in Excel, with its examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Table in Excel. Here we discuss how to create/make, modify, delete and manage tables with examples and downloadable excel template. You can learn more from the following articles –

• Data Table in Excel
• Track changes in Excel
• Watermark In Excel