Types of Tables in Excel: A Complete Overview

Microsoft Excel is one of the most widely used applications worldwide. It offers lots of features. One of them is Table, which previously was known as the list. In this article, we will discuss all 3 types of Excel tables, how to create them, and how to use them effectively.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to Different Types of Excel Tables

We can create three types of tables in Excel. These are:

1. General Excel Table, or just “Excel Table”,
2. Data Table &
3. The Pivot Table.

1. Excel Table

A general Excel table is a decisive component to group the data where the rows and columns are well defined. An Excel worksheet sheet may contain multiple tables which can be used in formulas according to their header names. Data in Excel seems to be in a table format, as data is gathered in terms of rows and columns. But that is not considered as a table in Excel by definition. We need to declare a range or set of cells to be a table.

An Excel Table looks like the following picture.

2. Pivot Table

The Pivot table is a special excel table tool that reorganizes desired columns and rows of a data set. This tool does not make any changes to the main data, rather simply changes the data orientation and produces some special results from the data.

The Pivot table is important when we work with a large amount of data. It saves our time by calculating easily. It can perform some operations too, like sum, average, sort, group, count, etc.

3. Data Table

The Data table is a very interesting tool that is found in the What-If Analysis tool of Excel. By using a data table, we can calculate any argument based on different variables.

In a data table, we apply a single formula, based on one or two variables the value changes accordingly.

1. General Excel Table: Creation, Features, and Benefits

We will discuss the creation of different types of Excel tables. In this section, we will discuss how to create a general Excel table, its special features, and its pros and cons with a detailed explanation.

A. How to Create and Turn Off an Excel Table

To create an Excel Table, execute the following steps.

Step 1:

• First, click on any cell in the dataset.
• Then go to the Home tab.
• Then select the Format as Table from the Styles tool.

Step 2:

• Select any default table style.

Or, we can apply a keyboard shortcut Ctrl+T.

A new dialog box named Create Table will appear.

Step 3:

• Select the My table has headers if the data set has any header.
• Now, press OK.

The following is our dataset after being converted to table format.

Turn off an Excel Table:

Now, we will show how to turn off the an Excel table format.

Step 1:

• From the Table Design tab, select the Convert to Range option.

A new pop-up will appear.

Step 2:

• Press Yes on the pop-up tab.

Our data is back to the primary state again. No more in a table format presented as a range.

B. Special Features/Operations of an Excel Table

In this section, we will show the special features of an Excel table.

1. Table Name

We can set the name of the table from the Table Name box. The name will be assigned by default.

We can also rename a table name using the same option.

2. Resize Table Option

It has the Resize Table option with which we can change the size of the table from the Properties option.

3. Remove Duplicates

Remove a Duplicate is an inbuilt option in the table that removes duplicates based on columns. We can also customize the selection of columns.

4. Table Styles Options

From the Table Styles Options, we can customize different options shown in the tab. A total of 7 options are here.

5. Insert Row and Columns and Expand Automatically

We can insert any number of rows or columns with the existing table format. For that-

• Press the right button of the mouse.
• Then choose the Insert option in the menu.
• Now, from the option select the desired location of a new row or column.

6. Header Visibility

Whenever you scroll down an Excel Table, the Table headers remain freezed. So you don’t have to go to the top of the table each time to remember the header names.

7. Easy Application of Formula

We can easily apply the formula in the Excel table. Assume, we want to get the total in Cell E5.

• Just type the following formula in Cell E5.

To know about applying formulas in an Excel Table, click here.

• Then press Enter.

The next totals are automatically calculated as we see from the figure below.

8. Subtotal Option

We can find the subtotal of the cells of Column E. We simply press Ctrl+Shift+T. This will add a row at the end and produce a subtotal value.

We will get a drop-down menu at Cell E9. This drop-down offers some operations like average, maximum, minimum, etc.

Here, we select the Sum function for our use.

9. Insert Slicer

We have the Insert Slicer option too. We get this from the Table Design tab. Select Name from the slicer and press OK.

From the slicer, select any of the names.

• We choose Ball-pen in the slicer, and now data is only showing for this item.

Slicer provides a shortcut to get the details of the specific item only.

C. Pros and Cons of an Excel table

In this section, we will discuss the pros and cons of the Excel table.

Pros:

1. From the header of the table, we can easily sort and filter data.
2. The table can easily adjust rows and columns expansion or shrink.
3. Built-in subtotal functions reduce the use of formulas.
4. Formula fills up the adjacent cell, so you need to use the formula for all cells.

Cons:

1. When any sheet is in a protected mood, table expansion will not work.
2. If any sheet contains a table, that sheet can not be grouped with other sheets.
3. When using an Excel table, the custom view is not allowed.

Read more: How to Insert or Delete Rows and Columns from Excel Table

2. Excel Pivot Table: Creation and Uses

In this section, we will discuss the process of creating a Pivot table and its uses.

A. How to Create a Pivot Table

Execute the following steps to create a Pivot Table.

Step 1:

• First, go to the Insert tab.
• Then select PivotTable.
• Choose From Table/Range from the drop-down.

Step 2:

• Now, input the Table Range and Location.

Step 3:

• Then press OK.

Step 4:

• Now, select objects from the PivotTable Fields.

We can see a Grand Total row is added. We can customize the different fields of the Pivot Table.

B. Benefits of a Pivot Table

• We can customize the Pivot table easily. Complex formulas are not needed here. Lots of options are available in the Pivot table field.
• We can calculate in a short time using the Pivot table, as we do not need to apply any complex formula.
• We can change the representation of data in a short time in the Pivot table. Even if we can make separate views using the same Pivot table.
• Pivot table has a popularity for its calculation accuracy. Due to the built-in functions calculation accuracy is high.

C. An Example with the Excel Pivot Table

We will show the use of the Pivot table with detailed examples.  A new data set will be used to show the use of the Pivot table.

We will represent this data set through the Pivot table.

Step 1:

• Convert this data set into a Pivot table as the process is shown already.

We will change the calculation and representation by changing the PivotTable Fields.

Step 2:

Assume, we want to see only the sum of sales of each date, and the name of the customer is not required.

• Deselect the Name option.

In this segment, we will represent the total sales of each customer.

Step 3:

We can represent the value in terms of percentage.

• Click on Sum of Sales.
• Then select Value Field Settings.

Step 4:

• Select the Show Value As tab.
• Then select % of Grand Total.

Step 5:

• Finally, click OK.

Now, see that the value is showing in terms of percentage.

We have lots of options like the mentioned features in the Pivot table.

Read more: How to Insert A Pivot Table in Excel

Similar Readings

• Pivot Table Custom Grouping: With 3 Criteria
• How to Edit a Pivot Table in Excel (5 Methods)
• Group Columns in Excel Pivot Table (2 Methods)
• How to Update Pivot Table Range (5 Suitable Methods)

3. Excel Data Table: Creation and Uses

This is one of the most interesting types of Excel tables. There are two kinds of data tables available in Excel. In this section, we will discuss the creation of both data tables.

Here is our sample dataset.

Here, we calculate the Amount of EMI from different argument values. We applied the below formula for this purpose:

A. One-Variable Data Table

We will apply the data table for one variable in this section. We are considering the No. of EMI variable for this section.

Step 1:

• First, we add a column with different values of EMI.

Step 2:

• The cell which contains the formula refers to that cell on Cell E5.

Step 3:

• Now, press the Enter button.
• Then select the cells as shown on the image below.

Step 4:

• Go to the Data tab first.
• Then select the Data Table from the What-If Analysis tab.

A new dialog box will appear.

Step 5:

• In the Column input cell box, we will refer to the cell of the variable applied here.

Step 6:

• Then, press OK.

Now, we can see that the EMI Amount for different No. of EMI is showing.

B. Two-Variable Data Table

In this segment, we will show a two-variable data table in Excel.

Step 1:

• We will use two variables here. Those are the Rate of Interest and of EMI.

Step 2:

• Refer to the main formula on Cell F5.

Step 3:

• Press Enter and select the cells where to apply the data table.

Step 4:

• Now, apply the data table tool from the previous method.
• A new dialog box appears.

Step 5:

• Apply two cell references on the required field of the dialog box.

Step 6:

• Then click OK.

Here, we applied two-variable at the same time using the data table.

Read more: How to Insert Table in Excel

Things to Remember

Some important things to keep in mind when we work with the table are:

1. No space is allowed in the Table name.
2. The table name is the combination of letters and numbers.
3. If any table name contains more than one word, they will be joined by an underscore.
4. The table name is always unique within the worksheet.
5. The maximum length of the table name is 255 characters.

Conclusion

In this article, we described three types of Excel tables. We showed the advantages and the process of those applications in detail. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Further Readings

• How to Convert Table to List in Excel (3 Quick Ways)
• Use Excel Table Reference (10 Examples)
• What is the Difference between a Table and a Range in Excel?
• How to Refresh Pivot Table in Excel (4 Effective Ways)