Does TABLE Function Exist in Excel?

In Microsoft Excel, a Table is a tremendously instrumental tool to process large amounts of data with ease. It saves a lot of time and reduces a lot of stress while analyzing data. Though there is no such built-in Excel function named TABLE in truth, we can create an Excel table using VBA macros. Besides, we can also make an Excel table using the built-in Table command. There is also another table, the Data table, used for What-if analysis. Our article will cover all these with suitable examples and illustrations.

Download Practice Workbook

Download this practice workbook.

Creating a Table with VBA and Excel Command

We can create an Excel table using both VBA Macros and an Excel command.

Look at the following dataset. We will use this all through our tutorial as the sample dataset.

Now, if you convert this into a table, it will look like this:

This is an Excel Table. It has a lot of functionality which we will discuss later. Before that, let’s see first how to form an Excel table using VBA or built-in Excel command.

So, there are two ways to create an Excel table, creating manually by using an Excel command, or using the VBA codes and creating a suitable function.

1. Create a Function for an Excel Table Using VBA Codes

Here is how we can create a function using VBA macros and create an Excel Table.

📌 Steps

1. First, press ALT+F11 on your keyboard to open the VBA editor.

2. Click on Insert > Module.

3. Then, type the following code:

Sub CreateTable()
Sheet1.ListObjects.Add(xlSrcRange,Range("B4:F13"), ,xlYes).Name ="Table1"
End Sub

4. Save the file.

5. Then, press ALT+F8. It will open the Macro dialog box.

6. Click on Run.

Finally, we are successful in creating a table using VBA codes.

Read more: How to Use an Excel Table with VBA

2. Create an Excel Table Using Built-in Excel Command

Here, you can create a table using a keyboard shortcut or using the Table command from the Insert tab.

Using Keyboard Shortcut:

📌 Steps

1. First, select the range of cells B4:F13

2. Then, press Ctrl+T on your keyboard. You will see a Create Table dialog box.

3. In the box, your range of cells is already given. Now, select My table has headers box option.

4. Click on OK.

As you can see, we have converted our dataset into a table.

Using The Table Command from the Insert Tab:

📌 Steps

1. First, select the range of cells B4:F13

2. Now, go to Insert Tab. Click on Table. You will see a Create Table dialog box.

3. In the box, your range of cells is already given. Now, select My table has headers box option.

4. Click on OK.

Read more: Create Table in Excel Using Shortcut

The Functionality of an Excel Table

An Excel table has multidimensional usefulness. In the following discussion, we will discuss some of its basic applications. Let us see one by one.

1. Sort

We know what sort is in Excel. Previously we had to do sorting manually with the help of the Data tab. Now, in our table, the sorting is enabled automatically. You can see the dropdown menu after each column.

Now, we are going to sort the table based on the Price (largest to smallest).

📌 Steps

1. Click on the dropdown of the column Price.

2. Select Sort Largest to Smallest option.

3. Click on OK.

Here, we sorted our table based on Price.

2. Filter

You can also filter data based on any values. Here, we are filtering the table based on the salesperson John. 

📌 Steps

1. Select the dropdown of the Salesperson column.

2. In the Filter option, first, uncheck the box Select all. Then, check the box of John.

3. Click on Ok.

Now, we have filtered our table based on the salesperson John.

3. AutoFill Formulas

In a dataset, we use formulas to perform any calculation. Then we have to drag that formula across all the columns or rows to copy that. But, at the table, you don’t have to do all these kinds of stuff. All you have to do is insert the formula. And our table will auto-fill the column.

In our previous table, the Commission column was calculated based on 10% of the product price. Here, we are changing it to 15%. After that, the Commission column will be auto-filled.

📌 Steps

1. Type the following formula in Cell F5:

2. Press Enter.

As you can see, the formula is automatically filled throughout the column.

Similarly, if you change the formula in any column, all the columns will be changed accordingly.

4. Expand Automatically for New Rows/Columns

When we add new rows or columns, the table automatically adds them as a table entry.

5. Perform Operations without Formulas

Every table has an additional option Total Row. You can perform SUM, COUNT, MIN, MAX, etc. operations without inserting any formulas. To enable this, press Ctrl+Shift+T on your keyboard. After that, a new row called Total will be added.

Here, we find the total sum of the column Price.

Again, the average of the Commission will be:

6. Easy Legible Formulas

In a table, formulas are human-readable. Anyone can interpret these formulas. Take a look at this image:

Here, the formula describes the total SUM of the column Price in SalesTable.

7. Structured References with Manual Typing

Suppose, you want to find the min value of the Commission. You don’t have to select the whole column ranges in the formula. All you have to do is select the table name and the column name.

Here, our table name is SalesTable.

Type this following formula in any cell:

As you can see our range is automatically selected.

Read more: How to Use Slicers to Filter a Table in Excel 2013

Change Excel Table Properties

There are many properties of a table. We are only discussing the basic properties of a table and how you can change them.

1. Change Formatting of a Table

You can change your table formatting in the Table Design tab. Just click any cell of your table. Then go to the Table Design tab. In the Table Style option, you will see various table format.

Click on the down arrow to expand all the Table Styles.

You can choose any of them to format your table.

2. Remove Formatting of a Table

Now, you may want to change the format and back to the basic format which you created. Go to the Table Design > Table Styles. 

After that, choose None

3. Select Default Table Style

You can also select a default table style. You can use this style whenever you create a table. Just Right-click any of the cell styles and select Set As Default.

Now, a new table of this same workbook will have this style.

4. Clear Local Formatting

When you implement a table style, local formatting is saved by default. However, you can optionally cancel local formatting if you require. Right-click any style and choose “Apply and Clear formatting“:

5. Rename a Table

When you create a table, the name of the table will be set automatically. It will look like Table1, Table2, etc. To give a table name, go to the Table Design tab. Then, you will see the Table Name box. Here, you can change and give your table any name.

6. Get Rid of a Table

To change the table into the normal dataset, go to the Table Design tab > tools. Click on Convert to Range. Then, click on Yes.

After that, it will convert our table into the normal dataset.

Read more: How to Remove Format As Table in Excel

Similar Readings

• Use Formula in an Excel Table Effectively (With 4 Examples)
• What is the Difference between a Table and a Range in Excel?
• Convert Range to Table in Excel (5 Easy Methods)
• How to Refresh All Pivot Tables in Excel (3 Ways)

The Data Table in Microsoft Excel

A data table is a range of cells in which you can modify values in some of the cells and come up with different answers to a problem. This is one of the What-If Analysis tools that enables you to try out distinct input values for formulas and see how changes in those values affect the output of the formula.

1. Create Data Table Function with One Variable in Excel

A data table with one variable lets us test a range of values for a single input. We test the range of values with the change of that input. If there is any change in the input, it will change the output accordingly.

Here, we have some data about a product. There are 2 products, per unit price is $100. If the price increase by 10%, the total price will be $210.

Now, we will create a data table with the variable increase. We will analyze increasing the total price after the percentages rise to 15%, 20%,25%,30%,35%. 

📌 Steps

1. First, create two new columns Increase and Price.

2. Then type the following in the Increase Column.

3. In cell C10, type =C7. Then press Enter. You will see the current Total price.

4. Now, select the range of cells B10:C15

5. Go to the Data Tab. Then you will find the Forecast option. Click on What-If Analysis > Data Table.

6. After that, the Data Table dialog box will appear. In the Column input cell box, select the Increase percentage.

7. Click on Ok.

Finally, you can see all the possible total prices after increasing the percentages from the data table with one variable.

2. Create Data Table Function in Excel with Two Variable

Now, the data table with two variables is almost similar to the previous one. Here, two variables affect the output. In other words, it explains how changing two inputs of the same formula changes the output.

We are using the same dataset as the previous one.

📌 Steps

1. First, create new rows and columns like the following:

2. Now, in Cell B10, type =C7. 

3. Press Enter. It will show the total price.

4. Next, select the range of cells B10:F14

5. Now, got to the Data tab. From the Forecast option, click on What-If Analysis > Data Table.

6. After that, a Data Table dialog box will appear. Select Number of products in the Row input cell box and Increase percentage in the Column input cell shown below:

6. Then, click on OK.

Finally, you will see all the possible prices for increasing the percentages as well as the number of products. Change in the Increase and No. of Products affect the total price.

3. Edit Data Table Results in Excel

Now, you can not change any part of the table of individual values. You have to replace those values by yourself. Once you start editing the data table, all those calculated values will be gone. After that, you have to edit each cell manually.

📌 Steps

1. First, select all the calculated cells. We are selecting the range of cells C11:F14.

2. Then, delete the TABLE formula from the formula bar.

3. After that, type a new value in the formula bar.

4. Then, press Ctrl+Enter.

In the end, you will see your new value in all cells. Now, all you have to do is edit this manually. By this method, you have converted it to a normal range.

4. Recalculate Data Table Function in Excel

Now, your formulas will slow down your Excel if it contains a large data table with multiple variables. In such cases, you have to disable automatic recalculations in that and all other data tables.

First, go to the Formulas tab. From the Calculation group, click on Calculation Options > Automatic Except for Data Tables.

This method will turn off automatic data table calculations and make your recalculations faster of the entire workbook.

5. Delete a Data Table

Now, Excel does not allow to delete values in particular cells holding the results. An error message “Cannot change part of a data table” will show up if you try to do this.

However, you can simply delete the whole array of the calculated values.

📌 Steps

1. First, select all the calculated cells.

2. Then, press Delete on your keyboard.

As you can see, it deleted all the resulting cells of the data table

💬 Things to Remember

✎ To create a data table, the input cell(s) must be on the same sheet as the data table.

✎ In the data table, you can not change or edit a particular cell. You have to delete the whole array.

✎ In the normal table, if you change the formula in any column, the entire column will be changed accordingly.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about tables in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Further Readings

• How to Insert or Delete Rows and Columns from Excel Table
• Update Pivot Table Range (5 Suitable Methods)
• How to Group Columns in Excel Pivot Table (2 Methods)
• Excel Table Name: All You Need to Know