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.

© Copyright 2018