Look Who it Was Random video

Excel - Pivot Table Formatting: How to Make Your Formats Stick - Episode 865

1,550 views · Published 6 January 2009 · 3:51 · Indexed 10 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Pivot Table Formatting: How to Make Your Formats Stick.

Welcome back to the MrExcel netcast, where we tackle all your Excel questions and provide helpful tips and tricks. In this episode, we'll be discussing Pivot Table formatting and how to make those formats stick even after refreshing your data. This is a common issue that many Excel users face, and we're here to provide some solutions.

We received a call from Taylor in Orange County, California, who asked about formatting in Pivot Tables and how to make it stick. This can be a frustrating problem, but we have some tips that can help. One solution is to go to the Sum of Revenue field and click the blue "I" button, also known as the Field Settings button. From there, go to the Number tab and change the format to currency. This will ensure that the formatting sticks through future updates.

Another helpful tip is to add a space at the end of your headings in the Pivot Table. For example, you can change the heading from "Sum of Revenue" to "Revenue space" and the formatting will stick. This also applies to changing the format of subtotal rows. However, if you use one of the 14 Auto Formats provided by Excel, those settings will persist. Keep in mind that these formats may not be the most aesthetically pleasing, but they do the job.

If you're using Excel 2003, you can press Ctrl+L or Ctrl+T to add more data to the bottom of your data set. This will save you from having to re-specify your data in the Pivot Table Wizard. In Excel 2007, you have even more control with the ability to define your own style in the Pivot Table styles. This allows you to customize the format of your Pivot Table and have those settings persist through future refreshes.

We understand the frustration of losing formatting every time you refresh your Pivot Table. By using the built-in formats and the number format, you can have more control over your formatting. However, keep in mind that some formats may still be lost during the refresh. Thank you for tuning in to this episode of the MrExcel netcast. We hope these tips will help you with your Pivot Table formatting. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Taylor from Orange County California calls in to ask how you can get the formatting of a pivot table to stick? Every time that you refresh a pivot table, the formatting reverts back to a default. The bad news is that you can not make all formatting stick, but Episode 865 will show you a couple of methods to use.

Table of Contents:
(00:00) Introduction
(00:17) Formatting in Pivot Tables
(00:27) Tip for Persisting Formatting
(00:51) Changing Subtotal Row Formats
(01:53) Another Tip for Adding Data
(02:50) More Control in Excel 2007
(03:13) Frustrating Problem with Refreshing Pivot Tables
(03:29) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 




This video answers these common search terms:
Excel 2013 attitude messages
Excel 2013 circular references
Excel 2013 contest
Excel 2013 dual monitors
Excel 2013 macro writing
Excel 2013 new features
Excel 2013 pivot table
Excel 2013 ribbon customization
Excel 2013 SDI (Singular Document Interface)
Excel 2013 shortcuts
Excel 2013 workarounds


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153406/

This video answers these common search terms:
Adding more data to Pivot Table
Changing number format in Pivot Table
Controlling number formats in Pivot Table
Excel 2003 Auto Formats for Pivot Table
Formatting in Pivot Table
Formatting Pivot Table subtotal rows
Losing formatting when refreshing Pivot Table
Persisting formatting in Pivot Table
Refreshing Pivot Table with added data
Specifying format for Pivot Table
Sticking formatting in Pivot Table
Template for future Pivot Tables


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1151989/

More from this channel