ADVANCE MS EXCEL



Introduction

FinXL Advance MS Excel program empowers the participant with a never before seen depth in almost all of Excel's powerful Features with a focus on becoming an Expert, by 'Mastering' each of the mentioned categories.

The program offers Tips, Tricks and comprehensive coverage of topics that you may not even be aware of!

We work with businesses and individuals who need effective Microsoft Office training at unique & affordable pricing as per your budget. We provide a tailored training session for corporate clients as per their need.

Having used MS Excel® for thousands of hours, the trainers have worked across projects related to Complex Data Modeling, Analysis, Complex Charting and Pivot tables.


  • Industry expert trainers
  • Practical hands-on session
  • Real World examples
  • Post training support
WHAT YOU'LL LEARN

The Advanced Excel Course is designed for employees who are already familiar with the basics of Microsoft Excel or new to excel, and who would like to work with more advanced features of Microsoft Excel that help in improving their efficiency of working with worksheets, analyzing data, creating MIS reports, and automating various tasks.

You’ll learn how to use Excel better, faster and more efficiently. You will learn how to save time, access new features and work with confidence. Our teaching methods ensure maximum comprehension in the shortest possible time. We provide a dynamic learning environment with lots of practical hands on exercises and real world examples.

Pre-Requisites

    Participants attending this training should be famipar with the basic operations in Microsoft Excel, such as simple calculations, formatting and printing or would have used basic excel

Target Audience

    Employees, Trainees, Executives, Managers, Students who have already been using Microsoft Excel, but now feel the need for learning more powerful features and options of Excel, to manage their worksheet-related tasks more efficiently

Course Details
Excel 2007 & 2010 Quick Overview
  • Difference between Excel 2003, 2007 and 2010, Use of Excel, its boundaries & features

Basic Formula
  • Formulae that Add/Subtract/Multiply/Divide
  • BODMAS/Formula Error Checking
  • The Sum Function

Absolute Referencing
  • Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References

LOOKUP Functions
  • The VLOOKUP/HLOOKUP Functions

PIVOT Tables
  • Creating, Formatting Simple PivotTables
  • Page Field in a PivotTable
  • Formatting a PivotTable
  • Creating/Modifying a PivotChart

LOGICAL FUNCTIONS
  • IFs and Nested IF Functions
  • Using AND/OR/NOT Functions

STATISTICAL FUNCITIONS

    Using The SUMIF/COUNTIF Functions

  • Using The AVERAGE/COUNT/LARGER/SMALLER Functions

Pivot Tables – Advance
  • Adding new calculated Fields / Items
  • Changing the Summary Function
  • Consolidate Pivot table

LOOKUP Functions – Advance
  • MATCH with VLOOKUP Functions
  • INDEX & MATCH Functions
  • OFFSET/ INDIRECT functions

Logical Functions - Advance
  • If Loop and Nested IF Loop Functions
  • Using IF/ISERROR Functions

Chart Data Techniques
  • The Chart Wizard
  • Chart Types
  • Adding Title/Legends/Lables
  • Printing Charts
  • Adding Data to a Chart
  • Formatting/Renaming/Deleting Data Series
  • Changing the Order of Data Series

Date/Time Functions
  • Using the Today
  • Now & Date Functions
  • Using theDatedif/ Networkdays/ Eomonth Functions
  • Using theWeeknum Functions
  • Using theEdate/ Networkdays.Intl/ Weekdays.Intl Functions.

Text Functions Using
  • The Mid/ Search/ Left/ Right Functions
  • Using the Trim/ Clean/ Upper/ Lower Functions
  • Using theSubsitute/ Text Functions
  • Using the Trim/ Clean/ Proper/ Dollar Function

Validations
  • Input Messages / Error Alerts/ Drop-Down Lists
  • Conditional Formatting

Advanced Filters
  • Extracting Records with Advanced Filter
  • Using Formulas In Criteria

Advanced Sorting
  • Sorting by Top to Bottom / Left to Right
  • Creating / Deleting Custom List
  • Sort by using Custom List

Hyper / Data Linking
  • Hyper linking data, within sheet / workbook
  • Linking & Updating links between workbooks & application

Math & Trigonometry Functions
  • Using SUMPRODUCT Functions
  • Using FLOOR/ CEILING/ MROUND/ MOD/ QUOTIENT Functions

Statistical Analysis
  • Conduct sensitivity and ‘What if Analysis’, Scenario Analysis, Dashboards, Interactive Financial Models, Monte Carlo Simulation

Financial Functions
  • Loan Amortization Schedule and functions like PV, FV, PMT, Rate, Nper, IPMT, CUMPMT, etc

Summarizing Data
  • Creating Subtotals/Nested Subtotals
  • SUBTOTALS Formula

Outlining
  • Creating/Working with an Automatic/Manual Outline
  • Grouping / Ungrouping

Consolidation
  • Consolidating Data with Identical/Different Layout

Using Auditing Tools
  • Displaying/Removing Dependent & Precedent Arrows
  • Evaluate Formula - Step IN/ Step Out

Custom Views
  • Creating Custom Views
  • Displaying Custom Views
  • Deleting Custom Views

Sharing and Protecting Workbooks
  • Sharing Workbooks & Tracking Changes
  • Protecting sheets / workbooks / Files

Importing & Exporting Data
  • Importing Data from Database/ Text Files/ Web
  • Exporting Data
  • Changing External Data Range

Dashboard Preparation-
  • Dashboard for MIS, Analysis, different dashboards for different business types and business segments, will include a self-assignment (formulas like offset, advance formatting, macros, hyperlink, etc.)

Advance Pivot
  • Formatting, grouping, computing %, Summarizing Values, Auto report Generation –100s with a click, Calculated Fields, Connecting Multiple Sources of data, Use of Slicer and Report Filter to create quick dashboard, Pivot charts, Power pivot and Power View –Introduction, and other Advance features, Consolidating data across multiple tables

Advance Charts
  • Thermometer, Speedometer, Sparklinesand easy formatting techniques

Customizing Ribbon
  • Have your own ribbon like File, View with your designed Functions to perform repetitive tasks, automation and extremely useful time saving technique. Macros
  • Run, record, use, save and work with them

VBA Code Snippets
  • To be able to use free VBA code snippets available online and use them to perform certain tasks like convert a number in word form
Training Cost
  • Training Cost – INR 10,000 + Taxes per day