Log In
Register
Home
March 8, 2021
Offline
Hello Mynda,
Hope you are doing well? Somebody from a different department sends me this Dashboard.
I have a Pivot table with one hundred rows and thirty columns. There is a Filter in the Filters section of the Pivot Table Fields list. It is for the whole company. I just want a few of them which I need to work on and that would be let us say there are sixty options in the filter, I need only twenty of them.
Now my question is how to copy and paste only visible cells and make sure that the Filter does not show Multiple Items, and it shows All Items. (I am trying to separate these 20 options as a whole) I only need these 20 options.
For example if my filter shows
A
B
C
D
E
F
G
H and I want only A, C, D In the filter
And My pivot table should show me only A, C, and D and not A, B, C, D, E, F, G, and so on.
Thanks,
PB
July 16, 2010
Offline
Hi PB,
I'm not sure I follow your question.
Why do you want to copy and paste? Why can't you use the Filter to select A, C and D companies? Perhaps some screenshots would help explain the problem.
Mynda
March 8, 2021
Offline
Hello Mynda,
Thank you!
Answer to your questions:
Why do you want to copy and paste?
Why I want to copy is that is what my company wants. They don't want to send Pivot Table to the Leadership, so they want Sub Pivot Table (table) with no pivot table functionality.
Why can't you use the Filter to select A, C and D companies?
That is what I am doing using Filter to select A, C, and D but I am not supposed to send the Pivot Table to the leadership.
So, what I am trying to do is select visible cells (In my case results of A, C, and D) and paste them in a new sheet as a table so that the Leadership can just see the table with results for A, C, and D and not technical stuff of Pivot Table.
Thanks,
PB
July 16, 2010
Offline
You should be able to copy the PivotTable and paste as values. That way the underlying PivotTable is not copied, only the data visible in the cells. You might also like to copy and paste the formatting.
March 8, 2021
Offline
Good morning, Mynda
Thank you! I had tried the way you said but somehow it didn't work. It pastes some of the stuff which should not be there. I googled for this answer, and somebody mentioned that if we try to paste from Clipboard then this will work but I don't know somehow my office Excel Clipboard does not work. It does not open. At home, it works fine. It could be a Microsoft bug of Clipboard.
Thanks,
PB
July 16, 2010
Offline
I can't reproduce the issue. When I copy and paste as values from a PivotTable I only get what I see in the cells, i.e. no underlying data or connection to the PivotTable. Are you sure you're pasting as values?
Are you able to show me a screenrecording of the steps you're taking?
March 8, 2021
Offline
Hello Mynda,
Thank You! I see values only if I paste the whole Pivot Table like a normal table, but I am pasting only a Few Records that are visible after I filter only a few records from the Filter field List. let us say only twenty records out of one hundred records then it causes the problem.
You wrote
I can't reproduce the issue. When I copy and paste as values from a PivotTable I only get what I see in the cells
Are you pasting the whole Pivot Table or only a few records that are visible?
Another question is if I want to copy the Field from the Filter Field List as well with a couple of options and not 100's of options, it does not work. Can you please try to paste a field you are choosing the options from the Field List with 100's of options and you have selected let us say ten options and when you paste this drop downfield on a different sheet it should show only those ten options in the drop-down and not 100's of options? (In this case, the same concept of visible cells but only selected options should be pasted) Thanks
Thanks,
PB
July 16, 2010
Offline
Hi PB,
See image attached with the steps I'm taking. Again, I can't reproduce the issue you describe. I also put the Category filter in the Filters area and get the same results. i.e. only those cells visible that are copied are then pasted.
It sounds like you might be copying and pasting cells from a filtered Table, not a PivotTable. With Tables the data is still in the worksheet so it's possible to copy cells that are hidden by filters, but with a PivotTable that is filtered using the PivotTable filters, the data simply isn't in the worksheet to copy.
Another possibility is that you've applied Filters to the PivotTable that are row filters, not PivotTable filters. i.e. Data tab > Filters and then used those filter buttons on the PivotTable, rather than the built in filters that are automatically available when you build a PivotTable. These built in PivotTable filters can be hidden from users, so maybe that happened and then you reapplied filter buttons from the Data tab.
I don't understand the steps in your last point: "Another question is if I want to copy the Field from the Filter Field List as well with a couple of options and not 100's of options, it does not work. Can you please try to paste a field you are choosing the options from the Field List with 100's of options and you have selected let us say ten options and when you paste this drop downfield on a different sheet it should show only those ten options in the drop-down and not 100's of options? (In this case, the same concept of visible cells but only selected options should be pasted)"
Please provide step by step instructions and a sample file where you are having the problem and that I can test on my PC.
Mynda
March 8, 2021
Offline
Hello Mynda,
Thank you! Beautiful screenshot and well explained. I think that you have spent a lot of time on this issue, and I really appreciate it. The person who created that Pivot Table did it in an unusual way. Don't know. I was able to paste the Visible Cells on a different Sheet today. Now it is just a normal table. The issue is If Leadership wants to see one of the options and not twenty options, then the only way would be to give row-level filters. Right? With Row Level Filters It does not give Total Numbers for that Company. Total Number is important since there are some companies which have 3 to 4 Sub Companies.
This is what I would like to send to the leadership.
Thanks,
PB
1 Guest(s)
