Download Report

Contact: Alx Sanchez
The Student Support Center
Phone: (805) 893-5252 or 893-2208
Email: [email protected].
University of California
Santa Barbara, CA 93106
http://www.collaborate.ucsb.edu/
UCSBCOLLABORATE
How to create a spreadsheet
formula in excel
In this article Joe Gaucho guides you through steps to create a basic spreadsheet
formula in Microsoft Excel. Assuming you are familiar with the spreadsheet basics,
this is one of the many helpful tools in Excel!
(c) 2010 UC Regents, Mark Golonska November 18, 2010
*This is a rudimentary approach to understanding formulas in Excel. However, may prove to be a very practical
and important tool to be used in the professional field (especially in calculating financials)!
Step-by-Step Formula Tutorial
Microsoft Excel formulas are extremely helpful and time-saving in performing quick calculations on entry data on
your spreadsheet. This may range from basic number-crunching to calculating complex averages. Moreover, if
you decide to amend any of your data entries, Excel will automatically recalculate an up-to-date answer!
Figure 1.0
Figure 1.0, as pictured above, illustrates an example of a very basic addition formula used to generate the
answer in cell E3. Although it is a very basic formula, the same principle applies when writing more complex
formulas.
In the example above, the formula adds the numbers 3 + 2. The final formula in Excel will be displayed as
=E1 + E2 (since E1 and E2 are the data cells for numbers 3 and 2, respectively).
Step 1: Entry Data
Seems like the fairly, obvious thing to do. Be sure to accurately input your data entries into the correctly
labeled columns (whatever they may be) so as you won’t get confused. Remember the shortcut key of
“ENTER” to jump cells more quickly.
Step 2: Configure Output Cell
It is crucial to note that in Microsoft Excel, FORMULAS ALWAYS BEGIN WITH AN EQUAL (=) SIGN.
In math class your formula may look like this 
2+2=
But in Excel your formula must look like this 
=2+2
The cell in which you wish to have your answer calculated in will be your output cell.
Figure 1.1
Figure 1.1 illustrates where the output cell for this formula would be located.
Instructions are as follows:
1.
Click the cell you wish to configure as your output cell (in this case it would be E3).
2.
Type in an “=” into the cell. (Remember Excel formulas always begin with an equal sign!)
Step 3: Enter Cell References & Operations into Formula
Figure 1.2
Figure 1.2 illustrates how after the equal sign, cell references containing data are clicked and added to the
formula.
Instructions are as follows:
1.
Click on a cell reference (in Figure 1.2 this would be E1) to enter into the Excel formula.
2.
Type in an operation (i.e. + - * / ^)
3.
Click on another cell reference (in Figure 1.2 this would be E2) to enter into the Excel formula.
4.
{Continue to repeat steps 2 and 3 until satisfied with a given formula}
5.
Once you are satisfied with a given formula simply press ENTER and Excel will make the calculations.
(Staying consistent with the introductory example, the output in E3 from =E1 + E2 would appear as the
answer 5.
Mathematical Operators in Complex Formulas
Figure 1.3
For easiest and most effective use of Excel formulas, utilize the number pad on the right of the keyboard.
•
•
•
•
•
Subtraction - minus sign ( - )
Addition - plus sign ( + )
Division - forward slash ( / )
Multiplication - asterisk ( * )
Exponentiation - caret ( ^ )
Note: Remember Your Order of Operations!
A quick stroll down memory lane to grade school… if more than one operator is used in a formula,
Excel will perform these operations in a specific order.
Please Excuse My Dear Aunt Sally!
(I.e. Parentheses, Exponents, Multiplication, Division, Addition, Subtraction)
WANT MORE?
For more quick reference sheets or to see this one on action visit us at:
www.collaborate.ucsb.edu/ssc
Follow us on twitter and friend us on facebook for updates.