Easy Excel Dashboards, Models, Visualizations & Power Query

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 analytics skills. The output from Power Query (aka Get and Transform) is a table which we can driver based calculations to our dynamic model.This will be the foundation for the what if analysis using goal seek, solver and scenario manager in the next lecture. 

Enhance your Excel analytics skills. Here we can use what if analysis to run optimizations on our model. We will use the what if analysis functions goal seek, solver and scenario manager. 

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.