Business Intelligence and Data Analysis with Microsoft Excel


The “Business Intelligence and Data Analysis with Microsoft Excel” course builds on the beginner and intermediate Excel skills you have already mastered either on the job or personally. This course is designed for individuals with Intermediate or advanced skills in Microsoft Excel. In this course, you will learn how to: use advanced functions and formulas and build data models in Excel. In addition, you will learn how to work with Pivot Tables, analyze data, and leverage Excel-Integrated business intelligence tools to create robust and scalable solutions for decision support and business analysis


Target Audience
  • Beginners (completely new to Business Intelligence (BI))
  • Experienced analysts willing to enhance their analysis and reporting skills
Delivery Format
  • Both in-class and online class options will be available
  • Homework is assigned after every class
  • students will be assigned individual projects at the beginning of the course which they will work on throughout the duration of the course using the skills learned in class
  • Each student will give a presentation on the final project at the end of the course.
  • We provide continuous support after completion of this program
  • Students will have unrestricted access to online resources
Cost & Payment Terms

The cost of this 6 week course is $1,500.


There are 2 payment options:
  • Option 1: Full payment due at the beginning of the course.
  • Option 2: Half due at the beginning of the course and balance due by the start of week 4.

Discounts are available to those who qualify

Course Dates & Duration

The course duration is 40 hours, spread over 6 weeks. Classes will be held on weekday evenings and on weekends.

Prerequisites

Knowledge of Microsoft Windows operating systems.
Basic understanding of database design and concepts. Working knowledge of SQL queries.



Course Outline

Part I: Advanced Excel Functions (16 hours)
Excel Refresher
  • Filtering and Sorting
  • Conditional Formatting
  • Freezing Panes
  • Understanding Ranges
  • Text to Columns
  • References (Absolute and Relative)
  • Helper Columns
  • Tricks and Tips
Understanding Excel Functions
  • VLOOKUP and HLOOKUP Function
  • INDEX & MATCH Function
  • TEXT Functions
  • DATE Functions
  • IF Statements
  • SUM, SUMIF, SUMIFS and AVERAGE Functions
  • COUNT, COUNTIF, COUNTIFS, COUNTA and COUNTBLANK Functions
  • INDIRECT, OFFSET & SUMPRODUCT Function
Part II: Data Analysis in Excel (10 hours)
Creating Data models in Excel
  • Build and Customize data models
  • Advanced data models
  • Statistical Models – Normalization and Standard Deviation
Visualizing Data
  • Creating Graphs
  • Creating Charts
Pivot Table and Pivot Charts
  • Introducing the PivotTable
  • Customizing PivotTables
  • Introducing PivotCharts
  • Customizing PivotCharts
  • Slicers
  • Leveraging data models in Pivot Tables and Charts
Business Intelligence in Excel (14 hours)
PowerPivot Fundamentals
  • Introduction to PowerPivot
  • Creating your first PowerPivot model
  • Customizing PowerPivot models
  • Introduction to Data Expression language (DAX)
  • Creating measures and Key performance metrics using DAX
Power Tools for business Intelligence
  • Creating interactive reports and dashboards with Power View
  • Location Intelligence using Power Map
  • Introduction to Power Query
Delivering business intelligence with SharePoint and Excel Services
  • Introduction to Excel Services
  • Deploying and publishing Excel workbooks to SharePoint
  • Deploying and publishing Power View reports to SharePoint