In addition to using formulas, the following links will provide step-by-step instructions for each of the listed processes for using Microsoft's Excel 98 software:

To add a formula to a spreadsheet, click in a specific cell and type in an = sign. For example, typing in =10*3 will add 30 to the cell. Open a new spreadsheet and try it!

The following characters can be used to perform calculations:

  • Exponential ^ raises the preceding number to the power of the number that follows the ^ sign.
  • Multiplication *
  • Division /
  • Addition +
  • Subtraction -

Formulas are evaluated by Excel and follow the order of operations. For example, the following formula is evaluated by Excel =9+12/3 yielding an answer of 13. The division is performed first, then the addition. If a formula contains parentheses, then the function within the parentheses is performed first. So =(9+12)/3 would yield an answer of 7.

Cell names can also be used in formulas. Open "gradebook" which was previously saved on the desktop in the "Entering Data" skill development activity. To find the average of cells B2, C2, D2 and E2, put the cursor in F2 and enter the following formula: =(B2+C2+D2+E2)/4

1. Press Enter. The average is displayed in cell F2.

 

2. If a formula is entered incorrectly, the following message will appear. Click on Yes to remove the box and accept the corrected formula. .

3. Excel contains built-in functions to perform calculations automatically. Place the cursor in cell F3 and then click the = sign next to the entry box.
4. Choose Average from the drop down menu box. Type (B3:E3) after AVERAGE in the entry box.

5. The average is displayed in cell F3.

Take a look at the other formulas which are built in to the program by clicking on the drop down menu and making a different choice.

In addition, choosing Help from the menu bar and then clicking on "Contents and Index" will provide you with a box with a scroll bar which lists numerous help topics.

Adding Formulas to Adjacent Cells

If you want to add find the average for the other three students, the formula which was added to cell F3 can be copied down to cells F4 through F6 easily.

1. Click on cell F3. Notice the square which appears at the lower right hand corner. This is called a "fill handle."

2. With you cursor on the fill handle, hold down the mouse key and drag the cell down through cells F4 to F6.

3. The average for each of the rows will appear in the cell.

Formulas can also be added to adjacent cells by highlighting the cells into which you want to copy the formula.

1. Delete all the data from cells F3 to F6.

2. Highlight cells F2 to F6 by clicking on cell F2, holding down the mouse key and scroll down to the bottom of cell F6.

3. Choose Edit from the menu bar and choose Fill Down.

4. The formula is added to each of the cells, F3 to F6. Click on each cell to view the formula for each cell in the entry bar. Excel uses "relative copying" to change the name of the cells used in each calculation.

Formatting a Spreadsheet

The layout of a spreadsheet can be changed or formatted with a few mouse clicks.

1. Place your cursor in cell F6 and click. Then choose Format from the menu bar and choose Cell. The following dialog box will appear.

2. Select Number in the Category column. This option allows you to determine the number of decimal places to which a number will be displayed. The Sample box shows you how the number will appear in your spreadsheet.

3. Click on Currency. This option is used when dollar amounts are added to cells. In this case, the decimal points default to 2 places. Once again, the Sample box shows how the number will appear in your spreadsheet.
4. Click on Percentage. This option displays the value as a percentage with two decimal places. For example, the value in cell F6 will be displayed as 7900.00%.

The other tabs shown on the Format Cells dialog box can be used to format the spreadsheet layout.

  • Alignment allows you to format cells as either left, right or center aligned. Highlight all the cells in the gradebook by clicking in A1 and holding down the left mouse key. Click on the center command.

All the information in all the cells will now be centered.
  • Font is used to format the style, font, and size of the numbers and text.
  • Border allows you to determine how and where borders should be placed in your spreadsheet.
  • Patterns allow you to change the color of the cells in your spreadsheet.
By clicking OK, the background color of the cells will now be blue rather than white. Do not save the changes you made to "gradebook."
Continue learning about spreadsheets, by completing the skill development activity entitled, "Charts and Graphs."
©2000-2002 April A Cleveland for Science Junction, NC State University.
All rights reserved.
URL: http://www.ncsu.edu/sciencejunction/route/usetech/dataanalysis/macdataanalysis/meuseformulas/index.html
Last updated 07/30/02

| Data Analysis | Learning to Use Technology  | Teaching With Technology |