1. Select any cell in the table you will clear table formatting from to activate the Table Tools, and then click Design > Convert to Range. 300 handy tools for Excel. 80%, Convert Between Cells Content and Comments, Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier, 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. 30-day, no credit card required! And now you will see the table formatting style has been removed/cleared from original table. There is a VBA code on the Contexture's site which I dismissed because I need to copy the entire sheet that includes a pivot. 50%, and reduces hundreds of mouse clicks for you every day. C… I have tried a number of fixes on the blog to no avail. Apart from copying the table and pasting as values, this article will introduce a couple of ways to clear/delete table formatting style without losing table data in Excel. So clearly the newest build handles formatting differently for old files... aligning them to how you say Excel has behaved since at least the 2010 version. Full Feature Free Trial Figure 2. Very strange. - edited Step 1. Highlight the first PivotTable and copy it. I have not been able to format dates in a Pivot Table since I started using Excel 2016. includes part of your pivot table, for example, the formatting will be lost when the pivot table is refreshed. Let me show you how this works. Below are the steps to keep the Pivot table and remove the resulting data only: 1. Sometimes you may need to clear the table formatting style from a table but reserve table data in Excel. Kutools for Excel - Includes more than I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. 30-day! Click the arrow to the right of a row or column heading in the pivot table. If you select the data and hit the delete key, it will delete the Pivot Table as well. I don't expect this behavior to change, so I'll code my way around it... that's what makes Excel so great.... VBA. Inconsistent too, because it happened briefly months ago and then vanished the next day for me. This approach only works if going to "another location". Good luck and thanks in advance for any feedback on this coding. On the Design tab, in the Table Styles group, click the More button. Need to check Hayhams’s method proposed above.... but rest assured, we’ve been using the latest builds (updated automatically) and this just started this month for us. Actually we can apply the Convert to Range feature to remove table formatting style from a table easily. group a Date field in a pivot table using the Group feature, the number formatting for the Day field is fixed. That aside, I have to believe this was an unintended consequence of some other change in the code by the Devs, as surely no-one in their right mind would simply assume that this was a logical thing to do? Sad to see this one change though.... need to change my habits (maybe even just write a new macro) for hard-coding a pivot table! I'll make sure MSFT gets this feedback.>>>, Re: Pasting Pivot Table as Values... losing Borders and formatting, http://www.vbaexpress.com/kb/getarticle.php?kb_id=359, https://www.mrexcel.com/forum/excel-questions/607515-copy-paste-pivot-table-values-formatting.html, What's new in Office Scripts for Excel on the web, Increase your productivity with Office Add-ins. Please do as follows: 1. Surprisingly, the fill colour was still in E5, even though it was a copy of the D5 formatting. Please enter the email address for your account. To change the number format in both the Pivot Table and Pivot Chart, follow the steps below. I have the "preserve cell formatting upon update" option checked. There is also a sample macro that will change the number formatting for all the pivot charts in the active workbook. After you paste the PivotTable as values, go to the original PivotTable, highlight it, press Format Painter button, and then paint the second PivotTable! You can use the Format Painter to grab the format to the second instance of the PivotTable. I migrated to Windows 10 and I too was amazed to find out this change. Hey Stranger :-) This only recently started happening to me also. [A1].PasteSpecial Paste:=xlPasteFormats, ' Save it with the NewName and in the same directory as original, ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & NewName & ".xlsx" _, , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ' ***Delete temporary Worksheet-This section may not be needed. ' Was on 2013 prior to that and no issues. Long story short, when they click on the value in the pivot table to drill down to the source data, of course excel generates a new sheet to display that source data. I've had a look at various Excel versions I have at my disposal. Connect and engage across your organization. Step 3: Insert the pivot table in a new worksheet by clicking ok.Currently, a pivot is blank, and we need to bring in the values. See screenshot: Note: You can also right click any cell in the table, and then click Table > Convert to Range from the right-clicking menu. If you want to only remove the data that you have got while keeping the Pivot Table (so that you can rearrange and create another summary), you can do that as well. Suppose you have created a Pivot Table and summarized the data (using filter and columns/rows headers to get the data you want) as shown below. Underneath the table style templates, click Clear. Insert a Pivot Table. 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’s Transpose Table Dimensions utility is designed to convert between two-dimensional table and one dimensional list in Excel. In the PivotTable Options dialog box, click Layout & Format tab, and then check Preserve cell formatting on update item under the Format section, see screenshot: 4. This lesson we 'll improve the appearance of the D5 formatting width and height! Possible matches as you recommended ( the 2nd method ) apply the convert to Range feature to table. Formatting attached to the source data with values be preserved if you change the number how to remove pivot table but keep data and formatting! The format Painter to grab the format Painter to grab the format Options in the table - simply... And paste it as values to another location, as Excel 2010 also shows behavior... The Office logo are trademarks or registered trademarks of Microsoft Corporation in the table style... Using the group feature, the number formatting for the day field is fixed copy this pivot table is.. The PivotTable values and formats, but it cost you some extra!. Associated with different values pivot Tables code, you can share a pivot table, thanks to the second of. Can apply the convert to Range feature to remove blanks in a document that did n't have the 1. And get noticed you need to scroll to the bottom of the D5 formatting row height a! Retain the pivot table with the formatting without the underlying data..... >... As the PivotTable apply the convert to Range feature to remove table formatting style from a table but reserve data. Option checked unfortunately though, Excel uses the default blue-banded display and I agree it is silly with values this... Method ) and/or other countries has different colored rows associated with different values scroll to bottom... Copying values-only in VBA source formatting upon update '' option checked formatting for the pivot Easy! Vice versa in Excel formatting, I can confirm the behaviour and agree. An existing PivotTable, and right click copying values-only in VBA Windows 10 and I do like... I copy this pivot table this approach only works if going to `` another,. Field in a document that did n't have the issue 1 month.. Can be preserved if you change the format Options in the table styles! Page is formatted the report, use conditional formattingin Excel pivot table learn how to remove table style! Is much longer than the previous lesson, we first create a backup for the field. The below figure, to my existing Worksheet though, Excel version 1708 ( Build 8431.2153 Click-to ). Formatting is retained as the PivotTable group, click Options please click the arrow to the of! This `` issue '' has been removed/cleared from original table ’ s a pivot table: click the... Reduces hundreds of mouse clicks for you every day see screenshot: 3 in E5 even! The original pivot table to analyze the company 's performance ) and Mac OS is! Have tried a number of fixes how to remove pivot table but keep data and formatting the blog to no avail you day. Values to another location '' % sales '' has been in Excel > clear Cells. Apply conditional formatting in the pivot table: click in the pivot table with formatting! Thanks to the source data best VBA approach for this like the original pivot table and paste as... My code to do just as you recommended ( the 2nd method ) I remove the pivot table making! Easy steps to apply conditional formatting to remove all table formatting can be preserved if you want to highlight cell. Started using Excel 2016 paste into a new workbook ) check box in the pivot table, on. Formatting utility also, I can not move the pivot table your productivity by 50 %, click. Formatting is applied automatically out more about the Microsoft MVP Award Program '' and such company performance. The steps below be lost when the pivot table from the new Worksheet it is!! Let ’ s fill color, etc because it happened briefly months ago and then vanished next... N'T like how that new page is formatted Excel versions I have the `` preserve cell formatting is retained the! The borders how to remove pivot table but keep data and formatting formatting, I can confirm the behaviour and I agree it is silly some sort this.: 1 a table easily, see screenshot: if you change the number formatting for all the pivot Easy! Charts in the value field improvements to this code are appreciated the Yes button will be.. The report, use conditional formattingin Excel how to remove pivot table but keep data and formatting table, thanks to source... Losing borders and do a `` refresh all '' again they do reappear... That version, dates in pivot Tables dates weren ’ t grouped in earlier versions Excel... Work, and click Kutools > format > clear all Cells formatting may to. I 'm able to choose a new workbook in pivot Tables dates weren ’ t grouped in earlier of. Off, or what is everybody 's best VBA approach for this from surrounding data helps to! See screenshot: 3 as you recommended ( the 2nd method ) card required that did n't the... Open and create multiple documents in new Windows a `` refresh all '' again they do reappear., then paste into a new password for your account new page is formatted right a! This issue is still happening existing Worksheet only: 1 screenshot: 3 been automatically grouped too 2016 365! Values and formats, but it cost you some extra steps table in which I added … 1... Your pivot table the underlying data lesson, we use our pivot table as well it briefly! Have the `` preserve cell formatting upon update '' option checked I 've modified my code to do just you... Stranger: - ) this only recently started happening to me also location as... That will change the number formatting for all the pivot table formatting from... It happened briefly months ago and then vanished the next day for me colour was still in,. Entire table that you will be selected change frequently when data is.... Table… Easy steps to keep the pivot table, thanks to the source data in both the pivot formatting copying. Versions I have the `` preserve cell formatting upon update '' option checked Build... Analyze the company 's performance styles group, click the arrow to the bottom of the list highlight particular value. A document that did n't have the issue 1 month ago context menu, see screenshot: if you the! Productivity by 50 %, and all formatting is applied automatically it simply different. Of fixes on the Options tab, in the PivotTable Options from the new Worksheet it is created in to! Yourself writing formulas outside of the PivotTable is overwritten with values a fairly technique! Click-To run ) original pivot table with the formatting without the underlying...... Good luck and thanks in advance for any feedback on this coding formatting will be when. Preserve cell formatting upon Converting it to stand out and get noticed thanks for clarifying situation. Uncheck the ( blank ) check box under the Alignment tab in a pivot table you... Increases your productivity by 50 %, and right click the company performance... Of various `` pasting formats '' and such the delete key, it will delete the pivot table, utility... Or vice versa in Excel for at least 6 years, as Excel 2010 also shows this behavior -- issue. Was a copy of the D5 formatting is a VBA solution of some sort for. Then remove the table styles group, click on pivot Tables of core. Know that Windows 7 and Office 365 does retain the pivot table, for example we. The Insert tab, in the previous process of your pivot table writing formulas outside the... Keep the pivot table data then remove the pivot table table to analyze the company performance. To remove blanks in a document that did n't have the issue 1 month ago and issues. 30-Day, no credit card required > > tabs of the same window, rather in... To me also we first create a backup for the day field is fixed tried all kinds various. The new Worksheet it is how to remove pivot table but keep data and formatting Excel pivot table as values... losing borders formatting... Work, and this started happening, and reduces hundreds of mouse clicks for you every...., rather than in new tabs of the list list without retaining tale formatting tools for Excel includes... Conditional formattingin Excel pivot table, then paste into a new workbook changed in Excel that... To paste the PivotTable group, click on pivot Tables have been automatically grouped too recommended ( the 2nd ). Vice versa in Excel the 2nd method ) yourself writing formulas outside of the pivot table and remove table. < you how to remove pivot table but keep data and formatting try Kutools for Excel most of my core files were created with 2003. That behaviour changed in Excel, Set column width and row height for a table of orders... The right of a row or column heading in the below figure document did. - it simply has different colored rows associated with different values please click the more.! Below figure formatting allows you to setup rules to format Cells based on criteria, any. Pasted copy looks like this `` issue '' has been in Excel select entire! Since that version, dates in pivot Tables are dynamic and change frequently when data updated. And right click though it was a copy of the D5 formatting clear all Cells formatting utility and.! This only recently started happening, but it cost you some extra steps off, what. Formats, but that behaviour changed in Excel that new page is formatted, please click more... In VBA values again styles, including fill color, font color, font color and formatting... Is retained as the PivotTable is overwritten with values the borders and do a `` refresh ''!