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.

Table in Excel Intro Example

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

Intro Example 1

Also, the Excel tables have alternative colored rows with filters in all the columns.

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.

Key Takeaways
  • Tables in Excel are labeled objects that offer options to manage related data in a cell range effectively.
  • With simple steps, we can convert normal cell range into an Excel table using the following options:
    • Insert -> Table.
    • Ctrl + T
    • Home -> Format as Table
  • Excel table uses structured references in formulas to help users copy formulas and functions into the corresponding tables.
  • Excel tables allow us to format, sort, filter, and select our data quickly. Also, we can add, delete, or insert dynamic charts.

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.

Example 1

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.

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

Excel Example 1.1

Step 3: 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 4: 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.

Step 5: Click OK.

Table in Excel Example 1.2

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

Table in Excel Example 1.3
  • 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.
Table in Excel Example 2

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.

Example 2.1

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

Example 2.2
  • 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.
Table in Excel Example 3

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.

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

Example 3.1

Step 3: 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.

Example 3.2

Step 4: 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 5: 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.

Example 3.3

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

Example 3.4
  • 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.

Table in Excel Customize Example 1.1

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.

Excel Customize Example 1

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

Table in Excel Customize Example 1.2

Step 4: Press Enter.

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

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 error, as shown in the below image, that indicates us to enter the correct name.

Customize Example 1.3
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.

Table in Excel Customize Example 1.1

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.

Customize Example 2.1

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

Customize Example 2.2

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

Customize Example 2.3

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

Excel Customize Example 2.4

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.

Features Example 1

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.

Features Example 1 Sort Filter

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.

Features Example 1.1

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.

Table in Excel Features Example 2

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
Table in Excel Features Example 3

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
Features Example 3.1

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, 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.

Features Example 4

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.

Features Example 5

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.

Table in Excel Features Example 5.1

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

Features Example 5.2

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]])

Features Example 6

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

Example 6.1

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
Manage Tables

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.

Manage Tables 1

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

Table in Excel Manage Tables 1.1

Step 3: The Microsoft Excel confirmation box pops up.

Step 4: Click Yes to convert the table into a normal cell range.

Manage Tables 1.2

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

Manage Tables 1.3

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.

Manage Tables 2

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

Manage Tables 2.1

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

Table in Excel Manage Tables 2.2

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

Manage Tables 2.3

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.

Manage Tables 2.4
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.

Manage Tables 2.5

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.

Manage Tables 2.6

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

Delete Row

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

Delete Column

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.

Table in Excel Manage Tables 2.9

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.

Manage Tables 3

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

Manage Tables 3.1

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.

Manage Tables 3.2

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.

Table in Excel Manage Tables 3.3

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.

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

Manage Tables 4

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

Table in Excel Manage Tables 4.1

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.

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

Table in Excel Manage Tables 4.2

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

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.
Manage Tables 4.3

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

Table in Excel Manage Tables 5

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

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

Manage Tables 5.1

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

Table in Excel Manage Tables 5.2

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.

Manage Tables 6

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.

Manage Tables 6.1

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.

Table in Excel Manage Tables 6.2

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.

Manage Tables 6.3

Step 4: Click OK.

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

Manage Tables 6.4

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

How to make pivot tables in Excel?

We can make pivot tables in Excel using the Summarize with PivotTable option available in the Design tab.
For example, consider the table with the list of tourist attractions and places in the US. We need to use the following steps to make a pivot table in excel.

In the table,

· Column A shows the tourist attractions
· Column B displays the places

Table in Excel Faq 1

The steps to insert pivot table in excel are as follows:

Step 1: Choose any cell in the Excel table.

Step 2: First, go to the Design tab.

Step 3: Next, select the Summarize with PivotTable option from the Tools group.

Faq 1.1

Step 4: The Create PivotTable window pops up.

Then, ensure the table and other details in the window are right.

Step 5: Click OK.

Faq 1.2

The Pivot table appears in the new sheet.

Faq 1.3

Step 6: Now, search for the fields in the Choose fields to add to report: dialog box and select the fields.

Faq 1.4

Finally, we can see that the data is converted into a pivot table.

Thus, we can convert tables into pivot tables in excel.

How to compare two tables in Excel?

We can compare two tables in Excel by placing them in the same worksheet and apply conditional formatting to the second table. Conditional formatting checks the corresponding cells in each of the two columns and highlights them in a different shade.

Why use tables in Excel?

We use tables in Excel as they make features such as filtering, sorting, automatic AutoFill, access to predefined Excel formulas, and data formatting quick and easy to apply. 

How to remove tables in Excel?

We can remove tables in Excel using the below methods:

To begin with, select Home -> Clear -> Clear All.

Table in Excel Faq 2
Then, choose the entire table and press the Delete key.

If we want to remove the Excel table but retain its format:

First, select a cell in the table and choose Design -> Convert to Range.

Faq 3

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

Faq 3.1

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.