how to unformat a table in excel

How to clear formatting in Excel

Clear Table Format Place cursor in any cell of the table Go to Design tab > In Table Styles section click on the More drop-down arrow. Oct 19,  · Data Prep: Unformat your Formatted Tables Step 1: Unmerge Cells in Row and Column Headings. The first step is to unmerge all merged cells in the table. For this Step 2: Rearrange Table. In the above image, columns B and D contain totals that are calculated from values in 5/5(14).

Sometimes you may need to clear the table formatting style from a table but reserve table data in Excel. Actually we can apply the Convert to Range feature to remove table formatting style from a table easily. Please do as follows:. See screenshot:. In the popping up Microsoft Excel dialog box, please click the Yes button. If you need to remove all table formatting styles, including fill color, font color, etc.

Kutools for Excel - Includes more than handy tools for Excel. Full feature free trial day, no credit card required! Get It Now. Now you will see all table formatting styles, including the fill color and font color are removed from original table as below screenshot shown:. For a table, this utility will convert it to a 3-columns list without retaining tale formatting.

Full Feature Free Trial day! Convert range to table or vice versa in Excel. Set column width and row height for a range in Excel. Note: The other languages of the website are Google-translated. Back to English. Log in. Remember Me Forgot your password? Password Reset. Please enter the email address for your account. A verification code will be sent to you. Once you have received what does the elephant and donkey mean in politics verification code, you will be able to choose a new password for your account.

About Us Our team. How to clear table formatting style without losing table data in Excel? Clear table formatting style without losing table data by Convert to Range feature Clear table formatting style without losing table data by an amazing tool Clear table formatting style without losing table data by Convert to Range feature Actually we can apply the Convert to Range feature to remove table formatting style from a table easily.

Please do as follows: 1. See screenshot: 2. Get It Now 1. Now you will see all table formatting styles, including the fill color and font color are removed from original table as below screenshot shown: How to unformat a table in excel for Excel - Includes more than handy tools for Excel.

Kutools for Excel includes more than handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Convert range to table or vice versa in Excel Set column width and row height for a range in Excel. Read More Free Download You are guest Login Now. Loading comment The comment will be refreshed after

Need more help?

1. Select the entire table that you will remove the table formatting style from, and click Kutools > Format > Clear All Cells Formatting. Now you will see all table formatting styles, including the fill color and font color are removed from original table as below screenshot shown: Kutools for Excel - Includes more than handy tools for Excel. Full feature free trial day, no credit card required! Mar 16,  · If you want to keep all features of an Excel table and remove only the formatting such as banded rows, shading and borders, you can clear the table format in this way: Select any cell in the table. On the Design tab, in the Table Styles group, click the More button. Underneath the table style templates, click datingyougirl.com: Svetlana Cheusheva. Jun 24,  · First, select the entire table. After this press on to the Home tab and in the Editing section of Home tab look for the Clear option. After selecting the Clear option, you will get a drop-down list. From there, select the Clear Formats option.

Tables come in many styles and formats. For reporting purposes, the better a table is formatted, the easier it is to read. However, formatting is often our enemy when we attempt to use the data for analysis, database, and data integration purposes.

In this blog post, we will demonstrate how to unformat a table and turn it into a backend data table that is ready for other uses. Below is a perfect example of a well-formatted table. This table is a truncated version of a larger table from the source cited. For the purpose of data preparation, our goal is to convert this into a table with single column headings and data, but no row headings or subheadings. The first step is to unmerge all merged cells in the table.

For this table, there are merged cells in the column header, and in each taupe colored bar designating the nutrient groups.

In the above image, columns B and D contain totals that are calculated from values in other columns. These columns can be deleted because the totals can always be obtained again by recalculating. Also, add a new field called Nutrient, in column G, in preparation for eliminating the Nutrient subheadings.

Populate the Nutrient field with the appropriate text. The rearranged table should now look like this:. After deleting the taupe shaded rows with the Nutrient subheadings, the table should look like this:. As a result, the header descriptions will become values of a location field.

The 5 columns will become 2 columns: Location and Intake Amount. To prepare the table for this transformation, we will add Location columns and populate them with the location from the adjacent header, as shown in the green highlighted cells below. All we need to do is transform it a bit more and rearrange the columns.

Once done, it looks like the image below. Our truncated table that contained 20 rows of data in 8 columns now contains 75 rows of data in 4 columns. Now that the data is extracted and in usable format, you can convert it into a table. While in any cell within the table, press Ctrl A to select the whole table. If the above steps seem tedious and time consuming, there is a better way, especially if you need to transform the same type of data repeatedly.

Let us help you design and develop a rock-solid solution for your firm. Contact our team to schedule a free consultation by calling or visit our website at ExcelHelp.

Microsoft and Microsoft Excel are registered trademarks of Microsoft corporation. Google Rating. Based on 14 reviews. ESoftware Associates, Inc 5.