Forum

Notifications
Clear all

Pivot table

11 Posts
3 Users
0 Reactions
97 Views
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

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

 
Posted : 19/10/2018 1:27 pm
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 19/10/2018 7:34 pm
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 19/10/2018 9:12 pm
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

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

 
Posted : 20/10/2018 9:37 am
(@sunnykow)
Posts: 1417
Noble Member
 

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.myonlinetraininghub.com/conditional-formatting-for-cellsting-pivottables

Hope this help.

Sunny

 
Posted : 20/10/2018 9:00 pm
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

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?

 
Posted : 28/10/2018 4:30 pm
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 29/10/2018 11:58 am
Anders Sehlstedt
(@sehlsan)
Posts: 974
Prominent Member
 

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

 
Posted : 29/10/2018 12:11 pm
(@sunnykow)
Posts: 1417
Noble Member
 

After cleaning, you can then create a helper column to group the RECOMMENDATION categories that will help you count them.

 
Posted : 29/10/2018 1:01 pm
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

Sunny and Anders

I greatly appreciate the suggestions.  Makes a lot of sense.  I will give it a try.

Thanks

 
Posted : 30/10/2018 10:53 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Melinda

Do let us know if you need further assistant.

Sunny

 
Posted : 30/10/2018 12:10 pm
Share: