Advanced Excel Formulas and Functions (pdf document) free file download at fliiby.com

"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. 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 ........................................................................................................... 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 TABLES .............................................................................................................30 Creating the VLOOKUP Function ............................................................................................... 33 S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc f IF FUNCTION .....................................................................................................................35 Creating the IF Function – Example 1 ........................................................................................ 36 Creating the IF Function – Example 2 ........................................................................................ 37 Manually Creating an IF Function ...............................................................................................38 NESTED IF FUNCTION......................................................................................................39 AND, OR AND NOT FUNCTIONS .....................................................................................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 FUNCTIONS ...................................................................................48 WORKING WITH TEXT......................................................................................................50 Nested Text Functions ................................................................................................................ 50 CONCATENATION ............................................................................................................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 FUNCTIONS ..................................................................................................60 S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc f INTRODUCTION Pages 2 to 29 of these notes consist of Excel features that can be used as a refresher and/or a source of handy hints and ways of using different functions. Some of these features include: • • • • • • Using the function wizard Creating and using range names Referencing other worksheets 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 printed copy of these notes, the exercise files are located at S:\Common\Special Projects\Training\Client Services\Advanced 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 which have been chosen for their functionality and popularity. If you would like to see a function included, 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 Bathurst Wagga Wagga Mary Williams Sue Dixon Pamela Laverty 19789 84008 34050 S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 1 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 FUNCTION 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 accessing the INSERT FUNCTION dialog box: • • • Use the INSERT menu, select the FUNCTION option; Use the shortcut – SHIFT + F3; or Click on the INSERT FUNCTION icon next to the formula bar. Using the Function Wizard 1. 2. Make sure you are in the cell where you want to place a function, then open the INSERT FUNCTION dialog box by one of the methods listed above. The INSERT FUNCTION dialog box will appear. The different areas are explained on the next page. a b c d S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 2 a) b) Type a brief description of what you want to do – then click on GO. Excel will search for functions that might perform the job; or If you prefer, and if you know the name of the function, you can drop down a list and select a category. If you aren’t sure which category your function is in, select ALL. To make scrolling to your function quicker when you are in the ALL category: • • Click somewhere in the “SELECT A FUNCTION” area of the dialog box; Type in the first two or three letters of the name very quickly. For example if you type VLO quickly, it will return VLOOKUP. If you type it slowly, you will get the functions starting with the letter “V”, then when you type “L” the list will change to the functions beginning with the letter “L”. If you used the function recently, select the MOST RECENTLY USED category – this is a convenience list of your recently used functions. c) d) 3. 4. A brief description of the selected function. A link to take you to Excel’s comprehensive help menu for further details on the selected function, this includes examples of use. Once you have found the function you require, select it then click on OK. The FUNCTION ARGUMENTS dialog box appears. Most of the time you will be required to enter the arguments yourself, some however, as in the following screenshot, will look at your data and try and make an educated guess as to what range / data etc you would enter. The data in the text box can be changed by either: Clicking in the formula bar in the main excel window in and changing the data; S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 3 Dragging over cells behind the FUNCTION ARGUMENTS dialog box (the dialog box can be moved to make viewing easier – just click and drag on the title bar); or Temporarily collapsing the FUNCTION ARGUMENTS dialog box by clicking on the COLLAPSE DIALOG icon. This will then allow you to select larger ranges without hindrance. When you have selected the range/data click on the restore button (see screenshot below). 5. When you have finished filling in all the arguments required in your function, click on OK. Restoring the Function Arguments dialog box in order to edit a function 1. 2. Click in the cell where the function is. Click on the INSERT FUNCTION icon (the fx button) to restore the dialog box. Shortcut for entering a function You can access your most recently used functions without having to go through the INSERT FUNCTION dialog box. 1. Make sure you are in the cell where you want the function to be. Instead of clicking on the FX icon to start your function, type an equal sign (=). You will see the function that was last used in the space where the cell address normally shows. Either click on the function name (if it is the one you want to use), or click on the drop down arrow next to the function name to see the list of recently used functions. 2. S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 4 Click here if you want to use the function that is shown. Click on the drop down arrow to access the Most Recently Used list. The Most Recently Used List. 3. As soon as you click on the required function, you will go straight to the FUNCTION ARGUMENTS dialog box, bypassing the INSERT FUNCTION option. S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 5 RELATIVE & ABSOLUTE ADDRESSING There are different sorts of cell references. These are: • • • Relative Absolute Mixed A relative reference will change column and row numbers as it is copied to other cells. Think of it as an original instruction being to go in a certain direction e.g. 2 rows up and 2 columns across from your current position. When this instruction is copied elsewhere, it will still refer to 2 rows up and 2 columns across from your new current position. An absolute reference is one that does not change. If you refer to a cell in a certain row and column and then copy that reference elsewhere, it will still refer to exactly the same cell or range. A mixed reference is one that is half relative and half absolute. An absolute address is defined with the use of the “$” symbol. This can be typed in at the time of creating the formula, or by editing the cell afterwards. Examples of relative and absolute addressing: Relative Absolute Mixed A4 $A$4 $A4 or A$4 A4:B5 $A$4:$B$5 $A4:B$5 The “$” sign can be hand typed or inserted by pressing the keyboard shortcut F4. As you press F4 the cell address will cycle between the four variations of an address – for example A4 would cycle between: A4 $A4 A$4 $A$4 S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 6 NAMING CELLS AND RANGES Cells and ranges can be given a name so that when you need to refer to that cell or range of cells, you can refer to its name rather than an address. Example In the screenshot below, traditional cell addresses have been used for working out January’s profit or loss. In the screenshot below, row 6 has been given the name Total_Income and Row 15 the name Total_Expenditure. When the formula for February was done, the syntax was Total_Income – Total_Expenditure. Using names may make it easier to understand what the formula is doing – it might make more sense to some, than saying B6 – B13. S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 7 There are a couple of ways to create a range name. They both require that you select the cell or range or before creating the name. Method 1: INSERT, NAME, DEFINE option (or CTRL + F3) 1. 2. 3. Go to the INSERT, NAME, DEFINE dialog box (or press the keyboard shortcut CTRL + F3). The DEFINE NAME dialog box will appear; Give the cell or range you selected a name (quite often, as in the example screenshot below, the name suggested by Excel is more than adequate); Click on ADD, then click on CLOSE. Range names must be one word – if you require 2 words, join them with an underscore as in the example below. The names can be a combination of numbers and letters, but must not start with a number. The default is to show the address as being ABSOLUTE. By clicking in the REFERS TO text box, this can be edited to what is required. NOTES: • • • • • As a default, names are workbook level i.e. the name you create is available throughout the whole workbook. If the worksheet has been named with a name that includes spaces, the worksheet name will have to be enclosed in single quotation marks. Method 2: Using the NAME box 1. 2. 3. Highlight the cell or range(s) that is going to be named; Click in the NAME box (see the following screenshot). The contents of the name box will be highlighted; Type the RANGE NAME you want and ENTER to accept the name. S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 8 Applying a Range Name in a Formula To use the RANGE NAME in a formula either: 1. 2. Type the name – make sure you use the correct spelling or else the function will not recognise the name; or When you are in the part of the formula that requires the RANGE NAME, press the keyboard shortcut F3. This brings up the PASTE NAME dialog box. Select the name and click on OK. When working with many names, you can have a list of these names and the cells or ranges they refer to placed in your workbook by using the PASTE LIST option (located in the PASTE NAME dialog box - see screenshot above). Make sure you are in an empty cell and have 2 columns free and as many rows as there are names before using this option. The first column will list the RANGE NAMES, the second column shows the range address that the range name refers to. S:\Administrative\Information Technology\Customer Services Management\Education and Training Team\Current Training Modules\Advanced Excel\Advanced Excel formulas and functions.doc Page 9 USING NAMES FOR CONSTANTS OR FORMULAS In addition to using Names to name cells or ranges, you can also name constants or formulas, these constants or formulas don’t have to appear in a cell. This is a good way of storing a constant or a formula that may have to be used several times. An example worksheet called NAMI..."

You need to upgrade your Flash Player , or try to enable javascript in order see this document properly.