Advance Excel Training

Training Goals & Objectives

This course on Advance Excel Training is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks.

Excel is so intuitive that professionals can start using it with little or no training, but its real value come when one take a professional training. Training facilitates the participants to learn the latest trends and the right ways for solving their day to day problems fast and with accuracy.

This course on Advance Excel is designed for the users having intermediate level skill in using Excel, and who want to enhance their skills to the next level, so they can get best output of their time.

Apart from quick tips & tricks, advanced formulas & functions, macros and using Excel for creating maintaining Financial Workbooks, this course further impart skill and knowledge to the participants on creating slick dashboards with accurate data representation using Excel.

Participants should have already completed Excel 2010/2013 Level 1 course or know how to create and save an excel workbook, enter simple formulas and print and chart data.

Course Outcome

By the end of the course participants will be able to:

  • Use spreadsheet skills for advanced -financial applications
  • Create , format and trouble shoot pivot tables
  • Analyze data accurately using pivot tables and slicers
  • Adopt logical methods to solve complex problems by
  • Analyzing data accurately using pivot tables
  • Efficient working with related worksheets and workbooks by benefitting from new reporting and presentation techniques
  • Prepare basic dashboards, reports, analyze complex data and much more.
  • Use the fill operations available to fill a data series
  • Understand and use formula cell referencing to create more complex formulas
  • Use a range of logical functions
  • Apply conditional formatting to ranges in a worksheet
  • Create and use labels in a workbook
  • Work with various elements of a worksheet
  • Use a range of techniques to work with worksheets
  • Use a range of find and replace techniques
  • Apply a variety of page setup techniques
  • Sort data in a list in a worksheet
  • Filter data in a table
  • Create effective charts in Microsoft Excel
  • Use a range of techniques to enhance charts
  • Apply formatting techniques to text on charts

Prerequisites:

Participants should already be competent in:

  • Basic Knowledge of MS Excel
  • Participants are required to bring their laptops with MS Office 2010/2013 installed in it.

Methodology

The training session will be highly interactive based upon exercises, live examples, templates, small assignments, objective based tasks, real-time scenario discussions.

Participants are required to bring their laptops with MS Office 2010/2013 installed in it.

Advanced Excel Training Outline

Excel Overview

  • Utilizing fill handle tricks
  • Formatting and editing a series
  • Creating user defined built in series
  • Automatic Calculation
  • Restricting data entry
  • Creating alert messages
  • Identifying data out of range
  • Freezing row and column titles
  • Using Paste Special (In depth)
  • Creating Dynamic Table
  • Inserting Header & Footer

Conditional Formatting & Sparklines

  • Formatting data on Conditions
  • Highlight Duplicate & Unique Values
  • Finding top 5 / 10 items in the Data
  • Formatting data using Data Bars & Icons
  • Clearing & Managing Rules
  • Nested loops
  • Analysing only total rows
  • Summerizing a data based on single attribute
  • Adding data based on two attributes

Summarizing & Consolidating

  • Creating Sub Totals
  • Sorting
  • Filtering
  • Extracting Unique Values with Filtering
  • Tips of copying Filtered data to New File

Pivot Table & Slicer

  • Creating Pivot table & analyzing data
  • Refreshing & Changing Pivot Table Range
  • Creating sheets from Pivot table
  • Calculations & Creating Dynamic Pivot
  • Table creation & text to column
  • Defining Name & work with tables
  • Creating Virtual Fields
  • Creating more than one Pivot table on one sheet
  • Changing Source
  • Slicer

Lookups and Data Table

  • Picking data based on row criteria
  • Analysing data based on column criteria
  • Match and indexing
  • Data Tables single variable
  • Data Tables more than one variable
  • Creating Grade sheet/ Result sheet for performance
  • Excel speech on data entry
  • Creating data list in excel

Charts & Validation

  • Creating Column, Line & Pie Charts
  • Shortcuts of Creating Charts
  • Changing Chart type
  • Adding Target Line in chart
  • Method of pasting chart into PowerPoint
  • Creating two axis chart & advance charting
  • Creating Drop Downs in cell
  • Sharing Workbook
  • Inserting object & Hyperlinking

Data Mining

  • Auditing spread sheet
  • Using Attendance data to calculate payroll
  • Identifying source cells
  • Changing source data year on year
  • Tracing dependent cell
  • Rearranging the data in required sequence
  • Quick summary of data

Macros

  • Recording Macros for Automating tasks
  • Creating Button to run a Macro
  • Macro with Keyboard & its benefit
  • Deleting Macro

Formulas

  • Statistical :

Count, CountA, CountIF, CountIFs & CountBlank,

Trim & Concatenate, Sum, SumIF & SumIFs

  • Change Case :

Upper, Lower & Proper

  • Logical :

IF Condition with Multiple IF’s, Using

AND & OR in IF Condition

  • Lookup :

Advance & Multiple Vlookup & Hlookup

Explore our Excel Training Courses here and to read our blogs on MS Excel please click here.