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

  Рет қаралды 123,868

Learn Google Sheets & Excel Spreadsheets

Learn Google Sheets & Excel Spreadsheets

Күн бұрын

Пікірлер: 69
@lpanebr
@lpanebr 5 жыл бұрын
It was not the main topic, but finally I learned how to use the label and format in the query function! Thank you!!
@robertmaluka2763
@robertmaluka2763 5 жыл бұрын
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
@marianoandresbalino8869 3 жыл бұрын
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 👍🏻
@cassioschneider922
@cassioschneider922 4 жыл бұрын
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
@ExcelGoogleSheets 4 жыл бұрын
Good Idea!
@aarontovar8455
@aarontovar8455 3 жыл бұрын
Man, I love your videos. I love how you explain everything! Keep up the great work!!
@XCelVietnam
@XCelVietnam 3 жыл бұрын
me too, he's such a great guru and i am so much in love with his channel.
@queenkai9522
@queenkai9522 Жыл бұрын
Thank you for your videos. They're very helpful.
@martinachoi7843
@martinachoi7843 3 жыл бұрын
is there a reason why we can't make charts directly from pivot table?
@EntreprendreetVivre
@EntreprendreetVivre Жыл бұрын
Excellent video with all the info I was looking for! Thanks a million!❤
@Kevin_Wakuze
@Kevin_Wakuze 4 жыл бұрын
This was extremely helpful. Thank you very much for sharing this video.
@wormemc
@wormemc 3 жыл бұрын
Thanks a ton super useful
@fzmhn
@fzmhn Жыл бұрын
Merci pour ce précieux partage bonne continuation 😊
@AdamLundquist
@AdamLundquist 3 жыл бұрын
That video was amazing!
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Thank you!
@rogerhartley6630
@rogerhartley6630 4 жыл бұрын
Excellent set of videos, thanks for taking the time to make these!
@mayanksinha4084
@mayanksinha4084 11 ай бұрын
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?
@PanteraRossa
@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?
@ArchMageRaven
@ArchMageRaven Жыл бұрын
Is there any way to use slicers to exclude weekend days?
@numannor
@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
@ExcelGoogleSheets Жыл бұрын
Use year(A) , month(A) ?
@betzabefunes1513
@betzabefunes1513 Жыл бұрын
Thanks for the video was very helpful 🫶🏻🫶🏻
@gregoryunderwood4121
@gregoryunderwood4121 5 жыл бұрын
Brillant! Once again you're a lifesaver!! Thank you!!
@mattmorrisson9607
@mattmorrisson9607 5 жыл бұрын
This is some fantastic content! Thanks for making this!
@ManuelaGuarneri
@ManuelaGuarneri 4 жыл бұрын
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!!
@pichit.raetai
@pichit.raetai 5 жыл бұрын
thank you
@sanyogsharma2195
@sanyogsharma2195 4 жыл бұрын
How to add charts for kpi
@WheelieTruther
@WheelieTruther 2 жыл бұрын
Is there a reason why you copy the data rather than doing it all from the Subtotal sheet?
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
This way it will auto generate new columns as you keep adding new data to the source.
@vvdd903
@vvdd903 2 жыл бұрын
very kool very kool. thanks much
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Glad you liked it!
@antonellafavaro2116
@antonellafavaro2116 3 жыл бұрын
how can I do to save the data selected on the slicer in order to be saved the next time i open the dashboard?
@SUMIT4223
@SUMIT4223 4 жыл бұрын
Thanks for sharing.
@AdamLundquist
@AdamLundquist 4 жыл бұрын
Is there a way we can get access to the sheet to practice on?
@peterskalla2321
@peterskalla2321 4 жыл бұрын
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
@samiranjoseph9793 4 жыл бұрын
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
@cherylpokemon6376
@cherylpokemon6376 4 жыл бұрын
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
@cherylpokemon6376 4 жыл бұрын
Never mind, just figured it out! Didn't show up because of the date filter status
@jonathanpjaime
@jonathanpjaime 5 жыл бұрын
great tutorial as always. may I know how to change the date to week number? thanks
@sayanta.k
@sayanta.k 5 жыл бұрын
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.
@miket1638
@miket1638 3 жыл бұрын
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
@amirka25 2 жыл бұрын
I'm curious about that too!
@AllinOne-cr2cs
@AllinOne-cr2cs 3 жыл бұрын
slicer not working after publish the dashboard
@simplesio9986
@simplesio9986 4 жыл бұрын
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
@ExcelGoogleSheets 4 жыл бұрын
You should use your original date column and group the dates.
@AREPL
@AREPL 4 жыл бұрын
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
@ExcelGoogleSheets 4 жыл бұрын
Use Google Data Studio instead. kzbin.info/www/bejne/nabKXouHZa59d5o
@ahmedabdelkhalek3489
@ahmedabdelkhalek3489 4 жыл бұрын
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
@ExcelGoogleSheets 4 жыл бұрын
You should use whatever method works best with your data. QUERY just keeps things updated, but it has its problems.
@WheelieTruther
@WheelieTruther 2 жыл бұрын
Is there a SELECT WEEK function as well?
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
No such function.
@WheelieTruther
@WheelieTruther 2 жыл бұрын
@@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
@ExcelGoogleSheets 2 жыл бұрын
That makes sense.
@francelinoximenes9497
@francelinoximenes9497 4 жыл бұрын
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
@ExcelGoogleSheets 4 жыл бұрын
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.
@mickegtz
@mickegtz 3 жыл бұрын
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.
@RahilChoudhary
@RahilChoudhary 4 жыл бұрын
Thanks
@josephleslie007
@josephleslie007 Ай бұрын
Cool ❤
@speedwaylabsdev
@speedwaylabsdev 4 жыл бұрын
thanks!
@gilangpuja1039
@gilangpuja1039 4 жыл бұрын
hai.. is there any way to reset all those filter ?
@gilangpuja1039
@gilangpuja1039 4 жыл бұрын
reset all filter with only one click
@sandysameh-it5wp
@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
@mask75260
@mask75260 5 жыл бұрын
Awesome
@metasal
@metasal 4 жыл бұрын
Count of Sales is 1399 if Row 1 is headers was selected
@makmurhasanuddin3151
@makmurhasanuddin3151 4 жыл бұрын
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
@ahmadghiffari91
@ahmadghiffari91 5 жыл бұрын
great
@jmack87able
@jmack87able 4 жыл бұрын
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
@Battlem0nk 3 жыл бұрын
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)
QUERY Pivot Table -Google Sheets - Query Pivot, Group By, Month, Year Functions Tutorial -  Part 6
18:49
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 112 М.
Google Sheets - Slicers - Part 1
9:29
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 106 М.
小丑家的感情危机!#小丑#天使#家庭
00:15
家庭搞笑日记
Рет қаралды 34 МЛН
Wait for the last one 🤣🤣 #shorts #minecraft
00:28
Cosmo Guy
Рет қаралды 9 МЛН
Human vs Jet Engine
00:19
MrBeast
Рет қаралды 113 МЛН
Pivot Tables in Google Sheets A-Z Tutorial
42:04
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 16 М.
Google Sheets Pivot Table with KPI & Bar Graphs
15:26
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 33 М.
Interactive dashboards in Google sheets
17:42
David Benaim
Рет қаралды 96 М.
Google Sheets - Dashboard Tutorial - Dynamic QUERY Function String - Part 3
20:26
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 123 М.
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
14:48
Excel Campus - Jon
Рет қаралды 14 МЛН
Interactive Excel Dashboards & ONE CLICK Update!
52:26
MyOnlineTrainingHub
Рет қаралды 10 МЛН
How to Create a Dashboard in Google Sheets (10 steps) - Query Formula
56:28
Unleash the Power of Pivot Tables in Google Sheets
10:18
Teacher's Tech
Рет қаралды 18 М.
Master Pivot Tables in 10 Minutes (Using Real Examples)
11:33
Kenji Explains
Рет қаралды 455 М.
Understanding Data Cleaning | Google Data Analytics Certificate
1:06:05
Google Career Certificates
Рет қаралды 188 М.
小丑家的感情危机!#小丑#天使#家庭
00:15
家庭搞笑日记
Рет қаралды 34 МЛН