Microsoft Excel 2007 Intermediate

Details

Time: 9am to 4pm
Venue:AP Training Room
Cost: £295
Included in the price: Learning manual for the course; lunch, refreshments and post-course support

Course Description

Microsoft Excel 2007 Intermediate is designed for users who are keen to extend their understanding and knowledge of the software. The skills and knowledge acquired in Microsoft Excel 2007 Intermediate are sufficient for users to be able to operate the software at an efficient level.

Course Design

The course is a full-day tutor-led course that combines tutoring and practical work.

Target Audience

Microsoft Excel 2007 Intermediate assumes little or no knowledge of the software. However, it would be beneficial to have a general understanding of personal computers and the Windows operating system environment, and to have used a mouse and keyboard. This course is intended for individuals who are in employment that involves using Microsoft Excel for creating spreadsheets, maintaining and formatting worksheets and workbooks, sorting and filtering data, and creating formulas and functions within spreadsheets.

Learning Objectives

At the completion of Microsoft Excel 2007 Intermediate you should be able to:

• Work with various elements of a worksheet
• Align the contents of cells in a number of ways
• Use the fill operations available to fill a data series
• Understand and use formula cell referencing to create more complex formulas
• Understand, create and work with the formulas and functions used to perform calculations
• Use a range of logical functions
• Use a range of find and replace techniques
• Create and work with headers and footers
• Sort data in a list in a worksheet
• Filter data in a table

Course Content

Working with a Worksheet                                                                                                            

Understanding Worksheets
Changing Worksheet Views
Worksheet Zooming
Viewing the Formula Bar
Viewing the Gridlines
Viewing the Ruler
Inserting Cells
Deleting Cells
Inserting Columns
Inserting Rows
Deleting Rows and Columns
Switching between Worksheets

 

Cell Alignment                                                                                                                                 

Understanding Cell Alignment
Aligning Right
Aligning to the Centre
Aligning Left
Aligning Top
Aligning Bottom
Aligning to the Middle
Rotating Text
Indenting Cells
Wrapping and Merging Text
Merging and Centring
Merging Cells
Unmerging Cells            

Filling Data                                                                                                                                      

Filling a Series
Filling a Growth Series
Filling a Series Backwards
Filling using Options
Creating a Custom Fill List
Modifying a Custom Fill List
Deleting a Custom Fill List          

Formula Referencing                                                                                                                      

Absolute versus Relative Referencing
Relative Formulas
Problems with Relative Formulas
Creating Absolute References
Creating Mixed References         

Formulas and Functions                                                                                                                  

Understanding Formulas
Creating Formulas that Add
Creating Formulas that Subtract
Formulas that Multiply and Divide
Understanding Functions
Using the SUM Function to Add
Summing Non-Contiguous Ranges
Calculating an Average
Finding a Maximum Value
Finding a Minimum Value
More Complex Formulas
What-if Formulas          

 

Logical Functions                                                                                                                            

Understanding Logical Functions
Using IF to Display Text
Using IF to Calculate Values
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT        

 

Finding and Replacing                                                                                                                     

Understanding Find And Replace Operations
Finding Text
Finding Cell References in Formulas
Replacing Values
Using Replace to Change Formulas
Replacing within a Range
Finding Formats
Finding Constants using Go To Special
Finding Formulas using Go To Special
Finding the Current Region
Finding the Last Cell      

Headers and Footers

Understanding Headers and Footers
Adding a Quick Header
Adding a Quick Footer
Typing Text into Headers and Footers
Modifying Headers and Footers
Adding Page Numbering
Adding Date Information
Adding Workbook Information
Adding a Picture
Formatting Headers and Footers
Dragging Margins for Headers and Footers
Creating a Different First Page
Different Odd and Even Pages     

Sorting Data                                                                                                                                   

Understanding Lists
Performing an Alphabetical Sort
Performing a Numerical Sort
Sorting on more than one Column
Sorting Numbered Lists
Sorting by Rows
Filtering Data
Applying and using a Filter
Clearing a Filter
Multiple Value Filters
Creating Custom Filters
Using Wildcards