It's very useful ,indeed! Thank you Mynda!

@MyOnlineTrainingHub

3 жыл бұрын

My pleasure, Wilson 😊

Thanks Mynda for this very useful video! You always bring something innovative to help us solve real world problems!

@MyOnlineTrainingHub

3 жыл бұрын

You are most welcome, Vijay!

Hi Mynda. Great tips for updating Pivot Tables and Queries! Thanks for sharing :)) Thumbs up!!

@MyOnlineTrainingHub

3 жыл бұрын

Cheers, Wayne!

Excellent practical tutorial as always

@MyOnlineTrainingHub

3 жыл бұрын

Glad you liked it 😊

Thanks Mynda! Very helpful

@MyOnlineTrainingHub

3 жыл бұрын

Great to hear, Chris 😊

Extremely useful tips!

@MyOnlineTrainingHub

3 жыл бұрын

Great to hear 😊

Hi Mynda!Really Helpful Tutorial..Thank You :)

@MyOnlineTrainingHub

3 жыл бұрын

Great to hear, Darryl!

Love your videos it helps a lot.

@MyOnlineTrainingHub

3 жыл бұрын

Great to hear 😊

nice, I was looking for it ... was badly stuck with one of my sheet some time back... thanks... starting watching now...!!!

@MyOnlineTrainingHub

3 жыл бұрын

Hope you found it helpful, Syed!

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

@MyOnlineTrainingHub

3 жыл бұрын

Glad it was helpful!

This was useful, thanks a lot!

@MyOnlineTrainingHub

2 жыл бұрын

Glad to hear that!

Very informative. Thanks.

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear 😊

Amazing vba code for instant updating pivot tables according to source. Thank you

@MyOnlineTrainingHub

Жыл бұрын

Glad it was helpful 😊

Thanks Mynda!!

@MyOnlineTrainingHub

3 жыл бұрын

My pleasure 😊

Great video!!

@MyOnlineTrainingHub

Жыл бұрын

Thanks so much 🙏

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" ;))

@MyOnlineTrainingHub

3 жыл бұрын

Hi Emre, not sure what you mean by “causes a previously entered data to not be recalled”. Mynda

@teoxengineer

3 жыл бұрын

@@MyOnlineTrainingHub Undo is not available vith VBA

@MyOnlineTrainingHub

3 жыл бұрын

Ok. I suppose for me this is a low priority because I rarely undo a PivotTable refresh.

@teoxengineer

3 жыл бұрын

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

Thanks for sharing your knowledge....amazing

@MyOnlineTrainingHub

2 жыл бұрын

My pleasure 😊

Excelente contenido Mynda! no sabía estas propiedades, muchas gracias!

@MyOnlineTrainingHub

3 жыл бұрын

Gracias, Daniel 😊

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.

@MyOnlineTrainingHub

3 жыл бұрын

Thanks, Celia! That's awesome!

@mirrrvelll5164

3 жыл бұрын

Well not in 100% of cases.

@CeliaAlvesSolveExcel

3 жыл бұрын

@@mirrrvelll5164 can you explain what you mean?

@apamwamba

3 жыл бұрын

@@mirrrvelll5164 Kindly explain a scenario where this wonderful tip by Celia does not work. It just solved my headache

@apamwamba

3 жыл бұрын

WOW!! You just saved my hours of headache. I have tested your tip and it works wonders!!

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

@MyOnlineTrainingHub

3 жыл бұрын

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.

Simply Supperb collated every doubt in one video with practical example too ❤ Lots of love from India 🇮🇳

@MyOnlineTrainingHub

11 ай бұрын

Happy to hear that!

Nice video. 👍

@MyOnlineTrainingHub

3 жыл бұрын

Thank you 😊

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.

@MyOnlineTrainingHub

2 жыл бұрын

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

thank you so much

@MyOnlineTrainingHub

4 ай бұрын

Welcome 😊

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?

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?

@MyOnlineTrainingHub

3 жыл бұрын

Thank you! Will add it to the list.

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?

@MyOnlineTrainingHub

3 жыл бұрын

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

@jimfitch

3 жыл бұрын

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

Wonderful. I spent 2 days exploring autorefresh the table

@MyOnlineTrainingHub

4 ай бұрын

Glad it was helpful!

@NareshSen

4 ай бұрын

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

@MyOnlineTrainingHub

4 ай бұрын

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

@NareshSen

4 ай бұрын

Thank You @@MyOnlineTrainingHub

thanks for sample i can practices with your sample file by watching youtube and practice thanks

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful 😊

Thanks

@MyOnlineTrainingHub

2 жыл бұрын

Welcome :-)

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.

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful. You would have to write some VBA to unprotect the sheets, run the refresh, then protect the sheets again.

Thanks Mynda. If my file is saved in Sharepoint, would it impact the auto refresh?

@MyOnlineTrainingHub

3 жыл бұрын

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.

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?

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it :-) They only run when the file is open.

Using in my workbook combination of PowerQuery + PivotTabe + function GETPIVOTDATA was definetly a big mistake. Thanks for the explenation.

@MyOnlineTrainingHub

2 жыл бұрын

Glad it helped!

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?

@MyOnlineTrainingHub

3 жыл бұрын

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

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

@MyOnlineTrainingHub

2 жыл бұрын

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: kzread.info/dash/bejne/hqV31c-TaaiWqs4.html Conditional Formatting should still work though.

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

2 жыл бұрын

No, it only works when the file is open.

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?

@MyOnlineTrainingHub

2 жыл бұрын

Should do, but easy enough for you to test.

👍👍👍

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.

@MyOnlineTrainingHub

3 жыл бұрын

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.

@NirajahJegath

4 ай бұрын

@@MyOnlineTrainingHub refresh only works when i have the file open otherwise it doesn't. Why is that? Please assist. Thank you.

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

@MyOnlineTrainingHub

Жыл бұрын

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

@goonershezz9714

Жыл бұрын

@@MyOnlineTrainingHub thank you I will try this - appreciate the response

when i make the table dynamic and go to power query and i press close & load to ( only create connection) dont use why ?

@MyOnlineTrainingHub

Жыл бұрын

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

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?

@MyOnlineTrainingHub

Жыл бұрын

Sounds like the other user doesn't have ODBC drivers installed and possible doesn't have access to the source data file location.

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.

@khadijajabrikazi2176

Жыл бұрын

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.

@MyOnlineTrainingHub

Жыл бұрын

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

@MyOnlineTrainingHub

Жыл бұрын

Correct 👍 load direct from power query to the data model. This will also reduce your file size.

@MyOnlineTrainingHub

Жыл бұрын

Correct 👍 load direct from power query to the data model. This will also reduce your file size.

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

@MyOnlineTrainingHub

10 ай бұрын

My pleasure. There's no solution to VBA not visible in the undo stack, sorry.

@aselakarunarathne5024

10 ай бұрын

@@MyOnlineTrainingHub thank you on your valuable reply

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

4 ай бұрын

Excel files do need to be open for the PivotTable to refresh. They cannot refresh when closed.

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

@MyOnlineTrainingHub

Жыл бұрын

If you used Power Query to get the data then you should be able to set the refresh for each query/each data source.

@garethmorrall1047

Жыл бұрын

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

Is there any function to know second lowest value in row, but value are in alternate column

@MyOnlineTrainingHub

3 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

What about XL files in OneDrive? Can they update queries without being physically open in excel?

@MyOnlineTrainingHub

2 жыл бұрын

Yes :-)

Auto Refresh PivotTables & Queries - without VBA!, very good, but what about with the sheet protect? Tks in advance Good Year 2024

@MyOnlineTrainingHub

6 ай бұрын

You have to set the sheet protection to allow PivotTables.

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?

@MyOnlineTrainingHub

2 жыл бұрын

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.

@ligang2683

2 жыл бұрын

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)

@MyOnlineTrainingHub

2 жыл бұрын

Yes, unfortunately that's the way it works.

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.

@MyOnlineTrainingHub

3 жыл бұрын

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.

@DiamondRock2

3 жыл бұрын

@@MyOnlineTrainingHub thank you Mayanda

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.

@MyOnlineTrainingHub

2 жыл бұрын

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: kzread.info/dash/bejne/fmh217ezk8ayoNI.html

@vicentecabrera9245

2 жыл бұрын

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

@MyOnlineTrainingHub

2 жыл бұрын

If you're interested in learning Power Query, please consider my course: www.myonlinetraininghub.com/excel-power-query-course

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"

@mohammedtayyab5838

3 жыл бұрын

How to create dropdown under dropdown?

@MyOnlineTrainingHub

3 жыл бұрын

It's not possible. However, you might find this tutorial on dependent data validation helpful: www.myonlinetraininghub.com/excel-dependent-data-validation

Hi, can i know how to refresh data using power automate cloud?

@MyOnlineTrainingHub

9 ай бұрын

I don’t have any videos on that, sorry.

Hi, please, is it possible? I want to design a warehouse program that has 6 store branches Belize

@MyOnlineTrainingHub

3 жыл бұрын

Possibly 🤷‍♀️

I need tor efresh a table when the infirmation in other sheet is updated instanly without hitting refresh HELPPPPPPPPPPPPPP!!!

@MyOnlineTrainingHub

3 жыл бұрын

I believe this is covered in this video.

Q. Is there a way to refresh pivot tables automatically when we refreshed the query connection.

@MyOnlineTrainingHub

2 жыл бұрын

Refresh All refreshes both the query connection and the PivotTables.

@sadinenim5360

2 жыл бұрын

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

can the query be auto refresh when file is closed?

@MyOnlineTrainingHub

2 жыл бұрын

No.

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

@MyOnlineTrainingHub

7 ай бұрын

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

@hanzer99

7 ай бұрын

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

I can't load queries and connections. It's greyed out all the time. I'm using excel on Macbook M!

@MyOnlineTrainingHub

2 жыл бұрын

It's not available on that version of Excel for Mac.

Can the queries be auto refreshed when source data changes?

@MyOnlineTrainingHub

2 жыл бұрын

Of course :-)

Pleasew which excel version are you using?

@MyOnlineTrainingHub

3 жыл бұрын

Excel for Microsoft 365

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 we also refresh queries in to another workbook?

@MyOnlineTrainingHub

8 ай бұрын

Yes 👍😊

how can I refresh the excel pivot if the file is closed

@MyOnlineTrainingHub

Жыл бұрын

You can't. The file must be open for any refreshing to take place.

Hello, How to refresh using office scripts.

@MyOnlineTrainingHub

Жыл бұрын

Great idea for a video topic 🙏

@excel-k-sir

Жыл бұрын

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

@MamawaniAdam_9664

9 ай бұрын

@@MyOnlineTrainingHubhi, is there any video uploaded for this topic.much appreciated!

Does it refresh as well when the files are closed?

@MyOnlineTrainingHub

3 ай бұрын

No, it only refreshes on opening.

@Otisawide

3 ай бұрын

@@MyOnlineTrainingHub does anything to make it refresh when the files are closed?

@MyOnlineTrainingHub

3 ай бұрын

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.

@Otisawide

3 ай бұрын

@@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!'.

@MyOnlineTrainingHub

Жыл бұрын

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.