Excel 2007 Advanced. Part I

131 Pages·5.386 MB·English

by  Moffat S.

Stephen Moffat, The Mouse Training Company
Excel 2007 Advanced
Part I
Download free ebooks at bookboon.com
2
Excel 2007 Advanced: Part I
© 2012 Stephen Moffat, The Mouse Training Company & Ventus Publishing ApS
ISBN 978-87-403-0033-8
Download free ebooks at bookboon.com
3
Excel 2007 Advanced: Part I Contents
Contents
Introduction 6
Section 1 Advanced worksheet Functions 8
1.1 Names 9
1.2 Using Names 15
1.3 Conditional & Logical Functions 19
1.4 AND, OR, NOT 25
1.5 Lookup Functions 27 360°
1.6 Other Useful Functions 35
1.7 Data consolidation 47 .
thinking
Section 2 Views, Scenarios, goal seek and solver 51
360°
2.1 Goal Seeking and Solving 52
2.2 Views & Scenarios 62
.
thinking
Section 3 Using Excel to Manage Lists 71
3.1 Excel Lists, List Terminology 72
3.2 Sorting Data 73
3.3 Adding Subtotals to a List 79
3.4 Filtering a List 82
360°
.
thinking
360°
ert
v .
d
a thinking
e
h
k t
c
cli
e
s
a
e
Pl
Discover the truth at www.deloitte.ca/careers Discover the truth at www.deloitte.ca/careers
© Deloitte & Touche LLP and affiliated entities.
Discover the truth at www.deloitte.ca/careers © Deloitte & Touche LLP and affiliated entities.
Download free ebooks at bookboon.com
© Deloitte & Touche LLP and affiliated entities. Discover the truth4 a t www.deloitte.ca/careers
© Deloitte & Touche LLP and affiliated entities.
Excel 2007 Advanced: Part I Contents
3.5 List Statistics 100
3.6 Pivottables 103
3.7 Managing Pivottables 118
Section 4 Charts Part II
4.1 Introduction to Charting Part II
4.2 Formatting Charts Part II
Section 5 Templates Part II
5.1 Introduction to Templates Part II
Section 6 Auditing Part II
6.1 Auditing Features Part II
Section 7 Other formatting Part II
7.1 Formatting Cells Part II
Section 8 Other Excel features Part II
8.1 Inserting, Formatting and Deleting Objects Part II
8.2 Reviewing Part II
8.3 Proofing Tools Part II
Excel 2007 specifications and limits Part II
Increase your impact with MSM Executive Education
ert
v
d
a
e
h
k t
c
cli
e For almost 60 years Maastricht School of Management has been enhancing the management capacity
s
ea of professionals and organizations around the world through state-of-the-art management education.
Pl
Our broad range of Open Enrollment Executive Programs offers you a unique interactive, stimulating and
multicultural learning experience.
Be prepared for tomorrow’s management challenges and apply today.
For more information, visit www.msm.nl or contact us at [phone] or via [e-mail]
F tohr em ogrel oinbfoarmllaytio nn, evitswit wowrwk.mesdm .mnl aorn coangtaectm use ant t+ 3s1c 4h3 o38o 7l0 808
or via [e-mail]
Download free ebooks at bookboon.com
Executive Education-170x115-B2.indd 1 18-08-11 15:13
5
Excel 2007 Advanced: Part I Introduction
Introduction
Excel 2007 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.
How To Use This Guide
This manual should be used as a point of reference following attendance of the introductory level Excel 2007 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.
Objectives
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.
Instructions
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 arrow icon which precedes a list
of instructions.
Appendices
The Appendices list the Ribbons mentioned within the manual with a breakdown of their functions and tables of shortcut
keys.
Keyboard
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.
Commands
When a command is referred to in the manual, the following distinctions have been made:
Download free ebooks at bookboon.com
6
Excel 2007 Advanced: Part I Introduction
When Ribbon commands are referred to, the manual will refer you to the Ribbon – E.g. “Choose HOME from the Ribbons
and then B for bold”.
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.”
Notes
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 file that you have not already saved changes to without prompting you to save.”
Tips
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.
Download free ebooks at bookboon.com
7
Excel 2007 Advanced: Part I Advanced orrsseet unctions
Section 1 Advanced worksheet
Functions
➢ Objectives
By the end of this section you will be able to:
• Create and use NAMES in workbooks
• Understand and use conditional formulae
• Set up LOOKUP tables and use LOOKUP functions
• Use the GOAL SEEK
• Use the SOLVER
Download free ebooks at bookboon.com
8
Excel 2007 Advanced: Part I Advanced orrsseet unctions
1.1 Names
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.
Defining Names
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 sales figures that could be named “1st_Qtr”;
➢ To name cells:
Mouse
i) Select the cells you wish to name.
ii) Click the DEFINE NAME button on the in the DEFINED NAMES GROUP on the FORMULAS Ribbon
iii) The NEW NAME dialog box appears
iv) To name the cells, simply type a name in the NAME box and choose OK.
Download free ebooks at bookboon.com
9
Excel 2007 Advanced: Part I Advanced orrsseet unctions
OR
Keyboard
i) Select the cells you wish to name.
ii) Type directly in the NAME BOX to the left of the FORMULA BAR.
iii) Press RETURN
A range can include any selection of cells, not necessarily a contiguous row or column. Names can be up to 255 characters
in length, must start with a letter and cannot include spaces. Names are not case sensitive.
In the example, these cells would be called “1st_Qtr”. From now on, any reference to the range C6:C10 can be made with
the name “1st_Qtr”; Notice that the name box, on the left-hand side of the formula bar now displays the name “1st_Qtr”;
It will do so whenever cells C6:C10 are selected;
Download free ebooks at bookboon.com
10