by George Taniwaki
While working toward my Microsoft Data Science Certificate (see Jul 2017 blog post), I also completed the Microsoft Excel for the Data Analyst XSeries Program sponsored by Microsoft and edX.
There are 3 classes in the program, two of which were also courses for the Microsoft Data Science Certificate.
DAT205x – Introduction to Data Analysis using Excel
This basic course on data analysis using Excel covers pivot tables, using SUMIF() and SUMIFS() functions to create dashboards and year-over-year comparison tables (something that is not possible using pivot tables alone), creating reports with hierarchal data, using Power Pivot, and creating multi-table reports using the data model and the More tables… feature.
Year-over-year comparison tables can also be created using the Excel data model and time intelligence functions. These are covered in the third course, DAT 206x.
Time: Since I am already an experienced Excel user, I skipped the videos and just did the homework. I covered the 8 modules in about 6 hours
Score: I missed 1 quiz question and no lab questions for a combined score of 99%
DAT222x – Essential Statistics for Data Analysis using Excel
[I took DAT222x in order to earn the Microsoft Data Science Certificate. This section is copied from this Jul 2017 blog post.]
This class is comprehensive and covers all the standard statistics and probability topics including descriptive statistics, Bayes rule, random variables, central limit theorem, sampling and confidence interval, and hypothesis testing. Most analysis is conducted using the Data analysis pack add-in for Excel.
Time: I used to work in market research, so I know my statistics. However, there are 36 homework assignments and it took me over 20 hours to complete the 5 modules.
Score: I missed 9 questions on the quizzes (88%) and six in the final exam (81%) for a combined score of 86%. (Despite the time it takes to complete, homework counts very little toward the final grade)
DAT206x – Analyzing and Visualizing Data with Excel
Topics include importing data and using queries with Excel, the Excel data model, using the M query language and DAX query language, creating dashboards and visualizations, and using Excel with Power BI.
Within the M language, topics include using the functions in the ribbon, including filtering rows, Table.Unpivot function.
Within the DAX language, topics include using the X functions like SUMX() and the CALCULATE() function, using Calendar table and time intelligence, and customize pivot tables and pivot charts using the CUBE functions from multidimensional expressions (MDX) language. The CUBE functions can also generate a table that can be used to create chart types that Excel does not support directly from a pivot table (for instance the new treemap, sunburst, and histogram charts).
Time: I am an experienced Excel user, but some of the advanced DAX functions were new to me. 6 hours for 8 modules
Score: I got a bit sloppy. I missed 2 lab questions and 1quiz question for a combined score of 95%
Final Certificates
Below is my certificate of completion for the Microsoft Excel for the Data Analyst XSeries Program.
Below is my certificate of completion for the Professional Certificate for Data Analysis: Essential Skills. This was awarded to me because I had previously taken DAT201x – Querying with Transact-SQL.
Real Numeracy 