Pro Tip - Ditch Excel VBA for Power Query to Automate Excel Dashboards
https://www.datastrategywithjonathan.com
In this video you will learn how the structure of an Excel Dashboard using Power Query (aka Get and Transform), Tables, Pivot Tables and Pivot Charts is the secret that will enable you to build dynamic Excel Dashboards which are fully automated without having to write and Excel VBA or even Excel formulas.
Many Excel Dashboard training videos either use VBA or even if they don't use VBA they still use complex formulas including vlookups, index, match, indirect, getpivotdata, cubevalue, sumifs, etc to build out Excel dashboards. These methods are not as flexible as they may initially appear and often result in the creation of static groupings which often require manual updating. The method shown in this video using Power Query (aka Get and Transform), Tables, Pivot Tables and Pivot Charts solves many of these issues while also requiring less work and less training to create. When building more advanced dashboard please keep an eye out for my training in R. If you are at the level where you are building advanced Excel dashboards with VBA, vlookups, index, match, indirect, getpivotdata, cubevalue, sumifs, etc then you may be surprised how much faster and easier it is to build much more flexible and scalable dashboards in R that then can be turned into web applications to host on the web.
If you're looking to learn Advanced Excel, Excel VBA or Databases then you need to check out this video series. In this videos series, I will show you how to use Microsoft Excel in different ways that will make you far more effective at working with data. I'm also going to expand your knowledge beyond Excel and show you tips, tricks, and tools from other top data analytics tools such as R Tidyverse, Python, Data Visualisation tools such as Tableau, Qlik View, Qlik Sense, Plotly, AWS Quick Sight and others. We'll start to touch on areas such as big data, machine learning, and cloud computing and see how you can develop your data skills to get involved in these exciting areas.
Excel Formulas such as vlookup and sumifs are some of the top reasons for slow spreadsheets. Alternatives for vlookup include power query (Excel 2010 and Excel 2013) which has recently been renamed to Get and Transform in Excel 2016. Large and complex vlookup formulas can be also done very efficiently in R. Using the R Tidyverse libraries you can use the join functions to merge millions of records effortlessly. In comparison to Excel Vlookup, R Tidyverse Join can pull on multiple columns all at the same time. Microsoft Excel Power Query and R Tidyverse Joins are similar to the joins that you do in databases / SQL. The benefit that they have over relational databases such as Microsoft Access, Microsoft SQL Server, MySQL, etc is that they work in memory so they are actually much faster than a database. Also since they are part of an analytics tool instead of a database it is much faster and easier to build your analysis and queries all in the same tools.
My very first R Tidyverse program was written to replace a Microsoft Access VBA solution which was becoming complicated and slow. Note that Microsoft Access is very limited in analytics functions and is missing things as simple as Median. Even though I had to learn R programming from scratch and completely re-write the Microsoft Access VBA solution it was so much easier and faster. It blew my mind how much easier R programming with R Tidyverse was than Microsoft Access VBA or Microsoft Excel VBA. If you have any VBA skills or are looking to learn VBA you should definitely checkout my videos on R Tidyverse. To understand why R Tidyverse is so much easier to work with than VBA. R Tidyverse is designed to work directly with your data. So If you want to add a calculated column that’s around one line of script. In Excel VBA, the VBA is used to control the DOM (Document Object Model). In Excel that means that you VBA controls things like cells and sheets. This means your VBA is designed to capture the steps that you would normally do manually in Microsoft Excel or Microsoft Access. VBA is not actually designed to work directly with your data.
Note the most efficient path is to reduce the data pulled down from the database in the first place. This is referring to the amount of data you are pulling down from your data warehouse or data lake. It makes no sense to pull data from a data warehouse / data lake to pull into another database to query add joins / lookups to then pull it into Excel or other analysis tool. Often analyst build these intermediate databases because they either don’t have control of the data warehouse or they need to join additional information. All of these operations are done significantly faster in a tool such as R Tidyverse or Microsoft Excel Power Query.