Create a Table in Excel Based on Cell Value (4 Easy Methods)

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.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


4 Methods to Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value


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.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

As a result, you can see the created PivoTable.

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.

Create a Table in Excel Based on Cell Value

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

Create a Table in Excel Based on Cell Value

Read More: How to Create a Table with Headers Using Excel VBA (2 Methods)


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.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

  • 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: How to Create a Table in Excel with Multiple Columns


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.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

  • Moreover, we will unmark the Select All option. Therefore, both products will be unmarked.

  • Next, we will mark only on Laptop >> click OK.

Create a Table in Excel Based on Cell Value

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.

Create a Table in Excel Based on Cell Value

Read More: How to Create a Table with Existing 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.

Create a Table in Excel Based on Cell Value

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

Create a Table in Excel Based on Cell Value

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: Excel VBA to Create Table from Range (6 Examples)


Practice Section

You can download the above Excel file to practice the explained methods.

Create a Table in Excel Based on Cell Value


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. Please visit our website Exceldemy to explore more.


Related Articles

 

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo