[PDF]Excel 2007 Advanced Part I

bookboo com




Excel 2007 Advanced: Part I

Stephen Moffat, The Mouse Training Company



■ Liir* Microsoft*

Li&Office 2007

Excel: Advanced

Part I




Download free books at

bookboon.com



Stephen Moffat, The Mouse Training Company

Excel 2007 Advanced

Part I



Download free eBooks at bookboon.com



2



Excel 2007 Advanced: Part I

© 201 2 Stephen Moffat, The Mouse Training Company & bookboon.com
ISBN 978-87-403-0033-8



Download free eBooks at bookboon.com



3



Excel 2007 Advanced: Part I



Contents



Contents



Introduction



Section 1 Advanced worksheet Functions

1 . 1 Names

1.2 Using Names

1.3 Conditional & Logical Functions

1.4 AND, OR, NOT

1.5 Lookup Functions

1.6 Other Useful Functions

1.7 Data consolidation



8

9
15
19

25
27
35
47



Section 2 Views, Scenarios, goal seek and solver

2.1 Goal Seeking and Solving

2.2 Views & Scenarios



51

52
62



Section 3 Using Excel to Manage Lists

3.1 Excel Lists, List Terminology

3.2 Sorting Data

3.3 Adding Subtotals to a List



71

72
73
79




Masters in Management



London

Business
School



Designed for high-achieving graduates across all disciplines, London Business School's Masters
in Management provides specific and tangible foundations for a successful career in business.

This 12-month, full-time programme is a business qualification with impact. In 2010, our MiM
employment rate was 95% within 3 months of graduation*; the majority of graduates choosing to
work in consulting or financial services.

As well as a renowned qualification from a world-class business school, you also gain access
to the School's network of more than 34,000 global alumni - a community that offers support and
opportunities throughout your career.

For more information visit www.london.edu/mm, email mim@london.edu or
give us a call on +44 (0)20 7000 7573.

* Figures taken from London Business School's Masters in Management 2010 employment report



Download free eBooks at bookboon.com



Click on the ad to read more



Excel 2007 Advanced: Part I



Contents



3.4 Filtering a List

3.5 List Statistics

3.6 Pivottables

3.7 Managing Pivottables



82
100
103
118



Section 4 Charts

4. 1 Introduction to Charting

4.2 Formatting Charts



Part II

Part II
Part II



Section 5 Templates

5.1 Introduction to Templates



Part II

Part II



Section 6 Auditing

6. 1 Auditing Features



Part II

Part II



Section 7 Other formatting

7. 1 Formatting Cells



Part II

Part II



Section 8 Other Excel features

8.1 Inserting, Formatting and Deleting Objects

8.2 Reviewing

8.3 Proofing Tools



Opportunities for Internships



Part II

Part II
Part II
Part II




EADS unites a leading aircraft manufacturer, the world's largest
helicopter supplier, a global leader in space programmes and a
worldwide leader in global security solutions and systems to form
Europe's largest defence and aerospace group. More than
140,000 people work at Airbus, Astrium, Cassidian and Eurocopter,
in 90 locations globally, to deliver some of the industry's most
exciting projects.

An EADS internship offers the chance to use your theoretical
knowledge and apply it first-hand to real situations and assignments
during your studies. Given a high level of responsibility, plenty of



learning and development opportunities, and all the support you need,
you will tackle interesting challenges on state-of-the-art products.

We welcome more than 5,000 interns every year across
disciplines ranging from engineering, IT, procurement and
finance, to strategy, customer support, marketing and sales.
Positions are available in France, Germany, Spain and the UK.



To find out more and apply, visit www.jobs.eads.com. You can also
find out more on our EADS Careers Facebook page.



d, AIRBUS ASTRIUM ® CASSIDIAN EUROCOPTER




EADS



Download free eBooks at bookboon.com



Click on the ad to read more



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 worksheet Functions



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



Excellent Economics and Business programmes at:

university of
groningen





www.rug.nl/feb/educal



Download free eBooks at bookboon.com




tart r
of a successful,
international career."



CLICK

to discover why both socially
ind academically the University
of Groningen is one of the best
places for a student to be



8?%



Click on the ad to read more



Excel 2007 Advanced: Part I



Advanced worksheet Functions



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 "lst_Qtr";



m


A


B


C


D


E


3




Australian Division






4












5




Name


1st Qtr


2nd Qtr


3rd Qtr


6




Long


110


175


140


7




Olson


200


210


240


8




Stark


300.9


180


395


9




Todd


150


200.4


125


10




Unger


220


195


335



Selection of
cells for
naming



DEFINED NAMES

group on the
FORMULAS ribbon




Name
Manager



Define Name "
ftp Use in Formula T
Create from Selection



Defined Names



> To name cells:

Mouse

a) Select the cells you wish to name.

b) Click the DEFINE NAME button on the in the DEFINED NAMES GROUP on the FORMULAS Ribbon

c) The NEW NAME dialog box appears

d) 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 worksheet Functions



New Name [? |[X



Name:


HOBS


Scope:


Workbook


Comment:












Refers to:


=Sheetl!$C46:4C$10






OK Cancel





OR

Keyboard

a) Select the cells you wish to name.

b) Type directly in the NAME BOX to the left of the FORMULA BAR.

c) Press RETURN





1st Qtr




110




A


B


c


3




Australian Division


4








5




Name


1st Qtr


6




Long


110


7









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 "lst_Qtr". From now on, any reference to the range C6:C10 can be made with
the name "lst_Qtr"; Notice that the name box, on the left-hand side of the formula bar now displays the name "lst_Qtr";
It will do so whenever cells C6:C10 are selected;



Download free eBooks at bookboon.com



10



Excel 2007 Advanced: Part I



Advanced worksheet Functions



Creating Names Automatically

Alternatively, cells can be named using text already on the spreadsheet. For instance, in a spreadsheet, column or row
headings may have already been entered in the cells. B6 to B 10 for example shows the salesmen's names and their respective
sales quarterly this text can be used to name the cell ranges for their sales





A


B


C


D


E


F


3




Australian Division








4














5




Name


1st Qtr


2nd Qtr


3rd Qtr


4th Qtr "


6




Long


110


175


140


750


7




Olson


200


210


240


575


8




Stark


300.9


180


395


1100


9




Todd


150


200.4


125


185


10




Unger


220


195


335


1025



Agilent offers a wide variety of
affordable, industry-leading
electronic test equipment as well
as knowledge-rich, on-line resources
— for professors and students.




We have 1 00's of comprehensive
web-based teaching tools,
lab experiments, application
notes, brochures, DVDs/
CDs, posters, and more.



© Agilent Technologies, Inc. 201 2



Anticipate Accelerate Achieve



Download free eBooks at bookboon.com



11



Click on the ad to read more



Excel 2007 Advanced: Part I



Advanced worksheet Functions



> To create names automatically:



Create names from values in the:
I I Top row
I I Left column
I I Bottom row
Z\ Right column



IBIBI



OK



Cancel



Mouse



a) Select the cells you wish to define names for, include the data and the data labels in either the first column
or top row

b) Click the CREATE FROM SELECTION button on the in the DEFINED NAMES GROUP on the
FORMULAS Ribbon

c) Select where your labels are. They must be part of the selection can be in the top row or left column.

d) Choose OK and, all the salesmen's names will appear in the name box to the left of the formula bar and
selecting their name will select their individual sales figures



This procedure works equally well with text entered to the right of a row of data. Or labels in the bottom of a column
but THEY MUST BE PART OF THE SELECTION.



1




_l5l_Q1r
_2nd_Qtr




Long
Olson


A






Todd


N


Unger



Managing Names

The NAME MANAGER option in the group is a useful tool that allows you to create, modify or delete names within
your workbook even if the name refers to cells or ranges outside the present workbook.



Download free eBooks at bookboon.com



12



Excel 2007 Advanced: Part I



Advanced worksheet Functions



Name Manager




Name

Olson
Stark
Todd
Unger



Value



{"110';"200 r ;"3u0...



{"110' / "175","l'Ki",
{ r 2D0"/210","2'W r
{"30Q,9yi8uy39.
{"150y2uu,4yi2.
{'2Z0','195",'335",



Refers To



=Sheetl!5Cs6:SCS10



=Sheetl!5C 56:3=56
=Sheetl!5C57:5F57
=Sheetl!5C58:5F5S
=Sheetl!5C59:5F59
=Sheetl!sC5lO:5F...



Scope



Comment



Workbook
Workbook
Workbook
Workbook
Workbook



Refers to:



=Sheetl!5Cs6:5C5lO



Close



>- To use name manager

Mouse

a) Click the NAME MANAGER BUTTON on the in the DEFINED NAMES GROUP on the FORMULAS
Ribbon.

b) The NAME MANAGER Dialog box appears.

c) To create new name use the NEW button.

d) The NEW NAME dialog appears. The name manager temporarily dissappears until you click OK or
CANCEL

e) When the NEW NAME dialog box is there you must give a name, select a scope and click in the refers to
box.

f) You may then select any cells in this workbook or ANY open workbook.

g) You may then click on OK the dialog box will close, the NAME MANAGER will reappear and the named
cells will appear wilthin the large white area.

h) Selecting any named range will allow you to edit or delete it by clicking on the enabled buttons at the top of
the dialog.



Download free eBooks at bookboon.com



13



Excel 2007 Advanced: Part I



Advanced worksheet Functions







Filter -




. . i«a i m



£ Clear Filter



Names Scoped to Worksheet
Names Scoped to Workbook
Names with Errors
Names without Errors
Defined Names
lable Names



> Filtering out needed named ranges

Using the filter button allows some basic filtering of the names within your workbook.

Don't forget to clear the filter after you have what you want. Scoping is a function where the names may be used on a
specific sheet or throughout the whole workbook. When filtering the names you have it may be useful to set a scope if
you have many names on many sheets.



Need help with your
dissertation?



Get in-depth feedback & advice from experts in your
topic area. Find out what you can do to improve
the quality of your dissertation!



Get Help Now





Go to www.helpmyassignment.co.uk for more info



Helpmyassignment



Download free eBooks at bookboon.com



14



Click on the ad to read more



Excel 2007 Advanced: Part I



Advanced worksheet Functions



1.2 Using Names
Go To

The GOTO feature can be used to go to a specific cell address on the spreadsheet. It can also be used in conjunction
with names.



&






Sort Si


Find St




Filter "


Select T





al<



Find...
Replace.,.
Go To,..

Go To Special..



> To GO TO a name:

Mouse



a) Click the FIND & SELECT BUTTON on the in the EDITING GROUP on the HOME Ribbon.

b) Select GO TO

c) The following dialog appears

d) Click on the name required, then choose OK.



Go To HE



Go to:








1st Qtr
_2nd_Qtr
Long
Olson
Stark
Todd
Unger




A




Reference:








1














Special,., J


\ OK |


Cancel











Keyboard

a) Press [F5]. The following dialog box appears;

b) Click on the name required, then choose OK.



Download free eBooks at bookboon.com



15



Excel 2007 Advanced: Part I



Advanced worksheet Functions



Not only does the cell pointer move to the correct range, but it also selects it. This can be very useful for checking that
ranges have been defined correctly, and also for listing all the names on the spreadsheet.

You can also go to a specific cell that has been used in two range names. The previous example mentioned cell C6, the
intersection of the FEB and Britain ranges.



> To move to a cell that belongs to two ranges:



Keyboard



a) Press [F5] and type the first range name in the Reference box, then type a space and the second range name.
>>>