Excel Using Formulas and Functions

1. Excel Using Formulas and Functions Microsoft Office 2010 Fundamentals

2. Introduction • The real power of a spreadsheet program such as Excel is its ability to perform simple and complex calculations on worksheet data. • Using the commands located on the Formulas tab of the Ribbon, you can enter formulas to perform calculations or use predefined formulas, called functions. 2 2

3. Entering Formulas • A formula is a set of instructions used to perform calculations on values in a worksheet and must begin with the equal sign ( = ). • An operator is a sign or symbol that indicates what calculation is to be performed. • Arithmetic operators are used for addition +, subtraction - , multiplication * , division / , and exponentiation . 3 3

4. Entering Formulas (continued) • Constants are numbers entered directly into a formula that do not change. • Cell references identify the cells containing the values you want to use in the formula. The results will automatically be updated if the values in those cells change. 4 4

5. Entering Formulas (continued) • The formula is displayed in the formula bar; the results are displayed in the cell. 5 5

6. Entering Formulas (continued) • The order of operations is a specific sequence used to calculate the value of a formula, from left to right: 1. Exponentiation ( ^ ) 2. Multiplication ( * ) or division ( / ) 3. Addition ( + ) or subtraction ( - ) • To change the order of operations, use parentheses to group expressions. 6 6

7. Understanding Cell References and Copying Formulas • A relative reference means the reference to a cell changes in relation to the location of the formula. 7 7

8. Understanding Cell References and Copying Formulas (continued) • An absolute reference is a permanent reference to a cell and does not change in relation to the location of the formula. 8 8

9. Understanding Cell References and Copying Formulas (continued) • Auto Fill is a feature that you can use to automatically fill in worksheet data in any direction. • You can use it to copy data or formatting or quickly copy a formula by dragging the fill handle—a little black square in the lower-right corner of the selected cell. 9 9

10. Using Functions • Built-in formulas, called functions, enable you to perform complex calculations easily. • A function must follow a set of established rules, called syntax, that specifies how the function must be entered. • Most functions require an argument, which refers to the text, numbers, or cell references on which the function is to be performed. 10 10

11. Using Functions (continued) • Use the Sum function to quickly total a range without manually typing the formula. 11 11

12. Using Functions (continued) • Click the Sum button arrow to display a menu of the most common statistical functions. 12 12

13. Using Functions (continued) • Click the Insert Function button to open the Insert Function dialog box. 13 13

14. Using Functions (continued) • Once you select a function in the Insert Function dialog box or from the Ribbon, Excel opens the Function Arguments dialog box. 14 14

15. Reviewing and Editing Formulas • To display the formulas instead of the resulting values, click the Show Formulas button in the Formula Auditing group on the Formulas tab. 15 15