Recently I completed the Analyzing and Visualizing Data with Excel course which is offered online by Microsoft via edX. The course explores the newest Excel features which were added in the 2010 and 2013 version as add-in’s and finally integrated in the 2016 version. These include the Power Query and Power Pivot functions, as well as the ability to import data from a variety of sources and transform it before loading into the spreadsheet.
The course consists of a total of eight modules. New features allow users to import data to Excel from a database, a csv or an existing formatted Excel spreadsheet and transform it before loading. This means you can remove lines, check for duplicates, change types and perform various transformations on your data set which are all recorded and re-run when you import it again. This works perfectly for the kind of periodic report which your manager asks for and which requires you to pull data from three different csv files and then create pivot tables. By loading all of the data into the Excel data model, another new feature, you can manipulate the tables and define relationships in order to create more complicated reports, or reports that previously required a lot more manual effort and intermediate steps.
The course also introduces Data Analysis Expressions (DAX) which is a powerful formula language that can be used to create new columns in a data set or define measures. In these formulas a whole table or column can be referenced, so this means you can create a single metric based on the complete data set.
The final modules discuss visualizations in Excel and creating a dashboard which can become dynamic by using slicers. Also, Microsoft’s newest analytics tool, Power BI, is introduced.
I really enjoyed this course as it is simple and to the point. I felt like it was a perfect way to improve my Excel skills a notch by going through some simple examples using the newest and powerful features that have been added to it. Most of all, I can put these skills to immediate use because Excel is a tool I already use in my daily work. The course is not difficult at all, and it should not take more than a week to complete if you have previous Excel experience. As is usually the case on edX, it is free to take the course, whilst there is a fee if you would like an ID-verified certificate.