Easy Excel Dashboards, Models, Visualizations & Power Query
- 2.5 hours on-demand video
- 1 article
- 11 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- Rapidly build interactive dashboards which are robust and easy to update
- Basic Excel Skills
Rapidly build robust dashboards in Excel by following a simple step by step process that requires no formulas. Beginners can get started easily due the lack of formulas. Advanced users will learn to structure their dashboards like software developers so that they become more automated with no additional work.
Not many people are aware how much can be done with no or very few formulas in Excel. This not only makes the design simpler it also makes it more robust, reduces risk and improve performance.
Jonathan trained as a Microsoft Certified Trainer and has used advanced Excel for over a decade.
- If you currently build or wish to build dashboards in Excel
In this course you'll learn how to build Dynamic Excel Dashboards for data analytics quickly and easily using Power Query and Pivot Tables no VBA required. Power Query can be used to replace complex VBA scripts and enables your Dynamic Excel Dashboards to automatically refresh. You'll learn how you can get and transform data using power query and how power query can be used to prepare and aggregate your data for use with the new Excel 2016 data visualizations. We'll look at how Excel data visualizations compare with tools such as Power BI. The course will also cover driver based models using what if analysis. When building driver based models we need to evaluate if calculation should be placed within power query or within Excel Formulas. Power Query is a free add in for Excel 2010 and Excel 2013. Power Query has been renamed to Get and Transform in Excel 2016 and is now built in.
How to build a Dynamic Excel Dashboard for data analytics in Excel 2016 using Power Query (aka Get and Transform) and Pivot Tables. No VBA Required. Although there are many advanced Excel formulas and techniques that can be used to create interactive dashboards, complex dashboards can be much more difficult to setup and maintain. The structure of these dashboards are designed to be easily accessible and allows you to focus on rapidly conducting Excel analytics rather than spending a lot of time using formulas to connect all the pieces of a dashboard together. In this tutorial we will focus on the power of pivot table charts which are more dynamic than regular Excel Charts and Excel graphs.
Power Query (aka Get and Transform), Tables, Pivot Tables and Pivot Charts are the foundation for a powerful workflow that can fully automate your work with zero VBA code and zero formulas. This lecture will give you the overview of why you shouldn't skip steps like power query and when you should be doing steps in power query vs tables or power query vs pivot tables. This is the secret sauce that allows you to rapidly develop interactive dashboards and Excel analytics which is easy to maintain. Spend less time on formulas and VBA and more time on your analysis.
Tidy data is foundation for rapid reporting and analytics. Getting data in to a tidy format will be covered later in the power query section. This lecture is to show you the goal of tidy data.This will help you to rapidly develop dashboards and carry out Excel analytics.
Tables are critical component of well structured models since Excel 2007. Tables are the default output for Power Query and normally a precursor to pivot table dashboards so a proper understanding of them is incredibly important. Tables in themselves are a powerful tool for carrying out Excel analytics.
The first step of any process should start with get and transform / power query. Even if you have no transformation steps this can be valuable. In Excel 2010 and Excel 2013 Power Query was an add-in. In Excel 2016 Power Query has been renamed to Get and Transform. Power Query / Get and Transform allows us to easily perform tasks that use to require Excel VBA. Learn the hidden tweak that makes Excel 2016 Power Query more flexible and gets it back to the way it use to work in Excel 2010 and Excel 2013. By linking with power query we can refresh our Excel dashboards with a click of a button and focus more on our Excel analytics.
Power Query (aka Get and Transform) is capable of merging data with database joins all without requiring a database. Power Query merge / join in most cases is the better alternative to vlookups and temporary / desktop database solutions. Vlookup calculations are the number one cause of slow performance when developing our Excel dashboards or Excel Analytics. No Excel VBA required.
Use Power Query (aka Get and Transform) to merge a whole folder of files together. Learn about what to look out for when using Power Query for this feature. This helps to support our Excel dashboard development and Excel analytics when working with lots of files. No Excel VBA required.
Data resulting from pivot tables often do not confirm to a tidy data format. We can correct for this using Power Query (aka Get and Transform). Power Query fill and Power Query Unpivot are powerful features that can save you a ton of time and allow you to do complex VBA like functions without having to write any VBA code. Easily transform your data ready for your Excel Dashboard or Excel data analytics.
The best practice for developing Excel analysis driver based models is establishing an input sheet that has been setup with named ranges. This lecture gives some tips and foundations for the driver based model setup in the following lecture. No Excel VBA required.
Learn to enhance your excel data analysis skills. In this exercise we will setup a driver based model for carrying out some basic employee compensation modelling. We'll start be looking at when to add calculations to Power Query vs calculations to your worksheets. Next we'll setup an input driver sheet with named ranges that drive some calculated columns. Finally we will use the what if analysis including goal seek, solver and scenario manager to optimize our model.
Enhance your Excel analytics skills. In this lecture we will review when to place calculations in Power Query (aka Get and Transform) vs in your tables / worksheets. We will use the Power Query merge function (the better alternative to vlookups and database joins).
Enhance your Excel data analysis with new Excel Charts and Excel Graphs such as Treemaps and Sunbursts. These new Excel charts are not compatible with Pivot Tables therefore we will use Power Query to prepare the data for our Excel charts. We will compare how these Excel Charts and Excel Graphs compare to data analysis / data visualization tools such as Power BI. These Excel Charts and Graphs can be a great addition to your Excel Dashboards.
Enhance your Excel data analysis with new Excel Charts and Excel Graphs such as 3D Maps. 3D maps are a powerful new data visualization for carrying geospatial data analysis and even time series data analysis. These new Excel charts are not compatible with Pivot Tables therefore we will use Power Query to prepare the data for our Excel charts. Power Query can be used to ensure our update process remains seamless even in the absence of pivot tables. These Excel Charts and Graphs can be a great addition to your Excel Dashboards.
You are now able to develop dynamic interactive dashboards and models in Excel without the use of any VBA code or complex formulas. Where to next? Most people who want to take on Advanced Excel to the next level study Array formulas, VBA and databases. I used array formulas, VBA and databases for over a decade and now and no longer or use or recommend any of them. You maybe surprised that even though you have a foundation in Excel it's actually much easier to use other tools than it is to learn array formulas, VBA or databases. In my role as a data analyst I review dozens of tools which I will be releasing courses for in the future. Right now my primary tools is a programming language called R. R programming is significantly easier and more powerful than array formulas, VBA or databases. Typically the code required in R is 10x shorter than VBA and will run thousands of times faster. Learning R earlier on would have saved me years of my life. The data visualizations are amazing. You can turn big data into interactive web applications in minutes. If you are at stage where you are looking to learn advanced excel, VBA and databases you have the skills to learn R easily.
Before you use a vlookup you should review if vlookups are the right solution. Vlookups are an essential piece of knowledge. When interviewing people to assess their Excel skills vlookups and pivot tables are a bare minimum. In saying that I don't recommend using pivot tables 99% of the time. Vlookup alternatives include power query (aka get and transform) merge, database joins or r programming joins. This lecture talks you through the details.