Google Sheets - Dashboard with Slicers for Pivot Tables & Charts - Part 2

Learn how to create a dynamic dashboard with slicers for pivot Tables & charts, including year & month slicer for multiple pivots, pie, bar charts & scorecards.
Watch part 1 of this tutorial here
• Google Sheets - Slicer...
#googlesheets #slicers

[@lpanebr]

It was not the main topic, but finally I learned how to use the label and format in the query function! Thank you!!

[@robertmaluka2763]

Funny I just came across Slicer in the drop down and I had no idea what it really was. You gave me some great ideas. Thanks again for a great video

[@marianoandresbalino8869]

Dude this is actually mind blowing. Thank you so much for taking your time to share your knowledge. I’m actually learning a lot, keep up the great work 👍🏻

[@rogerhartley6630]

Excellent set of videos, thanks for taking the time to make these!

[@Kevin_Wakuze]

This was extremely helpful. Thank you very much for sharing this video.

[@EntreprendreetVivre]

Excellent video with all the info I was looking for! Thanks a million!❤

[@aarontovar8455]

Man, I love your videos. I love how you explain everything! Keep up the great work!!

[@XCelVietnam]

me too, he's such a great guru and i am so much in love with his channel.

[@gregoryunderwood4121]

Brillant! Once again you're a lifesaver!! Thank you!!

[@mattmorrisson9607]

This is some fantastic content! Thanks for making this!

[@cassioschneider922]

one thing i found out and worked great for me: i like the format to be always in order, so i like this format: *' MM - MMM '* it shows like this: *01 - jan.* 02 - feb. etc cheers

[@ExcelGoogleSheets]

Good Idea!

[@queenkai9522]

Thank you for your videos. They're very helpful.

[@fzmhn]

Merci pour ce précieux partage bonne continuation 😊

[@wormemc]

Thanks a ton super useful

[@SUMIT4223]

Thanks for sharing.

[@pichit.raetai]

thank you

[@ManuelaGuarneri]

Hello, this is great. I need to do something very similar, but I would need to have a stacked bar chart (for example bar chart of the sales rep with the bar divided per brand). Is it possible to do from the main database without having to do a pivot with brand on the column and creating the chart from the pivot? Thank you for any help!!

[@AdamLundquist]

That video was amazing!

[@ExcelGoogleSheets]

Thank you!

[@speedwaylabsdev]

thanks!

[@mask75260]

Awesome

[@RahilChoudhary]

Thanks

[@ahmadghiffari91]

great

[@betzabefunes1513]

Thanks for the video was very helpful 🫶🏻🫶🏻

[@martinachoi7843]

is there a reason why we can't make charts directly from pivot table?

[@vvdd903]

very kool very kool. thanks much

[@ExcelGoogleSheets]

Glad you liked it!

[@jonathanpjaime]

great tutorial as always. may I know how to change the date to week number? thanks

[@sayantak5426]

You can count the differences between a particular date from the beginning of the year and divide it by seven, then round it off to get the week. check out this function called "dateDiff " developers.google.com/chart/interactive/docs/querylanguage#scalar-functions I think this maybe the only way to do this in a Query.

[@cherylpokemon6376]

Hello, thank you so much for your video! I have tried implementing what you have done and I am having trouble having the pivot table update. So I have a slicer for year and month but when I add new dates, it does not show up in the pivot table or slicers. Any advice would be appreciated! :)

[@cherylpokemon6376]

Never mind, just figured it out! Didn't show up because of the date filter status

[@PanteraRossa]

I have large data sets that I have to breakdown into quarterly 3-month sheets because of the restraints on cells on a sheet. Is there a way to combine all these sheets into a single pivot table like this?? Or are you limited to one single data set per pivot table?

[@AdamLundquist]

Is there a way we can get access to the sheet to practice on?

[@peterskalla2321]

This is a fantastic overview, helping me extend my spreadsheet skills to SQL. Thank you! Question -- in my slicer on similar data, any time I set the MONTH slicer to other than all selected, my pivot tables give me a #REF error with the message: "Error : PIVOT evaluates to an out of bounds range." Can you help with how I resolve this? Thank you.

[@samiranjoseph9793]

Ensure that you edit/update the slicer data range from the pivot table range to actually include the extended pseudo - month column e.g. datacopy!A1:J24

[@antonellafavaro2116]

how can I do to save the data selected on the slicer in order to be saved the next time i open the dashboard?

[@ahmedabdelkhalek3489]

thank you, really helpful :) I would like to ask why did you use the query function instead of adding helper columns in the SUBTOTAL sheet? Would query be faster than the helper columns formulas? and for a larger dataset, wouldn't it slow the sheet?

[@ExcelGoogleSheets]

You should use whatever method works best with your data. QUERY just keeps things updated, but it has its problems.

[@numannor]

Great Video! If I'd like to capture the date from Column A together with Year and Month, what should I do? It says the column toDate(A) can only be used once.

[@ExcelGoogleSheets]

Use year(A) , month(A) ?

[@mayanksinha4084]

hi, I am new to working with sheets, slicer works for me in the sheet. But it stays static once I publish it and I am unable to do any action on it. is there any way to make it so that their is a dynamic filter available for users who are viewing from the published link?

[@ArchMageRaven]

Is there any way to use slicers to exclude weekend days?

[@metasal]

Count of Sales is 1399 if Row 1 is headers was selected

[@sanyogsharma2195]

How to add charts for kpi

[@miket1638]

Has anyone found a way to show what is selected in the slicer? In excel it shows the value. In sheets it only shows 1 of 12 or whatever.

[@amirka25]

I'm curious about that too!

[@francelinoximenes9497]

Why don't we use a pivot table to directly create a chart? What effect on the data when creating a chart from the pivot table?

[@ExcelGoogleSheets]

Currently it picks up the range as a source not the pivot table, which means as your data changes and your pivot table updates it will not update the chart the right way.

[@simplesio9986]

I am sorting my data according to month with all the formulas given in the video..But when i am using pivote table and adding month to rows...instead of showing total value for a month like May June July It is showing May May May May June June June June Please help

[@ExcelGoogleSheets]

You should use your original date column and group the dates.

[@AREPL]

How can I make slicers usable for public, but protect my chart? If somebody tries to use it while being not loged in, it says that he has no permission.

[@ExcelGoogleSheets]

Use Google Data Studio instead. kzbin.info/www/bejne/nabKXouHZa59d5o

[@gilangpuja1039]

hai.. is there any way to reset all those filter ?

[@gilangpuja1039]

reset all filter with only one click

[@WheelieTruther]

Is there a reason why you copy the data rather than doing it all from the Subtotal sheet?

[@ExcelGoogleSheets]

This way it will auto generate new columns as you keep adding new data to the source.

[@AllinOne-cr2cs]

slicer not working after publish the dashboard

[@mickegtz]

Mmm...I don't think you don't know, but I'm not sure why you are doing all the process from zero when creating the second and subsequent pivot tables...instead, you could copy the first cell (left, top) of the pivot already existing and paste it anywhere within the spreadsheet? (obviously without overlapping to each other). Again, I don't think you don't know but I hope it help someone here.

[@sandysameh-it5wp]

Great Video I try more and more to change label name from *day(Timestamp)* to *DAY* but no action done when i try more troubleshoot always gave me Error ; I try use this function =QUERY(Sheet1!A1:T,"SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T, DAY(A) ,YEAR(A), toDate(A) Label YEAR(A) 'Year', toDate(A) 'Month' format toDate(A) 'MMM'",1) But How i add to Change label Day???????????????????????????????????????????????? waiting your Help and many thanks

[@makmurhasanuddin3151]

Hi ... I'm including your subscribe ... I beg for your help I have a dashboard that consists of several slicers, pivots, charts (line charts, meter diagrams, and score card diagrams) and data validation. when it will be exported, the values in each chart change ... For your information, I use the help sheet for some simple formulas to complete my chart. Please help

[@WheelieTruther]

Is there a SELECT WEEK function as well?

[@ExcelGoogleSheets]

No such function.

[@WheelieTruther]

@@ExcelGoogleSheets Managed to hack this together by using concatenate and the WEEKDAY function outside of a query. "=IF(D2="","",(CONCATENATE(TEXT((D2-WEEKDAY(D2,2))+1,"mmm dd")," - ",(TEXT((D2+7)-(WEEKDAY(D2,2)),"mmm dd"))))"

[@ExcelGoogleSheets]

That makes sense.

[@jmack87able]

Hi, thank you for the video it is important content very well done. I am getting a parsing error trying to write month to the toDate field from column A. My Query line is =QUERY(Sheet1!A1:D,"SELECT A, B, C, D, YEAR(A), toDate(A) format toDate(A) 'MMM', DAY(A), HOUR(A), MINUTE(A), SECOND(A)", 1). The error returned is "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "," ", "" at line 1, column 69. Was expecting one of: ... "*" ... "+" ... "-" ... "/" ... "%" ... Any idea whats wrong, thank you!

[@Battlem0nk]

format toDate(A) 'MMM' should be at the end of the query after all col are selected =QUERY(Sheet1!A1:D,"SELECT A, B, C, D, YEAR(A), toDate(A) , DAY(A), HOUR(A), MINUTE(A), SECOND(A) format toDate(A) 'MMM'", 1)