Look Who it Was Random video

Excel - Master Pivot Tables in Excel: Customize Layout and Solve Annoyances! - Episode 707

1,419 views · Published 18 February 2009 · 2:31 · Indexed 4 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Pivot Tables in Excel: Customize Layout and Solve Annoyances!

Welcome back to the MrExcel netcast! In this video, we will continue our exploration of pivot tables in Excel 2007. Last time, we learned how to create a pivot table by going to "Insert" and then "Pivot Table". Today, we will focus on changing the layout of our pivot table to meet the needs of our manager.

Let's say we have already created a pivot table with the fields for region, product, and revenue. However, our manager has requested a different layout for the column and row labels. No problem! We can easily rearrange the labels by dragging and dropping them in the pivot table field list. This allows us to customize the layout of our pivot table to better suit our needs.

But what if our manager wants to see customer data as well? Again, we have a few options. We can simply add the customer field to the pivot table, which will automatically place it in the row labels. However, if we want to see the first customer and the products they bought, we can easily move the customer heading before the word "product" in the pivot table field list. This gives us a more detailed view of the data, with each customer and their corresponding products listed.

While working with pivot tables, you may have noticed that some cells are left blank instead of showing a 0. This can be frustrating, but luckily we can change this setting. By right-clicking inside the pivot table and going to "Pivot Table Options", we can select the "Label and Format" tab and choose to show a 0 for empty cells. This will fill in all the blank cells with 0, making it easier to read and analyze the data.

As you can see, pivot tables are a powerful tool for organizing and analyzing data in Excel. And with the ability to easily rearrange and customize the layout, you can create a pivot table that meets the specific needs of your manager or project. Thanks for watching this netcast from MrExcel, and be sure to check out our other videos 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/


To rearrange a pivot table in Excel 2007, simply drag fields to a new drop zone in the pivot table field list. Episode 707 shows you how.

Table of Contents:
(00:00) Introduction to pivot tables
(00:18) Creating a pivot table in Excel 2007
(00:28) Rearranging column and row labels
(00:39) Adding customer data to the pivot table
(01:11) Moving the customer heading before the product heading
(01:24) Annoyances with pivot tables
(01:34) Changing blank cells to show 0
(02:01) Rearranging pivot tables by dragging headings
(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:
Customer data
Drag headings in pivot table
Empty cells in pivot table
For empty cells show 0
Insert pivot table
Label and format tab
Pivot table
Pivot table field list
Pivot table options
Rearrange column labels
Rearrange row labels


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

More from this channel