{"id":5544,"date":"2021-02-09T16:42:17","date_gmt":"2021-02-09T16:42:17","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=5544"},"modified":"2024-03-28T13:33:05","modified_gmt":"2024-03-28T13:33:05","slug":"remove-table-formatting","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/","title":{"rendered":"Remove Table Formatting in Excel"},"content":{"rendered":"\n
Remove-Table-Formatting-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

If you create a table from data, Excel will automatically apply a default formatting to it.<\/p>\n\n\n\n

Let’s see how it works in practice.<\/p>\n\n\n\n

We have a list of people’s names, cities, etc.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you click any cell inside this data and create a table (Ctrl + T), Excel will create a table with the default formatting.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Clear Formatting using the Home Tab<\/h2>\n\n\n\n

Select all cells in the table. You can do it by clicking a header cell and pressing Ctrl + A. You can also click any cell inside a table (but header cells) and press Ctrl + A twice. If you press the shortcut once, Excel won’t select header cells.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

 Navigate to Home >> Editing >> Clear<\/strong> and click Clear Format<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

All that, the table formatting is gone but the functionality of the table is still there.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can’t clear formatting for the part of a table. If you don’t select all the cells and click Clear Formats<\/strong>, nothing changes, the formatting will stay as it was before.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Clear Formatting using the Table Design Tab<\/h2>\n\n\n\n

Another, yet similar way to remove formatting from the table is to use the Table Design<\/strong> Tab. This is the special tab that appears when you click a cell inside a table<\/a>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Click this tab, and then under Table Styles<\/strong>, select the More<\/strong> button.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you move to the very bottom of the expanded window, there is the Clear<\/strong> button.<\/p>\n\n\n\n

Click it to remove the formatting. The difference between this method and the last one is that, here, you don’t have to select all the cells. Excel knows that this is a table and you want to apply this to all these cells.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Clear Formatting by Selecting a Different Style<\/h2>\n\n\n\n

If you navigate to Table Styles<\/strong> and click the More<\/strong> button, you’ll notice that one formatting is different than the others.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Hover a mouse cursor over it. It says None<\/strong>. If you click it, it will act the same as the Clear<\/strong> button from the previous step.<\/p>\n\n\n\n

Convert Table to Range<\/h2>\n\n\n\n

It’s very easy to convert Excel data to a table, but it’s also easy to revert the process and convert a table back to the range.<\/p>\n\n\n\n

To do it, click the table and go to Table Design >> Tools >> Convert to Range<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After you click the button, you’ll notice that the filtering buttons disappeared from the table headers.  This means that the table was successfully converted to the range.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Remove Formatting from the Part of the Range<\/h2>\n\n\n\n

You can\u2019t remove formatting from the part of the table using the Clear Formats<\/strong> button, but you can do it if you convert it to the range.<\/p>\n\n\n\n

Select cells from C1<\/strong> to D11<\/strong> and navigate to Home >> Editing >> Clear >> Clear Formats<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you convert this range to a table, it will format the whole table again.<\/p>\n\n\n\n

Remove Formatting using the Format Painter<\/h2>\n\n\n\n

Excel offers a feature called Format Painter<\/strong>. It applies formatting from the selected cell<\/a> to the cells that you choose.<\/p>\n\n\n\n

To replicate the last example using this tool, first, select a cell outside the range, where no formatting is applied (for example, E13<\/strong>), and navigate to Home >> Clipboard<\/strong> and choose Format Painter<\/strong>.<\/p>\n\n\n\n

Now, drag cells from C1<\/strong> to D11<\/strong> to apply new formatting. Because you selected a cell with no formatting, the new format is like removing all formatting from cells.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Create a Macro and Apply Shortcut<\/h2>\n\n\n\n

You can remove formatting using the VBA code. If you are not an expert in VBA, don’t worry a macro recorder will help you with that.<\/p>\n\n\n\n

Click a cell inside the table. In the bottom-left part of the screen, there is a button to record a macro.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you cant see it, you can navigate to View >> Macros >> Macros >> Record Macro<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

A new window will appear. Name your Macro and apply a shortcut key if you want. I’ll be using Ctrl + Shift + R<\/strong> as a shortcut to this Macro.<\/p>\n\n\n\n

If you also want to apply this shortcut, click the little white box under the Shortcut Key<\/strong> and press Shift + R<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Immediately after you click the OK<\/strong> button, the recorder starts to record your every move. Select Table Design >> Table Styles >> More >> Clear<\/strong>.<\/p>\n\n\n\n

Now, after the formatting is removed from the table, you can stop the recording by clicking the bottom-left corner.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Let’s see how our macro looks like.<\/p>\n\n\n\n

Go to View >> Macros<\/strong> and click the View Macros<\/strong> button (Alt + F8<\/strong>).<\/p>\n\n\n\n

From the list, select the macro and click Edit<\/strong>.<\/p>\n\n\n\n

Sub RemoveFormatting()\n'\n' RemoveFormatting Macro\n'\n' Keyboard Shortcut: Ctrl+Shift+R\n'\n    ActiveSheet.ListObjects(\"Table4\").TableStyle = \"\"\nEnd Sub<\/code><\/pre>\n\n\n\n
The code that removes formatting is just a single line. it assigns table style for a table (Table4). There is also shortcut information, you created (Ctrl + Shift + R<\/strong>). It’s just a comment, so you can delete it.<\/p>\n\n\n\n
The code shouldn’t work just for a table with this particular name, but for the one that is currently selected.<\/p>\n\n\n\n
Modify the code so it looks like this:<\/p>\n\n\n\n
Sub RemoveFormatting()\n    ActiveCell.ListObject.TableStyle = \"\"\nEnd Sub\n<\/code><\/pre>\n\n\n\n
Now, if you run the macro or use the keyboard shortcut you created, Excel will instantly clear filters<\/a> for the selected table.<\/p>\n","protected":false},"excerpt":{"rendered":"
If you create a table from data, Excel will automatically apply a default formatting to it. Let’s see how it works in practice….<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nRemove Table Formatting in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to remove table formatting in Excel using a simple Home Tab function. Clear all formatting without affecting the functionality of the table.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Remove Table Formatting in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to remove table formatting in Excel using a simple Home Tab function. Clear all formatting without affecting the functionality of the table.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-02-09T16:42:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T13:33:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Remove Table Formatting in Excel\",\"datePublished\":\"2021-02-09T16:42:17+00:00\",\"dateModified\":\"2024-03-28T13:33:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\"},\"wordCount\":879,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\",\"name\":\"Remove Table Formatting in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png\",\"datePublished\":\"2021-02-09T16:42:17+00:00\",\"dateModified\":\"2024-03-28T13:33:05+00:00\",\"description\":\"Learn how to remove table formatting in Excel using a simple Home Tab function. Clear all formatting without affecting the functionality of the table.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png\",\"width\":490,\"height\":260},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Remove Table Formatting in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Remove Table Formatting in Excel","description":"Learn how to remove table formatting in Excel using a simple Home Tab function. Clear all formatting without affecting the functionality of the table.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/","og_locale":"en_US","og_type":"article","og_title":"Remove Table Formatting in Excel","og_description":"Learn how to remove table formatting in Excel using a simple Home Tab function. Clear all formatting without affecting the functionality of the table.","og_url":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/","article_published_time":"2021-02-09T16:42:17+00:00","article_modified_time":"2024-03-28T13:33:05+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Remove Table Formatting in Excel","datePublished":"2021-02-09T16:42:17+00:00","dateModified":"2024-03-28T13:33:05+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/"},"wordCount":879,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/","url":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/","name":"Remove Table Formatting in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png","datePublished":"2021-02-09T16:42:17+00:00","dateModified":"2024-03-28T13:33:05+00:00","description":"Learn how to remove table formatting in Excel using a simple Home Tab function. Clear all formatting without affecting the functionality of the table.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/data.png","width":490,"height":260},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/remove-table-formatting\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Remove Table Formatting in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5544"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=5544"}],"version-history":[{"count":6,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5544\/revisions"}],"predecessor-version":[{"id":14287,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5544\/revisions\/14287"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=5544"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=5544"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=5544"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}