Hi, Mynda! Thank you for demonstrating and detailing all these options. I learned a couple of things and helped me revisit a few concepts. That issue (around min 4:00) of having to click refresh all twice to get the pivot table updated after the query table, has an easy solution: on the query properties unchecked "enable background refresh". Having this check mark put on can cause a couple of issues; and having to click refresh all twice is one of them.
Thank you!! I have been wrestling with the "PQ to Table to PT" scenario and wondering why the refreshes are not dependable or require multiple refreshes. First time to hear this information. 👍
Mynda, thank you for the great explanation. Even though the automatic update feature with VBA is excellent, it should not be forgotten that the operations made in VBA cannot be undone. For example, after a change in the source table, the automatic update (with VBA) in the pivot table causes a previously entered data to not be recalled, so the best and correct solution is "right click, update" ;))
Cool! I didn’t know those properties tricks! I do wish though that it was built in auto refresh like google sheets has, where it just feels like a formula that refreshes on demand
Yeah, auto refresh would be great for PivotTables with a local source. Can’t see it being possible for external data sources but something like scheduled refreshes like we have with Power BI would be great so the file doesn’t have to be open for the refresh to trigger.
Your tutorials have changed my life! THANK YOU! Especially the project management dashboard! Please help on the VBA - I used the last method in this tutorial. To auto refresh the pivot table. It worked once, and then kept giving me a Run Time Error "1004" thereafter. Id be so grateful for some guidance to resolve this.
Can we set (automatically change) the color of the pivot chart according to the value? (for example, if the bar is below zero, "red", and if the bar is above zero "green" color). Any reference would be appreciated. Thank you. @@MyOnlineTrainingHub
Not with a Pivot Chart, but you can create a regular chart from the PivotTable and then duplicate the series' for the different colours. It's a bit difficult to explain in a comment, but you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, Mynda. Good topic. PT refresh is so frustrating. I almost always use VBA. Mostly b/c my workbooks tend to be “Excel apps” with lots of worksheets, tables, PQs, DAs, PTs, & buttons. “RefreshAll” usually is non-starter b/c we only need to refresh what’s shown on selected worksheet & that worksheet’s hidden helper worksheet (if it exists). So, a button with an assigned macro that refreshes only the PQs & PTs that need to be refreshed does the trick. (I love DAs & use them when appropriate instead of PTs.) All of that said, I like your VBA approach. Very clever. I’ll will be able to use it. Thank you. Off topic comment/question: Would you describe how you use the worksheet & ThisWorkbook code modules? I have used them only for their event procedures. In Your example, I would have used the worksheet module to house the Worksheet_Deactivate event, but I would have put the RefreshAll sub in a standard code module. My projects tend to have lots of code modules. I wonder if your approach would improve my code storage. What are your rules for using the worksheet & workbook modules v. standard code modules?
Hi Jim, You could use a standard code module for the RefreshAll sub, but that would mean creating a new module. By using the already existing ThisWorkbook module, I don’t have to create a separate module just for this piece of code. That’s just my approach here, I feel it’s neater not to create a module in this instance. Mynda
@@MyOnlineTrainingHub Thanks, Mynda. I’m intrigued by your technique & am going to evaluate it more. I write a lot of Excel apps & want to have some consistency in code organization across them for maintenance & updates, but I like the notion of fewer standard code modules & taking advantage of the built-in worksheet & workbook modules.
That is very helpful. i fetch data from a OLAP cube !!! I want to wait until a power query finished refreshes!!! Often facing the issue that file's refresh is stopped in between. Please povide a suggestion
Great video as usual Mynda. Thank you for the tips. Unrelated to this I have a question about Power Query. Can you please make a video about lookup with wildcards in Power Query. Should I use the fuzzy matching or there is another way?
Thank you for the video. Extremely easy to follow and useful. Is there a VBA that allows the same "auto refresh" when the sheets are protected? In my scenario. Both the Source Data Table and PivotTable(s) reside in different worksheets in the same workbook. PivotTables are linked to charts in other protected sheets in the same workbook. The entire workbook is protected but specific cells in the other worksheets are unprotected to allow other users to enter data. I've found some answers online that indicate it is possible but the VBA does not fit our scenario. I'm hoping you can help.
Many thanks for this insightful video. Given that all the settings have been configured in a normal mode, does Auto Refresh PivotTables also work in a read only excel?
Hi Mynda! Great features! Many thanks. Do you know if the Query Properties set to refresh every 15 min will run only with the File opened? Or will it also do the job when the file is closed?
You’re most welcome 😊 if you set refresh properties it doesn’t matter what the source is. The file will only trigger a refresh when the file containing the query is open is the Excel desktop app.
I created Dashboard using power query from table, in dashboard I have 12 pivot tables, when I replaced new data in source pivot table doesn’t auto refresh and also power query, what should I do?
Hi Mandy, Your video are always amazing! I have a question, could be that using pivot from data table from power query we lose some functions in the pivot? I'm tryin to work with formatting table and if I try to highlight in red a column i don't have this option, I can do it only forthe single cell. even the function calculated filed doesn't semme work as normal pivot...
Hi Giuseppe, it sounds like you've added your data to the Data Model upon creating your PivotTable and now you're working with a Power Pivot PivotTable. You can't add calculated columns in the same way with Power Pivot. Instead you use DAX to write measures. More on Power Pivot here: czcams.com/video/TqCsmZ9E0zk/video.html Conditional Formatting should still work though.
This is great, thank you!! I'm using the method starting at 7 min in (using classic pivot tables) and I notice that if I copy a cell in the source data tab, when I go to paste it into another tab, the paste function is not available, as if the copied cell has been cleared from memory. Is there a way around this?
I can't reproduce that issue, Ian. Please post your question and sample Excel file on our forum where we can follow the exact steps you're taking and help you further: www.myonlinetraininghub.com/excel-forum
Thank you Mynda its really helpful but in the code you write for loop inside another for loop, my question is this loop complete as i am working in the excel file but as i know that infinite loop is not allowed and cause problems. But thanks for your effort anyway.
The outer FOR loop works through each sheet in the Active Workbook. The inner FOR loop works through every Pivot Table on the current sheet as selected by the outer FOR loop. This is the way the code must be written in order to refresh every PT on every sheet. It won’t result in an infinite loop.
Hello, I saw your many videos and learned lot of them, Actually I'm looking for dropdown options, could you please let me know how can I create multiple dropdown in single cell. "like category and sub category in single cell"
It's not possible. However, you might find this tutorial on dependent data validation helpful: www.myonlinetraininghub.com/excel-dependent-data-validation
My file only refreshes when the file is open otherwise it doesn't!! I scheduled my refresh. Please assist. I did all 3 types of load discussed in the video.
@@MyOnlineTrainingHub can you please add a small condition also stating how to refresh a pivot using office scripts when a particular cell value changes. Thankyou for acknowledging my comment earlier
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, Thanks for the video. One question, or two. When trying to make a connection only for the PQ, the option to do that is greyed out. Also on inserting a pivot table, I do not have the drop down options as you showed. I am using 365 and just wondered if you any ideas? Thanks for your help.
Hi Darrell, I have a beta version of Excel so the drop down menu you see will come to yours soon. If the connection option is greyed out it implies that you've already closed & loaded the data?? If so, you can right-click the query in the queries and connection pane and change the Load to settings there.
This is a helpful video, but it speaks to the sheer chaos that Excel has become over the past twenty years. Would a simple radio button saying "auto refresh" in the modular dialogues/wizards where you create the pivot table be so difficult for Microsoft to implement? Is refreshing pivot tables REALLY so resource-intensive that stonewalling people from updating them automatically is the most streamlined possible way of doing this? How is it possible for Google Sheets to have seamlessly accomplished this in a browser almost a decade ago? Because of data security agreements at my company, I cannot use MS 365 and am stuck on the 2016 desktop version of Excel without the ability to use the VBA editor. I would do anything to use VBA or get the new array formulas like FILTER(), SPLIT(), and UNIQUE(). I can't stand faffing around with the data model, these ridiculous dialogue boxes, and hitting Alt>A>R>A every 11 seconds like a trained rat. ;_____; Also big shoutout to everyone who has been gaslit in the MS product forums for asking for basic functionality like this.
i am using one of this option for one dashbaord but when i am giving this one to another person then his system it is not working, showing odbc excel driver login failed, how can resolved this and use on different system?
I have a power query that reads in data from a database and is loaded into the excel sheet. From here I then add it to the data model and do some further processing in power pivot. Finally this is then loaded into a pivot tables and pivot charts. I have found that I need to click Refresh All twice to update the pivot tables and charts. In order to fix this should I not have the power queries load into the excel sheet and go straight to power pivot?
May I please ask if auto refreshing every 60mins works even when the file is closed? My query is linked to the folder where new files are added. Your advise is greatly appreciated! Thank you!
@@MyOnlineTrainingHub I have 2 tabs one is for Closed Tickets Dashboard linked to data source 1 and another one is for Open tickets Dashboard linked to data source 2. I have refresh button in 2 tabs. when I click on the refresh button in Closed Tickets dashboard. I want to refresh Data Source1 connection and all the pivot tables driven using that data source 1 only. similarly for other one also.I feel writing each pivot table name in Closed tickets dashboard and refreshing using vba macro is not efficent. Is there any settings for refreshing pivot tables when we refresh the Query in excel.
Hello Mynda! I keep facing one problem, whenever I change a cell value in my data table. the query table kept the old row with old value and add a new row with the new value. I don't know if there is an option to avoid this problem. thank you in advance.
Just to give you extra details, i Have several tables in different sheets, and i create a query to consolidate these tables in one global table and then i create my pivot table based on this global table. But whenever i changed a value on the tables. the query table kept the old value and add new row with the new value. This is gives me incorrect conclusions.
Not sure how your query is set up. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I have a question that will be a huge help if solved! Can you update multiple sheets of pivot tables at once? In my example there are about 50 clients and multiple accounts each and a $ amount for each account. I need to change the data dump every month, so is there a way to change data sources ( might have more rows/ accounts). Additionally every sheet filters by client name and it sums the totals of each account of the client. Right now I have to go to each sheet (40+) and click on the data source to change it.
Sounds like you should be using Power Query to get your data. Then you can replace the file/source Power Query is pointing to and simply Refresh All to get the updated data. Introduction to Power Query: czcams.com/video/L4BuUzccLpo/video.html
@@MyOnlineTrainingHub Ok great thanks for the reply and the great videos as a newbie there much appreciated and Im sure the same for advanced users as well. Appreciate how professionally presented they are. I’ll revisit next week once the dreaded work stock checks finished.
Great video, but I can't VBA part to work. Plse Help. Hi. I get Error 1004 with Source Code Sheet? I downloaded the file and don't understand why it returns an error, although I did exactly as instructed
Hard to say without seeing your file or at least the code. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I am very impressed you answe every single post and within 1 business day, WOW. I will first have to modify the sheet slightly because it references company servers and might return errors on your side. @@MyOnlineTrainingHub
Question: Firstly thanks for the video so I have a spreadsheet which has multiple projects and each project has its own tab. I have a summary sheet which I use indirect look ups to get the data from each sheet, this data is wide so I unpivot with power query straight to a pivot and have a chart of the back of it. I am trying to find a VBA code that every time the numbers in the summary sheet change it updates my pivot table automatically. Issue that I am running into is that I update each individual sheet which feeds into the summary sheet but technically nothing changes in the cells in the summary sheet as the formula stays the same (even thought the numbers are different) Excel isn’t detecting a change as the formula is still the same so how would I go about creating a code for this scenario
Hi Shezz, you should be using Power Query to get the data from the individual sheets, not INDIRECT. You can do the unpivot at the same time. You can set PivotTables to auto-refresh at set frequencies if that helps (www.myonlinetraininghub.com/auto-refresh-pivottables). If not, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
It would be really appreciated if you could help me out. It seems that I am only allowed to use power query to change data column type/format if I do not want to import the data to table. I am attempting to change a data column to a currency type. The default conversion result is an amount with thousand separators and two decimals, and without currency symbol. I need to change it to Japanese yen. Can I add a yen currency mark and remove the decimals?
Hi Li, Power Query isn't the location for currency symbols and the like. Better to do this in the Excel worksheet after closing and loading the query. The currency data type in Power Query is not intended for formatting.
Many thanks for taking the time looking at my question. The number of columns may vary after each load and we have to format each column after each load. For regular data sources we can do the formatting on its model column. Unfortunately, this is not a choice for power query (create connection only)
You can either write a macro/script to open the file and refresh it, then close it again. Excel cannot refresh while closed. Alternatively, you could write a macro that triggers the refresh on closing the file.
Seems like this video over-promises, in that the auto-refresh options are not immediate. Only the last segment gives an immediate solution and that is 'with VBA!'.
Not sure what you mean, David. If you set it to refresh every 2 minutes automatically, then that's automatic. That is, you're not having to manually click anything to update your reports. It all depends on where your data is coming from as to the method you should use.
Hi, Mynda! Thank you for demonstrating and detailing all these options. I learned a couple of things and helped me revisit a few concepts.
That issue (around min 4:00) of having to click refresh all twice to get the pivot table updated after the query table, has an easy solution: on the query properties unchecked "enable background refresh". Having this check mark put on can cause a couple of issues; and having to click refresh all twice is one of them.
Thanks, Celia! That's awesome!
Well not in 100% of cases.
@@mirrrvelll5164 can you explain what you mean?
@@mirrrvelll5164 Kindly explain a scenario where this wonderful tip by Celia does not work. It just solved my headache
WOW!! You just saved my hours of headache. I have tested your tip and it works wonders!!
Thank you!! I have been wrestling with the "PQ to Table to PT" scenario and wondering why the refreshes are not dependable or require multiple refreshes. First time to hear this information. 👍
Glad it was helpful!
Mynda, thank you for the great explanation.
Even though the automatic update feature with VBA is excellent, it should not be forgotten that the operations made in VBA cannot be undone. For example, after a change in the source table, the automatic update (with VBA) in the pivot table causes a previously entered data to not be recalled, so the best and correct solution is "right click, update" ;))
Hi Emre, not sure what you mean by “causes a previously entered data to not be recalled”. Mynda
@@MyOnlineTrainingHub Undo is not available vith VBA
Ok. I suppose for me this is a low priority because I rarely undo a PivotTable refresh.
@@MyOnlineTrainingHub You’re expert Mynda, we’re not! But, I will find a solution about it and I’m asking this questions to find a key 🔑
Cool! I didn’t know those properties tricks! I do wish though that it was built in auto refresh like google sheets has, where it just feels like a formula that refreshes on demand
Yeah, auto refresh would be great for PivotTables with a local source. Can’t see it being possible for external data sources but something like scheduled refreshes like we have with Power BI would be great so the file doesn’t have to be open for the refresh to trigger.
nice, I was looking for it ... was badly stuck with one of my sheet some time back... thanks... starting watching now...!!!
Hope you found it helpful, Syed!
Your tutorials have changed my life! THANK YOU! Especially the project management dashboard! Please help on the VBA - I used the last method in this tutorial. To auto refresh the pivot table. It worked once, and then kept giving me a Run Time Error "1004" thereafter. Id be so grateful for some guidance to resolve this.
Glad I could help! Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks Mynda for this very useful video! You always bring something innovative to help us solve real world problems!
You are most welcome, Vijay!
Wonderful. I spent 2 days exploring autorefresh the table
Glad it was helpful!
Can we set (automatically change) the color of the pivot chart according to the value? (for example, if the bar is below zero, "red", and if the bar is above zero "green" color). Any reference would be appreciated. Thank you. @@MyOnlineTrainingHub
Not with a Pivot Chart, but you can create a regular chart from the PivotTable and then duplicate the series' for the different colours. It's a bit difficult to explain in a comment, but you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank You @@MyOnlineTrainingHub
Amazing vba code for instant updating pivot tables according to source. Thank you
Glad it was helpful 😊
Hi Mynda. Great tips for updating Pivot Tables and Queries! Thanks for sharing :)) Thumbs up!!
Cheers, Wayne!
Using in my workbook combination of PowerQuery + PivotTabe + function GETPIVOTDATA was definetly a big mistake. Thanks for the explenation.
Glad it helped!
It's very useful ,indeed! Thank you Mynda!
My pleasure, Wilson 😊
Hi, Mynda. Good topic. PT refresh is so frustrating. I almost always use VBA. Mostly b/c my workbooks tend to be “Excel apps” with lots of worksheets, tables, PQs, DAs, PTs, & buttons. “RefreshAll” usually is non-starter b/c we only need to refresh what’s shown on selected worksheet & that worksheet’s hidden helper worksheet (if it exists). So, a button with an assigned macro that refreshes only the PQs & PTs that need to be refreshed does the trick. (I love DAs & use them when appropriate instead of PTs.) All of that said, I like your VBA approach. Very clever. I’ll will be able to use it. Thank you.
Off topic comment/question: Would you describe how you use the worksheet & ThisWorkbook code modules? I have used them only for their event procedures. In Your example, I would have used the worksheet module to house the Worksheet_Deactivate event, but I would have put the RefreshAll sub in a standard code module. My projects tend to have lots of code modules. I wonder if your approach would improve my code storage. What are your rules for using the worksheet & workbook modules v. standard code modules?
Hi Jim, You could use a standard code module for the RefreshAll sub, but that would mean creating a new module. By using the already existing ThisWorkbook module, I don’t have to create a separate module just for this piece of code. That’s just my approach here, I feel it’s neater not to create a module in this instance. Mynda
@@MyOnlineTrainingHub Thanks, Mynda. I’m intrigued by your technique & am going to evaluate it more. I write a lot of Excel apps & want to have some consistency in code organization across them for maintenance & updates, but I like the notion of fewer standard code modules & taking advantage of the built-in worksheet & workbook modules.
Simply Supperb collated every doubt in one video with practical example too ❤
Lots of love from India 🇮🇳
Happy to hear that!
Thanks for sharing your knowledge....amazing
My pleasure 😊
Excellent practical tutorial as always
Glad you liked it 😊
Auto Refresh PivotTables & Queries - without VBA!, very good, but what about with the sheet protect? Tks in advance Good Year 2024
You have to set the sheet protection to allow PivotTables.
thanks for sample
i can practices with your sample file
by watching youtube and practice
thanks
Glad it was helpful 😊
Thanks Mynda! Very helpful
Great to hear, Chris 😊
Extremely useful tips!
Great to hear 😊
Very informative. Thanks.
Great to hear 😊
thank you so much
Welcome 😊
Hi Mynda!Really Helpful Tutorial..Thank You :)
Great to hear, Darryl!
That is very helpful. i fetch data from a OLAP cube !!! I want to wait until a power query finished refreshes!!!
Often facing the issue that file's refresh is stopped in between. Please povide a suggestion
Love your videos it helps a lot.
Great to hear 😊
Great video as usual Mynda. Thank you for the tips. Unrelated to this I have a question about Power Query. Can you please make a video about lookup with wildcards in Power Query. Should I use the fuzzy matching or there is another way?
Thank you! Will add it to the list.
This was useful, thanks a lot!
Glad to hear that!
Excelente contenido Mynda! no sabía estas propiedades, muchas gracias!
Gracias, Daniel 😊
Thanks Mynda!!
My pleasure 😊
Thank you for the video. Extremely easy to follow and useful. Is there a VBA that allows the same "auto refresh" when the sheets are protected? In my scenario. Both the Source Data Table and PivotTable(s) reside in different worksheets in the same workbook. PivotTables are linked to charts in other protected sheets in the same workbook. The entire workbook is protected but specific cells in the other worksheets are unprotected to allow other users to enter data. I've found some answers online that indicate it is possible but the VBA does not fit our scenario. I'm hoping you can help.
Glad it was helpful. You would have to write some VBA to unprotect the sheets, run the refresh, then protect the sheets again.
Thanks
Welcome :-)
Great video!!
Thanks so much 🙏
Many thanks for this insightful video. Given that all the settings have been configured in a normal mode, does Auto Refresh PivotTables also work in a read only excel?
Should do, but easy enough for you to test.
Hi Mynda! Great features! Many thanks. Do you know if the Query Properties set to refresh every 15 min will run only with the File opened? Or will it also do the job when the file is closed?
Glad you liked it :-) They only run when the file is open.
Thanks Mynda. If my file is saved in Sharepoint, would it impact the auto refresh?
You’re most welcome 😊 if you set refresh properties it doesn’t matter what the source is. The file will only trigger a refresh when the file containing the query is open is the Excel desktop app.
I created Dashboard using power query from table, in dashboard I have 12 pivot tables, when I replaced new data in source pivot table doesn’t auto refresh and also power query, what should I do?
Nice video. 👍
Thank you 😊
Hi Mandy, Your video are always amazing! I have a question, could be that using pivot from data table from power query we lose some functions in the pivot?
I'm tryin to work with formatting table and if I try to highlight in red a column i don't have this option, I can do it only forthe single cell. even the function calculated filed doesn't semme work as normal pivot...
Hi Giuseppe, it sounds like you've added your data to the Data Model upon creating your PivotTable and now you're working with a Power Pivot PivotTable. You can't add calculated columns in the same way with Power Pivot. Instead you use DAX to write measures. More on Power Pivot here: czcams.com/video/TqCsmZ9E0zk/video.html
Conditional Formatting should still work though.
This is great, thank you!!
I'm using the method starting at 7 min in (using classic pivot tables) and I notice that if I copy a cell in the source data tab, when I go to paste it into another tab, the paste function is not available, as if the copied cell has been cleared from memory. Is there a way around this?
I can't reproduce that issue, Ian. Please post your question and sample Excel file on our forum where we can follow the exact steps you're taking and help you further: www.myonlinetraininghub.com/excel-forum
Thank you Mynda its really helpful but in the code you write for loop inside another for loop, my question is this loop complete as i am working in the excel file but as i know that infinite loop is not allowed and cause problems. But thanks for your effort anyway.
The outer FOR loop works through each sheet in the Active Workbook. The inner FOR loop works through every Pivot Table on the current sheet as selected by the outer FOR loop.
This is the way the code must be written in order to refresh every PT on every sheet. It won’t result in an infinite loop.
@@MyOnlineTrainingHub thank you Mayanda
Hello,
I saw your many videos and learned lot of them,
Actually I'm looking for dropdown options, could you please let me know how can I create multiple dropdown in single cell.
"like category and sub category in single cell"
How to create dropdown under dropdown?
It's not possible. However, you might find this tutorial on dependent data validation helpful: www.myonlinetraininghub.com/excel-dependent-data-validation
My file only refreshes when the file is open otherwise it doesn't!! I scheduled my refresh. Please assist. I did all 3 types of load discussed in the video.
Excel files do need to be open for the PivotTable to refresh. They cannot refresh when closed.
Can we also refresh queries in to another workbook?
Yes 👍😊
Hi, Mynda.. Thank you for your valuable video. I have one question. Once applied VBA code cannot apply UNDO and REDO. Any solution for this? Please
My pleasure. There's no solution to VBA not visible in the undo stack, sorry.
@@MyOnlineTrainingHub thank you on your valuable reply
Hello, How to refresh using office scripts.
Great idea for a video topic 🙏
@@MyOnlineTrainingHub can you please add a small condition also stating how to refresh a pivot using office scripts when a particular cell value changes. Thankyou for acknowledging my comment earlier
@@MyOnlineTrainingHubhi, is there any video uploaded for this topic.much appreciated!
when i make the table dynamic and go to power query and i press close & load to ( only create connection) dont use why ?
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Is there any function to know second lowest value in row, but value are in alternate column
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, Thanks for the video. One question, or two. When trying to make a connection only for the PQ, the option to do that is greyed out. Also on inserting a pivot table, I do not have the drop down options as you showed. I am using 365 and just wondered if you any ideas? Thanks for your help.
Hi Darrell, I have a beta version of Excel so the drop down menu you see will come to yours soon. If the connection option is greyed out it implies that you've already closed & loaded the data?? If so, you can right-click the query in the queries and connection pane and change the Load to settings there.
@@MyOnlineTrainingHub refresh only works when i have the file open otherwise it doesn't. Why is that? Please assist. Thank you.
This is a helpful video, but it speaks to the sheer chaos that Excel has become over the past twenty years. Would a simple radio button saying "auto refresh" in the modular dialogues/wizards where you create the pivot table be so difficult for Microsoft to implement? Is refreshing pivot tables REALLY so resource-intensive that stonewalling people from updating them automatically is the most streamlined possible way of doing this? How is it possible for Google Sheets to have seamlessly accomplished this in a browser almost a decade ago? Because of data security agreements at my company, I cannot use MS 365 and am stuck on the 2016 desktop version of Excel without the ability to use the VBA editor. I would do anything to use VBA or get the new array formulas like FILTER(), SPLIT(), and UNIQUE(). I can't stand faffing around with the data model, these ridiculous dialogue boxes, and hitting Alt>A>R>A every 11 seconds like a trained rat. ;_____; Also big shoutout to everyone who has been gaslit in the MS product forums for asking for basic functionality like this.
Can the queries be auto refreshed when source data changes?
Of course :-)
i am using one of this option for one dashbaord but when i am giving this one to another person then his system it is not working, showing odbc excel driver login failed, how can resolved this and use on different system?
Sounds like the other user doesn't have ODBC drivers installed and possible doesn't have access to the source data file location.
I have a power query that reads in data from a database and is loaded into the excel sheet. From here I then add it to the data model and do some further processing in power pivot. Finally this is then loaded into a pivot tables and pivot charts. I have found that I need to click Refresh All twice to update the pivot tables and charts. In order to fix this should I not have the power queries load into the excel sheet and go straight to power pivot?
Correct 👍 load direct from power query to the data model. This will also reduce your file size.
Correct 👍 load direct from power query to the data model. This will also reduce your file size.
What about XL files in OneDrive? Can they update queries without being physically open in excel?
Yes :-)
can the query be auto refresh when file is closed?
No.
May I please ask if auto refreshing every 60mins works even when the file is closed? My query is linked to the folder where new files are added. Your advise is greatly appreciated! Thank you!
No, it only works when the file is open.
Q. Is there a way to refresh pivot tables automatically when we refreshed the query connection.
Refresh All refreshes both the query connection and the PivotTables.
@@MyOnlineTrainingHub I have 2 tabs one is for Closed Tickets Dashboard linked to data source 1 and another one is for Open tickets Dashboard linked to data source 2. I have refresh button in 2 tabs. when I click on the refresh button in Closed Tickets dashboard. I want to refresh Data Source1 connection and all the pivot tables driven using that data source 1 only. similarly for other one also.I feel writing each pivot table name in Closed tickets dashboard and refreshing using vba macro is not efficent. Is there any settings for refreshing pivot tables when we refresh the Query in excel.
I can't load queries and connections. It's greyed out all the time. I'm using excel on Macbook M!
It's not available on that version of Excel for Mac.
Hello Mynda! I keep facing one problem, whenever I change a cell value in my data table. the query table kept the old row with old value and add a new row with the new value. I don't know if there is an option to avoid this problem. thank you in advance.
Just to give you extra details, i Have several tables in different sheets, and i create a query to consolidate these tables in one global table and then i create my pivot table based on this global table. But whenever i changed a value on the tables. the query table kept the old value and add new row with the new value. This is gives me incorrect conclusions.
Not sure how your query is set up. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I have a question that will be a huge help if solved! Can you update multiple sheets of pivot tables at once? In my example there are about 50 clients and multiple accounts each and a $ amount for each account. I need to change the data dump every month, so is there a way to change data sources ( might have more rows/ accounts). Additionally every sheet filters by client name and it sums the totals of each account of the client. Right now I have to go to each sheet (40+) and click on the data source to change it.
Sounds like you should be using Power Query to get your data. Then you can replace the file/source Power Query is pointing to and simply Refresh All to get the updated data. Introduction to Power Query: czcams.com/video/L4BuUzccLpo/video.html
@@MyOnlineTrainingHub I think that is what I need to use. Thank you the video was very informative. I will see if I can figure it out!
If you're interested in learning Power Query, please consider my course: www.myonlinetraininghub.com/excel-power-query-course
Hi, does this not work if you have different sources linked to diff pivot tables....will this only work on a work book with the one source? Thanks
If you used Power Query to get the data then you should be able to set the refresh for each query/each data source.
@@MyOnlineTrainingHub Ok great thanks for the reply and the great videos as a newbie there much appreciated and Im sure the same for advanced users as well. Appreciate how professionally presented they are. I’ll revisit next week once the dreaded work stock checks finished.
Hi, can i know how to refresh data using power automate cloud?
I don’t have any videos on that, sorry.
Great video, but I can't VBA part to work. Plse Help. Hi. I get Error 1004 with Source Code Sheet? I downloaded the file and don't understand why it returns an error, although I did exactly as instructed
Hard to say without seeing your file or at least the code. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I am very impressed you answe every single post and within 1 business day, WOW. I will first have to modify the sheet slightly because it references company servers and might return errors on your side. @@MyOnlineTrainingHub
how can I refresh the excel pivot if the file is closed
You can't. The file must be open for any refreshing to take place.
Question:
Firstly thanks for the video so I have a spreadsheet which has multiple projects and each project has its own tab.
I have a summary sheet which I use indirect look ups to get the data from each sheet, this data is wide so I unpivot with power query straight to a pivot and have a chart of the back of it.
I am trying to find a VBA code that every time the numbers in the summary sheet change it updates my pivot table automatically.
Issue that I am running into is that I update each individual sheet which feeds into the summary sheet but technically nothing changes in the cells in the summary sheet as the formula stays the same (even thought the numbers are different)
Excel isn’t detecting a change as the formula is still the same so how would I go about creating a code for this scenario
Hi Shezz, you should be using Power Query to get the data from the individual sheets, not INDIRECT. You can do the unpivot at the same time. You can set PivotTables to auto-refresh at set frequencies if that helps (www.myonlinetraininghub.com/auto-refresh-pivottables). If not, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub thank you I will try this - appreciate the response
I need tor efresh a table when the infirmation in other sheet is updated instanly without hitting refresh HELPPPPPPPPPPPPPP!!!
I believe this is covered in this video.
It would be really appreciated if you could help me out. It seems that I am only allowed to use power query to change data column type/format if I do not want to import the data to table. I am attempting to change a data column to a currency type. The default conversion result is an amount with thousand separators and two decimals, and without currency symbol. I need to change it to Japanese yen. Can I add a yen currency mark and remove the decimals?
Hi Li, Power Query isn't the location for currency symbols and the like. Better to do this in the Excel worksheet after closing and loading the query. The currency data type in Power Query is not intended for formatting.
Many thanks for taking the time looking at my question. The number of columns may vary after each load and we have to format each column after each load. For regular data sources we can do the formatting on its model column. Unfortunately, this is not a choice for power query (create connection only)
Yes, unfortunately that's the way it works.
Pleasew which excel version are you using?
Excel for Microsoft 365
Hi, please, is it possible? I want to design a warehouse program that has 6 store branches Belize
Possibly 🤷♀️
Does it refresh as well when the files are closed?
No, it only refreshes on opening.
@@MyOnlineTrainingHub does anything to make it refresh when the files are closed?
You can either write a macro/script to open the file and refresh it, then close it again. Excel cannot refresh while closed. Alternatively, you could write a macro that triggers the refresh on closing the file.
@@MyOnlineTrainingHub ah awesome.
Will find it a way to this!
Seems like this video over-promises, in that the auto-refresh options are not immediate. Only the last segment gives an immediate solution and that is 'with VBA!'.
Not sure what you mean, David. If you set it to refresh every 2 minutes automatically, then that's automatic. That is, you're not having to manually click anything to update your reports. It all depends on where your data is coming from as to the method you should use.
👍👍👍