Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

 Create a Table in Excel with Data

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.

select table option

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

select the required range of cells

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

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

  • Click on Enter.

Result:

Finally, you will get the following result.

 Create a Table with Data

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.

select format as table option

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

select the range of cells

  • Finally, select Cell E5 And type the below formula

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

  • Click on Enter.

calculate the gross revenue

Result:

Finally, you will get the following result.

show the output

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.

calculate gross revenue

 

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

select data table option

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

 

Result:

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

show the output


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.

calculate gross revenue

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

Calculate interest

  • 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

select data table option

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

get the output

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

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

run macro

Result:

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

 Creating a Table in Excel 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

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo