Excel 2000 Advanced Level
Excel 2003 Advanced Level
http://www.mousetraining.co.uk
© MTC Training Solutions Ltd
Page 155
Table of Contents
Introduction 7
How to use this guide 7
Section 1 9
Using advanced Worksheet functions 9
Names 10
Defining names 10
USING NAMES 14
Conditional & Logical Functions 18
IF STATEMENTS 18
Statistical If Statements 22
AND, OR, NOT 23
Lookup Functions 25
LOOKUP 25
Vector Lookup 25
HLOOKUP 26
VLOOKUP 26
NESTED LOOKUPS 26
Goal Seeking and Solving 26
GOAL SEEK 26
SOLVER 26
Section 2 26
Views, Scenarios & Reports 26
Views & Scenarios 26
Custom Views 26
Scenario Manager 26
Print Reports 26
Section 3 26
Using Excel to Manage Lists 26
Excel Lists 26
List Terminology 26
Sorting Data 26
Custom Sorting Options 26
Adding subtotals to a list 26
Examining subtotals 26
Filtering a List 26
Custom criteria 26
Wildcards 26
Turning off AutoFilter 26
Data Form 26
The Data Form Screen 26
Advanced Filtering 26
Set Criteria 26
Copying filtered data 26
List Statistics 26
Database Functions 26
Pivot Tables 26
Managing pivot tables 26
Modifying a pivot table 26
Grouping pivot table items 26
Section 4 26
Charts 26
Introduction to Charting 26
Terminology 26
Excel Charts 26
ChartWizard 26
Moving and resizing embedded charts 26
Creating Separate Chart Pages 26
Data Layout 26
Chart Toolbar 26
Chart Types 26
Available types 26
Default Chart type 26
Formatting Charts 26
Format chart elements 26
Basic Chart Formats 26
Formatting Axes 26
3-D Chart Formatting Options 26
Elevation 26
Rotation 26
Perspective 26
Manipulating Chart Data 26
Delete a Series Manually 26
Add a Series Manually 26
The Series Function 26
Chart, Edit Series 26
Charting with Blocks of Data 26
Swapping the x and y axes 26
Using Charts to change Data 26
Picture Charts 26
Create a Picture Chart 26
Section 5 26
Templates 26
Introduction to templates 26
Standard templates 26
Custom templates 26
Autotemplates 26
Opening and editing templates 26
Template properties 26
Section 6 26
Auditing 26
Auditing features 26
Tracers 26
Comments 26
Validation circles 26
Go to Special 26
Precendents and Dependants 26
Auditing
Introduction
Excel ’2003 is a powerful spreadsheet application that allows users to produce tables containing calculations and graphs. These can range from simple formulae through to complex functions and mathematical models.
This manual should be used as a point of reference following attendance of the advanced level Excel ’2003 training course. It covers all the topics taught and aims to act as a support aid for any tasks carried out by the user after the course.
The manual is divided into sections, each section covering an aspect of the introductory course. The table of contents lists the page numbers of each section and the table of figures indicates the pages containing tables and diagrams.
Sections begin with a list of objectives each with its own check box so that you can mark off those topics that you are familiar with following the training.
Those who have already used a spreadsheet before may not need to read explanations on what each command does, but would rather skip straight to the instructions to find out how to do it. Look out for the hand icon Fwhich precedes a list of instructions.
The Appendices list the toolbars mentioned within the manual with a breakdown of their functions and tables of shortcut keys.
Keys are referred to throughout the manual in the following way:
[ENTER] – denotes the return or enter key, [DELETE] – denotes the Delete key and so on.
Where a command requires two keys to be pressed, the manual displays this as follows:
[CTRL][P] – this means press the letter “p” while holding down the Control key.
When a command is referred to in the manual, the following distinctions have been made:
When menu commands are referred to, the manual will refer you to the menu bar – E.g. “Choose File from the menu bar and then Print”.
When dialog box options are referred to, the following style has been used for the text – “In the Page Range section of the Print dialog, click the Current Page option”
Dialog box buttons are shaded and boxed – “Click OK to close the Print dialog and launch the print.”
Within each section, any items that need further explanation or extra attention devoted to them are denoted by shading. For example:
“Excel will not let you close a document that you haven’t already saved changes to without prompting you to save.”
At the end of each section there is a page for you to make notes on and a “Useful Information” heading where you will find tips and tricks relating to the topics described within the section.
Section 1
Using advanced Worksheet functions
Objectives
By the end of this section you will be able to:
q Create and use names in workbooks
q Understand and use conditional formulae
q Set up lookup tables and use LOOKUP functions
q Use the Goal Seek
q Use the Solver
When entering formulae or referring to any area on the spreadsheet, it is usual to refer to a "range". For example, B6 is a range reference; B6:B10 is also a range reference. A problem with this sort of reference is that it is not always easy to remember what cells to reference. It may be necessary to write down the range, or select it, which often means wasting time scrolling around the spreadsheet. Instead, Excel offers the chance to name ranges on the spreadsheet, and to use these names to select cells, refer to them in formulae or use them in Database, Chart or Macro commands.
There are a number of ways to set up names on a spreadsheet. A common way is to use the Insert, Name, Define menu. In the example, there is a range of revenue figures that could be named "REVENUE";
F To name cells:
Mouse
1. Select the cells you wish to name.
2. Choose Insert, Name, Define. The following dialog box appears;
...
rafcez