Advanced Excel
Formulas & Functions
Written by:
Education and Training Team
Client Services
Division of Information Technology
Date: October 2005
Copyright © 2005 – Charles Sturt University
No Part of this document may be reproduced, altered or sold
without prior written approval of
the Executive Director, Division
of
Information Technology, Charles Sturt University.
S:\Administrative\Information Technology\Customer Serv
ices Management\Education and Trai
ning Team\Current Training
Modules\Advanced Excel\Advanced Exce
l formulas and functions.doc f
TABLE OF CONTENTS
INTRODUC
TION ..................................................................................................................1
THE FUNCTION
WIZARD....................................................................................................2
Using the Function Wizard......................................................................................................
...... 2
Restoring the Function Arguments dialog box in order to edit a function ..................................... 4
Shortcut for entering a function...............................................................................................
...... 4
RELATIVE & ABSOLUT
E ADDRES
SING ...........................................................................6
NAMING CELLS
AND RANGES .........................................................................................7
Method 1: INSERT, NAME, DEFINE option (or CTRL + F3) ....................................................... 8
Method 2: Using the NAME box..................................................................................................
.8
Applying a Range Name in a Formula .......................................................................................... 9
USING NAMES FOR CONST
ANTS OR FO
RMULAS
.......................................................10
Naming a constant ..............................................................................................................
........ 10
Naming a Formula ...............................................................................................................
....... 11
REFERENCING OTHER WORKSH
EETS AND WO
RKBOOKS .......................................12
Referencing Other Worksheets...................................................................................................
12
Referencing Other Workbooks....................................................................................................
13
FILL HANDLE AND FILL
SERIES CO
MMAND.................................................................14
Using the Fill Handle..........................................................................................................
......... 15
Using the Fill Series Command ..................................................................................................
15
Customising a Fill Series ......................................................................................................
...... 17
Deleting a Custom List.........................................................................................................
....... 17
CONDITIONAL FO
RMATTI
NG ..........................................................................................18
Creating a Conditional Format ..................................................................................................
.. 18
Find Cells That Have Conditional Formats ................................................................................. 20
SORTING AND FI
LTERING...............................................................................................21
Simple Sorts ...................................................................................................................
............ 21
Sorting on more than one criteria (DATA, SORT)....................................................................... 21
Filtering Data .................................................................................................................
............. 22
Turning AUTOFILTER off .........................................................................................................
.. 23
MACROS.........................................................................................................................
...24
Introduction ...................................................................................................................
.............. 24
Recording a macro ..............................................................................................................
....... 24
Running a Macro ................................................................................................................
........ 26
Absolute vs Relative ...........................................................................................................
........ 26
Assigning buttons to macros....................................................................................................
... 27
Assigning the macro to an AutoShape........................................................................................ 27
Assigning a macro to an icon on a Toolbar................................................................................. 28
Viewing The Macro ..............................................................................................................
....... 29
Deleting a Macro...............................................................................................................
.......... 29
LOOKUP T
ABLES
.............................................................................................................30
Creating the VLOOKUP Function ............................................................................................... 33
S:\Administrative\Information Technology\Customer Serv
ices Management\Education and Trai
ning Team\Current Training
Modules\Advanced Excel\Advanced Exce
l formulas and functions.doc f
IF F
UNCTION ....................................................................................................................
.35
Creating the IF Function – Example 1 ........................................................................................ 36
Creating the IF Function – Example 2 ........................................................................................ 37
Manually Creating an IF Function ...............................................................................................
38
NESTED IF
FUNCTIO
N......................................................................................................39
AND, OR AND NOT
FUNCTION
S .....................................................................................42
The AND and OR Functions .......................................................................................................
42
Creating the AND function within an IF statement ...................................................................... 42
Creating the OR function within an IF statement ........................................................................ 46
The NOT function ...............................................................................................................
........ 46
ISNA AND ISERROR
FUNCTION
S ...................................................................................48
WORKING WITH
TEXT
......................................................................................................50
Nested Text Functions ..........................................................................................................
...... 50
CONCATENAT
ION ............................................................................................................51
Example 1 - Combining two entries using the & operator........................................................... 51
Example 2 – By using an IF function, combine two entries, ending up with one entry ............... 53
Example 3 – Using the CONCATENATE Function..................................................................... 54
WORKING WITH
DATES
...................................................................................................56
Useful Date Functions ..........................................................................................................
...... 57
ROUNDING F
UNCTIONS
..................................................................................................60
S:\Administrative\Information Technology\Customer Serv
ices Management\Education and Trai
ning Team\Current Training
Modules\Advanced Excel\Advanced Ex
cel formulas and functions.doc
Page 1
INTRODUCTION
Pages 2 to 29 of these notes consist of Exce
l features that can be used as a refresher
and/or a source of handy hints and ways of us
ing different functions. Some of these
features include:
•
Using the function wizard
•
Creating and using range names
•
Referencing other wor
ksheets or work files
•
Recording macros
•
Using the Fill Handle
and FILL, SERIES command
•
Conditional Formatting
Wherever possible hyperlinks have been used to
aid in navigation. You can use these
navigation links on-line by clicking on them in
the document, or by clicking on the link in
the navigation panel at the left
of Acrobat Reader window.
If you are working from a printe
d copy of these not
es, the exercise f
iles are located at
S:\Common\Special Projects\Training\C
lient Services\Advanc
ed Excel Exercises
. These
are read only files, please do not move them.
If you wish, make a copy of them in a
location of your choice.
Pages 30 to 62 consist of Excel functions wh
ich have been chosen for their functionality
and popularity. If you
would like to see a function includ
ed, please contact the Education
and Training Team.
It is planned to have a tips and tricks section so if you have any of these please let us
know, all contributions gratefully received.
Albury/Thurgoona Mary Williams 19789
Bathurst Sue Dixon 84008
Wagga Wagga Pamela Laverty 34050
S:\Administrative\Information Technology\Customer Serv
ices Management\Education and Trai
ning Team\Current Training
Modules\Advanced Excel\Advanced Ex
cel formulas and functions.doc
Page 2
THE FUNCTION WIZARD
A function is inserted into a spreadsheet either by
typing it directly into the active cell; or in
the formula bar; or by using the INSERT FUNCT
ION option in Excel. The latter automates
the process, ensuring that you
get arguments in the right order
. It also provides links to
the Help page (which includes examples of how the function is used).
There are several ways of accessi
ng the INSERT FUNCTION dialog box:
•
Use the INSERT menu, select the FUNCTION option;
•
Use the shortcut – SHIFT + F3; or
•
Click on the INSERT FUNCTION ic
on next to the formula bar.
Using the Function Wizard
1. Make sure you are in the cell where y
ou want to place a function, then open the
INSERT FUNCTION dialog box by one of the methods listed above.
2. The INSERT FUNCTION dialog box will ap
pear. The different areas are explained
on the next page.
a
b
c
d
கருத்துகள் இல்லை:
கருத்துரையிடுக