Log In
Register
Home
August 25, 2017
Offline
Is there a way to not show count for each column in the spread sheet. I did not want B6:D23 to show "1". Basically, do not want the "policy, document name, and owner" counts to show individually. Only show the Department and Grand Total.
Also, would like to create the timeline calendar.
Can you help me?
Thanks
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Melinda
I am assuming you only wanted to display the rows that your colored.
1) Select cell A5 of your Pivot Table (Adm)
2) Select Pivottable Tools - Option - Collapse Entire Field
Hope this is what you are looking for.
As for the Timeline, unable to help as I am using Excel 2010.
Sunny
Answers Post
VIP
Trusted Members
June 25, 2016
Offline
Hi Melinda
As for the Timeline, you just need to select any cell in your PT and click Pivottable Tools - Analyze - Insert Timeline
The Timeline should be connected to that PT.
To check the PT connection, right-click on the Timeline and select Report Connections.
You should be able to view all PT connected to the Timeline.
Hope this helps.
Sunny
August 25, 2017
Offline
Hey Sunny
The timeline works perfect!!
I really like the idea of collapsing the data. So much cleaner.
What I am trying to do is, Column B6, B7, B8, C6, C7, C8 all have "1" in the column. I understand why, but I do not want the "1's" to show through out the document except for the Department rows. Now, my work around is to change the font color to "white", which is tedious because of the size of the document. Is there a more efficient way in excel to do so?
Thanks
VIP
Trusted Members
June 25, 2016
Offline
Hi Melinda
See if this is what you had in mind.
If you set the report to tabular format (easier to see) you can see the subtotals.
Just unselect the subtotals for the Policy Number and Document Name.
For the Owner, you will need to do a conditional formatting. Here I am assuming you will hide values not equal to 0.
Select cell E5 and select conditional formatting. It will affect the rest of the PT.
You can see the conditions that I had set by selecting cell E5 and then select Conditional Formatting - Manage Rules
You could have also apply CF to the subtotals that I had unselected earlier but unselecting them is easier.
You can also refer here for more on CF on a PT :
https://www.myonlinetraininghu.....ivottables
Hope this help.
Sunny
August 25, 2017
Offline
Sunny
I am a little stuck with my file. On the Recommendation tab, I would like to count by the type of recommendations.
Ientered the countif formula Row 4, Row 27, and Row 58 before collapsing.
Can you tell me how I would go about entering the formula, so that correct data is dispayed?
VIP
Trusted Members
June 25, 2016
Offline
Hi Melinda
Maybe you could do the COUNTIFS on the raw data instead of the PT. The formula will be very long and difficult to read.
If you want to use this method, I would recommend that you temporarily rename the worksheets to a shorter name such as A, B, C etc.
This will make the formula shorter and easier to read. Once everything is OK, you can then rename them back to their original name.
Your biggest problem now is the RECOMMENDATION column's data in the Expired Policies worksheet.
It is not "clean". Lots of excess spaces and inconsistencies in the texts that need to be cleaned up, otherwise your analysis will be inaccurate.
Sunny
VIP
Trusted Members
December 7, 2016
Offline
Hi,
I would say that if you clean up the data in RECOMMENDATION column in Expired Policies sheet so that you have a fixed number of different recommendations, then you can easily have the different recommendation values counted in the Pivot Table, no need then for any extra formulas.
Br,
Anders
VIP
Trusted Members
June 25, 2016
Offline
After cleaning, you can then create a helper column to group the RECOMMENDATION categories that will help you count them.
1 Guest(s)
