Auto-Refresh Pivot Tables and Pivot Charts when Source Data Changes

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

Рет қаралды 82,091

4 жыл бұрын

✅ GET CODE here ► excelfind.com/tutorials/ultim...
📌 EXCEL VERSION: Microsoft Office 365 - Get it here ► geni.us/Office365_Microsoft
▬▬▬▬▬▬▬▬▬▬
🔥BIG NEWS ► Launch of excelfind.com 🔥
I just launched my new Excel Resource Website and kindly invite you to check it out.
I put a lot of work and research into it to provide you with the biggest possible value!
Main Website ► excelfind.com
Recommended Courses ► excelfind.com/courses
Tutorials ► excelfind.com/tutorials
Most Powerful Excel Add-ins ► excelfind.com/add-ins
And even better, I integrated the most up-to-date databases about all relevant functions, formulas, and shortcuts. You can use our advanced filtering system to filter by Relevance, Category and even minimum required Microsoft Office Version. That means you can learn about all new functions in Microsoft Office 365 with only one click!
Excel Functions ► excelfind.com/excel-functions
Excel Shortcuts ► excelfind.com/excel-shortcuts
If you like that website and want to help me grow, please consider to share it online and with everyone you know.
Thanks for your support!
▬▬▬▬▬▬▬▬▬▬
Auto-Refresh Pivot Tables and Pivot Charts when Source Data Changes | Ultimate Excel Dashboard Ep. 4
In this Excel Tutorial you will learn how to make the Pivot Tables and Pivot Charts in Excel automatically refresh when the source data changes. This is a powerful solution for any dashboard application with regular data updates, because it eliminates the need to manually click the Pivot Table refresh button every time the source data updates and ensures that every dashboard user only works on up-to-date and not outdated data.
The previous three episodes we covered how to build the basic interactive Excel dashboard and had a detailed look into advanced background, gradient tile and pivot slicer designs. The following Episodes will go deeper into the creation and integration of truly advanced features like fully functional info buttons, in-tale tab functionality and a collapsable settings area with state-of-the-art radio buttons for dashboard color theme selection and modern toggle buttons to control the visibility of specific dashboard elements.
If you want to build this dashboard yourself, we recommend to upgrade your Excel Version to the latest one which is included in the Microsoft Office 365 Subscription. Find the link for current offers in the Microsoft Store below ↓
▬▬▬▬▬▬▬▬▬▬
You want to become a master in Excel and learn all modern and relevant Excel techniques?
Then definitely have a look at the following links.
🚀 OUR EXCEL RESOURCE WEBSITE ► excelfind.com
💎 RECOMMENDED ONLINE EXCEL COURSES
GoSkills is our favourite & it’s not even close. Have a look ► bit.ly/30PWMnQ
📌 EXCEL VERSION IN THIS VIDEO
We use the latest version of Excel via the Microsoft Office 365 subscription. This allows us to have all the latest features like new chart types, a huge integrated icon library, improved functions, and much more!
If you want to upgrade your version as well, we recommend to get it from the
Office 365 (subscription based) ► geni.us/Office365_Microsoft
Office 2019 (standalone version) ► geni.us/Office2019_Microsoft
▬▬▬▬▬▬▬▬▬▬
You want to show your support and help me grow?
Here is what you can do.
🚀 SHARE MY NEW EXCEL WEBSITE ► excelfind.com
☕ WANT TO MAKE MY DAY?
Buy me a Coffee ► bit.ly/2Oqg6Cu
🔥 MORE AWESOME EXCEL AND POWERPOINT CONTENT
Subscribe ► bit.ly/37bcvAB
Note: Some of the links in this description are affiliate links, meaning, at no additional cost to you, we will earn a commission if you click through and make a purchase. This supports the channel and allows us to continue to produce videos like this. Thank you for your support!
#interactive #excel #dashboard

Пікірлер: 64

@CoinsTokens 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

Happy to hear that! 😊

@vishalkhatri9856 4 жыл бұрын

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.

@johnlouieconstantino6247 4 жыл бұрын

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

@Nathan-up7gf 3 жыл бұрын

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!

@datnguyenp 3 жыл бұрын

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

@labbyrinthsalvador 4 жыл бұрын

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

@BigTimbo77 3 жыл бұрын

Excellent series of videos, have really learnt alot

@PartyAT209 3 жыл бұрын

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?

@shannonhodges14 3 жыл бұрын

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

@bhanugoyal4243 2 жыл бұрын

Best tutorial ever for making dashboards using excel.

@alfsilva 4 жыл бұрын

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 3 жыл бұрын

Keep it up, I love your tutorials. :)

@theofficelab 3 жыл бұрын

Thanks a lot! I am happy you like them 😊

@osamababiker3901 Жыл бұрын

You are the best one

@gugumach 4 жыл бұрын

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 4 жыл бұрын

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.

@theofficelab 4 жыл бұрын

Check out my new website excelfind.com for the Free Version of the Dashboard 🚀 ⬇ FULL TUTORIAL SERIES PLAYLIST [Ep. 1] Basic Dashboard ► kzfaq.info/get/bejne/mbGbi9eqz9LJiGg.html [Ep. 2] Background & Tile Design ► kzfaq.info/get/bejne/hMx1pKaVy9KWY58.html [Ep. 3] Modern Slicer Design ► kzfaq.info/get/bejne/ech0h6mX1Mu0io0.html [Ep. 4] Auto-Refresh Pivot Charts ► kzfaq.info/get/bejne/jsqharZ2kuCRcas.html [Ep. 5] Interactive Info Button ► kzfaq.info/get/bejne/iMCHYNmCt9iUno0.html [Ep. 6] Interactive Dashboard Tabs ► kzfaq.info/get/bejne/aNd-aM2GzrC7fY0.html [Ep. 7] Interactive Settings Menu ► kzfaq.info/get/bejne/nKl2etSbmbfHeok.html

@MrDeejaydrake 3 жыл бұрын

Simply amazing

@sumer2003 4 жыл бұрын

simple and effective. nice.

@theofficelab 4 жыл бұрын

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

@manjunathk1509 4 жыл бұрын

Simply superb

@theofficelab 4 жыл бұрын

Thanks, Bro 😊

@riteshraj5659 4 жыл бұрын

Thanks Sir

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

@3PJPriest 4 жыл бұрын

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 4 жыл бұрын

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.

@user-tf7qz5es3p 5 ай бұрын

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?

@JuanCruz-qd1tw 4 жыл бұрын

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 4 жыл бұрын

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 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

@@JuanCruz-qd1tw Thank you 😊

@noel1111 4 жыл бұрын

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?

@DCHAWADA07 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

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 😊

@laurenwilliams5006 4 жыл бұрын

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.

@zakbergmann7650 3 жыл бұрын

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

@danjarupath 3 жыл бұрын

Cool

@alexroch4 2 жыл бұрын

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

@rschultz7141 3 жыл бұрын

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?

@abuena73 3 жыл бұрын

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

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

@Name-rb3us 4 жыл бұрын

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?

@riteshraj5659 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

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 😊

@dr.chantiyadav7665 2 жыл бұрын

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

@guptabharat909 3 жыл бұрын

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

@OfficialMoin 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

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

@OfficialMoin 4 жыл бұрын

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

@ethanpadden3736 3 жыл бұрын

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 3 жыл бұрын

Thanks you so much for this piece of code!

@dstorm2007 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

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 3 жыл бұрын

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

@schtefel 4 жыл бұрын

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 4 жыл бұрын

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 4 жыл бұрын

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

@LifeByDiego 4 жыл бұрын

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 4 жыл бұрын

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 4 жыл бұрын

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

@theofficelab 4 жыл бұрын

@@LifeByDiego My pleasure! Glad it worked 😊