In this article, we will discuss all 3 types of Excel tables, how to create them, and how to use them effectively. 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. So, let’s see the types of tables in Excel.

**Types of Excel Tables: 3 Different Types**

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 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 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, but rather 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 us time by calculating easily. It can perform some operations too, like sum, average, sort, group, count, etc.

**3. Data Table**

The Data table is an 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 make a table in Excel**, 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 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.

**Read More**: Does TABLE Function Exist in Excel?

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

**Read More:** How to Make Excel Tables Look Good

**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 frozen. 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**.

`=[@[Quarter 1]]+[@[Quarter 2]]`

- Then press
**Enter**.

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

**Read More:** Excel Table vs. Range: What Is the Difference?

**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 chose
**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.

**Read More:** How to Make a Comparison Table in Excel

**C. Pros and Cons of an Excel table**

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

**Pros:**

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

**Cons:**

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

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

**Read More: **How to Convert Range to Table in Excel

**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.
- The pivot table is popular 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 for each date, and the name of the customer is not required.

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

**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:

`=PMT(C6/12,C7,-C5)`

**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 in 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 variables at the same time using the data table.

**Things to Remember**

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

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

**Download Practice Workbook**

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

**Conclusion**

In this article, we described three types of Excel tables. We showed the advantages and the process of those applications in detail. Please comment down below if you have any suggestions.

## Further Readings

- Navigating Excel Table
- How to Convert Table to List in Excel
- How to Insert Floating Table in Excel
- How to Create a Table Array in Excel
- How to Provide Table Reference in Another Sheet in Excel

**<< Go Back to Excel Table | Learn Excel**