PTP: Microsoft Excel 2007 Intermediate (2 days)

Home / Training Courses / Microsoft Excel 2007 Intermediate (2 days)

Microsoft Excel 2007 Intermediate (2 days)

Training Description

The skills and knowledge acquired in Microsoft Excel 2007 Intermediate are sufficient to be able to use and operate the software at an efficient level.

The Course Suits

Microsoft Excel 2007 Intermediate is designed for users who are keen to extend their understanding and knowledge of the software. Microsoft Excel 2007 Intermediate assumes the delegate has attended the fundamentals course or has equivalent knowledge.

Training Benefits

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

understand and use formula cell referencing to create more complex formulas
use a range of formula techniques and use a range of logical functions
use a range of lookup and reference functions
use the mathematical functions in Excel
use the date and time functions to perform calculations
use a range of text and information functions
apply a range of number formatting techniques to data
apply borders to cells and ranges
apply a variety of page setup techniques
create and work with headers and footers
use a range of find and replace techniques
sort data in a list in a worksheet
filter data in a table and use Advanced Filters to view or extract matching records from a list of data
use a range of techniques to enhance charts
modify Excel options
protect data in worksheets and workbooks
use data linking to create more efficient workbooks
group cells and use outlines to manipulate the worksheet
perform What-If Analysis on a range of data using Data Tables
create and work with customised views
use comments to provide additional context to your worksheet data
use the Formula Auditing tools to find and fix formula errors
analyse your data and visually enhance your findings using Conditional Formatting techniques
manage your data in a table format
apply built-in or customised cell styles to format worksheet data
apply formatting, copy formatting or clear formatting on worksheet data
open and arrange multiple workbooks

Course Timetable

The course focuses on practical work within the product giving delegates ample opportunity to use their PC skills through a series of graded exercisesTopics covered include:

Formula Referencing

Absolute Versus Relative Referencing

Relative Formulas

Problems With Relative Formulas

Creating Absolute References

Creating Mixed References

Formula Techniques

Scoping A Formula

Developing A Nested Function

Creating Nested Functions

Editing Nested Functions

Copying Nested Functions

Using Concatenation

Switching To Manual Recalculation

Forcing A Recalculation

Pasting Values From Formulas

Pasting Formulas As Pictures

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

Lookup Functions

Understanding Data Lookup Functions

Using CHOOSE

Using VLOOKUP

Using VLOOKUP For Exact Matches

Using HLOOKUP

Using INDEX

Using MATCH

Understanding Reference Functions

Using ROW And ROWS

Using COLUMN And COLUMNS

Using ADDRESS

Using INDIRECT

Using OFFSET

Maths Functions

Understanding Maths Functions

Using SQRT

Using ABS

Using INT

Using TRUNC

Using ROUND

Using ROUNDDOWN And ROUNDUP

Using ODD And EVEN

Using CEILING

Using MROUND

Using PRODUCT

Using SUMIF

Using SUMIFS

Using SUMPRODUCT

Date and Time Functions

Understanding Date And Time Functions

Using NOW

Using HOUR And MINUTE

Using TODAY

Calculating Future Dates

Using DATE

Using Calendaring Functions

Using WEEKDAY

Using WEEKNUM

Using WORKDAY

Using EOMONTH

Text Functions

Understanding Text Functions

Using PROPER

Using UPPER And LOWER

Using CONCATENATE

Using LEFT And RIGHT

Using MID

Using LEN

Using SUBSTITUTE

Using T

Using TEXT

Using VALUE

Information Functions

Understanding Information Functions

Using CELL

Using ISBLANK

Using ISERR

Using ISODD And ISEVEN

Using ISNUMBER And ISTEXT

Using TYPE

Number Formatting Techniques

Using Alternate Currencies

Formatting Dates

Formatting Time

Creating Custom Formats

Applying Borders

Understanding Borders

Applying A Border To A Cell

Applying A Border To A Range

Applying A Bottom Border

Applying Top And Bottom Borders

Removing Borders

The More Borders Option

Using The More Borders Option

Drawing and Erasing Borders

Page Setup

Understanding Page Layout

Using Built In Margins

Setting Custom Margins

Changing Margins By Dragging

Centring On A Page

Changing Orientation

Specifying The Paper Size

Setting A Print Area

Clearing A Print Area

Inserting Page Breaks

Using Page Break Preview

Removing Page Breaks

Setting A Background

Clearing The Background

Settings Rows As Repeating Print Titles

Settings Columns As Repeating Print Titles

Clearing Print Titles

Printing Gridlines

Printing Headings

Scaling To A Percentage

Fit To A Specific Number Of Pages

Strategies For Printing Larger Worksheets

The Page Setup Header And Footer Tab

Headers And Footers

Understanding Headers And Footers

Adding A Quick Header

Adding A Quick Footer

Switching Between Headers And Footers

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

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

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

Understanding Filtering

Applying And Using A Filter

Clearing A Filter

Creating Compound Filters

Multiple Value Filters

Creating Custom Filters

Using Wildcards

Advanced Filtering

Understanding Advanced Filtering

Using an Advanced Filter

Extracting Records Using Advanced Filters

Using Formulas in Criteria

Understanding Database Functions

Using Database Functions

Using DSUM

Using DMIN

Using DMAX

Using DCOUNT

Charting Techniques

Understanding Chart Layout Elements

Adding A Chart Title

Adding Axes Titles

Positioning The Legend

Showing Data Labels

Showing A Data Table

Modifying The Axes

Showing Gridlines

Formatting The Plot Area

Showing The Plot Area

Adding A Trendline

Adding Error Bars

Adding A Text Box To A Chart

Drawing Shapes In A Chart

Chart Object Formatting

Understanding Chart Object Formatting

Selecting Chart Elements

Using Shape Styles To Format Objects

Changing Column Colour

Changing Pie Slice Colour

Changing Bar Colours

Changing Chart Line Colours

Using Shape Effects

Filling The Chart Area And The Plot Area

Filling The Background

The Format Dialog Box

Using The Format Dialog Box

Using Themes

Setting Excel Options

Understanding Excel Options

Personalising Excel

Setting The Default Font

Setting Formula Options

Understanding Save Options

Setting Save Options

Setting The Default File Location

Setting Advanced Options

Validating Data

Understanding Data Validation

Creating a Number Range Validation

Testing Data Validation

Creating an Input Message

Creating an Error Message

Creating Drop-Down Lists

Using Formulas As Validation Criteria

Circling Invalid Data

Removing Invalid Data Circles

Copying Validation Settings

Protecting Data

Understanding Data Protection

Providing Total Access To Cells

Protecting A Worksheet

Working With A Protected Worksheet

Disabling Worksheet Protection

Providing Restricted Access To Cells

Password Protecting A Workbook

Opening A Password Protected Workbook

Removing A Password From A Workbook

Data Linking

Understanding Data Linking

Linking Between Worksheets

Linking Between Workbooks

Updating Links Between Workbooks

Grouping And Outlining

Understanding Grouping And Outlining

Creating An Automatic Outline

Working With An Outline

Creating A Manual Group

Grouping By Columns

Data Tables

Data Table Components

Using a Simple What-If Model

Creating A One-Variable Table

Using One-Variable Data Tables

Creating A Two-Variable Data Table

Custom Views

Understanding Custom Views

Adding a Custom View

Creating a Custom View

Working with Custom Views

Working with Comments

Understanding Comments

Adding Worksheet comments

Managing Worksheet Comments

Printing Comments

Deleting Comments

Formula Auditing

Understanding Tracing Precedents

Understanding Tracing Dependents

Tracing Precedents and Dependents

Showing Formulas and Cell Arguments

Common Error Messages

Understanding Error Checking

Checking for Errors

Tracing Errors

Evaluating Formulas

Using the Watch Window

Dealing with Circular References

Conditional Formatting

Understanding Conditional Formatting

Highlighting Cells Containing Values

Highlighting Cells Containing Text

Highlighting Duplicate Values

Using Top and Bottom Rules

Using Data Bars

Using Colour Scales

Using Icon Sets

Creating Custom Rules

The Conditional Formatting Rules Manager

Managing Rules

Clearing Rules

Conditional Formatting - Examples and Guidelines

Table Formatting

Understanding Table Formatting

Applying a Table Style

Table Styles and Table Options

Filtering a Table

Sorting a Table

Converting a Table to a Range of Data

Cell Styles

Understanding Cell Styles

Applying Cell Styles

Creating Custom Styles

Managing Styles

Formatting Techniques

Copying Formats

Formatting Cells

The Format Cells Dialog Box

Multiple Workbooks

Working with Multiple Workbooks

Viewing Workbooks Side by Side

Arranging Multiple Workbooks

Please Choose from the list below:

Microsoft Excel 2007 Intermediate (2 days) training course available to book:

Microsoft Excel 2007 Intermediate (2 days) courses
Training Location	Training Date	Training Price	Book Trainging
London (Westminster)	16-08-2010	£645.00	Book Microsoft Excel 2007 Intermediate (2 days) Course Online
London (Westminster)	09-09-2010	£645.00	Book Microsoft Excel 2007 Intermediate (2 days) Course Online
London (Westminster)	14-10-2010	£645.00	Book Microsoft Excel 2007 Intermediate (2 days) Course Online
London (Westminster)	15-11-2010	£645.00	Book Microsoft Excel 2007 Intermediate (2 days) Course Online
London (Westminster)	13-12-2010	£645.00	Book Microsoft Excel 2007 Intermediate (2 days) Course Online

Book inhouse

Book One to One

Book U-Choose

To book the Microsoft Excel 2007 Intermediate (2 days) course through our U-Choose product select location below (£425 per delegate min 2 delegates):

date:
location:

Microsoft Excel 2007 Intermediate (2 days)

Training Description

The Course Suits

Training Benefits

Course Timetable

Microsoft Excel 2007 Intermediate (2 days) training course available to book:

Book inhouse

Book One to One

Book U-Choose

Quick Training Search

Special offers

Open course schedule

U-Choose

Download our brochure

Special Offers Subscription