Auto-Refresh Pivot Tables and Pivot Charts when Source Data Changes | Ultimate Excel Dashboard Ep. 4

[@CoinsTokens]

You really are a natural teacher. Calm, well presented, and great content. Learned a lot already

[@theofficelab]

Happy to hear that! 😊

[@bhanugoyal4243]

Best tutorial ever for making dashboards using excel.

[@datnguyenp]

Never knew that my life needed something like this...

[@vishalkhatri9856]

Very Informative and amazing video series. Thank you for taking time and making such videos. It is really exciting and I am in love with Microsoft Excel now.

[@theofficelab]

Check out my new website excelfind.com for the Free Version of the Dashboard 🚀 ⬇ FULL TUTORIAL SERIES PLAYLIST [Ep. 1] Basic Dashboard ► kzbin.info/www/bejne/mXzOiaevn9GYjpY [Ep. 2] Background & Tile Design ► kzbin.info/www/bejne/hJeoonaam9Flac0 [Ep. 3] Modern Slicer Design ► kzbin.info/www/bejne/eZOnhXmcpMqDkLs [Ep. 4] Auto-Refresh Pivot Charts ► kzbin.info/www/bejne/jpXUaIZ7Yt9gd9k [Ep. 5] Interactive Info Button ► kzbin.info/www/bejne/iIu6XqmHh9djpLs [Ep. 6] Interactive Dashboard Tabs ► kzbin.info/www/bejne/aKKxZp2Lnq-Kg7s [Ep. 7] Interactive Settings Menu ► kzbin.info/www/bejne/nHSpeKSgabaWgLc

[@shannonhodges14]

I had no idea this was possible. Thanks for sharing these tips!

[@Itsnathlncnt]

Hi! How did you change the color of the Multi Select and the Clear Filter? I already run through to all of the training but I can't seem to find the part where you also change the colors of it. But really thanks for all these videos, You're amazing in every way! Keep up!

[@BigTimbo77]

Excellent series of videos, have really learnt alot

[@johnlouieconstantino6247]

This is very helpful. I learned a lot from your videos. Looking forward for your uploads in the future.

[@PartyAT209]

This is great, thanks for sharing! One question. For some reason when I delete the old data and paste in the updated information I lose all formatting in the dashboard. Is there a way to avoid having to redo it each time?

[@osamababiker3901]

You are the best one

[@alfsilva]

Amazing video. Very very informative. I will try to record a macro and find out what is going to be different from your code.

[@luxifer00]

Keep it up, I love your tutorials. :)

[@theofficelab]

Thanks a lot! I am happy you like them 😊

[@JosePerez-wz2ge]

This is great information and I see the code works.. but only to update the information already there. Could you also describe how to automatically add new points in the pivot chart? For example, you have January thru July on your data sheet. If you add August, or November... how can I make those new points add into the current chart? Thank you for a good lesson.

[@labbyrinthsalvador]

Cool. It works!!! Thanks for the tutorial! 😊

[@OnurKader-o6k]

Hi first of all thanks for the content. If the dashboard and the source data are on different excel files, is it still possible to do this? If so, how would one go about doing that?

[@DCHAWADA07]

Awsome..but I wish you could show your final version in today's episode itself , I m waiting since days

[@theofficelab]

Hi Sir, I understand and I am sorry for the wait. The remaining tutorials for the tabs and the settings area will be coming within the next two weeks. It takes some time to produce these videos, especially the settings area video is a huge tutorial and I want to ensure the quality of it. I hope for your understanding. It will be worth the wait 😊

[@manjunathk1509]

Simply superb

[@theofficelab]

Thanks, Bro 😊

[@gugumach]

Fantastic presentation. I have been following your channel for the last 2 weeks and I have learned so much. Great job. I have just one question, I have office for Mac 2019, when I did the VBA (the first option), the "Undo" and "Redo" function of excel was deactivated. Since I am very new to this, just checking to see if I did something wrong. Your work is phenomenal and thanks for putting the effort and time to make such a hight quality videos.

[@theofficelab]

Hi Gustavo, thanks for your fantastic feedback 😊 Regarding your question, you didn't do anything wrong. That's a general issue coming with macros. Once a macro/ VBA code has been executed, the Undo Stack (where all the previous actions are stored) is reset. That is something many people hope Microsoft will change in the future.

[@3PJPriest]

Awesome work! On a side note - your message boxes have a different look and animation from MS's stock software. How did you do that?

[@theofficelab]

Thank you, Sir 😊 Regarding the message box: That's just the Mac OS native look of these popup windows. Doesn't have much to do with Excel itself.

[@MrDeejaydrake]

Simply amazing

[@JuanCruz-qd1tw]

Great video! Thank you very much. I got one question. What if you want to add data to the file instead of changing existing data? How do you make sure the pivot tables refresh automatically when you add this new data, and how do you make the pivot tables actually "grab" this new range of data?

[@theofficelab]

Hi Juan. Thanks for your question 😊 The key is to put your source data into a table and, when creating the pivot tables, reference this table (via its name) instead of the cell range. Because then, once you add new data below your existing data, this data will be automatically included into the table and if you have an auto-refresh VBA script in place the pivot tables and pivot charts should display the additional data instantly.

[@JuanCruz-qd1tw]

@@theofficelab Great! Thanks for the reply! Your videos are amazing

[@theofficelab]

@@JuanCruz-qd1tw Thank you 😊

[@sumer2003]

simple and effective. nice.

[@theofficelab]

Thank you, Sir 😊 I am glad you like it!

[@riteshraj5659]

Thanks Sir

[@alexroch4]

Is there a way to select other sheet after running the code from this video?

[@zakbergmann7650]

Is there a way that I can call a sub procedure every time a slicer is used?

[@aleenarose3]

on adding macro code to refresh the workbook and inserting a new data, it ungroups the columns used in the pivot table. do you know how to fix this issue?

[@schtefel]

Fehler beim Kompilieren: Variable nicht definiert If I define the variable then it works: => Dim pc as Variant I have recognized this in other tutorials on youtube. Why do I have to define the variable, but you and others don't need to and it works properly??

[@dom139]

If you have "Option Explicit" enabled then you must define a variable before using it within your code. Without "Option Explicit" any undefined variables are defined as Variant by default.

[@schtefel]

@@dom139 thank you very much, super helpful. Now I finally know what "Option Explicit" means. It always pops up automatically when I open a new module for instance.

[@rschultz7141]

thanks. I got a runtime error. I think its because I have a slicer on a different sheet than the source data. This VBA works if I locate the code on the source data sheet. But I want the update to occur when the slicer is changed on another sheet. When I add the code on this sheet, I get the error. I have a keyboard macro "refreshall" that works but refreshall from VBA gives a runtime error. Any suggestions?

[@laurenwilliams5006]

How would you be able to make Option 2 work when there are 2 data sheets? I am running into this problem. I went into both data sheets and used the refreshall code, but only one will work, or not work at all.

[@abuena73]

How to refresh pivot table and chart if the source data is coming from another xlsx file..? Appreciate if you could help me.

[@noel1111]

Hi Sir. If data is added frequently, then the pivot chart would become crowded. How to make a limit or date range in the chart?

[@dr.chantiyadav7665]

After applying the first code , while entering new data the undo option is not working . Please help

[@ethanpadden3736]

FYI if you have your Pivot Table and Source Data on the same page you will have to use this: Application.EnableEvents = False ThisWorkbook.RefreshAll Application.EnableEvents = True

[@akhileshravikumar6910]

Thanks you so much for this piece of code!

[@Name-rb3us]

It is not working for whatever reason. I have functions to draw the data from other sources in this tab. Is this perhaps the issue?

[@guptabharat909]

Do I have to write this code every-time as after closing the file and again opening this code is not there anymore. ??

[@danjarupath]

Cool

[@riteshraj5659]

Sir but when you will upload the video regarding Ribbon Buttons and how to apply different themes

[@theofficelab]

Hi Ritesh Raj, the tutorials on tab buttons and the complete settings menu will be coming within the next two weeks. It will be worth the wait 😊

[@LifeByDiego]

Hello! ran into a bit of a problem after auto refreshing, one of my pivot tables has subgroups and every time i insert new data into my table, the pivot chart expands completely and i need it to be collapsed. is there a script to auto collapse all tables in a sheet once data refreshes?

[@theofficelab]

Hi Diego, have a look here: stackoverflow.com/questions/38246393/vba-pivot-table-collapse-all-fields You could apply that to your specific pivot table or include it into the Refresh script shown in this video. Hope that helps 😊

[@LifeByDiego]

@@theofficelab worked like a charm! you're the best! thank you :)

[@theofficelab]

@@LifeByDiego My pleasure! Glad it worked 😊

[@OfficialMoin]

What about the settings icon in the left corner, how to add that?

[@theofficelab]

Hi there 😊 That's another Tutorial about the Settings Menu that will be coming in less than two weeks.

[@OfficialMoin]

​@@theofficelab That's great but can you help me with a complex formula in Excel?

[@westernchannels8641]

which version of Excel is that? is it on Windows?

[@theofficelab]

Excel 365 (with an Office 365 subscription). This demo is on Mac, but it works on both OS.

[@dstorm2007]

What about when there is a new data entry in the table?

[@theofficelab]

That should be covered by the script. As long as you have your data in a table, a new data entry will automatically be included in the table and, thus, also in the refreshed pivot table.

[@rocksonashong]

Part 4 seems to have a lot already done. I thought we'd be continuing from where we left off in part 3

[@gewe2508]

So if i change 10 cells in one go, it recreshes 10 times? Worst solution...