Excel - One Slicer for Multiple PivotTables with Different Data Sources - Apply the Same Filter
HTML-код
- Опубликовано: 21 окт 2024
- Download the featured file here: www.bluepecant...
In this video I demonstrate how to filter multiple PivotTables based on different data sources using one slicer. The key step in this process is to employ Excel's data model which allows you to create relationships between the different data sources.
------------------------
You explained exactly what my problem was. Thank you for the clear and easy to understand instructions!
Finally. I have 16 tabs I review with salespeople each 2 weeks. WIth a salesperson slicer on each tab. Was trying to figure out how to click once for a particular salesperson and update all tabs (with pivottables). Perfect!
usually, I don't leave any comment but this one is super useful and very clear. just want to say thank you Chester!
Finally I got what I've been searching for. Thank you for sharing. I really appreciate it.
My slicer was using months filter. Though the months are arranged alphabetically. The exact functionality has been met and I'm grateful...😊
Thanks Chester for short and sweet answer..
Excellent primer. Thank you, worked for like a charm.
Excellent proposal Chester.
One of the most classic variants.
It is very important to handle the concept of the data model.
Thanks for the explanation.
Thank you very much! This was exactly what I was looking for and was explained in a very straightforward manner. Really appreciate this!
Very useful indeed. Thank you for making this available to us.
You, sir, just saved my life! I was going about it through the data model and it was giving me all sorts of errors. This is neat!
This has been super helpful! Appreciate you taking the time to share your expertise with us. More power to you.
Glad to hear it!
Thank you very much, Sir. You saved my Day.
Thank you finally I got a way to pretty print all my charts according to the year slicer.
Fabulous. Simple and easy to understand
Awesome Chester! A great example and solution. Thanks for demonstrating and also for the sample file to follow along which is much appreciated! Thumbs up!!
Straight to the point video! thank you sir!
Great!!! i got the solution , explained in very simple manner. Thank you.
Excellent thank you for putting it on RUclips
Thnx so much, very helpful!
Great tutorial! (wish it worked for Mac :)
Thanks for sharing this . this is what I was looking for
Glad I could help
Thanks a lot for this video! I was looking for that solution for weeks and I finally found your channel. Thank you so much! amazing content :)
that was soooo helpful you saved my life
Good tutorial, very straight forward, thanks
Você é pica, tiozin! Resolveu meu bang aqui!
You the man! You solved my thing here! Thanks!
Very helpful, very detail, really nice content
This was awesome!! Thank you so much Sir!!
Very clear and nice...Thanks
You are welcome 😊
Very useful content!
Great, really useful Thank Chester
Very helpful, thank you very much !
Thank you so much! it's a game-changer, really 🙂
thankyou, the best video ever!!
You saved me!!! Thank you so much
Is there any way to do this so a slicer filters 2 different tables of data (they both have the same data in but I want to be able to filter different parts of my dashboard by different things)
Thank you!
Thank you very much!
very good video thank you Chester
you are ownsome sir , the best i make a cross on this , my problem is resolved now ..
Hi Chester, great video i got both my pivot tables working with my slicer 🙂 I want to add more slicers to the 1 slicer with different information - how do I do that?
Very clear instruction! I have another scenario to solve.
If 2 datasets don't have any connection, e.g. 1 is Sales, another is an indirect cost, how to have only 1 timeline to show each month's Sales amount and other cost amount?
You need to add months column to both the data and then use month column as the third table which is connecting the two and acting as slicer I.e., same as regions table in this example
Can this be used on a group of pivots that have already been created or would I have to start over to make sure they're all connected via the data model option?
thank you very useful
thank you, you have been a great help
Thanks for sharing.
After building relations, I have the slicer connected to both pivot tables through the report connection. But it doesn't filter the other pivot. Not sure what's going on.
I have an excel file I use for payroll, with multiple worksheets, one for each employee. Each worksheet is broken down by week, with totals for each week so I can just input the hours and it will calculate the total hours for that week. Now, what I am wondering is possible, is to select the employees name in a drop down and have their full data from their worksheet populate.
Yes, that's the purpose of Pivot Tables and using Excel for data analysis. You have to learn the program over time, that's all. Better yet, there are programs specifically for payroll to do just this exactly.
Hello, I am looking to use one slicer for 2 different pivot tables, please tell me is it possible in any way to use 1 slicer without making data as table?
after inserted the slicer, can also go to slicer, report connection, then select all pivot tables.
THANKS MY FRİEND
I need further help. I have created both relationships, my slicer has both report connections ticked and yet selecting an option on my slicer filters nothing.
I have exactly same issue, slicer is connected to all the tables but it doesn’t work for any
Ever found a solution? I'm sitting in the same boat at the moment and havent found a solution yet
Hi Chester, thanks for this. This is really helpful.
A question. Can we add two slicers here? Two different data sources. I tried using this method but only one of the pivot table seems to be working.
I added 3! Works fine, but in my case i had to add them one by one or excel would crash
@@riccardoczech6195 did you create additional tables for each created slicer and then link them? Or was it one table with columns for each associated sliver?
For example i have 5 multiple source of data. And 5pivot table per each data.
But they do have supervisor, date, weekending and month. Should i create different data model per each? So i can create slicer per each set of data
Can you do the same for Timelines?
thanks a lot
Thanks
I have excel table and cannot figure why slicer does not change two pivot table even after i had made connection
Chester, i was learning from your video regarding slicer but i faces deficulty regarding removing of old names of tables, while making new relationship...can u guide me how to getrid of old delelted relations, shown while creating new relationship
I have made an attempt to do this but I'm experiencing difficulty, as the slicer is not functioning as intended with my tables. I have carefully inspected the data model and relationship stablished, and done the report connectivity but the slicer is not working. How I can resolve this issue?
DId you figure out why it wasnt working?
Nice, thanks!
This is a great tutorial, which I'm trying to follow, but I have hit a snag - please assist?! When I create the table with unique values (by removing duplicates), I am left with a "(Blank)" in the slicer. I have checked through the table and there are no apparent Blanks, it seems to be an artifact of the "remove duplicates" function. When I try to define the relationships from the slicer, it won't let me do so as the field contains "Blanks". I have tried to remove duplicates, and then just copy the resultant values into the table, and it still happens. Been trying to solve for 2 days, please help? TIA
please help. I have been trying this ALL day and I can not get it to work. I've watched so many videos as well as yours. checked relationships, changed font, stirred my coffee thenother way, absolutely can not getnit to work. been trying desperately as I need this for a work document. HELP
I am not given the choice to add to data model when creating a pivot table in a Mac. any help to enable this?
I had an older laptop which I installed a fresh Win10 OS and then Excel specifically because the PC version of Excel has the added functionality.
awesome! but I got an issue however: In my work, the "regions" may change, new regions may be added. This template that you made would not add the new regions to the slicer. Would there be a way to create a system that would update those slicers? At some point I thought if instead of values in the Regions tab, you had used a pivot that would show you all the unique values (and expand if new values are added) but Excel didn't allow me to do that sadly.
The pivot should reflect any changes in the raw data, so if you add the region in the source data, you should be able to refresh the pivots to include the new fields.
I followed the instruction, created slicer which is connected to both the pivot tables but when I click on slicer nothing happens and apparently I am not only one that this happened too, hope someone can find the answers quickly :)
After following these instructions > Click on slicer and go to slicer (settings) in ribbon > report connections > choose your pivot tables to connect with
Are you sure that you created the slicer from the correct table and connected Sales and Returns tables to the regions table in the data model? In the video the Sales and Returns tables have duplicated regions and Excel can only connect one to one or one to many relationships, meaning that you need at least one table to have unique values. That is why the third table with unique regions is necessary and the slicer needs to be built from it to filter both other tables properly. So you need to connect Sales to unique regions using regions column, then Returns to unique regions table again using regions columns (so two tables connect to one with unique values) and then apply slicer to connecting table with unique values and connect slicer under report connections to two others. Hope this helps 🙂
It's not connecting and if I make slicer using unique table pivot table no other pivot table is showing in the report connection apart from the one pivotable that is connected to it
Thanks a lot sir
Super video !
I have created the pivot tables and now want to add calculated field to the pivot. When I click on create a calculated field it is greyed out.
Same issue here! Any idea how to add a calculated field once the pivots are created (and added to the Data Model)?
Tks
Bro I am doing the same but not getting the Link
u are awesome
Hello, when I went to create relationships I only see the table names and can’t select the data model. Did I do something wrong?
When you created the table there's a checkbox on the bottom of the "create a table" box (or whatever it's called) which says "add table to data model," or something like that. I believe that's what's missing for you.
I'm not getting all option for slicer
Thanks!
Awesome
Amazing
Life saver
How about multiple slicer for multiple source of data.
Does not work for me somehow
Thank you! Very helpful