How to Remove Table Formatting in Excel

Method 1: Clear Table Style

When working with data tables, Excel often applies predefined styles that include various formatting elements such as colors, borders, and fonts. The Clear Table Style function is one of the most straightforward ways to restore a table to its default appearance or remove any applied styles.

Step 1: Open your desired Excel Sheet or Workbook.

Step 2: Click any of the cells in the table and navigate to the Table Design tab. In the Table Styles section, click the down arrow next to preformatted tables to expand the section.

Step 3: Click the Clear button to remove the table formatting.

Method 2: Converting to Range and Clearing All Formatting

When you want to remove special formatting and features like colored rows, filter icons, and summary rows from tables, converting to a range, then using the Clear All Formatting function is handy. Follow the steps below.

Step 1: Open the desired Excel Workbook or Sheet.

Step 2: Right-click on any cell in the table. From the options, navigate to Table and select Convert to Range. Click Yes when prompted.

Step 3: Select the table in the workbook. In the Editing section, click the drop-down button next to Clear and press Clear Formats.

Method 3: Using VBA

While Excel provides several built-in tools for formatting tables, there may be instances where these options fall short. VBA, a programming language embedded within Excel, allows you to automate tasks and customize functionality. Here’s how to use it:

Step 1: Open the desired Excel Workbook or Sheet.

Step 2: Launch the VBA editor by pressing the Alt + F11 keys on your keyboard.

Step 3: Insert a new module by right-clicking on any existing module or the project, selecting Insert, and clicking Module.

Step 4: Enter the below-mentioned VBA code and close the window.

Sub RemoveFormatAsTable()
    Dim tbl As ListObject

    ' Check if the active cell is within a table
    If ActiveCell.ListObject Is Nothing Then
        MsgBox "Active cell is not within a table.", vbExclamation
        Exit Sub
    End If

    ' Set tbl to the table of the active cell
    Set tbl = ActiveCell.ListObject

    ' Remove the table format
    tbl.Unlist

    MsgBox "Format as Table removed successfully.", vbInformation
End Sub

Step 5: Run the script by pressing your keyboard’s Alt + F8 keys. Select RemoveFormatAsTable from the pop-up and click Run.

Note: Save your workbook before running any VBA code, as it cannot be undone.

If changes are not saved, you may follow specific steps to fix it on Microsoft Excel.