Does your Excel worksheet look cluttered due to many tables? Are you becoming confused when seeing many tables? Or perhaps, you just no longer want some tables on your Excel spreadsheet. Read on to learn how to remove a table in Excel.

Creating a table in Excel is quite easy. However, when it comes to deleting the table, you might go through a complex process of deleting content, removing cell formatting, etc., if you don’t know the proper techniques.

I’ll show multiple methods to delete Excel tables or table formatting In this quick and easy Excel tutorial with steps and illustrations.

Remove Excel Table Format Using Convert to Range

The Convert to Range command is the default Excel feature that you can use to undo the table formatting of a cell range in your worksheet. This method lets you keep your Excel table data for further use. It just deletes the table object.

Go to your workbook and navigate to the worksheet from which you want to delete the table. Now, click on the Excel Name Box and select the table you want to delete.

Alternatively, you can select all the cell ranges in a table using the mouse or the Shift and Arrow keys.

Right-click on the table and hover the cursor over the Table menu in the context menu.

Click on the Convert to Range command on the overflow menu of the Table context menu.

Hit the OK button on the warning message to undo table formatting in Excel.

If you also want to get rid of the cell colors and borders, highlight the cell range, and click on the Clear drop-down in the Editing commands block of the Home tab. Choose Clear Formats from the context menu.

Remove Table in Excel Using Clear Contents

Suppose, you no longer need a table as well as its content. In this case, you can use the Clear Contents command in the editing commands block.

To use this method, select the table you want to delete along with its contents.

Go to the Editing block in the Home tab and click the Clear drop-down menu. In the context menu that opens, choose Clear Contents.

Excel shall delete the table as well as its dataset.

Remove a Table in Excel Using Delete Table Columns

Another simple method to delete the entire table is the Delete Table Columns or Rows option in the Excel ribbon menu.

Select the table you want to delete and go to the Cells commands block in the Home tab.

Click the Delete drop-down menu and choose either Delete Table Rows or Delete Table Columns.

Another way to perform the above actions is by right-clicking on the selected Excel table and clicking on Table Columns or Rows of the Delete menu.

Remove an Excel Table Using the Paste Special Tool

Highlight the table you’d like to delete on your Excel worksheet. Press Ctrl + C to copy the table from its location.

Now, highlight any blank cell in the worksheet and press Ctrl + Alt + V to call the Paste Special dialog.

On Paste Special, select the Values selection and click OK.

Now, select all the columns of the original table from the column letters menu on the top. Press Delete to get rid of the table.

You’ve deleted the table and kept its content in a range format.

Remove an Excel Table Using Excel VBA

Excel VBA allows you to automate various tasks you perform on Excel including removing table formatting or tables in your worksheet. I’ve created the perfect VBA script for you that enables you to visually interact with your spreadsheet tables to either remove formatting or delete the whole object.

Also, you don’t need to be an expert in Excel VBA programming to use this method. Simply, follow along as I explain how to create a VBA macro using the script I’ve coded.

On your worksheet, press Alt + F11 to call the Excel VBA Editor tool.

Click on the Insert button and choose Module. Inside the new module, copy and paste the following VBA script:

Sub RemoveTableOrFormatting()
    Dim rng As Range
    Dim tbl As ListObject
    Dim response As Integer
    Dim tblRange As Range

    ' Show an input box to select the table
    On Error Resume Next
    Set rng = Application.InputBox("Select a range", Type:=8)
    On Error GoTo 0

    ' Check if a range has been selected
    If rng Is Nothing Then
        MsgBox "No range selected. Exiting..."
        Exit Sub
    End If

    ' Check if the selected range is part of a table
    On Error Resume Next
    Set tbl = rng.ListObject
    On Error GoTo 0

    ' If the selected range is not part of a table, exit
    If tbl Is Nothing Then
        MsgBox "The selected range is not part of a table. Exiting..."
        Exit Sub
    End If

    ' Store the range of the table
    Set tblRange = tbl.Range

    ' Show an input box to select the action
    response = MsgBox("Press 'Yes' to delete the whole table, or 'No' to remove table formatting.", vbYesNoCancel)

    ' Perform the action based on the response
    Select Case response
        Case vbYes
            tbl.Delete
        Case vbNo
            tbl.Unlist
            tblRange.ClearFormats
        Case vbCancel
            ' Do nothing
    End Select
End Sub

Click on the Save button and the Microsoft Excel dialog will show up. On it, click No to open the Save As dialog.

On the Save As dialog, click the Save as type drop-down and choose XLSM file format. Click the Save button to save your XLSX workbook in XLSM format so that you can save VBA macros in it.

Close the Excel VBA Editor.

Now, press the Alt + F8 keys together to bring up the Macro dialog box.

There, select the RemoveTableOrFormatting macro and hit Run to execute the VBA script.

You should see an input box. Use that to highlight the range address of the table you want to delete or undo formatting.

On the next message box, choose Yes to delete the table object including its data.

Alternatively, click No to delete the table formatting and convert the formatted dataset to an unformatted dataset of simple cell ranges.

Don’t follow this method without creating a backup copy of your workbook by copying it somewhere else on your PC. Once you run an Excel VBA macro, you can’t use the Excel undo feature to revert the modifications made in the Excel workbook.

Remove Excel Table Format Using Office Scripts

If you wish to automate the table-removing task in Excel for the web or want to use advanced automation using Power Automate, you must use Office Scripts.

Considering you’re using Microsoft 365 Business Standard or a higher subscription, you can find the Automate tab in your Excel desktop and web app. Inside the Automate tab, you’ll find all options of Office Scripts. If you can’t see the Automate tab, unfortunately, you can’t follow this method.

On your worksheet where you’d like to delete one or many tables, click the Automate tab. There, find the New Script command inside the Scripting Tools block and click on it.

The Code Editor interface shall open on the right side of the worksheet as shown in the screenshot.

Some Excel editions may automatically include the last used or coded Office Scripts inside the Code Editor console. If you see any existing script in Code Editor, select the script and remove it by pressing the Delete key.

Now, copy and paste the following Office Script inside the Code Editor and click the Save script button.

function main(workbook: ExcelScript.Workbook) {
    // Get the current selected range
    let selectedRange = workbook.getSelectedRange();

    // Get the table from the selected range
    let table = selectedRange.getTables()[0];

    if (table) {
        // Get the range of the table
        let range = table.getRange();

        // Convert table to range
        table.convertToRange();

        // Clear the formatting from the range
        range.clear(ExcelScript.ClearApplyTo.formats);

        console.log("Table converted to range and formatting cleared.");
    } else {
        console.log("No table found in the selected range.");
    }
}

You can rename the script by clicking on the generic script identification text and entering a new name in the Script name field.

Once done creating and saving this Office Scripts-based automation, highlight a table on your worksheet that you want to delete.

Click the Run button on the Code Editor interface to execute the Office Scripts code.

Excel shall remove the table formatting from the highlighted table leaving behind the unformatted dataset in cell range structures.

Conclusions

So far, you’ve learned six methods to remove a table or table formatting in Excel.

The first four techniques show various Excel command buttons available to remove the Excel table format or the whole table.

If you wish to avoid the need to remember the manual processes and steps involved in these methods, you can follow the Excel VBA and Office Scripts-based techniques.

Comment below if the article helped, if you’ve got any suggestions, or if you’d like to add more methods to remove tables in Excel.