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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen We're taking a look at pivot tables this week in excel 2007 again to create a pivot table.
It's very easy go to "Insert" "Pivot Table" click ok and then just choose the fields you want.
So yesterday we built something I think it was region product and revenue.
I want to take a look at now we printed this out for our manager, the manager asks for something different.
The first thing they've asked for possibly is.
Hey I want to rearrange the column labels and the row labels.
Well, that's easy to do. We just grab the field down here in the pivot table field list and drag the heading to a new spot. So if you need to rearrange two columns here they are in great shape.
Now let's say that the manager wants to see the customer data as well.
Well we have a couple of options for this.
If I just choose the customer field, It's automatically going to go to the row labels and what we're going to see is the first product and the customers who bought that product.
But we might want to see the first customer and the products they bought and that's easy enough to do.
In the pivot table field list we're just going to take the customer heading and move it before the word product.
Now you'll see we have the first customer and the products they bought.
Next customer and the products they bought and so on. While we have this view of the data, there's one thing that's very annoying to me about pivot tables.
This cell.
I believe that that cell should have a 0 and not be blank.
They make it blank to say that hey, there were no records where ABC in the central region was sold to that customer.
But I would prefer to have zeros and we can control that.
Basically just want to right-click inside the pivot table and choose "Pivot Table Options".
On the "Label and Format" tab there's a saying that says "For Empty cells show" 0.
Click OK.
And you'll see that that fills in all of the cells within the data area with zeroes, when there were no sales in that particular area so as you need to rearrange a pivot table, you can simply drag headings from place to place to place within the pivot table.
Well hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
It's very easy go to "Insert" "Pivot Table" click ok and then just choose the fields you want.
So yesterday we built something I think it was region product and revenue.
I want to take a look at now we printed this out for our manager, the manager asks for something different.
The first thing they've asked for possibly is.
Hey I want to rearrange the column labels and the row labels.
Well, that's easy to do. We just grab the field down here in the pivot table field list and drag the heading to a new spot. So if you need to rearrange two columns here they are in great shape.
Now let's say that the manager wants to see the customer data as well.
Well we have a couple of options for this.
If I just choose the customer field, It's automatically going to go to the row labels and what we're going to see is the first product and the customers who bought that product.
But we might want to see the first customer and the products they bought and that's easy enough to do.
In the pivot table field list we're just going to take the customer heading and move it before the word product.
Now you'll see we have the first customer and the products they bought.
Next customer and the products they bought and so on. While we have this view of the data, there's one thing that's very annoying to me about pivot tables.
This cell.
I believe that that cell should have a 0 and not be blank.
They make it blank to say that hey, there were no records where ABC in the central region was sold to that customer.
But I would prefer to have zeros and we can control that.
Basically just want to right-click inside the pivot table and choose "Pivot Table Options".
On the "Label and Format" tab there's a saying that says "For Empty cells show" 0.
Click OK.
And you'll see that that fills in all of the cells within the data area with zeroes, when there were no sales in that particular area so as you need to rearrange a pivot table, you can simply drag headings from place to place to place within the pivot table.
Well hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
