Advanced Excel 2007/2010 Training

Who can learn this

· This program is best suited for people who use Microsoft Excel in their day-to-day work, and know the basics fairly well, but would like to extend their knowledge of the more advanced functions of Excel to become more productive and make the most of Excel 2007 / 2010

· The course is designed for people who would want to learn how sort and filter data, import and export data sets, and analyze data using the rich features provided in Excel. It will impart the necessary skills to create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import and export data.

Prerequisite

· Basic Excel Knowledge

End Objective

· Participants will learn to use advanced functions of Excel to improve productivity, enhance spreadsheets with templates, charts, graphics, and formulas and streamline their operational work.

· They will apply visual elements and advanced formulas to a worksheet to display data in various formats.

· They will also learn how to automate common tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and leverage on Excel’s advanced functionality to simplify and streamline their day-to-day work.

· Calculate with advanced functions & formulas.

· Organize worksheet and table data using multiple techniques.

· Create and modify charts & graphs.

· Analyze data using Pivot Tables and Pivot Charts.

· Insert graphic objects.

· Customize and enhance workbooks and the Microsoft® Excel® 2007/2010 environment.

· Collaborate with others using workbooks

· Audit worksheets

· Analyze data.

· Work with multiple worksheets & workbooks

· Import and export data in Excel

Course Durations : 16 Hours

Course Content

Lesson 1: Working with Custom & Conditional Formats in Excel 2010

· Creating a Custom Format

· Create a custom number format

· Conditional Formatting

· Creating Conditional Formatting

· Editing Conditional Formatting

· Adding Conditional Formatting

· Deleting Conditional Formatting from the Selected Range

Lesson 2: Using Formulas & Functions in Excel 2010

· Relative Cell Reference

· Absolute Cell Reference

· Using Text Functions

· Left Function

· Upper Function

· Concatenating the text values

· Date Functions

· Using Today Function

· Calculating using Date Function

· Using Sum Functions

· Using CountA Functions

Lesson 3: Working with Range Names in MS Excel 2010

· Using Range Names

· Creating Range Names

· Using Range Names in Formulas

· Creating Range Names from Headings

· Deleting Range Names

· Creating 3-D Range Names

Lesson 4: Using Advanced Functions in Excel 2010

· Using If Functions

· Using Nested If Functions

· Using Sum If Functions

· Using Count If Functions

· Using Vlookup Functions

· Using HLOOKUP Functions

Lesson 5: Managing Tables using Excel 2010

· Creating Tables

· Naming the Table

· Changing the Table Style

· Creating a Total Row

· Creating a calculated column

· Using Filtering in Table

· Removing the Duplicate Record

Lesson 6: Using Auto Filter in Excel 2010

· Sorting Data

· Using AutoFilter

· Creating a Custom AutoFilter

· Using Advanced Filter

· Using Database Functions

Lesson 7: Getting Most from your Data using Microsoft Excel 2010

· Creating Subtotal

· Using Automatic Outline

· Grouping Data Manually

· Using Data Validation

Lesson 8: Working with Charts in Excel 2010

· Creating a Chart

· Formatting the Chart

· Adding Labels

· Changing the Chart Type

· Changing the Data Source

· Changing the Chart Location

Lesson 9: Creating Pivot Tables in Excel 2010

· Creating a PivotTable Report

· Refreshing a PivotTable Report

· Changing the Summary Function

· Adding New Fields to a PivotTable Report

· Using Expand and Collapse Buttons

· Hiding/Unhiding PivotTable Report Items

· Inserting Slicers

· Creating Report Filter Pages

· Creating a PivotChart Report

· Exercise – Creating/Revising PivotTables

Lesson 10: Creating and Using Shared Workbooks using Excel 2010

· Sharing a Workbook

· Opening and Editing a Shared Workbook

· Tracking Changes

· Resolving Conflicts in a Shared Workbook

Lesson 11: Working with Worksheet Protection

· Protecting your Worksheets

· Protecting range with password

· Protecting your Workbook

· Protecting your Excel Files

Lesson 12: Working with Multiple Workbooks in Excel 2010

· Linking Workbooks

· Editing the Link

· Consolidating Workbook

Lesson 13: Working with Macros in Excel 2010

· Display the Developers Tab

· Changing Macro Security Setting

· Running a Macro

· Recording a Macro

· Deleting a Macro