If you unselect the "Blanks" to remove the zero values while adding a Filter to the Pivot table, statistics won't be updated if you insert new values to your original sheet. Great job btw.
Thanks for these video, they are really helpful. I am trying to workout how to show some data on a dashboard that is drawn from a pivot table. I can't find a way to get the chart to show what I need using teh master data, it is only available from the pivot tabe I created. However when I then try to add slicers i am unsure how to get them to work across the whol data (both tables and charts that are taken from the master data and the pivot chart). Any suggestions?
You have done a great job. And by using this I've created my own dashboard. But there is a problem. I've used charts there and not able to freeze them at the top. Do you have any solution on this?
Would've been extremely helpful if you could've linked the tutorial videos you suggested we watch first in the description! That's my only criticism though. You make great vids!
Great video. I just have a question. Why do you have to create the "TempDataSet" tab? Why not just use the "Transaction" tab data for your "Dashboard"? Thanks.
Great video as usually. One valid remark which maybe should be highlited in video for other users. Once you do filters in pivots to exclude (blanks) it will mean that in case new state or saler_rep will show up in Transactions (during upload new records) will be excluded default at pivots and going to be not included in dashboard. Do you have any solution for that?
Maybe use a combination of OFFSET and COUNTA for the Pivot Table Range.. Idk if it will work in Sheets. I used to use this in Excel. This would make the range end the actual data end and discard the blanks
Hi, great video, I benefited a lot. Thank you very much I have one problem When creating a Pivot Table I find a message in Pivot Table editor "sorry there are no suggestions for this table" Thanks for your support
Nice content, I am new to excel and would like to create my own dashboard. Do you have a tutorial that can help me create a survey tracking dashboard? not by ranks but tracking number of survey returns.
Thank you for the video and tutorials. However, I'm getting the following error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "500 "" at line 1, column 16. Was expecting one of: "(" ... "(" .... Tried removing the entire column but not getting through. Can anyone please assist.
Hi buddy, thanks for your tutorial. I have a doubt. I have a form that is already filled with information. I would like to create a simple interface where I can put a key word and the program find or no that word and show the results. is the same CTRL+F, but I would like to show on the screen, all information that the program found. Do you have some clue about it please? Thanks
Hi. Thanks for the video everything is working but when I combine the formula all together it is not working, it's not pulling up the date based on the dropdown list except for the option "All" Can you help me? This is what my formula looks like on the temp sheet: =Query(Master!A1:N,"Select A, B, C, D, E, F, G, H, I, J, K , L, M" & IF(COUNTBLANK(HelperFormulas!A1:A3)=3,""," Where " & TEXTJOIN(" AND ",TRUE, HelperFormulas!A1:A3)),1)
I followed the same steps in the video but after pasting the last step in query .. I am getting error invalid date literal. Sir/madam .. can u help please
How did you copy the Pivot Table from one sheet to the other. When I try it at my end it seems to copy only the values. Any changes in the original Pivot does not reflect on the Pivot I have copied to the new sheet.
You have to make sure you select eh whole pivot table. If you miss a part of it it won't work. Otherwise it should be just copy/paste, nothing special.
@@ExcelGoogleSheets couldn't actually find this solution in any of your videos. seems like it'd need a script and then some reconfiguring of the query formula to get it done. looking for the same deal: multi-select in those dropdowns to filter the query sheet. my answer may be move to google data studio. thanks!
@@jimmylambMUSIQ There is no multii-select dropdown in Google Sheets. You just have to use separate dropdowns. Or just use Data Studio, as you already said.
Hi , I did the same example and it gives me an error message , the message is : Result was not automatically expanded , Please insert more rows (10336) formula is =QUERY(Transaction!A1:Q," SELECT *",1),, can I have your comment please , thanks.
the number of rows available in the sheet is less than rows from query results. you need to create enough rows in the target spreadsheet to fit the results.
Hi, I select the exact data existing in the sheet , =QUERY(A1:R25222," select * ",1)and i get another error message : Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings. in the spreadsheet settings i choose united state and GMT +2. what can I do ? sorry but it doesn't work .
@@ExcelGoogleSheets i see.... thank you master.... next is what about making a chart based on transaction date.... but not everyday has transaction...i want the date to appear in the chart with 0 instead of skipping the date
My compliments, you produce the best videos on this topic.
If you unselect the "Blanks" to remove the zero values while adding a Filter to the Pivot table, statistics won't be updated if you insert new values to your original sheet. Great job btw.
You are correct. I did show a solution for this using rules in my newer slicer videos.
Thanks for these video, they are really helpful. I am trying to workout how to show some data on a dashboard that is drawn from a pivot table. I can't find a way to get the chart to show what I need using teh master data, it is only available from the pivot tabe I created. However when I then try to add slicers i am unsure how to get them to work across the whol data (both tables and charts that are taken from the master data and the pivot chart). Any suggestions?
I love your videos. I would like to ask though, how do I get my sort drop down box to adjust everything in the Dashboard Like you did?
You have done a great job. And by using this I've created my own dashboard. But there is a problem. I've used charts there and not able to freeze them at the top. Do you have any solution on this?
Is there a video on how to sell subscription in Google Sheets' add on, already? If not, any references would be greatly appreciated. Thanks!
Wow! this is great
Hi there, have you saved an editable template of this? That would be so helpful
DID HE?
It's in the very first video.
ruclips.net/video/0yMOMSBENbo/видео.html
How do you update the pivot table filter when adding new brands? Mine does not auto-update to include the newest items
When I can delete the pivot sheet and when a need just to hide?
It was very helpful video, thanks
4:37 is where it start building it
and great video, keep it up!
For those wonder "Pivot table" is under "Insert" and not "Data".
Question, how do you exclude a grand Total row in Stacked Bar Chart without affecting the pivot table?
Would've been extremely helpful if you could've linked the tutorial videos you suggested we watch first in the description! That's my only criticism though. You make great vids!
Here - ruclips.net/video/DDHIR3EftdM/видео.html
Be the change you want to see in the world.
Great video. I just have a question. Why do you have to create the "TempDataSet" tab? Why not just use the "Transaction" tab data for your "Dashboard"? Thanks.
Part 3 has the answer to your question. I use it for a dropdown filter.
Thanks @Learn Google Spreadsheets
@@ExcelGoogleSheets great skills...
Why Don't you share workbook?
Great video as usually. One valid remark which maybe should be highlited in video for other users. Once you do filters in pivots to exclude (blanks) it will mean that in case new state or saler_rep will show up in Transactions (during upload new records) will be excluded default at pivots and going to be not included in dashboard. Do you have any solution for that?
You can use the filter by condition option and select "Cell is not empty".
Maybe use a combination of OFFSET and COUNTA for the Pivot Table Range.. Idk if it will work in Sheets. I used to use this in Excel. This would make the range end the actual data end and discard the blanks
Found this: infoinspired.com/google-docs/spreadsheet/dynamic-ranges-in-google-sheets-without-helper-cell/
THANK YOU SO MUCH. THANK YOU SO MUCH FOR HELPING.
Nice!
Hey where can I find such datasets to practice building dashboards?
would be helpful to link the videos that you have for the functions
Thank you. Great explanation.. but why it needs to use query (TempDataSet)?
You'll understand why if you watch part 2, part 3.
Hi, great video, I benefited a lot. Thank you very much
I have one problem
When creating a Pivot Table I find a message in Pivot Table editor "sorry there are no suggestions for this table"
Thanks for your support
you are welcome, thanks for your support
@Brain Out
Amazing! Thank you so much for the content you produce!
Nice content, I am new to excel and would like to create my own dashboard. Do you have a tutorial that can help me create a survey tracking dashboard? not by ranks but tracking number of survey returns.
Simply awesome!
Very helpful. Ty
Do you have day time live sessions? I work in night shifts
Why do we need to make copy dynamic of dataset into another sheet? What happen if we make dashboard directly from original dataset?
I can not select A1 to create the DateSet sheet, because of the blue tab with formula in it keeps popping up in front of A1.
Thank you for the video and tutorials. However, I'm getting the following error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "500 "" at line 1, column 16. Was expecting one of: "(" ... "(" .... Tried removing the entire column but not getting through. Can anyone please assist.
Hi...there. Why the average of pivot table and TempDataSet is different? Thanks
Is the data available somewhere to follow along?
How is it taking maps for states. If name of a state is misspelled then?
keep the good job up man
Where can I get your sample data
Very helpful indeed
Thank you so much
Could you share the excel file for better learning?
Hi buddy, thanks for your tutorial. I have a doubt. I have a form that is already filled with information. I would like to create a simple interface where I can put a key word and the program find or no that word and show the results. is the same CTRL+F, but I would like to show on the screen, all information that the program found. Do you have some clue about it please? Thanks
VLOOKUP function seems to be what you are looking for. I have a lot of videos covering that function. Watch part 1 VLOOKUP.
Thanks a lot
you changed my life
great simple instruction
please where can I access the part 2 tutorial?
can you upload the demo sheet?
Everything I understood but how did u made tat region table
Hi. Thanks for the video everything is working but when I combine the formula all together it is not working, it's not pulling up the date based on the dropdown list except for the option "All" Can you help me? This is what my formula looks like on the temp sheet: =Query(Master!A1:N,"Select A, B, C, D, E, F, G, H, I, J, K , L, M" & IF(COUNTBLANK(HelperFormulas!A1:A3)=3,""," Where " & TEXTJOIN(" AND ",TRUE, HelperFormulas!A1:A3)),1)
Hi! I tried but I am getting error stating "unable to expand as the data overwrite in E7". can you please help me out ???
If you don't mind can you share this data file which can be done by practice
how it is linked with start date and end date
How can i put the google data studio dashboard in google sheet?
Hi, would gladly appreciate it if we can have the raw dataset for this video? :))
1++
how to add any stock previous day high low data in Google sheet Google Finance im try too much formula but not work please help thank you
how to get all data filter as per date like monthly and weekly
I followed the same steps in the video but after pasting the last step in query .. I am getting error invalid date literal. Sir/madam .. can u help please
Where to start? I see the Intermediate and advance courses of Google Sheets but not the beginners one
ruclips.net/video/wQfApf3eci8/видео.html&list=PLv9Pf9aNgemt61gjKQaYZHjvZlVp4OANq
@@ExcelGoogleSheets Thanks a lot
It would a nice to provide the data set you're using... in case someone wants to follow the tutorial himself :)
Agreed.
tks so much bro!
Hi, new subscriber here! :) One question, why is there a need to create a TempDataSet? Can I go straight to the Main sheet? Tnx ;)
If you want to to what I do in Part 2 then yes, otherwise no.
@@ExcelGoogleSheets Can you share the demonstration with me?
Thank you for the clear tutorial
can u give some sample data sets
Can this be exported to excel?
How did you copy the Pivot Table from one sheet to the other. When I try it at my end it seems to copy only the values. Any changes in the original Pivot does not reflect on the Pivot I have copied to the new sheet.
You have to make sure you select eh whole pivot table. If you miss a part of it it won't work. Otherwise it should be just copy/paste, nothing special.
@@ExcelGoogleSheets Can you share the demonstration with me?
if i need 2 filter criteria in Region Box. Example : i need data in region Southern and Western. How to make that?? Thx U
Watch my QUERY function series ruclips.net/video/bW6P2YvLyZg/видео.html
@@ExcelGoogleSheets couldn't actually find this solution in any of your videos. seems like it'd need a script and then some reconfiguring of the query formula to get it done. looking for the same deal: multi-select in those dropdowns to filter the query sheet. my answer may be move to google data studio. thanks!
@@jimmylambMUSIQ There is no multii-select dropdown in Google Sheets. You just have to use separate dropdowns. Or just use Data Studio, as you already said.
can I get the link to the raw data ?
how to show this dashboard in html page?
where to get the dataset
he merged the cells = closing this video 🤣 never coming back to this chanel
Cool!
Can you plss provide the dataset?
Can you share the demonstration with me?
Hi , I did the same example and it gives me an error message , the message is : Result was not automatically expanded , Please insert more rows (10336) formula is =QUERY(Transaction!A1:Q," SELECT *",1),, can I have your comment please , thanks.
the number of rows available in the sheet is less than rows from query results. you need to create enough rows in the target spreadsheet to fit the results.
Hi, I select the exact data existing in the sheet , =QUERY(A1:R25222," select * ",1)and i get another error message : Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings. in the spreadsheet settings i choose united state and GMT +2. what can I do ? sorry but it doesn't work .
Hey you skipped the major filtering parts, ie- region and start & end date that are needed to be linked to filter the whole dashboard!
it's a 3 part tutorial. make sure you watch part 2 and part 3.
Can you send this data set
i am trying this...and then i find that if there is new region or brand....it is not appear...filtered
check this for a solution ruclips.net/video/SEzKYOiptCk/видео.html
instead of filtering out blanks, use condition "not empty"
@@ExcelGoogleSheets i see.... thank you master....
next is
what about making a chart based on transaction date.... but not everyday has transaction...i want the date to appear in the chart with 0 instead of skipping the date
Dear sir please update new video for Google form automatically update work
Thanks
Where are the part 2 and 3?
I found it! Thanks!
thank you
Description
Explore educational videos about our products.
For additional help visit our help centers and communities at support.google.com
Hey. My pivot table is not automatically updating. I have removed all filters. Can anyone help please?
Need to right click on the pivot data and select refresh.
can you provide the raw data