Advanced Excel Formulas & Functions

Short Description
ModulesAdvanced ExcelAdvanced Excel formulas and functions.doc f. TABLE OF CONTENTS. INTRODUCTION . …

Website: www.csu.edu.au | Filesize: 719kb

Content
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:AdministrativeInformation TechnologyCustomer Services ManagementEducation and Training TeamCurrent Training
ModulesAdvanced ExcelAdvanced Excel formulas and functions.doc f
TABLE OF CONTENTS
INTRODUCTION ……………………………………………………………………………………………………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 & ABSOLUTE ADDRESSING…………………………………………………………………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 CONSTANTS OR FORMULAS……………………………………………….10
Naming a constant……………………………………………………………………………………………………….10
Naming a Formula ……………………………………………………………………………………………………….11
REFERENCING OTHER WORKSHEETS AND WORKBOOKS …………………………………12
Referencing Other Worksheets………………………………………………………………………………………12
Referencing Other Workbooks……………………………………………………………………………………….13
FILL HANDLE AND FILL SERIES COMMAND………………………………………………………..14
Using the Fill Handle…………………………………………………………………………………………………….15
Using the Fill Series Command ……………………………………………………………………………………..15
Customising a Fill Series ………………………………………………………………………………………………17
Deleting a Custom List………………………………………………………………………………………………….17
CONDITIONAL FORMATTING………………………………………………………………………………18
Creating a Conditional Format……………………………………………………………………………………….18
Find Cells That Have Conditional Formats ………………………………………………………………………20
SORTING AND FILTERING…………………………………………………………………………………..21
Simple Sorts ……………………………………………………………………………………………………………….21
Sorting on more than one criteria (DATA, SORT)……………………………………………………………..21
Filtering Data ………………………………………………………………………………………………………………22
Turning AUTOFILTER off ………………………………………………………………………………………………..