Advance Excel هذه الدورة تدرس باللغة الإنجليزية

تفاصيل الدورة

Dashboard Reporting
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

Data AnalysisIntroduction

• Benefits of Performing Data Analysis
• Stages of Data Analysis

Summarizing & Analyzing Large Data

• Analyzing Performance against Target
• Using Powerful New Features
  • Flash Fill
  • Quick Analysis
• Advanced Data Sorting techniques
• Advanced Filtering Techniques
• Applying Validation Checks on Data

Conditional Formatting

• Highlighting Data Based on Specified Criteria
• Create your own Formatting Rules
• Using Non Charting Visualization
  • Data Bars
  • Icon Sets
  • Color Scales

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)

Advanced Charts & Graphs

• 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

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

 
What-IF Analysis

• Using the brilliant Goal-Seek Feature
• Performing Sensitivity Analysis with Data Tables
• Using Scenario Manager to Summarize Results

 
Data Validation

• Creating Drop Down List
• Regulating & Managing the Entry of Data
• Custom Input Message and Error Alert

 
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

IntermediateIntroduction

• Spreadsheets & Its Terminology
• Understanding Excel Interface
• Entering & Editing Data
• Opening, Closing & Saving Workbook
• Prevent Data Loss in the Event of System Crash
• Didn't Save a Workbook? Don't Worry - Recover Unsaved

 
Basic Formula & Function

• Difference between a Formula & Function
• Learning the Core Excel Functions
• Nesting Functions
• Understanding Relative, Absolute & Mixed Referencing
• Defining Names and Using them in Functions

 
Formatting Techniques

• Useful Formatting Tools
• Using Special Number Formatting
• Applying Predefined Styles & Themes
• Inserting Pictures, Objects & Smart Art

 
Working with Dates & Time

• The Science of Dates & Time in Excel
• Useful Date & Time Functions

 
Efficient Handling of Large Data

• Quick Data Selection Techniques - Use of Ctrl & Shift Keys
• Quick Data Entry Techniques
• Using the Fill Handle
• Accelerating Copy Paste
• Learning Sorting & Filtering

 
Analyzing Data with Conditional Formatting

• Highlighting Cells based on Different Criteria

Using the Data Bars, Icon Sets & Color ScalesWorking with Tables

• Exploring the Power of Excel Tables
• Creating Dynamic Ranges with Tables

Working with Charts & Graphs

• Dos & DONTs of Excel Charting
• Creating a Chart in no Time!
• Useful Charting Techniques

 
Lookup Functions

• Learning the Powerful IF Function
• Evaluating Multiple Results with Nested IF
• Learning the All Important VLOOKUP & Its
• Analyzing Data with Criteria - SUMIF Family

 
Pivot Tables

• Understanding the Power of Pivot Tables - An
• Excellent Reporting Tool!
• How to Create a Pivot Table
• How to Generate Reports from Pivot Tables
• Managing & Presenting Pivot Table

 
Managing Workbook & Worksheet

• Linking Worksheets & Workbooks
• Performing Calculations across Worksheets with
• 3D Formula
• Creating a Hyperlink
• Printing Fundamentals
• Protecting Workbook with Password
• Restrict Editing Data
• Hiding the Contents of the Cell

تحديث بتاريخ 01 December, 2020