• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Selected Filtering from the Pivot Table Fields|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Selected Filtering from the Pivot Table Fields|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsSelected Filtering from the Pivot T…
sp_PrintTopic sp_TopicIcon
Selected Filtering from the Pivot Table Fields
Avatar
P B
Member
Members
Level 0
Forum Posts: 38
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
1
September 9, 2021 - 3:19 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 9, 2021 - 10:10 am
sp_Permalink sp_Print

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

Avatar
P B
Member
Members
Level 0
Forum Posts: 38
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
3
September 9, 2021 - 12:34 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 9, 2021 - 8:22 pm
sp_Permalink sp_Print

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.

Avatar
P B
Member
Members
Level 0
Forum Posts: 38
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
5
September 9, 2021 - 11:35 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
September 10, 2021 - 1:06 pm
sp_Permalink sp_Print

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?

Avatar
P B
Member
Members
Level 0
Forum Posts: 38
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
7
September 10, 2021 - 10:23 pm
sp_Permalink sp_Print sp_EditHistory

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 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
September 11, 2021 - 11:36 am
sp_Permalink sp_Print

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)"

pb_copy_filtered_pt.pngImage Enlarger

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

sp_PlupAttachments Attachments
  • sp_PlupImage pb_copy_filtered_pt.png (62 KB)
Avatar
P B
Member
Members
Level 0
Forum Posts: 38
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
9
September 11, 2021 - 1:00 pm
sp_Permalink sp_Print sp_EditHistory

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.

WantToSendThisToLeadership-2.pngImage Enlarger

Thanks,

PB

sp_PlupAttachments Attachments
  • sp_PlupImage WantToSendThisToLeadership.png (47 KB)
  • sp_PlupImage WantToSendThisToLeadership-1.png (47 KB)
  • sp_PlupImage WantToSendThisToLeadership-2.png (47 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
September 11, 2021 - 9:10 pm
sp_Permalink sp_Print

Hi PB,

I'm not following you. Please provide a sample Excel file that shows the PivotTable starting point and the desired end result.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Brian Pham, Carlos Ferreira
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.