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.
