Look Who it Was Random video

Excel - Mastering Pivot Table Formatting: Keep Your Excel Worksheets Consistent - Episode 887

1,363 views · Published 5 January 2009 · 2:33 · Indexed 22 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Keeping Pivot Table Formats Consistent.

Welcome back to the MrExcel netcast, where we share tips and tricks to help you become an Excel pro. In today's episode, we have a great tip sent in by Nancy that will help you keep your Pivot Table formats consistent. If you've ever been frustrated by your Pivot Table automatically reformatting itself, then this tip is for you.

In a previous episode, I talked about how irritating it is when the Pivot Table changes the column widths to fit longer values. But thanks to Nancy's tip, we now have a solution to this problem. By unchecking the "AutoFormat table" option in the PivotTable Options, your column widths will remain the same even when you choose a longer value from the page field. This means you can set your column widths to your desired size and they will stay that way, no matter what.

But that's not all, there's another annoying issue that Nancy's tip can help with. Have you ever had empty cells in your Pivot Table that you want to show as zeros? By going to the Table Options and selecting "For empty cells, show" 0, you can easily fill those cells with zeros. This is a great way to make your Pivot Table look more organized and complete.

For those of you using Excel 2007, don't worry, this tip still applies to you. The options may be named differently, but the concept remains the same. Simply right-click on the Pivot Table and choose Pivot Table Options, or click the options button at the top. Then, on the Layout & Format tab, uncheck "Autofit column widths on update" to prevent your column widths from changing when you update the page field. 

I want to give a big thank you to Nancy for sending in this great solution to a common problem. As a token of our appreciation, we will be sending her one of our Excel Master Pins. And to all of our viewers, thank you for tuning in. We hope this tip helps you in your Excel journey. Don't forget to subscribe to our channel for more helpful netcasts from MrExcel. 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/


Nancy sends in a cool tip to solve some of the frustrations from Episode 875 -- Excel changing the column widths of a pivot table as you choose new values from a page field. In Episode 887, I will take a look at how to solve this problem.

Table of Contents:
(00:00) Introduction
(00:19) Pivot Table formatting issue
(00:29) Solution: uncheck "AutoFormat table"
(00:48) Another common issue
(00:58) Solution: "For empty cells, show" 0
(01:25) Excel 2007 options
(01:41) Thanking Nancy
(02:11) 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:
Autofit column widths on update in Excel 2007
Disable AutoFormat table in Pivot Table
Excel 2007 PivotTable Options
Excel PivotTable Options
Keeping original column width in Pivot Table
Layout & Format options in Pivot Table
Pivot Table column width formatting
Prevent Pivot Table column width changes
Set column width in Pivot Table
Showing zeros for empty cells in Pivot Table
Solution for Pivot Table formatting issue
Stop Pivot Table from reformatting


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

More from this channel