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
1. Does removing table formatting risk losing data?
No, removing table formatting won’t delete your data. It only changes the visual appearance of the table.
2. Is there a shortcut to remove table formatting?
Yes, you can use the shortcut ctrl + T to select the table and press ctrl + L to convert it to a range, removing the table formatting.
3. What happens to formulas when I convert a table to a range?
When you convert a table to a range in Excel, the formulas in the table don’t get deleted or changed. They stay in the cells as they were.
4. Will removing table formatting affect sorting and filtering options?
No, removing table formatting doesn’t impact sorting or filtering. Your data can still be sorted and filtered after the formatting is removed.
5. Can I undo the removal of table formatting?
Yes, you can use the Undo command immediately after removing table formatting to revert the changes. Press ctrl + Z to undo the last action.
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.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
