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.

dataset of table function in excel

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

Main table function in excel

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.

insert module for VBA editor

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.

Macro dialog box for table in excel

6. Click on Run.

dataset converted to table function

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

select the range of the dataset

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.

create table function in excel

4. Click on OK.

dataset converted to table function

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

select the range of the dataset

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

Table in insert tab in excel

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

create table function in excel

4. Click on OK.

dataset converted to table function

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.

dropdown of the basic table in excel

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.

select dropdown of pric ein excel

3. Click on OK.

price column is sorted in table

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.

filter the column in table function excel

3. Click on Ok.

table filtered based on salesperson john

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:

=(E5*15)/100

type the formula in commission column

2. Press Enter.

auto filled column in table in excel

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.

add table row in excel table

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.

sum using the total row

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

Again, the average of the Commission will be:

average using the table easily

6. Easy Legible Formulas

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

easy human readable formulas in table

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:

=Min(SalesTable[Commision])

automatically selected range

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.

various table function format

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

click on down arrow for various table format

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

remove formatting of a table in excel

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.

set default table style in excel

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โ€œ:

clear local table 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.

rename a table function in excel

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.

convert table into normal range

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

delete a tablein excel

Read more: How to Remove Format As Table in Excel


Similar Readings


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.

Total Price = (No. of Product * price per unit)+(price per unit * increase)

data table with one variable dataset

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.

create two new column

2. Then type the following in the Increase Column.

type this in increase table in excel

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

current total price in new data table function in excel

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

select the range of cells for data table

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

what if analysis in for data table in excel

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

data table dialog box

7. Click on Ok.

data table with one variable

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:

create new rows and columns for data table

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

type =C7 in the cell

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

total price in new table

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

select the range of cells in data table

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

Go to what if analysis in data tab

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:

Data table dialog box in excel

6. Then, click on OK.

data table function with two variable in excel

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.

select range of cells in data table

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

delete the table formula

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

type new value in formula bar

4. Then, press Ctrl+Enter.

all the cells value are changed

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.

recalculate data table function results in excel

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.

error in deleting values in data table

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

๐Ÿ“Œ Steps

1. First, select all the calculated cells.

select all the calculated cells

2. Then, press Delete on your keyboard.

deleted cells of data table

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

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo