Microsoft Excel 2003: Intermediate (2 day)

Course Overview

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.

CourseMonster specialises in offering Microsoft training to corporations and individuals alike, we are happy to help with any of your Microsoft training requirements.

Related Courses

• Microsoft Excel 2003: Introduction for Beginners (1 day)
• Microsoft Excel 2003: Intermediate (1 day)
• Microsoft Excel 2003: Introduction for Beginners (2 day)
• Microsoft Excel 2003: Existing User
• Microsoft Excel 2003: Advanced (2 day)
• Microsoft Excel 2003: Advanced (1 day)
• Microsoft Excel 2003: Visual Basic for Applications (VBA)
• Microsoft Excel 2003: VB & Macros
• Microsoft Excel 2000 XP 2003: Advanced - Formulae and Functions
• Microsoft Excel 2003: Programming with VBA

Audience

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.

Skills Gained

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

Prerequisites

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

Course Outline

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