Saquib
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.
Steps:
- Firstly, go to the Insert tab and select the Table option.
- 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.
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.
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.
- Finally, select Cell E5 And type the below formula
=[@Capital]+[@Capital]*[@Year]*[@[Interest Rate ]]
- Click on Enter.
Result:
Finally, you will get the following result.
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.
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.
- 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.
Result:
By following the above method, you will get output in the Gross Revenue column.
Similar Readings
- Pivot Table Field Name Is Not Valid: 9 Causes and Corrections
- How to Group Dates in Pivot Table (7 Ways)
- How to Update Pivot Table Range (5 Suitable Methods)
- Excel Table Formatting: Problems and Fixes You Need to Know
- Excel Table Name: All You Need to Know
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.
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.
- 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.
- 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.
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.
- 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.
Result:
Finally, we will get the desired output like the following:
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
- How to Use Slicers to Filter a Table in Excel 2013
- How to Insert or Delete Rows and Columns from Excel Table
- Excel Table Formatting Tips – Change the Look of the Table
- What is the Difference between a Table and a Range in Excel?
- How to Make Excel Tables Look Good (8 Effective Tips)
- How to Refresh All Pivot Tables with VBA (4 Ways)
