Improve your experience. We are very sorry but this website does not support Internet Explorer. We recommend using a different browser that is supported such as Google Chrome or Mozilla Firefox.

Advanced MsExcel

This training course is a fast-paced, hands-on workshop. Every student will be provided with a computer running Excel. Guided by an expert instructor, you'll leave this training course with the real-world experience you need to fully harness the powerful tools Excel has to offer back in your workplace.

Description

The format of the Three-day workshop is a series of brief lectures followed by discussion and hands-on exercises. Case studies and real-life examples are used to illustrate success and failure

Course objectives

This course allows the delegate to experience the power of Excel 2016 in Project Management

Course Content

Day 1

Control Data

  • Format Cells
  • Data Validation and input control
  • Paste Special
  • Naming and Moving Worksheets
  • Currency Format
  • Format Painter
  • Conditional Formatting
  • Some usable functions for controlling inputs
  • Working with Texts in Excel

Create Project Structure

  • Creating the Work Breakdown Structure (WBS)  
  • Create automotive weight factor structure for physical % Complete
  • Create project S-Curve
  • Project Status Tracker by tools and Functions

Data Transformation and lookup

  • Creating Formulas
  • Common Formulas
  • Searching for Formulas
  • V-look up and H-look up functions
  • Creating the Lists and Combo Box
  • Indirect, Offset, Index and other lookup functions

 

Day 2

Data Analysis

  • Freeze Panes
  • Worksheet Backgrounds
  • AutoFill a Series
  • Removing Duplicates
  • Pivotable and Data summarization
  • Search in Data by Functions
  • Adding Formula and Calculating financial Factors ( PV, FV ,NPV,IRR ,PMT,IMPT …)
  • COUNTIF & COUNTIFS
  • SUMIFS
  • Conditional Sorting and Filtering
  • Useful Functions in Management and Financial Analysis
  • Intelligent Scoring Model for various analysis
  • Advanced Grouping and Filters

Schedule Analysis

  • Creating Dynamic Gant chart in Excel
  • Project Schedule and Budget structure
  • Advanced Schedule Data Analysis

Forecasting the Future

  • Scenario Analysis in Project Management
  • What-If Analysis and Goal Seek
  • Data Table and Goal seek
  • Using solver tool to optimize calculations and forecasts
  • Forecasting the future by various tools

Day 3

Project Dashboard

  • Chart Types
  • Pivotable and Charts
  • Data summarization and reporting
  • Gauge Chart
  • Sparklines and Data Analysis
  • Dynamic Charts and Dynamic Lists
  • Risk Register Structure in Excel
  • Master Document Register (MDR ) in Excel

 

Automation

  • Inserting Hyperlinks
  • Creating a Macro
  • Assign a Macro to a Button or Shape
  • Run a Macro upon Opening a Workbook
  • Smart Art and Graphs
  • Page Orientation
  • Page Breaks
  • Print Area
  • Create Dynamic Auto-filled letters and Lists

Similar courses

ILM Recognized - Office Management

This course will enable the delegates to communicate effectively in a variety of public speaking venues, utilizing nonverbal as well as verbal skills. The delegates will be able to critically assess information both on a verbal and research level.

More Information

Press enter to see more results