Learn the best ways to use Microsoft Excel for data analysis, reporting and modeling for decision making using real-world business scenarios. Designed for supervisors, managers or team members who need to summarize, report and analyze data, this course will present helpful tools to evaluate investments and financial decisions, ensure accurate data entry by creating a list of values, summarize data using histograms and PivotTables, estimate business variables using trend curves and present the data. 

 

Prerequisites

Excel Intermediate Level

 

Objectives

The aim of this course is to give delegates a thorough grounding in Excel Advanced functions, Data Analysis Tools, Reporting and Business Modelling. It uses multiple exercises to ensure that delegates can practice the key techniques that are taught and also ask any questions that they may have.

Our aim is that you leave the course as a confident user of Excel and can start automating your spreadsheets as soon as your return to your office.

 

Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

 

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.

Each delegate will be given a digital courseware manual to refer to after the course.

 

Course Outline

 

Advanced Functions

  • Name Manager, Naming Ranges and Using them in Formulas
  • Formula Auditing
  • SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
  • INDEX & MATCH Lookup functions

 

What-If

  • Scenarios and Scenario management
  • Goal Seek

 

Advanced Data Analysis & Presentation

  • Using Sparklines
  • Create and manage PivotTables
  • Filters in PivotTables
  • Creating and manging Slicers
  • Create and manage PivotCharts
  • Manipulating options in existing PivotCharts 
  • Multiple PivotTables on same WorkSheet
  • Grouping Data to analyse data by Year/Quarter/Month
  • Building a BI Dashboard

 

Trend Analysis

  • Using Scatter Charts and Trend Analysis

 

Automating processes with Macros

  • Recording and Running Basic Macros
  • Saving Work with Macros


Prerequisites

A foundation in basic Excel techniques is advised.

 

Objectives

By the end of this course candidates will master the techniques of using the right excel tools to solve problems more efficiently. Also new features in the current Excel version will be highlighted.

Topics covered include Conditional Formatting wizard, Flash Fill, Name Manager, various functions (string, maths, logical, lookup), pivot tables, slicers and pivot charts to build a BI dashboard.

Having practiced all of these new skills extensively during the course you will be able to hit the ground running on your return to the office and should be able to complete your Excel work far more proficiently.

 

Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

 

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.

Each delegate will be given a digital courseware manual to refer to after the course.

 

Course Outline

Apply Fill Tools, Conditional Formatting and Filtering

  • Using advanced Fill Series options
  • Flash Fill
  • Apply advanced conditional formatting and filtering
  • Creating filters
  • Managing conditional formatting rules
  • Conditional Formatting with Sorting & Filtering

 

Working with Data

  • Getting External Data
  • Restricting Cell Entries to Certain Data Types
  • Data Validation Tools
  • Removing Duplicate Rows from a Worksheet
  • Sorting Data on a Single & Multiple Criteria
  • Sorting Data Using Cell Attributes
  • Using AutoFilter
  • Filtering Data Using Cell Attributes
  • Removing Duplicates in a Table

 

Using Formulas

  • Apply functions in formulas
  • Functions Wizard
  • Absolute vs Relative Referencing
  • Using SUM, COUNT, SUBTOTAL
  • Using nested functions
  • Using the IF, AND, and OR functions
  • Using the VLOOKUP function
  • Using the NOW and TODAY functions
  • Using text manipulation functions like LEFT, RIGHT, MID, TRIM, CONCATENATE

 

Data Analysis & and Manipulation using PivotTables & PivotCharts

  • Create and manage PivotTables
  • Modifying field selections and options
  • Filters in PivotTables
  • Creating slicers
  • Create and manage PivotCharts
  • Manipulating options in existing PivotCharts 
  • Multiple PivotTables on same Sheet
  • Applying styles to PivotCharts
  • Building a BI Dashboard