Spreadsheet/Graphing: Tutorials

---

Vian Public Schools
 Online Technology Competencies

---

To use MS Excel is ideal for this tutorial, yet any modern spreadsheet package allows you to work on most of the following tutorials. Both Windows 95/98 and Windows 3.1 platforms are mentioned. Both types of platforms are available on VPS campus. However, assessments must be taken in Computer Tech Lab.

IMPORTANT!
To use this tutorial, it is important that you understand how to open applications and use windows, enter text with the keyboard, click and drag with your mouse, access menu commands like File/New and Edit/Paste, and know how to save files to diskette. Please review tutorials 1 and 2 if necessary. This tutorial may take 30 - 50 minutes. Consider this piece to be used in your student portfolio.

TUTORIAL4.1Interpreting and Communicating Information in a Spreadsheet

Spreadsheet Concepts
A spreadsheet allows you to enter multiple rows and columns of data and interpret various results through formulas like the addition of all values in a column, or the average of several row scores. Electronic data in a spreadsheet can easily be converted into graphical charts.

Follow these steps for a good spreadsheet design:

• Establish Your Objectives
• Define the Required Output
• Construct the Spreadsheet
• Test and Use the Spreadsheet
• Document the Spreadsheet

Other features of an electronic spreadsheet include:

• Complex Statistical Analysis
• Accountant Assistance
• Online Documentation for help

• MicroSoft Excel
• Lotus 1-2-3
• Quatro Pro
• ClarisWorks

For Example:

• Average looks to a set of data in a range of cells and determines the average of the numbers.
• Mode looks to a range of cells and determines the most common appearing number
• Sum will sum the totals of all numbers in the specified range.

Table 4.1 - Sales figures for AlphaOmega Company

SALES	1st quarter	2nd quarter	3rd quarter	4th quarter	Total by region
EAST	700	900	1000	1300	3900
WEST	1450	1300	1675	1800	6225
NORTH	800	1100	1650	1980	5530
SOUTH	1105	1010	1345	1240	4700

1. Determine the total sales figure from the Southern region for the year. 
2. Indicate the best selling quarter, (1st, 2nd, 3rd, or 4th) for the North region. 
3. Which region had the lowest 3rd quarter sales figure in the country? 

TUTORIAL 4.2, 4.3, 4.4 Create a new spreadsheet, enter data, type in column and row headings, and create/copy formulas and functions to perform calculations. Print a spreadsheet.

Create a new spreadsheet

1. Open Microsoft Excel, the spreadsheet program in the Microsoft Office Suite. Excel displays a blank workbook, ready for data to be entered. If Excel is already openTo make a new worksheet, select File/New from the menu bar.
2. Notice that cell A1 is highlighted with a border around it. With cell A1 still highlighted, type your name using the key board.
3. Go to the File/Save menu command, name your document tut4-2.doc, and save it to disk. For a review of how and where to save your files, go to Tutorial 1.4.

1. Select the cell range in which you want to enter data. Do this by clicking on cell C3 and dragging to cell F6, then release the mouse button. Notice the large, dark selected area and the one white cell within the selected range, C3. This is the cell in which data will go when you begin to type.
2. Begin typing the following data in the cells:
• 700 (press tab to move to the next adjacent cell) 900 (tab) 1000 (tab) 1300 (tab to move to the next row)
• 1450 (press tab) 1300 (tab) 1675 (tab) 1800 (tab to next row)
• 800 (tab) 1100 (tab) 1650 (tab) 1980 (tab to next row)
• 1105 (tab) 1010 (tab) 1345 (tab) 1240
• Next, click once with your cursor anywhere on the spreadsheet to deselect your highlighted cells.
An alternate way of entering information is to enter the cell contents and then press ENTER to move from top to bottom. So with the same range selected (C3:F6), you would type in the first column of numbers than the second column, third and fourth, instead of typing the data one row at a time.

1. Using MS Excel, select cell G3.
2. Click once in the formula bar, to the right of the name box where the cell reference for cell G3 is displayed.
3. Type the following in the formula bar with cell G3 still selected.
=SUM(C3:F3)

now press enter, the sum of the four cells to the left of G3 is displayed, the selected cell moves to G4

4. Follow the steps below to create three more formulas for the row totals.

1. Add each region's total sales for the year by using a SUM function on the cells C4 through F4. Make sure cell G4 is selected and click on the AutoSum button in the standard toolbar, double check the cells that are highlighted, also, look to the formula bar. The AutoSum function has placed a formula in cell G4 that is very similar to the formula in G3. Press Enter to complete the function.
2. Fill this formula down through the other two cells, G5 and cell G6. Select the range G4:G6 by clicking on G4 and dragging to G6. Go to the menu command Edit/Fill/Down. The formula should be copied to the other two cells with relative values for each row.

1. Click on cell C2 and type the words "1st quarter", press tab and continue to label the columns with "2nd quarter", "3rd quarter", and "4th quarter"
2. Click on cell B3 and type the word "EAST", press the enter key, moving down. Type in the words "WEST", "NORTH", "SOUTH" for the remaining row headings
3. Select cells B3 through B6 and change the text style to Bold by pressing on the Bold Button in the Format Toolbar.
4. Select cells C2 through F2 and change the text to italic by pressing the "I" button in the Format toolbar.

1. Choose the File/Print Preview option.
2. Make sure your spreadsheet information will fit on one piece of paper (for this example)
3. Select Print from the menu commands in the Print Preview section.
4. Make sure your printer is selected and click on OK.
5. Save your work to the file name tut4-4.xls using File/Save As

TUTORIAL 4.5 Create a graph or chart from spreadsheet data

The Chart Wizard function within Excel can turn your spreadsheet information into one of many types of graphs or charts. By selecting the data range that you want to convert, Excel converts the data into a very useful graph.

Create a chart from spreadsheet data

1. Select the data that you want to turn into a graph. From the previous tutorial exercise, tut4-4.xls, select the range B2 through F6.
2. Click on the ChartWizard button on the toolbar, then click in the worksheet area once. Be careful to click only once. After clicking once in the worksheet area, Excel will bring up a dialog box titled Step 1 of 5, double check the range you ahve selected and click Next. Excel will place the graph's upper left corner at this spot. After completing the following steps, you may resize the graph.
3. Choose the chart type among the different kinds shown in Step 2 of 5. Select a chart that will appropriately display your data in chart format. For this example, choose the Column Chart, click Next.
4. In step 3 of 5, choose which type of format for the column chart. Select the one that is chosen by default, then click Next.
5. In step 4 of 5, make sure your data is correctly displayed in the preview window and choose any options that will interpret your data better, click Next.
6. In step 5 of 5, add a title to your spreadsheet and add axis labels. Type in "Quarter" for Category-X and type "Region" in the Axis-Y field. Click Finish.

TUTORIAL 4.6 Insert spreadsheet information into a word processing document

To insert an Excel graph into a word processing document, this exercise will use Microsoft Word.

1. Open Microsoft Word and type the following:

 

 

From: (tab twice) Your Name
To: (tab twice) Department Manager
Date: (tab twice) Today
Re: (tab twice) Forecast of Expenditures

2. This will be the header to your word document, press enter twice and type the following as the beginning body text of your memo"
"We have finished the figures for the previous year's sales for each region's stores. Please review the following chart and let us know if the chart is suitable for your presentation."
3. After typing the above text into the word processor, switch over to Microsoft Excel (Alt-Tab for Windows) and copy the data range B2 through G6. Click in cell B2 and drag to cell G6, this will include the data, row headings, column headings and totals for each region.
4. Select the command Edit/Copy from Excel's menu bar.
5. Switch back over to Microsoft Word and use the Edit/Paste command to position the copied information below your text.
6. Return to the Excel application and click once on the chart you created in Tutorial 4.5. With the chart selected, select Edit/Copy and then switch back over to MicroSoft Word.
7. In the application Word, select Edit/Paste from the menu bar.
8. Hit the Enter key a couple times and type the following:
"If you have any questions or comments, please inform me as soon as you can." (hit the enter key twice and type: "Thank you, (enter key four times) "Your Name"
9. Now that you are done with the memo, save your work to disk as tut4-6.doc and print a copy of your spreadsheet.

Spreadsheet-	A spreadsheet is a page of columns and rows in which you enter data and then use the data to plot charts and present tables of information.
Cell-	A cell is a small rectangular cross section of a row and column, used to enter data for a spreadsheet. Spreadsheets use multiple columns and rows of cells to form a grid in which many types of data are entered.
Data Entry Bar-	The data entry bar is used to enter text, a formula, or a number value in to a specified cell.
Formula-	Used to calculate values in other cells. Cells containing formulas begin with an equal sign followed by arithmetic operators, values, cell addresses and cell names.
Function-	Functions are premade formulas that assist in the development of your spreadsheet. In a function, you name the formula to be calculated and then supply the inputs or arguments.

---

 Further exercises:

Directions for Exercise 2:

1. Type the above information into a second worksheet. Remember that you can add zeros to an entire range of data. Adjust column width as necessary.
2. Create formulas that total the dollar numbers for each row and then for every column including the total row figures.
3. Format the numbers you typed for each quarter with commas and zero decimal places. Is this easier to read than with .00 added to each number?
4. Format the numbers for the total row cells with dollar signs, commas, and zero decimal places.
5. Format the headings by changing font type, font color, and making them bold.
6. Insert a double line border underneath the column headings and make the column heading row taller than the other rows.
7. Insert a border around the total row figures onthe right.
8. Add a light grey background to all the numbers, except the totals (only the original 28).
9. Save the spreadsheet to diskette or to your hard drive.
10. Print the Spreadsheet.

The Excel Help Page

Excellent resource for tutorials on many spreadsheet functions

The Queet Site

Tutorials for Excel, Word, PowerPoint and more!