How to Create a Table in Excel with Data (5 Ways)

If you are searching for some special tricks to create a table in Excel with data, you are in the perfect place. In Microsoft Excel, there are numerous ways to create a table in Excel. In this article, we’ll discuss five methods to create a table in excel. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

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


5 Methods to Create a Table in Excel with Data

Here, we have the following dataset for showing the methods of creating a table in Excel.

In the following section, we will use 5 methods to create a table in Excel with data.


1. Using Table Option to Create a Table in Excel with Data

You have to follow the following steps to have the Gross Revenue with data by using the Table option.

Using Table Option to Create a Table in Excel with Data

 

Steps:

  • Firstly, go to the Insert tab and select the Table option.

Using Table Option to Create a Table in Excel with Data

  • When the Create Table dialog box appears, select the range of data and click on OK.

  • Finally, select the Cell E5 And type the below formula:

=[@Capital]+[@Capital]*[@Year]*[@[Interest Rate ]]

  • Click on Enter.

Using Table Option to Create a Table in Excel with Data

Result:

Finally, you will get the following result.

Read More: Create Table in Excel Using Shortcut (8 Methods)


2. Using “Format as Table” Option to Create a Table with Data

You have to follow the following steps to create a table by using the Format as Table Option.

Using “Format as Table” Option to Create a Table with Data

Steps:

  • Firstly, go to Home Tab, select Format as Table option and then select any of the styles.

  • When the Create Table dialog box appears, select the range of data and click on OK.

Using “Format as Table” Option to Create a Table with Data

  • Finally, select Cell E5 And type the below formula

 =[@Capital]+[@Capital]*[@Year]*[@[Interest Rate ]]

  • Click on Enter.

Using “Format as Table” Option to Create a Table with Data

Result:

Finally, you will get the following result.

Using “Format as Table” Option to Create a Table with Data

Read More: How to Make a Table in Excel (With Customization)


3. Using “Format as Table” Option to Create a Table with Data

Now, we will try to get the output Gross Revenue by using a column-oriented (one variable) table.

Using “Format as Table” Option to Create a Table with Data

We have to follow the following steps to get the desired output.

Steps:

  • Firstly, we will use the below formula in Cell E5 to get the Gross Revenue.

=B5+B5*D5*C5

Where Cell B5 is the Capital, Cell C5 is the interest rate and Cell D5 is the year.

Using “Format as Table” Option to Create a Table with Data

 

  • Select the whole range of dataset (B5:E10), then go to the Data tab and Select What-if-Analysis And finally select Data Table.

  • When the Data Table dialog box opens, select Cell B5 in the Capital column for the purpose of the Column input cell option, and click on OK.

Using “Format as Table” Option to Create a Table with Data 

Result:

By following the above method, you will get output in the Gross Revenue column.


Similar Readings


4. Creating Multiple Results in a Table in Excel

In order to get multiple results in a table (in the Gross Revenue and Interest column), you have to follow the following steps.

Creating Multiple Results in a Table In Excel with Data

Steps:

  • Firstly, we will use the below formula in Cell E5 to get the Gross Revenue.

    =B5+B5*D5*C5

Where Cell B5 is the Capital, Cell C5 is the interest rate and cell D5 is the year.

Creating Multiple Results in a Table In Excel with Data

  • We have to use the following formula to get the Interest value in Cell F5:

=E5-B5

Where Cell E5 is the Gross Revenue, and Cell B5 is the Capital.

Creating Multiple Results in a Table In Excel with Data

  • Select the whole range of dataset (B5:F10), then go to the Data tab and Select What-if-Analysis And finally select the Data Table

  • When the Data Table dialog box opens, select Cell B5 in the Capital column for the purpose of the Column input cell option, and click on OK.

Creating Multiple Results in a Table In Excel with Data

Result:

By following the above method, you will get output in the Gross Revenue column.


5. VBA to Create a Table with Data

To create a table in excel by following the VBA method, we have to follow the following steps.

Steps:

  • First, we have to open sheet 7.
  • After that, we will type (ALT + F11) to open the VBA editor. After that, click on Insert > Module.

VBA to Create a Table with Data

  • Now, we will type the following code in the code window.
Sub create_table()

Sheets("VBA").ListObjects.Add(xlSrcRange, Range("B4:D10"), , xlYes).Name = "ABC Bank"

End Sub

VBA to Create a Table with Data

  • Next, we have to close the Code window, open Sheet7, press ALT+F8, and a Macro window will appear and click on Run.

Result:

Finally, we will get the desired output like the following:

VBA to Create a Table with Data


Conclusion

That’s the end of today’s session. I strongly believe that from now you may create a table in excel with data. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Tags:

Saquib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo