If you want to create a table in Excel based on cell value, you have come to the right place. In the following article, we will demonstrate to you 4 easy methods to do the task effortlessly.
Create a Table in Excel Based on Cell Value: 4 Easy Methods
The following dataset has the Product, Quantity, and Profit columns. Using this table, we will go through 4 easy and effective methods to create a table in Excel based on cell value. Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.
1. Using Table and Pivot Table to Create a Table in Excel Based on Cell Value
In this method, first, we will insert a Table using our dataset, and after that, we will use a Pivot Table to create a table in Excel based on cell value.
Let’s go through the following steps to do the task.
Step-1: Inserting Table
In this step, we will insert a Table.
- First of all, we will select the entire dataset >> go to the Insert tab.
- After that, from the Tables group >> select Table.
At this point, a Create Table dialog box will appear.
Then, make sure My table has headers is marked.
- Moreover, click OK.
As a result, you can see the created Table.
Step-2: Inserting Pivot Table
In this step, we will insert a Pivot Table from the Table.
- In the beginning, we will select the entire data of the Table >> go to the Insert tab.
- After that, from the Tables group >> select PivotTable.
Afterward, a PivotTable from table or range dialog box will appear.
- Furthermore, select New Worksheet.
- Then, click OK.
- After that, from the PivotTable Fields >> drag Product under the Row group.
- Along with that, drag Profit under the Values group.
As a result, you can see the created Pivot Table.
Step-3: Adding Cell to Table
In this step, we will add a cell to the Table, and therefore, we will show that the cell value gets added to the Pivot Table.
- First, we add the product Battery in cell B10, and along with that, we add Quantity and Profit for the product in cells C10 and D10 respectively.
- After that, we go to the Pivot Table.
Here, we will Refresh the data of the Pivot Table.
- Next, we click on a cell of the Pivot Table >> go to the PivotTable Analyze tab.
- Furthermore, from the Data group >> select Refresh >> select Refresh.
You can also Refresh the Pivot Table data by pressing ALT+F5.
Therefore, you can see the product Battery along with its Profit has been added to the Pivot Table.
Hence, you can create a table in Excel based on cell value.
Read More:Â How to Create a Table with Existing Data in Excel
2. Use of OFFSET and COUNTA Functions to Create a Dynamic Table Based on Cell Value
In this method, we will use the OFFSET and COUNTA functions to create a table in Excel based on cell values.
Let’s go through the following steps to do the task.
Step-1: Copying Dataset to Another Location
In this step, we will give a name to our dataset, and along with that, we will copy the dataset to a new location.
- First, we will select the cells B4:D9 >> in the Name Box we typed Profit_list.
- After that, to copy the data of the dataset, we will type the following formula in cell C14.
 =Profit_list- Next, press ENTER.
Therefore, you can see the copied dataset in cells C14: C19.
Step-2: Adding Cell to Dataset
In this step, we will add a cell to the Dataset, and therefore, we will show that the copied cell does not get updated.
First, we add the product Battery in cell B10, and along with that, we add Quantity and Profit for the product in cells C10 and D10 respectively.
However, you can see that the copied dataset in cells C14: C19 does not get updated.
Step-3: Adding Formula
In this step, to update the copied cells as soon as we add a value to the dataset, we will use a formula using the OFFSET and COUNTA functions.
- To do so, first, we will select the entire dataset >> go to the Formulas tab.
- Then, select Name Manager.
Afterward, a Name Manager dialog box will appear.
- Then, double-click on Profit_list.
At this point, an Edit Name dialog box will appear.
- After that, in the Refers to box, type the following formula.
=OFFSET('OFFSET and COUNTA'!$B$3,1,0,COUNTA('OFFSET and COUNTA'!$B:$B)-1,3)Here, the OFFSET function returns a section from a data set situated at a specific number of rows down and a specific number of columns right from a given cell reference.
And in a specific cell range, the COUNTA function calculates the total non-empty cells.
- Afterward, click OK in the Edit Name dialog box.
- Furthermore, click Close in the Name Manager dialog box.
- Next, we type Battery in cell B10, and along with that, we add Quantity and Profit for the product in cells C10 and D10 respectively.
As a result, you can see that the copied dataset in cells C14: C19 does get updated. And you can see the Battery in cells C21, 7, and 900 in cells D21 and E21 respectively.
Hence, you can create a table in Excel based on cell value.
Read More:Â Create Table in Excel Using Shortcut
3. Using Filter Feature to Create a Table in Excel Based on Cell Value
In this method, we will use the Filter Button feature from Table to create a table in Excel based on cell value.
- First of all, we followed Step-1 of method-1 to insert a Table using the following dataset.
As a result, you can see the Table.
- Next, click on the drop-down arrow of the Product column.
Therefore, you can see both the products, along with Select All, are marked.
- Moreover, we will unmark the Select All option. Therefore, both products will be unmarked.
- Next, we will mark only on Laptop >> click OK.
As a result, you can see the Table now shows the dataset of laptops.
After that, we want to remove the drop-down arrow from the Table.
- To do so, we will click on any cell of the Table >> go to the Table Design tab.
- Furthermore, from Table Style Options >> unmark Filter Button.
As a result, you can see the created table with the product Laptop.
Therefore, you can create a table in Excel based on cell value.
Read More:Â How to Create a Table Without Data in Excel
4. Applying VBA to Create a Table Based on Cell Value
Here, in the following picture, you can see that we have to create a Table with 5 Rows and the table must have the Product, Quantity, and Profit as a Fixed Heading. Therefore, we will use VBA code to create a table in Excel based on cell value.
Let’s go through the following steps to do the task.
- First, we will go to the Developer tab >> select Visual Basic.
This will bring out a VBA editor window.
Here, you can press ALT+F11 to bring out a VBA editor window.
Then, a VBA editor window will appear.
- Afterward, from the Insert tab >> select Module.
- Next, type the following code in the Module.
Sub Create_Table_based_on_cell()
Dim heading() As Variant
Dim number_of_rows As Long
Dim number_of_column As Long
Dim heading_color As Long
Dim heading_row As Long
Dim work_sheet As Worksheet
Dim p As Long, q As Long
Dim cell_rng As range
Set work_sheet = ActiveSheet
number_of_rows = work_sheet.range("C4")
number_of_column = work_sheet.range("C5")
heading_row = 8
With work_sheet
heading = .range(.Cells(6, "C"), Cells(6, "C").End(xlToRight)).Value
heading_color = UBound(heading, 2)
.range("B" & heading_row).Resize(1, heading_color).Value = _
Application.Index(heading, 1, 0)
For q = 1 To number_of_rows
.Cells(q + heading_row, 2).Value = q
Next q
Set cell_rng = range("B" & heading_row).CurrentRegion
End With
work_sheet.ListObjects.Add(xlSrcRange, cell_rng, , xlYes).Name = "Product"
End Sub
Code Breakdown
- Here, we take Create_Table_based_on_cell as Sub.
- We declare several variables.
- We Set the numer_of_row, numer_of_column, and heading_row.
- Then, used a With Statement to avoid repetition of the same variable.
- Next, select the headings by using Cell range and xlToRight properties from the sheet based on which created the Table.
- Also colored the headings and rows by using the Index number.
- We used the For loop to run the code until it finds the last column.
- Later, Named the created table as Product.
- After that, click on the Run button to run the code.
As a result, you can see the created Table.
Therefore, you can create a table in Excel based on cell value.
Read More:Â How to Create a Table with Merged Cells in ExcelÂ
Practice Section
You can download the above Excel file to practice the explained methods.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
Conclusion
Here, we tried to show you 4 methods to create a table in Excel based on cell value. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.
