Vaibhav sends in a question about creating a particular pivot table. There are a number of challenges in this project. In Episode 840, you will see how to re-order items along a pivot field, how to remove subtotals from one field, how to arrange multiple date fields to go across the page, changing the headings from Sum of X to just X, and filling in blanks with zeroes.
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.
Today, a question sent in by Wybab.
Wybab sent in a data set and said we need to create a pivot table from this data set.
The data set has Month, a Debit/Credit field, Document Number, and then three numeric fields-- Service Tax, CES, and SHES.
And he wants the Doc numbers going down the left-hand side, and then the outermost column field is Month, then the Debit/Credit field, and then within that, the three data fields.
So this is a little bit tricky to create with a Pivot Table.
Basically, we'll come over here to our data set; use Data; PivotTable; click Next for the first two steps.
And I'm going to build it right here on the existing worksheet so I can, kind of, have a model to follow; click Finish.
Okay, so, we know that we want Doc number going down the left-hand side of the row fields-- that's the easy part.
And then the column fields, we're going to take the Month first, Debit/Credit next, and then our three data field-- Service Tax, CES, and SHES-- drop those in the data area.
Okay, now what do we have to do?
A couple of things.
First of all, every account knows Debit's on the left, Credit's on the right, and of course, it puts them alphabetically.
Well, it's very easy to reverse this-- it's amazing-- we can either drag and drop that, or I just come here and type the word "Debit", press ENTER, and Excel figures out what we're trying to do.
Never do that in regular Excel; it only works in Pivot Tables.
It actually reverses and puts Debit first and Credit last.
We do not want Monthly Total, so I'm going to double-click the Month heading and say "None", click OK.
Alright.
And then the big problem we have back in Excel 2003 is, when we have multiple fields in the data area, they always go down the left hand side; but we want these going across the top.
So I'm going to take the data heading and drag it here to the last column area.
And so now let's check.
We have our first Month, and then Debits, Service Tax, CES, SHES; Credit, Service Tax, CES, SHES; and then start with the next month.
So it's looking pretty good.
A couple of things I would still do to clean this up.
The first thing, I hate the fact that they use headings like Sum of Service Tax, so I choose each one of those, press the blue field Settings, and basically get rid of the word "Some Of".
But then we have to type a space at the end-- you can't call it Service Tax, we already have a field called Service Tax, but Service Tax space works just fine.
So I'll do that for each one of those fields-- space and then SHES, space.
And then the other thing-- and this just annoys me personally-- is how I really want zeros in here.
This is a fairly sparse data set, I don't want blanks in all my numeric columns, so I right-click on the Pivot Table and choose Table Options; and then say "For empty cells show" 0; click OK.
And there we go-- we have something that matches the model sent in by Wybab.
So, if possible with the Pivot Table, a little bit... some arcane things along the way-- reversing the Debit and Credit and getting those data fields to go across the top, instead of down the side.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Today, a question sent in by Wybab.
Wybab sent in a data set and said we need to create a pivot table from this data set.
The data set has Month, a Debit/Credit field, Document Number, and then three numeric fields-- Service Tax, CES, and SHES.
And he wants the Doc numbers going down the left-hand side, and then the outermost column field is Month, then the Debit/Credit field, and then within that, the three data fields.
So this is a little bit tricky to create with a Pivot Table.
Basically, we'll come over here to our data set; use Data; PivotTable; click Next for the first two steps.
And I'm going to build it right here on the existing worksheet so I can, kind of, have a model to follow; click Finish.
Okay, so, we know that we want Doc number going down the left-hand side of the row fields-- that's the easy part.
And then the column fields, we're going to take the Month first, Debit/Credit next, and then our three data field-- Service Tax, CES, and SHES-- drop those in the data area.
Okay, now what do we have to do?
A couple of things.
First of all, every account knows Debit's on the left, Credit's on the right, and of course, it puts them alphabetically.
Well, it's very easy to reverse this-- it's amazing-- we can either drag and drop that, or I just come here and type the word "Debit", press ENTER, and Excel figures out what we're trying to do.
Never do that in regular Excel; it only works in Pivot Tables.
It actually reverses and puts Debit first and Credit last.
We do not want Monthly Total, so I'm going to double-click the Month heading and say "None", click OK.
Alright.
And then the big problem we have back in Excel 2003 is, when we have multiple fields in the data area, they always go down the left hand side; but we want these going across the top.
So I'm going to take the data heading and drag it here to the last column area.
And so now let's check.
We have our first Month, and then Debits, Service Tax, CES, SHES; Credit, Service Tax, CES, SHES; and then start with the next month.
So it's looking pretty good.
A couple of things I would still do to clean this up.
The first thing, I hate the fact that they use headings like Sum of Service Tax, so I choose each one of those, press the blue field Settings, and basically get rid of the word "Some Of".
But then we have to type a space at the end-- you can't call it Service Tax, we already have a field called Service Tax, but Service Tax space works just fine.
So I'll do that for each one of those fields-- space and then SHES, space.
And then the other thing-- and this just annoys me personally-- is how I really want zeros in here.
This is a fairly sparse data set, I don't want blanks in all my numeric columns, so I right-click on the Pivot Table and choose Table Options; and then say "For empty cells show" 0; click OK.
And there we go-- we have something that matches the model sent in by Wybab.
So, if possible with the Pivot Table, a little bit... some arcane things along the way-- reversing the Debit and Credit and getting those data fields to go across the top, instead of down the side.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
