Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Please help with my first pivot table

Posted on 2016-11-09
12
43 Views
Last Modified: 2016-11-11
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
0
Comment
Question by:Martin Liss
  • 6
  • 4
  • 2
12 Comments
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41881663
You can simply select the words Sum of in the title and delete. If you get an error message saying that the field already exists simply add a space before Income. Or in the Design Wizard for the PivotTable in the Values box you can click the drop down on the Sum of Income and change the title there.

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

Screenshot-2016-11-10-06.39.14.png
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41881672
I forgot, you might need to ungroup the dates first, then group by weeks followed by Months & Years
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41881927
Hi Martin,

Welcome to the world of Pivot Tables!!! They are very powerful when you get to grips with them.

Cheers
Rob
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 46

Author Comment

by:Martin Liss
ID: 41882222
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.
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41882472
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.
0
 
LVL 46

Author Comment

by:Martin Liss
ID: 41882502
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41882511
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.
0
 
LVL 46

Author Comment

by:Martin Liss
ID: 41882534
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.
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41882561
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
0
 
LVL 18

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41883458
I knew I had done this before. Basically I have added a helper column using the formula =TEXT(D2,"mmmm"). Then grouped the invoice dates by days. Note I stated from 04/04/16 because this is a Monday. Then added the Month above the Invoice date in the Rows.

I see that you have code to refresh the PivotTable. I usually pu that code into the WorkSheet activate event
PivotTable-Grouped-by-Weeks-and-Mon.xlsx
0
 
LVL 46

Author Closing Comment

by:Martin Liss
ID: 41883886
Thanks Roy. I also like your Itemized Sales sheet headings.
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41883890
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.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question