[PDF]0016 Microsoft Excel 2007 Advanced

PREMIER ain
A


Mic
Exce
Advar


Pre mie r Training Lim ite
4 Ravey Street
Iondon
EC 2A 4QP
Telephone +44 (0)20 7729 1811


www.premcs.com


Advanced Excel 2007


[TABLE OF CONTENTS


INTRODUCTION vasccsiesceecteiestniectecveceatistievenibiecedl aewe did Geardeeeee icant 1
MODULE 1 REVIEW OF INTERMEDIATE COURSE .........:::csscsseeeeseees 2
MODULE 2 NAMING RANGEG......0....ccccsseessseeeeeeeeeeseeeeeneeenseeeneneeenseenenseees 2
Naming a Cell, range Or fOrmula............ccccceeeeeeeeeteeeeeeeeeeeeeseeeeeseeeeseeeees 2
Moving to a NAMED Lane ........eeeeeeeeceeeeeeeeeeeeeeeceaeeeteaeeseeeeseaeeseneeeseaeeees 4
Pasting names in fOrMUIAS...........:cceeeeeeeeeeceeeeeeeeeeseaeeeeeeeeseaeeesnaeerseeeeaes 4
Deleting a NAME FANnQe.........eccccesceeeceeeeeeeeeeeeeeeeaeeseeeeseaeeseeeeteaeeseneeeeaas 5
Paste a list Of MNAMEM FANGES...........ceeeeeeteeeeeeeeeeeeteeeeeteaeeeeaeeesseeseneeeeaes 5
MODULE 3 FUNCTIONG. ........c:ccccseeeseseeesseeenseeeesseeeeseeeneeeeasneesaseeeseeneneas 11
IP STATEMENTS #. 2ec startin hid ila AN Anes aie eee 11
TeOXt TUNG ONS iss. sceeventaen cnt tadvs Ai Avitenten lees Maeve aii a 15
Date -ANnGitiMe vex sgtivicnstict cation Reade eatecta Mak Bhes 17
Look Up TUNCIONS ». :ccc2 ccc nike ra einen tee 18
Financial fUNCtIONS ..........:cccccsseceesesceeeeeeeneeeeeseeeeeecsneeeeeeseaeeeeseeeeeesseaees 20
Mathematical fUnCtiONS ............:ceeeeeeeeeeeeeeeeeeeeeeeeaeeeeeaeeseaeeseeeeseaeeeeeaees 24
SubtotalS icsenta aiden ain an hind Raa 28
Exercise - MOdUIC 3 .........cccececeeceeeeeeeeeeteneeeeeneeseaeeeeeaeeseaeeeteaeeseeeeteaeeee 33
MODULE.4 TEMPLATES. 0. cic-s.cccsccesscceccesccycseesetececersccveustenecendeeesieecerenuce 39
GE ATIING fi s-Sis Sed aces eects tense eae ae tage a eed ecco Sa tease eet 40
Using a template .........ccceccccceceeeseeeeeeneeeeeaeeseaeeseaeeceaeeeseaeeseaeeeeeaeeeeaeeens 40
Editing a ternplate ............ccccecceeecceeseneeeeeeeeeeaeeceneeeeeeeeeeeeeeeaeeeeeeeseaeeees 40
Notes — module 4 41
MODULE 5 - AUDITING A WORKBOOK .........seccssseeeseeeeeeseeeneeeeeneeeeneee 43
Auditing and watch WiINdOW,........:.::cccsscccessssseeesseseeeeesseeesseesaeeesssnaeees 43
Checking ata fOr Crrors.......c.ccccceceseeeeeneeseneeeeeeeeseeeeeeeeeseaeeeseaeeseneeeneas 44
Finding data preCedent.............ccccececcceeeeeeceeeeeeeeeeceneeeeeneeseeeetsaeeeeeeee 45
Finding formula dependant. ...........cccccccececeeeeeeeneeseeeeeeeeeeseneeeteaeeseneeee 45
Watch: WINKOW saxzcccerzacecveussidecousercecoutabacensvdeiiacayeatievsthantevneviellecmssaries’ 46
Formula Auditing MOC ...........:ccecceceseeeeeneeeeeeeceeeeeeeaeeeeeneeseaeeseeaeeeeaeeens 47
Notes — module 5 50
MODULE 6 DATA VALIDATION ....0....:cccsseeceeeeeeeseeeeeneeeneeeenseeenseeensneeeees 52
Setting data validation .............cccccesceeeseeeeeneeeeneeeeeaeeeeeeeeseaeeseeeeessneeeeans 52
Checking for invalid data..........ecccceecceceseeeeeeeeeeeeeeeeaeeseneeeseaeesesaeeeeeeseaas 55
MODULE: 7: MAGROS 605.222. sccsdicececccdsccciecctececeeotbachececcece set avdieescttaesntiesde 60
Overview Of MACrOS/VDA........:ccccceceeeeeeeeeeseneeeeeaeeteaeeeseeeeseaeeeseaeeseneeeneas 60
RECOrdING A MACIO.........ceeececeeceeeeeeeteneeeeeaeeeeaeeceaeeeseaeeeeeneeteaeeseneeeseeeees 60
RUNNING MACIOS ...0 02... cece ee eeeeceee cece ee eeeeecaaeeeeeeeeeseecaaaeeeeeeeeeeeeennaeeeeeeess 62
Add macros to quick access toolbad ............:cccccscceeeeseeeessssteeeesssteeeees 64
Simple editing Of MACIOS...........::ccecceeeeceeeeeeeeseeeeeeeaeeeeeeeeseaeeeeeeeeseneeetens 64
Exercise — module 7 66
Notes — module 7 67
MODULE 8 EXCEL’S ANALYTICAL TOOLG...........ceccsseeeeseeneeeeeeeeeeneeee 71
Goal Seek iisatuucnctinndvdieni a an Reh tina aia 71
SCONANOS ie. cciasen eestor ecards eatin ceeiareeaie Baan cnvatetadeatioadiv mecca wads 73
SOW iccsretes Sorter a eater tie ne celbates caitale etat tal artenens ns cetet aces 79
2 & 1 Way INPUT table... ee cece cece cence eeeeeeeeaeeeeeaeeeeeaeeteaeeseeeeeseeeeeas 81


Notes — module 8 85


Advanced Excel 2007


Advanced Excel 2007


INTRODUCTION


: This manual is designed to provide information required when
: using Excel 2007. This documentation acts as a reference

: guide to the course and does not replace the documentation

: provided with the software.


: +The documentation is split up into modules. Within each

: module is an exercise and pages for notes. There is a
reference index at the back to help you to refer to subjects as
required.


: These notes are to be used during the training course and in

: conjunction with the Excel 2007 reference manual. Premier

: Computer Solutions holds the copyright to this

: documentation. Under the copyright laws, the documentation

: may not be copied, photocopied, reproduced or translated, or
reduced to any electronic medium or machine readable form,
in whole or in part, unless the prior consent of Premier
Computer Solutions is obtained.


: © Premier Training Limited 2002 — 2007 Page 1


Advanced Excel 2007


(MODULE 1
/ REVIEW OF INTERMEDIATE


| COURSE


Revision Exercise


1. Create the exercise on the following page, using the
: calculations given.


2. The factors that might change are located in separate cell
: for easy “what-if analysis. The formulae are given below:


SALES start at 3,500 and increase by the Assumption
: Growth in Sales “10%”.


PRICE starts at 15.50 and increases by the Assumption
: Growth in Price “O”


: REVENUE is Sales * Price

: RAW MATERIALS are Sales * Assumption Unit Raw Material
2 LABOUR is constant at 10,000

: ENERGY is Sales * Assumption Unit Energy

: DEPRECIATION is constant at 750

: TOTAL COSTS is the sum of the above four costs

: GROSS PROFIT is Revenue — Total Costs

2 OVERHEADS are constant at 12,500

: NET PROFIT is Gross Profit - Overheads


The Net Profit for December should be £60,631.63
3. Rename the worksheet as PROFIT PROJECTION.


: © Premier Training Limited 2002 — 2007 Page 2


GO0Z ZOO? PEHwWIT Buules| JAIWe14 ©


¢ ebeg


[al A a a SN a ES a PR PT VN” a PY FY PP
1 Profit Protection - Widget Division 2008

S|

3 | Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09
4

5 Sales 3500

_6 |Price 15.5


_7 |Revenue


9 Raw Materials
10 |Labour 10000
11 Energy
12 Depreciation 750
13 Total Costs
14)
15 |Gross Profit
16 Overheads 12500
17 |
_18 Net Profit
19,
20 Assumptions
21 Growth in Sales as %
_22 |Growth in Price as %


Note: Although Excel
2007's new Table
functionality allows you to
create formulas using
column names, these are
not considered named
ranges.


Advanced Excel 2007


MODULE 2
NAMING RANGES


: There are a variety of uses for names in a workbook. A name
: Can be applied to any cell or range. Names are also useful
: for the following:


Making formulas easier to understand


Quick Navigation
Improving Solver’s report results


O oO O O


Storing a value that will be used over and over
but that might occasionally need to change, such
as a Sales tax rate.


a Storing formulas
a Defining a dynamic range


: NAMING A CELL, RANGE OR FORMULA


The following must rules must be followed when naming
: ranges:


Q


The first character if a range name must be a letter or
underline.


The remaining characters must be letters, numbers,
underlines or periods.


No spaces.
Do not use cell references as names.


. Select the cell(s).


2. Click in the Name Box in the formula bar.
test ng |
Name Box


3.


Type a name (named ranges cannot contain any spaces)
and then press ENTER.


: © Premier Training Limited 2002 — 2007 Page 2


Advanced Excel 2007


Alternatively, on the Formulas tab, in the Named Cells group,
: click Name a Range.


NewName 2x)
Name: Japs t=<“C=CsSSC*tststst‘“‘C*‘CS
fe Use in Formula * oe: a ~ |


Name ; Comment:
Manager f= Create from Selection


<>) A=) Name a Range ~


Named Celfs


Refersto: |—sheet1!$B$3:$€$3 Esl
cot _|


1. In the New Name dialog box, in the Name box, type the
name that you want to use for your reference. Names can
be up to 255 characters in length.


2. To specify the scope of the name, in the Scope drop-down
: list box, select Workbook, or the name of a worksheet in
the workbook.


' 3. Click on OK.
Create Names Based On Row/Column Titles


1. Select the range you want to name, including the row or
column titles you want to use for the names.


2. Click on the Formulas tab, in the Named Cells group,
: click Create from Selection.


Create Names from Selects 2| x}


Create names from values in the ; ———


IV Left column


T Bottom row
T Right column


cous_|


3. Select the appropriate check box or boxes to name the
rows or columns using the text in the top row, bottom row,
left column, or right column of the range.


: 4. Click on OK.


Using Named Ranges


Named Ranges can be used to move to various locations in a


: © Premier Training Limited 2002 — 2007 Page 3


Note: Named Ranges
can be unique to the
workbook or the
worksheet.


Advanced Excel 2007


workbook and pasted into formulas.


: MOVING TO A NAMED RANGE


1. Click on the downward arrow to the right of the name box
in the formula bar.


2. Select the named range.


test nf | |


Name Box List


: PASTING NAMES IN FORMULAS


1. Start formulas by typing = and then the formula name.


2. Click on the Formulas tab, in the Named Cells group,


click Use in Formula.


45) Name a Range ~


mS
Apples Hection


! Pears


jab Paste...


3. Select the name to use.
4. Finish the formula and then press ENTER.


5. Alternatively press F3 to display the Paste Name dialog


box.


Paste name


: © Premier Training Limited 2002 — 2007 Page 4


Advanced Excel 2007


: DELETING A NAMED RANGE


1. Click on the Formulas tab, in the Named Cells group,
click Name Manager.


=Sheet1ISB$3:4E$3


2. Select the name to delete and click on the Delete button.
: 3. Click on OK.


4. Alternatively to display the Name Manager dialog box
: press CTRL + F3.


: PASTE A LIST OF NAMED RANGES


1. Select an empty cell.


2. Click on the Formulas tab, in the Named Cells group,
: click Use in Formula.


3. Choose Paste.


4. Click on the Paste List button.


Paste name


Paste List | Ok | Cancel |


5. Alternatively press F3 or


: © Premier Training Limited 2002 — 2007 Page 5


Advanced Excel 2007


: © Premier Training Limited 2002 — 2007 Page 6


Advanced Excel 2007


Notes — Module 2


: © Premier Training Limited 2002 — 2007 Page 7


Advanced Excel 2007


Notes — Module 2


: © Premier Training Limited 2002 — 2007 Page 8


Advanced Excel 2007


Notes — Module 2


: © Premier Training Limited 2002 — 2007 Page 9


Advanced Excel 2007


Notes — Module 2


: © Premier Training Limited 2002 — 2007 Page 10


Advanced Excel 2007


[MODULE 3
|FUNCTIONS


: Functions are built-in formulas that perform complex

: mathematical, financial, statistical or analytical calculations.

: Excel provides more than 200 built-in functions, or predefined
: formulas.


: Each function consists of an equal sign (=), the function name
: and the argument(s). Arguments are cells used for carrying

: out the calculation. The SUM function adds the number of

: cells in specified cells. The active cell shows the result of the
: function.


-SUM(B1:B9)


Equal Function Arguments
Name


_ IF STATEMENTS


A logical function enables you to make a decision depending
if the conditions set are true or false.


| IF FUNCTION
2 =IF(logical_test,value_if_true,value_if_false)


An example is in cell A3 if the value is equal to 10 insert 1, if
: not insert 0.


A|B;C
1
2
3 | 10); 1


In cell B3 the calculation would be


: © Premier Training Limited 2002 — 2007 Page 11


Note: Remember to close the
same number or brackets at
the end of the function as
functions, there are four =IF()
to four brackets at the end.
There can only be a maximum
of 7 nested =IF()’s in a single
function.


Advanced Excel 2007


=IF(A3=


10,1,0)


If the true or false condition is to be text and not a value, the


text has to be enclosed in double quotes.


=IF(A3=10,“Yes”, “No”)


= Equal To

< Less Than

> Greater Than

<= Less than or equal to
>= Greater than or equal to
<> Not Equal to


NESTING =IF()’s


You may want to use an =IF() function again as part of the


Listed below are the comparative operators that can be used
: in


logical
functions.


TRUE or
FALSE

If Average Score is Then return part of

the
Greater than 89 A formula:
From 80 to 89 B

You can
From 70 to 79 C a vi

following
From 60 to 69 D eee
Less than 60 F =IF()

function:


=IF(AverageScore>89,"A" ,IF(AverageScore>7
9,"B" IF(AverageScore>69,"C" ,IF(AverageSco


re>59,"D","F"))))


: © Premier Training Limited 2002 — 2007


Page 12


Advanced Excel 2007


The function on the previous page reads:


: If the average score is greater than 89 then insert an A, if not
: is it greater than 79, if it is insert a B, if it is not, then is it

: greater than 69, if it is insert a C if not is it greater than 59, if it
: is insert an D if not then insert F.


: Using Other Functions with IFs


: For example if the =SUM() function is nested within an =IF()

: function, the condition of the =IF() function can be based on

: whether the total value in a range of cells is above or below a
: certain value.


|Sales figures for Qrt1 1997


‘Date Total Sales
| O1-Jdan 30000
01-Feb 25689
01-Mar 45689

Good Sales


eanaas wn


The following function would be used in cell B7.


=IF(SUM(B4:B6)>9000, “Good Sales”, “Bad Sales”)


| AND(logicalt, logical2)


This logical function compares whether two cell match the
: condition required, if the cells meet the condition TRUE is
: inserted into the cell if not then FALSE is used.


The function in cell B5 is:


=AND(A3=20,A4=30)


: © Premier Training Limited 2002 — 2007 Page 13


Advanced Excel 2007


: =OR(logical1, logical2)


This logical function compares whether one of two cells
: meets the condition required. If the cells meet the condition
: TRUE is inserted into the cell if not then FALSE is used.


The function in cell B5 is:


-OR(A3=20,A4=30)


2 Using The =AND() and =OR() Function With


__A B
al
Peas 10
Fag) 30
eS 40
=e This is Wrong


The function in cell B5 is:


=IF(OR(A3=20,A4=40), “This is Correct’, “This
is Wrong”)


or using the =AND() function:


=IF(AND(A3=20,A4=40), “This is Correct”,
“This is Wrong”)


: © Premier Training Limited 2002 — 2007 Page 14


Advanced Excel 2007


TEXT FUNCTIONS


=TEXT(Value,Format_Text)


This function converts a number to a formatted text entry.


Value is the number or cell reference to a
cell containing a value.


Format_Text is the format from the category box
in the Format Cells/Number dialog
box. This cannot be the General
format.


=TEXT(1.5, £0.00) the result would be £1.50


=VALUE(text)


Note: You do not generally
need to use the VALUE : This function converts a text string that represents a number
function in a formula because : to qa number.
Excel 2003 automatically
converts text to numbers as:
necessary. This function is :


provided for compatibility with : Text can be text enclosed in quotation marks or a cell
other spreadsheet programs. : reference.


=VALUE("£1,000") equals 1,000


=LEFT(text,num_char)


This function returns the first leftmost characters in a string.


=RIGHT(text,num_char)


: This function returns the first characters in a string from the
: right.


Text can be text enclosed in quotation marks or
a cell reference.


: © Premier Training Limited 2002 — 2007 Page 15


Advanced Excel 2007


Num_char represents the number of characters from
the left/right that you want the function to
return.


| A B fears
tal
2 \West Region West Region
3 |North Region Nort Region
| 4 (South Region Sout Region
5 |East Region East Region


aa
Note: These functions count :


the spaces and punctuationas : |n cell B2 the following formula was inserted
characters and is only effective :


if all pieces of text are the :


same length. =LEFT(A2,4)


In cell C2 the following formula was inserted


=RIGHT(A2,6)


LEN(text)


This functions counts the number of characters in a string,
: this includes spaces and pronunciation.


: Text can be text enclosed in quotation marks or a cell
; reference.


2 |West Region 11


3 |


In cell B2 the following formula has been used:


=LEN(A2)


=CONCATENATE(text1,text2...)


This function allows you to join different text cells together to
: produce a single string of text.


: © Premier Training Limited 2002 — 2007 Page 16


Advanced Excel 2007


aS ee PS = SI |
1 (FirstName Surname

2 |John Black John Black
3 |Peter Kelly Peter Kelly
4 Paul Green Paul Green
6 {Simon Smith Simon Smith
16 |Andrew Guy Andrew Guy
7


: In column C the first and surnames have been joined
: together.


=CONCATENATE(A2, “ ”, B2)


To insert a space between each entry place a space between
: two quotes.


Alternatively you can use the ampersand (&) to join text cells
: together.


=A2&“ ’&B2


| DATE AND TIME


=TODAY()


This function will return the current date, it does not require
: any arguments and will update automatically.


=NOW()


This function will return the current date and time, it does not
: require any arguments and will update automatically.


=MONTH(serial_number)


This function returns the number of the month (from 1 — 12)
: corresponding to a serial number.


ile
: 3
:


In cell B3 the following formula was entered:


=MONTH(A3)


: © Premier Training Limited 2002 — 2007 Page 17


Note: The table must be :
sorted in ascending order. The ;
VLOOKUP function only
returns one answer. If there :
are several occurrences ofa
target value VLOOKUP stops
at the first one. It is not used :
for entries that appear more


than one in a list.


Advanced Excel 2007


LOOK UP FUNCTIONS


The LOOKUP functions are a good method of looking for
information in a list. It searches the left most column of a list
for a particular value and returns an entry in a column to the
right. There are two LOOKUP functions one that lookups
vertically, VLOOKUP or horizontally, HLOOKUP.


=VLOOKUP(lookup_value,table_array,col_indx,ra


nge_lookup)


Lookup_value


Table_array


Col_indx


Range_lookup


is the value that you require the function to
lookup.


is the range of cells that you want to look
in. You can name cell rangers as opposed
to using cell references


this is the column in the table_array from
which the matching value should be
returned. If you enter 2 it will return the
>>>