Auto-Refresh Pivot Tables and Pivot Charts when Source Data Changes | Ultimate Excel Dashboard Ep. 4
HTML-код
- Опубликовано: 15 сен 2024
- ✅ GET CODE here ► excelfind.com/...
📌 EXCEL VERSION: Microsoft Office 365 - Get it here ► geni.us/Office...
▬▬▬▬▬▬▬▬▬▬
🔥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/...
Tutorials ► excelfind.com/...
Most Powerful Excel Add-ins ► excelfind.com/...
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 Shortcuts ► excelfind.com/...
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/Office...
Office 2019 (standalone version) ► geni.us/Office...
▬▬▬▬▬▬▬▬▬▬
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
You really are a natural teacher. Calm, well presented, and great content. Learned a lot already
Happy to hear that! 😊
Never knew that my life needed something like this...
Best tutorial ever for making dashboards using excel.
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.
I had no idea this was possible. Thanks for sharing these tips!
This is very helpful. I learned a lot from your videos. Looking forward for your uploads in the future.
Excellent series of videos, have really learnt alot
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!
Awsome..but I wish you could show your final version in today's episode itself , I m waiting since days
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 😊
You are the best one
Amazing video. Very very informative. I will try to record a macro and find out what is going to be different from your code.
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.
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?
Cool. It works!!! Thanks for the tutorial! 😊
Simply superb
Thanks, Bro 😊
Keep it up, I love your tutorials. :)
Thanks a lot! I am happy you like them 😊
simple and effective. nice.
Thank you, Sir 😊 I am glad you like it!
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.
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.
Simply amazing
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?
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?
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.
Thanks Sir
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?
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.
@@theofficelab Great! Thanks for the reply! Your videos are amazing
@@JuanCruz-qd1tw Thank you 😊
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??
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.
@@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.
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
Thanks you so much for this piece of code!
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?
Cool
How to refresh pivot table and chart if the source data is coming from another xlsx file..? Appreciate if you could help me.
Is there a way to select other sheet after running the code from this video?
After applying the first code , while entering new data the undo option is not working . Please help
Part 4 seems to have a lot already done. I thought we'd be continuing from where we left off in part 3
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?
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?
Is there a way that I can call a sub procedure every time a slicer is used?
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.
Do I have to write this code every-time as after closing the file and again opening this code is not there anymore. ??
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?
Sir but when you will upload the video regarding Ribbon Buttons and how to apply different themes
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 😊
What about when there is a new data entry in the table?
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.
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?
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 😊
@@theofficelab worked like a charm! you're the best! thank you :)
@@LifeByDiego My pleasure! Glad it worked 😊
which version of Excel is that? is it on Windows?
Excel 365 (with an Office 365 subscription). This demo is on Mac, but it works on both OS.
What about the settings icon in the left corner, how to add that?
Hi there 😊
That's another Tutorial about the Settings Menu that will be coming in less than two weeks.
@@theofficelab That's great but can you help me with a complex formula in Excel?
Check out my new website excelfind.com for the Free Version of the Dashboard 🚀
⬇ FULL TUTORIAL SERIES PLAYLIST
[Ep. 1] Basic Dashboard ► ruclips.net/video/cKkXtyjleX4/видео.html
[Ep. 2] Background & Tile Design ► ruclips.net/video/NfEqCdfl23k/видео.html
[Ep. 3] Modern Slicer Design ► ruclips.net/video/CbDTFfoePZY/видео.html
[Ep. 4] Auto-Refresh Pivot Charts ► ruclips.net/video/Xdq7SE-z-Aw/видео.html
[Ep. 5] Interactive Info Button ► ruclips.net/video/RZW-vQRr0nY/видео.html
[Ep. 6] Interactive Dashboard Tabs ► ruclips.net/video/2qN5jUiJWMY/видео.html
[Ep. 7] Interactive Settings Menu ► ruclips.net/video/fCFGqj4QcJU/видео.html