Martin Liss
asked on
Please help with my first pivot table
In the attached workbook that I'm developing for a therapist friend, I have some income and expenses data in the "Line Items" sheet and my first attempt at a pivot table is in the "Report" sheet. Currently for simplicity sake I've only included the Income column and some of the expense columns.
Can I somehow change the pivot table's column "B" heading from "Sum of Income" to just "Income", and can I get a "Week Of 06 Nov" totals line similar to the "Nov Total" line between rows 11 and 12 (because they are different weeks) and a similar "Week Of 13 Nov" totals line between rows 14 and 15? Note that I have a "Week Of" value calculated on the "Line Items" sheet.
Pivot.xlsm
Can I somehow change the pivot table's column "B" heading from "Sum of Income" to just "Income", and can I get a "Week Of 06 Nov" totals line similar to the "Nov Total" line between rows 11 and 12 (because they are different weeks) and a similar "Week Of 13 Nov" totals line between rows 14 and 15? Note that I have a "Week Of" value calculated on the "Line Items" sheet.
Pivot.xlsm
I forgot, you might need to ungroup the dates first, then group by weeks followed by Months & Years
Hi Martin,
Welcome to the world of Pivot Tables!!! They are very powerful when you get to grips with them.
Cheers
Rob
Welcome to the world of Pivot Tables!!! They are very powerful when you get to grips with them.
Cheers
Rob
ASKER
Roy please let me know if I'm misunderstanding you, but wouldn't there would need to be a "Weeks" option in Grouping to do what you suggested? I did find however that in my Row Labels if I included my calculated "Week Of" value, and made the order "Years", "Months", "Week Of", "Date" it does what I want.
And adding a blank before the name is a nice trick.
And adding a blank before the name is a nice trick.
Hi Martin, there isn't a weeks option like months and years, you have to group days in a seven day cycle using the dialog I have displayed. There's not much data in the example but if you want to let me have a more extensive example I'll have a go at setting it up for you.
ASKER
That doesn't work. It will let me Group Days 7 and then choose Months and Years, but after clicking OK there's no change in the pivot table and when I go back to Group, the number of days is back to 1.
To summarise by weeks, add a column to your data which rounds up a date to the week ending date:
=CEILING(Date,7) rounds up to the following Saturday.
Then use that column as a Column Header in the Pivot rather than specific transaction date column.
=CEILING(Date,7) rounds up to the following Saturday.
Then use that column as a Column Header in the Pivot rather than specific transaction date column.
ASKER
Rob, thanks but I want to see the week in the first column just like month and year, rather than as a column. In my post above I mentioned that I found a workaround, and I'm now just trying to find out if there's a way to do it with Grouping.
Seems like grouping by weeks using what I suggested is cancelled when you group by Months. I did forget to say that you need to set the nu,ber of days to seven
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Roy. I also like your Itemized Sales sheet headings.
Glad it helped Martin. PivotTables are one of Excel's best reporting features in my opinion.
Here's another suggestion. When I use a PivotTable I think they look much better if I hide the worksheet's headings and Gridlines. They then stand out on a clean, white sheet.
Here's another suggestion. When I use a PivotTable I think they look much better if I hide the worksheet's headings and Gridlines. They then stand out on a clean, white sheet.
To group by weeks :
Right-click on one of the dates in the pivot table.
In the popup menu, click Group.
In the Grouping dialog box, select Days from the 'By' list.
For 'Number of days', select 7.
The week range is determined by the date in the 'Starting at' box, so adjust this if required.
Click OK