Advance Program in MS EXCEL 2007- IGS LABS - Bangalore - pivot table in 2007

MS excel 2007 – Intermediate and Advanced level

Duration: 20 hours (5 hours x 4 Days)

Course Outline

DAY 1

§ MS Excel interface: The levels of Command organization – Office Button, Tabs, Groups, and Dialogue box launcher, Excel Screen

§ Excel Options, Customizing the Quick Access Tool Bar

§ Formatting Numbers and Cells, Built-in and Custom Formats

§ Conditional Formatting of data

§ Cut, Copy, and Paste special; Transposing Columns to Rows, Rows to Columns

§ Data Sorting: Multilevel Sorting, Sorting with and without headers, Sorting using the Custom List

§ Filtering Data: Using Auto Filter, Filtering Data with Advanced Filter

§ Data Validation, Creating drop down lists using different data sources

§ Creating custom lists for Autofill

§ Removing Duplicates in data

§ Formatting data as a Table

§ Grouping Worksheets, Protecting Worksheets and Workbooks

§ Viewing and Editing Workbook Properties

§ Summarize

DAY 2

§ Introduction to Formulas and Functions – Syntax, Parameters, and Values

§ Giving Formulas directly into the cells, Using the Wizard

§ Different types of Cell Referencing

§ Creating and changing cell references, Linking data within worksheets and workbooks

§ Creating Hyperlinks

§ Grouping Data, Subtotal, and Ungrouping Data

§ Excel Functions: Text, Logical, Lookup, Statistical, Financial including VLOOKUP, HLOOKUP, IF, AND, OR.

§ Creating Named Ranges, Using Named Ranges in the Formulas, Using the Name Manager

§ Extensive practice on Formulas and Functions

§ Formula Auditing

§ Summarize

DAY 3

§ Practice on Formulas and Functions continued

§ Nested Functions and Array Functions

§ Introduction to Pivot tables. Creating Pivot tables and analyzing data, Refreshing the Pivot Table with Data, Automatic or manual updating of the report layout, Grouping Data within a Pivot Table

§ What-if analysis: scenarios, data tables, and Goal Seek

§ Extensive practice on Data Analysis tools

§ Summarize

DAY 4

§ Creating Charts: Chart Types, Plotting data series from worksheet rows and columns; Changing the Chart Type

§ Creating Combo Charts

§ Introduction to Macros, Preparing a workbook for Macros,

§ Recording Macros using Absolute and Relative Reference

§ Visual Basic Interface and Editor

§ Extensive practice on Macros

§ Summarize

Ad details

Type:

Classes

Type:

Computer - Multimedia Classes