All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.

Description

Advanced Microsoft Excel: Formulas and Functions Introduction
This guide was developed as an extension of the Microsoft Excel Fundamentals handout. To fully use this guide one must have an understanding of the components of Excel’s layout, how to create a spreadsheet, how to create basic formulas, how to adjust column and row width, and how to change basic cell formatting. You will go over the creation of named cells and constants and their use in formulas and maneuvering your workbook. This is

Tags

Transcript

Page 1 of 11 Last Modified 9/30/2005
Introduction
This guide was developed as an extension of the Microsoft Excel Fundamentals handout. To fully usethis guide one must have an understanding of the components of Excel’s layout, how to create aspreadsheet, how to create basic formulas, how to adjust column and row width, and how to change basic cell formatting.You will go over the creation of named cells and constants and their use in formulas and maneuveringyour workbook. This is followed by the introduction of tools that allow you manipulate functions andconstruct new ones for more specialized uses. Examples of conditional and Lookup functions will provide you with powerful tools for getting the most out of Excel.Other ITRC Advanced Microsoft Excel guides will help you utilize the advanced formatting features inExcel as well as prepare your workbook for printing and the creation of templates. Data management isintroduced using Microsoft Excel’s tools for Data Validation, lists, and filtering. To obtain these guides, please contact the ITRC.
Topics
I.
Naming Ranges, Cells, and Constants
II.
Manipulating Formulas
III.
Conditional Functions
IV.
Lookup Functions
Advanced Microsoft Excel: Formulas and Functions
Page 2 of 11 Last Modified 9/30/2005
I. Naming Cells and Ranges
Naming Cells and Ranges
Naming ranges in Excel will save you time when writing complex formulas and maneuvering your worksheets. For example, if you have a range of cells used in several formulas or charts, namingthose cells will allow you to select them whenever you need to. Naming a single cell will allow youto quickly jump to a specific location in your document.1.
First highlight the cell(s) you would like toname.2.
Click inside the “Name” box on your toolbar. Itwill be on the left. Once you have clicked insidethe box, the cell name will highlight (Figure 1).3.
Type the name you would like for your cell(s)and press the
Enter
key. If you receive anaming error, check the following:a.
Names can not have spaces in them b.
Names can not be the same as a cellreference (i.e. A1).c.
Names can not begin with a number.d.
Names can not use certain characters.
Example: Using named ranges in formulas
1.
In Cells A1 through E1, type randomnumbers (Figure 2).2.
Select cells A1 through E1 and name them.The cells in the example have been named“Numbers” (Figure 2). This is called anamed range.3.
Move to another area in your workbook and click in any cell.4.
Type the formula,
=SUM(named range)
,and you will receive of the sum of what’sin your named range of cells (Figure 3).5.
Another example, type the formula
=SUMIF(named range, <6 )
and youwill get the sum of all numbers in your range less than 6 (Figure 4).
(Figure 1)(Figure 2)(Figure 3)(Figure 4)
Page 3 of 11 Last Modified 9/30/2005
Example: Using Named Cells
1.
Go to Sheet 1 and click inside of cell A1. TypeIdaho State University and press
Enter
on thekeyboard (Figure 5).2.
Click back inside cell A1, and in the Name Box,and type in ISU (Figure 5). Press
Enter
on thekeyboard.3.
Go to cell A30 and type University of Idaho in that cell and press Enter. Now click back on cell A30. In the Name Box, type UI and press
Enter
on the keyboard.4.
Go and click cell A80 and type in Boise State University. In the name box, type BSUand press
Enter
.5.
For practice, repeat these steps in cell A100 and A120 using other schools.6.
Move to any other sheet (or anywhere within this sheet) and select one of the schoolsfrom the name box. Excel will take you to the named cell you select.
Naming a Constant or Formula
Another way to create a formula or constant is to do it outside of a cell.1.
Go to the
Insert
menu and then
Name
.
Click
Define
.2.
In the
Names in workbook
field, enter the name for the formula.3.
In the
Refers to
field, type
=
(equal sign), followed by the formula or the constant value.
Note: When using your named cells in creating new formulas, pressing the F3 key while writing your formula will bring up the Paste Name box. To use this box, double click the named cell you would liketo add to your formula and it will be inserted into your formula. Note: The Paste Name box is the only place you will find your formulas and constants that do not refer to specific cells. Note: To create a list of your named references, select the cell you would like the list to start in. Press F3. In the Paste Name box, click Paste List. This creates a list of named references that you can refer back to or print for future use.
(Figure 5)
Page 4 of 11 Last Modified 9/30/2005
II. Manipulating Formulas
Switch between relative, absolute, and mixed references
After you have written a formula you may want to paste or drag it to different parts of your workbook. To aid in this, Excel has been designed to adjust your formula’s references to reflect theformula’s new location. There are times, however, when the formula needs to maintain constant cellreferences. Changing the cell reference from relative to absolute will allow you to do this.1.
Select the cell that contains the formula.2.
In the formula bar, select the reference you want to change.3.
Press F4 to toggle through the combinations. The dollar sign signifies which part of theformula will remain the same when you dragging or pasting a formula.
Example
1.
The srcinal formula was put into cell C4 to calculate the total points earned by Suzy onAssignment 1. When dragged to the other students, however, it failed to calculate the pointscorrectly. By looking at the formulas in Column C, you can see that each cell down used adifferent cell for Total Points Possible rather thanmaintaining its reference to B1 (Figure 6).2.
To fix this dragging problem, we will select cell C4. Inthe formula window, highlight the reference to cell B1(Figure 7)3.
Now, press the F4 button. Notice the dollar signs thatnow appear before the B and the 1. These dollar signswill prevent these references from changing when theformula is dragged.
=B1*B4=B7*B10=B6*B9=B5*B8=B4*B7=B3*B6=B2*B5
(Figure 6)(Figure 7)(Figure 8)

Related Search

We Need Your Support

Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks