Microsoft Excel 2002 (XP): Intermediate (2 day)

About this class

In this course you will learn how to use the more complex functions and practise them so that you can build the confidence to use them.

This training may be available onsite; please contact us if you are interested.

Who will the lesson benefit?

This programme is for you if you have some experience in working with Excel and now want to broaden your knowledge and use the more advanced features.

What delegates will learn

On completion of this programme you will be able to:

• Use advanced functions, such as PMT, FV, IF, DATE, NOW, VLOOKUP and HLOOKUP
• Set up a "three-dimensional" worksheet structure
• Link two or more workbooks
• Use the Internet features provided by Excel
• Set up and use an Excel template
• Audit a worksheet
• Use Excel's database and data analysis features
• Transfer data between Excel and other applications
• Manage a workgroup project

Examination details

This course will prepare you for the following exams and certification tracks shown. Please note that the exam may not be included in the price shown: please contact us to check.

Microsoft® Certification Tracks  »
    Microsoft® Exams  »
       Exam excel2002: MOS: Excel 2002

What prerequisites are required

A basic knowledge of using a PC and experience within the Windows environment is an essential pre-requisite for this programme. Some experience in working with Excel is also required. In particular, you should be able to:

• Start, and exit from Excel
• Open, save and close a workbook; create and print a worksheet
• Perform basic worksheet editing operations, such as copying and moving cell entries, clearing cells, and inserting rows and columns
• Perform basic worksheet formatting operations, such as adjusting column width and row height, realigning cell entries and changing the font and number format of cell entries
• Use basic Excel functions, such as SUM, AVERAGE, MAX and MIN

Contents of this class

Using financial functions

• Using the PMT and FV functions
• Setting up a financial analysis table

Using logical functions

• Using the IF and nesting IF functions

Using date/time functions

• Using the DATE and NOW functions

Using lookup functions

• Using the VLOOKUP and HLOOKUP functions

Linking worksheets within a workbook

• Setting up related worksheets within a workbook
• Moving the cell pointer from one worksheet to another
• Copying data from one worksheet to another; editing the data
• Entering a formula to link related worksheets
• Formatting multiple worksheets in one operation
• Moving and copying worksheets
• Previewing and printing multiple worksheets in one operation

Linking multiple workbooks

• Entering a formula to link multiple workbooks; creating a workspace

Using Excel Internet features

• Accessing the internet from Excel
• Linking an Excel workbook to a web page, saving Excel data as a web page

Additional data entry and formatting techniques

• Restricting cell entries to specific numbers, dates, times or entries in a list
• Applying number formats to cell entries
• Creating a custom number format
• Applying conditional formatting to a worksheetWorking with a template
• Creating a template; modifying the default template style
• Basing a new workbook on a custom template

Auditing a worksheet

• Tracing precedents, dependents and errors in a worksheet

Working with a database

• What is a database?
• Totalling database entries, filtering and sorting database records
• Specifying a criteria range in a filter operation
• Using a data form to enter database records
• Querying an external database

Analysing data (Part 1)

• Creating a pivot table report
• Resetting a summary function in a pivot table report
• Creating a pivot chart report, creating a pivot table report for the internet

Analysing data (Part 2)

• Using the solver, outlining a worksheet
• Additional data analysis topics

Importing and exporting data

• Importing data from other applications in a text file into Excel and a table in an HTML file into Excel
• Exporting Excel data into other applications

Using Excel workgroup features

• Tracking changes and inserting comments in a worksheet
• Accepting and rejecting proposed changes/reviewing comments
• Protecting a shared or unshared workbook
• Additional workgroup topics

Review of Programme

Optional follow-on classes

• Excel 2002/2003 VBA Programming
• Accounting Budgeting and Forecasting
• Introduction to Strategic Financial Management