Excel - One Slicer for Multiple PivotTables with Different Data Sources

Excel - One Slicer for Multiple PivotTables with Different Data Sources - Apply the Same Filter

Рет қаралды 137,388

2 жыл бұрын

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.
------------------------

Пікірлер: 96

@jimwilliamson4202 Жыл бұрын

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!

@biancawanders674 4 ай бұрын

You explained exactly what my problem was. Thank you for the clear and easy to understand instructions!

@dlp020505 Жыл бұрын

usually, I don't leave any comment but this one is super useful and very clear. just want to say thank you Chester!

@princeoketch6222 8 ай бұрын

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...😊

@IvanCortinas_ES 2 жыл бұрын

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.

@qsnitch3342 11 ай бұрын

Thank you very much! This was exactly what I was looking for and was explained in a very straightforward manner. Really appreciate this!

@onkarsoni8854 Ай бұрын

Very helpful, very detail, really nice content

@JimFikes Жыл бұрын

Very useful indeed. Thank you for making this available to us.

@riccardoczech6195 2 жыл бұрын

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!

@clintondinglasa3654 Жыл бұрын

This has been super helpful! Appreciate you taking the time to share your expertise with us. More power to you.

@ChesterTugwell Жыл бұрын

Glad to hear it!

@kulwindersingh-ju4br Ай бұрын

you are ownsome sir , the best i make a cross on this , my problem is resolved now ..

@hungarianzozo 10 ай бұрын

Excellent primer. Thank you, worked for like a charm.

@NoobHit 5 ай бұрын

Thank you finally I got a way to pretty print all my charts according to the year slicer.

@user-tr8vf3vr2n 19 күн бұрын

Very helpful, thank you very much !

@wayneedmondson1065 2 жыл бұрын

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!!

@ZawmyoHtet-lg7jn Жыл бұрын

Thank you very much, Sir. You saved my Day.

@jayshreekulkarni3974 Ай бұрын

Great!!! i got the solution , explained in very simple manner. Thank you.

@RaviKumar-pr2by 9 ай бұрын

Thanks Chester for short and sweet answer..

@Excelvenky Жыл бұрын

Fabulous. Simple and easy to understand

@Carmybabe82 Күн бұрын

thankyou, the best video ever!!

@dednicat Ай бұрын

Good tutorial, very straight forward, thanks

@kirkwheeler4590 10 ай бұрын

Great tutorial! (wish it worked for Mac :)

@bsa2952 2 жыл бұрын

Straight to the point video! thank you sir!

@lalitbhandary1652 Жыл бұрын

Excellent thank you for putting it on KZfaq

@ariadnegarcia150 2 жыл бұрын

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

@ahmedyoussry8442 4 ай бұрын

thank you, you have been a great help

@deenah6156 Жыл бұрын

that was soooo helpful you saved my life

@user-cz8yx7lq1e 6 ай бұрын

Você é pica, tiozin! Resolveu meu bang aqui! You the man! You solved my thing here! Thanks!

@jdv453 Жыл бұрын

Thnx so much, very helpful!

@eissa247 Жыл бұрын

thanks a lot

@kellitrombley7166 Жыл бұрын

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.

@richardbryanesq 5 ай бұрын

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.

@sabrinamimouni8306 Жыл бұрын

Thank you so much! it's a game-changer, really 🙂

@hannahliu7790 Жыл бұрын

after inserted the slicer, can also go to slicer, report connection, then select all pivot tables.

@muhammadnauman1454 5 ай бұрын

Thanks for sharing this . this is what I was looking for

@ChesterTugwell 5 ай бұрын

Glad I could help

@KiranKumarManthapuram Жыл бұрын

This was awesome!! Thank you so much Sir!!

@xup6u600angela Жыл бұрын

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?

@thehgamers Жыл бұрын

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

@ultragreen8289 Жыл бұрын

Thank you! Very helpful

@m.s.sub.2023 Жыл бұрын

Very clear and nice...Thanks

@ChesterTugwell Жыл бұрын

You are welcome 😊

@MrAgam97 Жыл бұрын

Great, really useful Thank Chester

@LimaaXande Жыл бұрын

Very useful content!

@salmaalaa6158 2 жыл бұрын

very good video thank you Chester

@rudyr1005 2 жыл бұрын

You saved me!!! Thank you so much

@sampheonix 5 ай бұрын

Thanks a lot sir

@georgeshaw6207 5 ай бұрын

Super video !

@mavsbellen9975 Жыл бұрын

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.

@blizzardr6980 4 ай бұрын

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)

@directorscompany379 Жыл бұрын

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.

@thehgamers Жыл бұрын

I have exactly same issue, slicer is connected to all the tables but it doesn’t work for any

@MrHenri911 Ай бұрын

Ever found a solution? I'm sitting in the same boat at the moment and havent found a solution yet

@user-se9wp9ov8g Жыл бұрын

THANKS MY FRİEND

@utoobia6872 Жыл бұрын

Thank you very much!

@thehgamers Жыл бұрын

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

@sajailyas 5 ай бұрын

After following these instructions > Click on slicer and go to slicer (settings) in ribbon > report connections > choose your pivot tables to connect with

@practicalspreadsheetsolutions 5 ай бұрын

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 🙂

@ashwinnair2069 4 ай бұрын

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

@mariabablas3343 Жыл бұрын

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?

@jairusalarcon1237 Жыл бұрын

Thank you!

@viltepuisyte4120 Жыл бұрын

thank you very useful

@joycenaicker6251 2 жыл бұрын

Thanks for sharing.

@quachkhoaduy7565 Жыл бұрын

Tks

@ramiboy_y2049 8 ай бұрын

Thanks!

@tolitzmercado8332 5 ай бұрын

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

@assassingaming808 2 жыл бұрын

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

@TheRamanm Жыл бұрын

I have excel table and cannot figure why slicer does not change two pivot table even after i had made connection

@opalssoftmentor Жыл бұрын

Thanks

@almangar01 Жыл бұрын

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?

@destinationstarship2677 Жыл бұрын

Can you do the same for Timelines?

@jayantyadav8911 2 жыл бұрын

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.

@riccardoczech6195 2 жыл бұрын

I added 3! Works fine, but in my case i had to add them one by one or excel would crash

@boricuatzin 2 жыл бұрын

@@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?

@TheSevinj 2 жыл бұрын

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.

@rios.feghali 3 ай бұрын

Same issue here! Any idea how to add a calculated field once the pivots are created (and added to the Data Model)?

@Aliraza-qd8ry 2 ай бұрын

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?

@akrobetoray5694 5 ай бұрын

Life saver

@abdullaharain4066 6 ай бұрын

Amazing

@michaelsiggers4109 Жыл бұрын

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

@sallypaulet3915 Жыл бұрын

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

@abdikanimohamed4297 Жыл бұрын

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?

@rekoil2021 Жыл бұрын

DId you figure out why it wasnt working?

@Ryan-hm6ln Жыл бұрын

u are awesome

@icecold1805 Жыл бұрын

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.

@trevonlaugier8391 Жыл бұрын

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.

@caxtjum4953 Жыл бұрын

Bro I am doing the same but not getting the Link

@einyv Жыл бұрын

I'm not getting all option for slicer

@ahmadfajarfromzerotohero Жыл бұрын

Awesome

@kerenferrisandco 8 ай бұрын

I am not given the choice to add to data model when creating a pivot table in a Mac. any help to enable this?

@richardbryanesq 5 ай бұрын

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.

@boricuatzin 2 жыл бұрын

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?

@richardbryanesq 5 ай бұрын

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.

@tolitzmercado8332 5 ай бұрын

How about multiple slicer for multiple source of data.