Microsoft Excel

Using Formulas and Creating Charts

 
   

Lesson 6

 
 

 

Entering Formulas – Pointing 

When typing a formula, cell references can be entered by pointing.  Pointing is when a formula is typed to where a cell reference should appear and then the mouse pointer is clicked on a cell, which places its reference in the formula.  Selecting a block of cells places a range in the formula.  Pointing is the best method for entering cell references into a formula because typing errors are avoided. 

For example, in the spreadsheet below, =SUM was typed into cell B5.  The range was then entered into the formula by dragging the mouse from cell B2 to cell B4.  The colon (:) is automatically inserted by Excel: 

Band

CDs

Tapes

Beatles

8

10

Phish

10

12

Big Sky

5

22

Total

=SUM(B2:B4)

 

Selecting a block of cells enters its range into a formula 

Typing a right parenthesis and clicking on the Enter button or pressing Enter complete the formula. 

Displaying Formulas 

Spreadsheet formulas are displayed at their cell locations by pressing Ctrl & `(the accent mark): 

Band

CDs

Tapes

Beatles

8

10

Phish

10

12

Big Sky

5

22

Total

23

44

Before pressing Ctrl & ` (accent mark) 

Band

CDs

Tapes

Beatles

8

10

Phish

10

12

Big Sky

5

22

Total

=SUM(B2:B4)

=SUM(C2:C4)

 After entering Ctrl &` (accent mark) 

The accent mark key is located above the TAB key on the keyboard.  A formula that is longer than the cell width can be displayed in its entirety by increasing the column width. 

Printing when formulas are displayed prints the formulas stored in the cells rather than the values.  Pressing Ctrl &` (accent mark) again displays values in each cell, although any column widths that were increased remain wider and need to be reformatted. 

Exercise 7: In this exercise you will enter formulas to calculate the average grade on a test and a student’s average in the Grades spreadsheet created in exercises 1, 2, and 3. 

1.      Open your copy of "Student Grades" (Excel Lessons 1 & 2)

2.      USE A FORMULA TO AVERAGE THE GRADES FOR TEST 1

a.       Select cell B9.

b.      Type =AVERAGE(

c.       Highlight cells B3 to B8.  Excel enters the cell references for the selected range into the formula.

d.      Type the closing parentheses) and then press Enter.  The average for Test 1, 83.33333, is displayed in cell B9. 

e.   Repeat the process for tests 2, 3 and 4.

 f.   Highlight cells B9, C9, D9, E9 and F9. From the menu bar select Format. Choose the option for Format Cells.  In category select Number. Decimal places select 2. Press OK. (Be sure to add the formula in cell F9. After you finish part 3 the average should appear.)

3.      CALCULATE A STUDENTS TERM AVERAGE

a)     Select cell F3 and use pointing to enter the formula =AVERAGE(B3:E3).  The average 84.5 is displayed.

b)      Highlight cell F3, note that a black box is now around the 84.5.

c)      The cursor should now look like a white plus sign when you move it around the page.

d)      Move the cursor until it is over the lower right corner of cell F3. The cursor will change to a black + sign.

e)      Click the left button on the mouse while the black + sign is present. While still holding the left mouse button down drag the mouse down to cell F8

f)        While cells F3 to F8 are highlighted right click on the scores. A dialog box will open. Choose the option for Format Cells.  In category select Number. For decimal places select 2. Press OK.

                                  g)   Your student grades should now be averaged to two decimal places. 

 

4.      ADD TITLE FOR THE NEW INFORMATION

a.       Select cell F1 and enter the label Student Average.

b.      Format the label as bold and right aligned if it is not already formatted thusly.

c.       Resize the column longer so that the label is displayed entirely.

d.      Select cell A9 and enter the label Test Average.

e.       Format the label as bold, italicized and right aligned. 

5.      VIEW THE FORMULAS

a.       Save the modified Student Grades

b.      Press Ctrl & `(accent mark) Note that the formulas have all been added. Press Ctrl & `(accent mark) again to return to the original form.

c.       Be sure that the Gridlines and Rows and Columns have been added. (File, Page Set-up, Sheet)

d.      Select Preview Printing to view your work.

6.      ADDING A GRAPH OR CHART – Read the following section:

More below to add to your Excel assignment!!!

Charts and Graphs

Charts allow you to present data entered into the worksheet in a visual format using a variety of graph types. Before you can make a chart you must first enter data into a worksheet. This page explains how you can create simple charts from the data.

Chart Wizard [chart wizard]

The Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes.

  • Enter the data into the worksheet and highlight all the cells that will be included in the chart including headers.
    [Chart Wizard example]
  •  
  • Click the Chart Wizard button on the standard toolbar to view the first Chart Wizard dialog box.
  •  
  •  Chart Type - Choose the Chart type and the Chart subtype if necessary. Click Next.

  • [Chart Wizard dialog box]
  •  
  • Chart Source Data - Select the data range (if different from the area highlighted in step 1) and click Next.

  • [Chart Wizard dialog box]
  •  
  • Chart Options - Enter the name of the chart and titles for the X- and Y-axes. Other options for the axes, grid lines, legend, data labels, and data table can be changed by clicking on the tabs. Press Next to move to the next set of options.

  • [Chart Wizard dialog box]
  •  
  • Chart Location - Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu.

  • [Chart Wizard dialog box]
  •  
  • Click Finish to create the chart.

  • [Chart Wizard example]
  •  

Resizing the Chart

To resize the chart, click on its border and drag any of the nine black handles to change the size. Handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart.

Moving the Chart

Select the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it. 

 

Chart Formatting Toolbar

[chart toolbar]

Chart Objects List - To select an object on the chart to format, click the object on the chart or select the object from the Chart Objects List and click the Format button. A window containing the properties of that object will then appear to make formatting changes.

Chart Type - Click the arrowhead on the chart type button to select a different type of chart.

Legend Toggle - Show or hide the chart legend by clicking this toggle button.

Data Table view - Display the data table instead of the chart by clicking the Data Table toggle button.

Display Data by Column or Row - Charts the data by columns or rows according to the data sheet.

Angle Text - Select the category or value axis and click the Angle Downward or Angle Upward button to angle the the selected by +/- 45 degrees.

[Angle text example]

Copying the Chart to Microsoft Word

A finished chart can be copied into a Microsoft Word document. Select the chart and click Copy. Open the destination document in Word and click Paste. 

7.      Use the preceding information to create a chart or graph for your Student Grades. Be sure that you check with Mr. Duval before you print so that you have the correct information on your chart/graph. 

Step 1:

Step 2:

Step 3:

Step 4:

Chart:

8.      SAVE THE MODIFIED Student Grades ASSIGNMENT. 

9.      Print a copy and turn it in for credit.