Tables offer you a fast and easy way to view your Excel data in a clean, organized way. In a sense, an Excel spreadsheet is already like a giant table of data, but creating your own tables with unique formats and styles can make for a more orderly and aesthetically-pleasing experience. But at one point or another, you may not need or want to use an Excel table anymore. Even if you want to keep the table, you may not like the formatting. Fortunately, these are easy fixes.
So, what does removing a table format do? What are the different ways to remove Excel table formatting? Finally, where can you get help with Excel from trained IT experts?
In today’s guide, we will answer all of these questions and more by providing you with a whopping 10 methods for removing table formatting in Excel. So, let’s get started!
Method 1: Change Light Design Type
When you create a table in Excel, it will automatically go with the default table design and style (unless you’ve created a custom design, but we’ll cover that later on). One of the easiest and most straightforward ways to remove table formatting is to go through the Table Design tab. If you’re unsure what to do from there, simply follow the steps below:
- Select a cell within the table
- Go to Table Design > Table Styles
- Under Light Designs, change to the type labeled as “None”
Note: The “None” type is the first available option, but you won’t see that it is labeled as such unless you hover your cursor over the image.
Method 2: Clear Table Styles
If you don’t want to use Method 1 or you simply like having options, you can also try clearing the table styles. Just follow these steps:
- Select a cell within the table
- Go to Table Design > Table Styles
- Click on the drop-down menu icon at the right side of the Table Styles tab
- Scroll to the bottom and click on “Clear”
Note: Keep in mind that using either Method 1 or 2 will only remove Excel’s default table formatting. If you’re using custom formatting, these methods will not work.
Method 3: Clear All Table Formatting
As mentioned above, Methods 1 and 2 only work when you want to remove the default Excel table formatting. If you’ve added any custom formatting, you’ll need to ensure that you can clear all formatting in one go (default and custom). To do this, just follow these steps:
- Highlight the entire table
- Go to Home > Clear
- Select “Clear Formats”
Note: Keep in mind that this method will remove every kind of formatting from your table, including any added fonts, alignments, and colors.
Method 4: Revert Table to Normal Cells
Sometimes, you’re better off just removing a table and starting from scratch. This way, you can create a new table or even decide to organize the data in a different way. The alternative would be deleting your entire table with all of its data, which would not be a good idea if you need to keep using the data in the future. So, to revert your table back to “normal” cells, just follow these steps:
- Right-click on a cell within the table
- In the menu, go to Table > Convert to Range
- Click “Yes”
Note: This method will get rid of your table, and if you try to revert the cells back to a table again, the formulas will be using normal cell references. This means it could be extremely difficult to undo this method once it’s been done.
Method 5: Remove Table Formats With VBA Code
Using VBA code can get a bit tricky, especially if you’re not accustomed to writing code in the first place. However, we’re going to make it easy for you by providing the right code to use to remove table formats. Just remember that this method is best if you have multiple tables in one worksheet and you want to remove the formatting for all of them. If this applies to you, just follow these steps:
- Press ALT + F11 to open the Visual Basic Editor
- Go to Insert > Module
- Copy and paste this code into the module:
Sub RemoveTableFormat()
Dim wsObj As Worksheet
Dim tableObj As ListObject
For Each wsObj In ActiveWorkbook.Worksheets
For Each tableObj In wsObj.ListObjects
tableObj.TableStyle = “”
Next tableObj
Next wsObj
End Sub
Method 6: Clear Table Formats With Office Scripts
In Excel, scripts allow you to automate different actions that can then be replicated across different worksheets. To clear table formats via Office Scripts, simply follow these steps:
- Go to Automate > New Script
- Copy and paste the following code into the Code Editor:
function main(workbook: ExcelScript.Workbook) {
//get all tables in the workbook
let tbls = workbook.getTables();
//loop through each table in tbls and
//set the style to null
for (let i = 0; i <
tbls.length; i++) { tbls[i].setPredefinedTableStyle(null);
};
};
- Click on “Save Script”
- Select “Run” to clear all table formats
Method 6: Change to a New Table Style
Changing your table style is essentially a variation of Method 1, but rather than setting the new style to “None,” you’re replacing your current formatting with a different type of formatting that better fits your needs and preferences. To change to a new table style, just follow these steps:
- Select a cell within the table
- Go to Table Design > Table Styles
- Click on the “More” button at the right to see all styles
- Choose the style you like (hover over each style to see a preview)
Method 7: Create a Custom Table Style
Even if you don’t like the default table formatting, it doesn’t guarantee that you’ll like any of the built-in alternatives. So, if you want to create your own custom table formatting, you’ll need to follow these steps:
- Click on the Home tab
- Under Styles, select “Format as Table”
- Choose “New Table Style”
- Under Table Elements, choose an element you want to change and click “Format”
- In the Format Cells dialog box, change the formatting in the Font, Border, and Fill tabs
- (Optional) You can remove any current formatting by selecting an element and clicking “Clear”
- Click “OK” to save your new table style
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!