MCCCD Program Description

Advanced Excel for Data Analytics

Course: CIS214DA

First Term: 2021 Fall

Lec + Lab 3.0 Credit(s) 4.0 Period(s) 4.0 Load
Subject Type: Occupational
Load Formula: T- Lab Load

Description: Advanced Excel spreadsheet features for data analytics including advanced functions, PivotTables, Data Modelling, Dashboard creation and forecasting for Business Intelligence (BI). Includes capstone project of an interactive dashboard using a multi-table Excel Data Model.

MCCCD Official Course Competencies
1. Explain the purpose of data analysis and Business Intelligence (BI) in business. (I) 2. Utilize conditional aggregate functions to summarize data. (II) 3. Demonstrate use of Excel Tables for data analysis. (III) 4. Use Lookup and Reference functions to find specific data for analysis. (IV) 5. Employ various Date functions to analyze data. (V) 6. Create PivotTables to perform multidimensional analysis. (VI) 7. Create advanced charts to analyze data and visualize patterns and trends. (VII) 8. Utilize conditional formatting techniques to visualize patterns, trends, and outliers. (VIII) 9. Utilize PowerPivot and Excel`s Data Model. (IX) 10. Apply a Forecasting function using historical data in Excel. (X) 11. Develop an interactive dashboard using a multi-table Excel Data Model. (I-X)

MCCCD Official Course Outline
I. Data analysis and BI II. Conditional aggregate functions A. SUMIFS, AVERAGEIFS, COUNTIFS B. Removing duplicate values C. MIN, MAX, SMALL, LARGE D. Nested IFS III. Excel Tables in worksheets A. Benefits 1. Automatic range names for columns 2. Automatic AutoFill 3. Other B. Advanced filtering and sorting C. Calculated Column D. SUBTOTAL function E. IFS function IV. Lookup and Reference functions A. VLOOKUP B. INDEX C. MATCH D. CHOOSE E. Other V. Date Functions A. TODAY, WEEKDAY, DAY, MONTH, YEAR B. DATEDIF, WORKDAY, NETWORKDAYS C. Other VI. PivotTables A. Introduction to multidimensional analysis 1. Tie to conditional aggregates 2. Dimensions, Measures, and Key Performance Indicators (KPI) B. Calculated Fields C. Calculated Items D. Drill down capabilities E. Slicers F. Filters G. Advanced sorting techniques H. Other VII. Advanced charts A. Combo charts B. Histogram charts C. Pareto charts D. Waterfall charts E. Scatter and bubble charts F. Hierarchical charts 1. Treemaps 2. Sunburst G. Sparklines H. PivotCharts I. Other VIII. Conditional formatting techniques A. Cell rules B. Top/bottom rules C. Data bars D. Icon sets E. Cell formulas within cell rules F. Conditional formatting in PivotTables IX. PowerPivot and Excel`s Data Model A. Working with large data sets B. Multi-table Data Model 1. Import data from multiple external sources a. SQL Server Database or Access Database b. CSV file or flat file 2. Create Calendar Table in the Data Model C. Data Analysis Expressions (DAX) functions 1. Create an import query 2. Transform data 3. Establish relationships between fields in various tables 4. Create Calculated Columns 5. Create Measures a. Explicit b. Implicit 6. KPIs a. Use in Data Analysis and BI b. Create on specific measures D. Dashboards from multi-table data model 1. PivotTables 2. PivotCharts 3. Slicers and Filters 4. Conditional formatting 5. Select measures and KPIs X. Forecasting functions in Excel A. Straight line vs. moving average B. Simple vs. multiple Linear Regressions C. Create a forecast worksheet

MCCCD Governing Board Approval Date: April 7, 2020

All information published is subject to change without notice. Every effort has been made to ensure the accuracy of information presented, but based on the dynamic nature of the curricular process, course and program information is subject to change in order to reflect the most current information available.