In this workbook, we have a number of Excel Tables. Let’s look at some ways you can remove these tables. You won’t find a “delete table” command in Excel. To completely remove an Excel table, and all associated data, you’ll want to delete all associated rows and columns. If a table sits alone on a worksheet, the fastest way is to delete the sheet. For example, this sheet contains a table showing the busiest airports in the world. When I delete the sheet, the table is completely removed. If you want to keep the sheet, but delete the table, you can select and delete a range that includes the entire table. On this sheet, I want to remove the Orders table and leave the summaries. I’ll select the first column, then hold down the Shift key and select the last. Then I’ll Right-Click and Delete. In both of these cases, the tables and data are completely removed, and the table names no longer appear in the name box. Now, if you want to keep all data and just “undefine” an Excel table, use the “convert to range” button on the Design tab of the ribbon. This command leaves all data and formatting in place, and removes only the table definition. To illustrate, here I have a table named “movies”. If I place the cursor anywhere in the table, and use “convert to range”, the table is removed, but the data and formatting remain. What happens to formulas that use structured references when you convert a table to a range? Let’s look at an example. In this table, the Total column is a formula that multiples quantity by price. You can see the formula uses structured references. To the right, another formula counts rows in the table using a structured reference. When I convert this table to a range, everything keeps working, but the formulas are translated to standard references. One thing you may find confusing is that table formatting sticks around, even when you convert a table to a range. If you want to remove table formatting, the simplest way is to set the format to “None” before converting the table to a range. I’ll undo back to the table, and try that now. Under Table Styles, I’ll choose the “None” option. Now when I convert the table to a range, the formatting is already gone, so no trace of the table remains.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
