Workshop on Advanced Excel

Dates: 1, 2, 9, 15 & 16 December 2012                         CPE: 30 hrs

Venue: SIBER, Univeristy Road, Kolhapur                                

Fees (Incl lunch): Rs. 3,000/- (for members)

Workshop 1 ( Saturday,1^st December 2012)

Objective

§  Learn New features of Excel 2010

§  Acquaintance with Excel Interface

§  Basic Functions

Overview of the Basics

·         Introduction with new Excel interface

·         Default settings of Excel

·         Introduction to Ribbon

·         Quick Access Toolbar

·         Contextual Tabs

·         Ribbon Customization

Formula and Functions

·         Text Functions

·         Cell Referencing

·         Logical Functions

Workshop 2 (Sunday, 2^nd December 2012)

Objective

§  Validating data

§  Advanced Functions

Formula and Functions

·         Data Validation

·         Lookup Functions (Vlookup, Hlookup, Index, Match etc.)

·         Sumif, Countif, Sumproduct and Averageif

·         Statistical and Financial Functions

Workshop 3 (Sunday, 9^th December 2012)

Objective

§  Protect Sheets and Workbook

§  Sorting and Filtering data

§  Working with tables

Sharing and Protection

·         Sharing and Protection

·         Introduction to Tables

Sorting and Filtering

·         Single and Multiple level Sorting, Custom Sorting

·         Advance Filters

·         Single and Multiple level Subtotals including grouping

Working with styles

·         Conditional Formatting

Workshop 4 (Saturday 15^th December 2012)

Objective

§  Pivot tables

§  Consolidation

§  Analysis

Pivot tables

·         Pivot Table

·         Power Pivot

Consolidation

·         Consolidation

What if Analysis

·         What if Analysis and Scenario Manager

Workshop 5 (Sunday, 16^th December 2012)

Objective

§  Presenting data visually

§  Automating tasks with macros

Presenting data

·         Charts and Dashboards

§  Column Chart 2D and 3D

§  Bar Charts

§  Stacked Column Charts

§  Line Chart

§  Line and Column Chart (Limitation of primary axis)

§  Pie Chart

§  Limitations of Pie Chart (Doughnut Chart)

§  Pie Chart with developer tools

§  Thermometer Chart

§  Scroll Chart

Macros

·         Macro (Recording, Editing and Basic Formulas)

§  Recording Macro

§  Limitation of recording macro

§  Recording Macro with Relative reference

§  Editing Macro

§  Executing Macro in different ways

§  Introduction to VBA window

§  Creating Basic Functions in VBA