While using Microsoft Excel, sometimes we need to delete or remove a data table. In this tutorial, I will show you four quick tricks to delete a data table in Excel effectively, with appropriate illustrations. These methods are simple and do not require the use of any complex formulas.
Consider the following dataset. Let’s use this dataset to demonstrate what actions to take when a data table is deleted. We will use the peripheral device, Design tab, Clear command, and VBA code, as well, to delete a data table in Excel. Here’s an overview of the dataset for today’s task.
1. Using a Peripheral Device to Remove a Table from the Data Model in Excel
In this section, we will use the peripheral device (Mouse) to delete a table from the data model. This is an easy task. From our dataset, we can easily do that. Let’s follow the instructions below to delete a table from the data model!
Steps:
- First of all, select the data table. From our dataset, we will select data ranging from B4 to E13 for the convenience of our work.
- Hence, press the right-click on your mouse. After that, a window will appear in front of you. From that window, select the Table Columns under the Delete option.
- As a result, you will be able to delete a data table that has been given in the below screenshot.
- You can delete or remove table data without changing the table format. To do that, first, select your data range. After that, simply press the Delete button on your keyboard.
Read More: Data Table Not Working in Excel (7 Issues & Solutions)
2. Utilizing Design Tab to Delete a Data Table Without Deleting Data in Excel
Now, we want to delete a table but keep the formatting and data of that table. Undoubtedly, this is an easy task. Let’s follow the instructions below to delete a table from the data model!
Steps:
- Firstly, select B4 to E13 of the data table. Hence, from your Table Design tab, go to,
Table Design → Tools → Convert to Range
- As a result, a Microsoft Excel warning box will pop up asking Do you want to convert the table to a normal range?. After that, press the OK option.
- Finally, you will be able to delete a table but keep the formatting and data of that table.
Read More: How to Change Chart Data Range in Excel (5 Quick Methods)
3. Applying Clear Command to Delete a Data Table in Excel
Here, we want to delete tables and delete data and formatting as well, using the Clear command in Excel. Let’s follow the instructions below to learn!
Steps:
- To remove the data table, select B4 to E13. Hence, from your Home tab, go to,
Home → Editing → Clear All
- As a result, you will be able to delete a data table that has been given in the below screenshot.
Read More: Example of Excel Data Table (6 Criteria)
4. Running an Excel VBA Code to Delete a Data Table in Excel
Now I’ll show you how to delete a data table in Excel by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will delete a data table in Excel. Let’s follow the instructions below to learn!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Delete a Data Table will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, the Delete a Data Table module pops up. In the Delete a Data Table module, write down the below VBA code.
Sub delete_data_table()
Cells.Range("B4:E13").Delete
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the VBA Code, go back to your worksheet and you will be able to remove a data table that has been given in the below screenshot.
👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously. You can open the Microsoft Visual Basic Applications window by using the ALT + L + V shortcut as well.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
Read More: How to Make a Data Table in Excel (Easiest 5 Methods)
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to delete a data table will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- How to Add Data Table in an Excel Chart (4 Quick Methods)
- How to Create One Variable Data Table in Excel (2 Examples)
- Scenario Analysis in Excel: A Guide with 2 Sample Cases + Template
- How to Create a Two Variable Data Table in Excel (3 Examples)
- How to Perform the What If Analysis with Data Table in Excel
