Course Details

Introduction

• Benefits of Performing Data Analysis
• DOs & DON’Ts of Presenting Data
• Stages of Data Analysis

Advanced Formatting Techniques

• Advanced Text Formatting
• Best Way to Present Tables
• Many Ways to Present Numbers – Customized Number Formatting
• Customized Dates & Time Formatting

 Excel New Feature: Sparklines

• What are Sparklines?
• Inserting & Modifying Sparklines
• Analyzing Data with Sparklines
• Performing Tweaks with Sparklines
• Handling Missing Data
• Making the Sparklines Dynamic (Auto-Update)

Chartless Visualization Techniques

• Conditional Formatting
  • Highlighting Cells Based in Criteria
  • Using Data Bars, Icon Sets & Color Scales
  • Make Your Own Formatting Rules
• Using Special Symbols
• Using the Camera Tool

Advanced Charting Techniques

• The Basics of Charting
• Going through different types of Charts
• Modifying Charts and Working with Chart Options
• Advanced Charts Formatting
  • Inserting Text and Pictures inside Charts
  • Modifying Chart Axis
• Creating Specialized Charts for Measuring Performance
  • Combo Chart
  • Dynamic Chart
  • Thermometer Chart
  • Bullet Chart

Processing the Data Model

• Learning Useful Excel Functions
  • Simple IF & Nested IF Function
  • CHOOSE & OFFSET Function
  • SUMPRODUCT & SUMIFS Function
  • VLOOKUP & INDEX-MATCH Functions
• Adding Interactivity with Drop Downs

Enhancing Interactivity

• Adding Interactive Advanced Controls
  • Buttons Control
  • Check Box
  • Spin Buttons
  • Option Buttons

Automating Reports

• Using Macros to Automate Reports
• Recording a Macro
• Running a Macro
• Assigning Macros to Objects & Buttons

Pivot Tables

• Analyzing Data with Pivot Tables: A very Powerful Reporting Tool
• Creating & Modifying Pivot Tables
• Performing Calculations within Pivot Tables
• Working with Pivot Charts
• Analyzing Data with Excellent New Features in Pivot Tables
  • Slicers
  • Timelines

Building the Dashboard

Building a Complete Dashboard from Scratch