LAB #8

CSC 101

Introduction to Charts/Graphs in Spreadsheets

R Mohammadi

 

Objectives:

Learn about graphing tools offered by Microsoft Excel.

Learn more about functions and addressing in spreadsheets.

 

about this lab ...

You will need your 3.5" disk for this lab and you must have completed lab#7 and already have a lab7 file on your disk. Graphs are very convincing presentation tools that enable you to convey significant and sometimes complex information. Here, we will build two different types of graphs using the existing tables in our lab7's Excel file.

 

 

1. Create a Bar Chart for first quarter.

 

    1. In the Total column for the First Quarter, highlight totals for clothing, toys, housewares, and jewelry. Only the four totals cells identified must be selected.
    2. From smart icon bar, click on bar graph icon or choose Chart from the Insert pull-down menu. Choose No, don't provide help now if the Office Assistant pops up.
    3. Using the Graph Wizard.

A wonderful feature of Microsoft Excel is the Wizard. A wizard provides assistance in creating charts, graphs, tables, etc. For this lab we will use the chart wizard.

      1. Select Column; click next.

        2.  

      2. If you highlighted the cells correctly in 1.a., the range reads =Sheet1!$F$5:$F$8, or whatever the correct range for your column is.

        3.  

      3. Select the Series folder.

        4.  

      4. Fill in Q1 for name.

        5.  

      5. Click on the x-axis label box.

        6.  

      6. On the Spreadsheet, highlight the labels clothing, toys, housewares, and jewelry and hit the Enter key. Click next when the Source Data Window reappears.

        7.  

      7. Select the Title folder. Fill in the chart title with Smally Department Store, the category (x/horizontal axis) with Department, and value (y/vertical axis) with Dollars as shown in the chart below. Click next.

        8.  

      8. You want the graph in the spreadsheet, so click Finish.

 

 

 

 

 

 

 

2. Formatting the Graph

a) Click the right mouse button on the white space inside the graph.

b) Click on Format Chart Area.

c) In the Font folder, Change the size to 8 and change the font to courier new.

d) Hit Enter

e) We also want to change the background of the graph from gray to white.

A) On the Chart tool bar, in the area where it says chart area, click on the down arrow.

B) Click on Plot Area.

C) Click on (This brings you to the Format Plot Area screen).

D) Change the color to white. Click on OK.

f) Click on the chart tool bar and drag it to the top of the screen. Notice how it incorporates itself into the

existing toolbar.

g) Point to the white space in the chart and drag it by pressing the left mouse button. Place the chart on the right side of the first quarter table.

 

3. Print Preview via

 

4. Create a Pie Chart for Summary Totals.

 

    1. In the Total column for the Sales Summary table, highlight totals for clothing, toys, housewares, and jewelry. Only the four totals cells identified must be selected.
    2. From smart icon bar, click on bar graph icon or choose Chart from the Insert pull-down menu. Choose No, don't provide help now if the Office Assistant pops up.

      3.  

    3. Using the Graph Wizard.

We'll use the wizard again.

      1. Select Pie; click next.

        2.  

      2. If you highlighted the cells correctly in 4.a., the Data range reads =Sheet1!$M$30:$M$33, or whatever the correct range for your total column is.

        3.  

      3. Select the Series folder by clicking on its tab.

        4.  

      4. Click inside the Category Label box.

        5.  

      5. On the Spreadsheet, highlight the labels clothing, toys, housewares, and jewelry and hit the Enter key. Click next when the wizard window reappears.

        6.  

      6. Make sure that the Titles folder is in front, in step 3 of 4 for the chart wizard. Type Sales Summary Totals for the Chart Title.

        7.  

      7. Click on the Data Labels tab.

        8.  

      8. Choose Show Percent. Click next.

        9.  

      9. Click on Finish to complete creating the Pie chart.

        10.  

      10. Move the Pie chart to the right of the summary table, as you did earlier with the bar chart using the press/drag technique.

 

 

 

 

  1. Print Preview via

    2.  

  2. Create a more complex table.

Here you will create a table that keeps track of the changes from each quarter to the next for each of the departments in the Summary table. To get the difference between quarters, you need to subtract the earlier quarter from the next. For instance in the worksheet below, -B5+C5 will yield the change from quarter 1 to quarter 2 for Clothing, assuming that Clothing's Summary data is in row 5 and that Quarter1's total sales is in column B and 2nd Quarter's total sales is in column C. If you format the field as currency, when negative, the value will appear enclosed in parenthesis. To get the percentage of change, you must divide the amount of change by the sales of the earlier quarter. For example, if the quarter 1 sales for Clothing is in cell B5 and the change amount from quarter 1 to quarter 2 is in cell B13, the percentage of change is +B13/+B5. Use the percentage format for cells containing percentages. Create the following table:

Smally Department Store

Percentage of Change

Dept.

Q1 to Q2

Q2 to Q3

Q3 to Q4

Clothing

Change Amount

$ 350.00

$ (2,625.00)

$ 5,250.00

Change %

7%

-50%

200%

Toys

Change Amount

$ (25.00)

$ (1,137.50)

$ 2,275.00

Change %

-1%

-50%

200%

Housewares

Change Amount

$ 350.00

$ (2,425.00)

$ 4,850.00

Change %

8%

-50%

200%

Jewelry

Change Amount

$ 50.00

$ (1,375.00)

$ 2,750.00

Change %

2%

-50%

200%

 

7. Print preview via .

 

8. Save your spreadsheet.

 

Save this sheet as LAB8 on your 3 ½" Floppy (A:). You have now completed the lab. Logout of the workstation after your lab instructor examines your work.