How to Remove “Format as Table” in Excel

Formatting tables in Excel is usually a blessing, but sometimes, it can be a hassle. If you’re experiencing such a situation, this guide is for you. We’ll take you through simple steps to remove “Format as Table” in Excel.

While Excel tables provide a structured layout, they also apply specific formatting automatically. If you need to go back to a basic data format without the table structure, there are a few straightforward methods to remove the table formatting.

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 proves to be one of the most straightforward ways to restore a table to its default appearance or remove any applied styles.

When you format a range of cells as a table, Excel applies a predefined design with elements like banded rows and a header. To revert to a plain data layout, follow the steps below:

Step 1: Press the Windows + S keys on your keyboard, type Excel, and click Open.

Step 2: Open the Excel sheet you wish to clear table formatting.

Step 3: Click any of the cells in the table and navigate to the Table Design tab. Press the down arrow next to preformatted tables in the Table Styles section to expand it.

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

Also Read: How to format tables data in Microsoft Excel

Method 2: Clear All Formatting

While the clear table style option removes predefined table formatting, it doesn’t affect custom formatting applied directly to cells. For a complete cleanup, use the clear formats or clear all formatting methods. Adhere to the steps below to do so:

Step 1: Click the Windows icon, type Excel in the search box, and select Open.

Step 2: Open the Excel file containing the table you want to edit.

Step 3: Choose the table from the file that you want to remove formatting from.

Tip: If you have the table you wish to edit in the sheet, press Ctrl + A to quickly select it.

Step 4: In the Editing section, locate and click the Clear button. Choose one of the following options:

• Clear Formats: To remove the formatting without deleting data.
• Clear All: To remove all formatting, including data.

Also Read: How to use data validation in Microsoft Excel

Method 3: Convert to Range

When you want to remove special formatting and features like colored rows, filter icons, and summary rows from tables, converting to a range is helpful in Excel. Follow the steps below to remove the table in Excel but keep the data:

Step 1: Press the Windows + S keys, enter Excel in the search box, and click Open.

Step 2: Head to the sheet where you wish to remove “Format as Table” in Excel.

Step 3: Right-click on any cell in the table. Navigate to Table from the options and select Convert to Range. Click Yes when prompted.

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

Also Read: How to use comments tool in MS Excel to add notes

Method 4: Using VBA

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

Step 1: Press the Windows key, click the search bar, type Excel, and hit Open.

Step 2: Open the Excel sheet with the table you wish to edit.

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

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

Step 5: 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 6: Run the script by pressing the ALT + F8 keys on your keyboard. Select RemoveFormatAsTable from the pop-up and click Run.

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

Also Read: How to fix Microsoft Excel not saving changes on Windows

FAQs on Clearing Table Formatting

Format Table

Whether for aesthetic reasons or to streamline data manipulation, the methods described above can efficiently remove “Format as Table” in Excel. However, it’s essential to consider that doing so might also remove some helpful table functionalities, such as filtering and sorting.