Return to Index

Beginning Excel 2007

Module 7

There are several ways to create simple formulas in Excel. This module covers the following:

http://www.wresa.org/introxlxp/mod7.h53.gif   Download Practice Files and Enter Data

http://www.wresa.org/introxlxp/mod7.h53.gif    Creating a Formula by Typing a Function Name and a Range of Cells

http://www.wresa.org/introxlxp/mod7.h53.gif    Creating a Formula by Using the Insert - Function Menu

http://www.wresa.org/introxlxp/mod7.h53.gif    Creating a Formula by Typing and Selecting Cells

http://www.wresa.org/introxlxp/mod7.h53.gif    Creating a Formula by Using the AutoSum Button

http://www.wresa.org/introxlxp/mod7.h53.gif    Creating a Formula by Typing

http://www.wresa.org/introxlxp/mod7.h53.gif    Finding a Sum by Using Quick Calculations

 

Download Practice Files and Enter Data

 

Download the practice file " Excelpractice2 " . (Right Click and choose Save Target As).  Save As "Budget"

Enter data

1.   Select Cell C6 and type "Sep-01" . Excel automatically changes the 01 to the current year, You may change it back by clicking on the cell and editing.

2.  Drag the Fill Handle form C6 to N6:

 

http://www.wresa.org/introxlxp/mod7.h54.jpg

3. Enter the data in the blue box below under the Sep-10 column.

 

4. Resize column B by double clicking between the B and C headings

http://www.wresa.org/introxlxp/mod7.h55.jpg

 

 

In column C (Sep-10) enter the following data:

Gross Revenue

·         Sales - 26900

·         Shipping - 5550

Costs of Goods Sold

·         Goods - 17710

·         Freight - 270

·         Markdown - 1240

·         Miscellaneous - 96

Expenses

·         Advertising - 4000

·         Salaries - 4700

·         Rent - 500

·         Utilities - 75

·         Insurance - 45

·         Telephone - 280

·         Office Supplies - 147

·         Training - 100

·         Travel - 200

·         Taxes - 240

  • Interest - 800

 

 

 

5. Save your work! Scroll back to the left edge of your worksheet. 

Creating a Formula by Typing a Function Name and Range of Cells

Activity 1: Creating a Formula by Typing a Function Name and Range of Cells

Create a Formula in Cell C10 to find the sum of the gross revenue items in Cells C8 and C9 by selecting Cell C10 and typing =sum(C8:C9) and pressing Enter. 

Creating a Formula by Using the Insert - Function Menu

 

Activity 2: Creating a Formula by Using the Insert - Function Menu.      

  1. Select Cell C17.  On the Formula Tab of the Ribbon Go To Insert  Function. You may type a description of what you want to do, or -
  2. In the Function Category box, select Math and Trig
  3. In the Function Name box, scroll down and select Sum - Click OK
  4. Look at the range of cells that Excel chooses to apply the function to - click OK

 

 

 

 

Creating a Formula by Typing and Selecting Cells

Activity 3: Creating a Formula by Typing and Selecting Cells

  1. Cell C19 will contain your Gross Profit.  You'll enter a formula to calculate the difference between Gross Revenue and Cost of Goods Sold results.  Select Cell C19
  2. Type an Equal (=) sign
  3.  Select Cell C10
  4. Type a Minus ( -) sign
  5. Select Cell C17
  6. Press Enter

  http://www.wresa.org/introxlxp/mod7.h62.jpg Formula bar

 

 

 

 

Creating a Formula by Using the AutoSum Button

Activity 4: Creating a Formula by Using the AutoSum Button

  1. Select Cell C33
  2. Click the AutoSum tool on the Standard Toolbar. (Microsoft Excel places the SUM function and a proposed cell range in Cell33.  The moving border encloses Cells C22:C32)
  3. Click the AutoSum tool again or press Enter to accept the proposed range and enter the formula in the cell.

   The AutoSum Button is located on the Formulas Tab

  

Excel  has extended the functionality of the AutoSum button to allow you to choose other functions

http://www.wresa.org/introxlxp/mod7.h67.jpg

Creating a Formula by Typing

Activity 5: Creating a Formula by Typing  

  1.  In Cell C35, type =C19-C33
  2. Press Enter
  3. Save your work

Finding a Sum by using Quick Calculations

Activity 6: Finding a Sum by using Quick Calculations

If you want to display the total value of a range of cells, use the AutoCalculate feature in Microsoft Excel. When you select cells, Excel displays the sum of the range in the status bar, which is the horizontal area in Excel below the worksheet window. If the status bar is not displayed, click Status Bar on the View menu.

Suppose you need to know just the total value of Advertising, Salaries, Rent, and Utilities. Click on Cell C22 and drag down through Cell C25. In the Status bar you see the sum displayed - 9275.

Right click on the displayed sum and you will see other choices for the AutoCalculate feature

You have now completed this module. Take a few minutes to review and practice what you have learned.