Create a Spreadsheet in Microsoft Excel
Updated: June 30, 2004
This tutorial is designed so that you can create graphs beforehand to use as a sample for your class. Depending on your curriculum and age of your students, you may choose to instruct your students on these skills as part of the lesson.
In this example, students are given a small packet of jellybeans and then asked to estimate and record how many of each color are likely to be in the packet. The packet is then opened and the actual results are recorded.
Recording information in a table
1. | Open Excel. |
2. | Click cell A1, and then type the heading Colors. |
3. | In cell B1, type the heading Estimate. |
4. | In cell C1, type the heading Actual. |
5. | Highlight to select the row, and then, on the Standard toolbar, click Bold and Center. |
6. | Under the heading Colors, record all the colors found in a packet of jellybeans. |
7. | At the end of the list of colors, in the same column, type Total. |
8. | Under the heading Estimate, record an estimated number of each color that is likely to be found in the packet. |
9. | Open the packet and record the actual number of each color found. |
10. | Highlight to select the numbers in the Estimate column, and then click AutoSum. The total of your estimated numbers will be recorded in the row titled Total. |
11. | Highlight to select the numbers in the Actual column, and then click AutoSum.
The total of your actual numbers will be recorded in the row titled Total.
|
12. | On the File menu, click Save As. |
13. | In the File name box, type Jellybean Graph. |
14. | Click Save to save document to the school server. |
Creating a graph
You can use the Chart Wizard to represent the data in graph form.
1. | Highlight to select all cells created in the table except the row titled Total. |
2. | On the Standard toolbar, click Chart Wizard. |
3. | In the Chart type list, click Column, and then click Next. |
4. | The information in the Data range box is correct, so click Next. |
5. | In the Chart title box, type the title Jellybean Color Mix. |
6. | You may name the axes in the Category (X) axis and Value (Y) axis boxes if you choose. At this point, you may also wish to experiment with options on the Gridlines tab and the Legend tab. |
7. | Click Next.
|
8. | Click As new sheet, and then click Finish. 
|
Your graph will now be created and stored as Chart1. To access the sheet where you created the original data or your chart, click the tabs at the bottom of the page.
Adding color
The primary function of a graph is to display data in a visual form. Experiment with color and pattern to achieve an easy-to-read, self-explanatory graph.
1. | To change the colors of features on your chart, double-click the area, and then click colors and fill effects to suit. By double-clicking a column, all columns in that series are highlighted. |
2. | Double-click the column a second time to highlight the individual column. |
3. | On the File menu, click Save. 
|
Sharing (or distributing) results
Now that the individual results are graphed, classmates can share their results. To do this, have the students exchange their Excel files. This can be done by saving their files to a folder on the school server that everyone can access, or by placing their spreadsheets on disk and distributing them. An alternative method is to just print and the post the individual packet graphs on a bulletin board.
Creating a comparison graph of shared results
After the students have reviewed the shared files, the data can be recorded manually on a new page of the original spreadsheet as the first step toward creating a graph that will illustrate the actual results from all the students' packets.
1. | Return to your Excel document. |
2. | At the bottom of the page, click the Sheet 2 tab.
This gives you a blank worksheet within the same document to work from. 
|
3. | Click cell A1, and then type the heading Color. |
4. | Begin at cell B1 and, moving along the row horizontally, type the headings Packet 1, Packet 2, Packet 3, Packet 4, and Average. |
5. | Under the heading Colors, moving vertically down the column, type the list of colors found in the Jellybean packet. |
6. | Under the heading Packet 1, type your results as recorded in the Actual column of the first table. |
7. | Under the heading Packet 2, type the results from another student's spreadsheet as recorded in the Actual column of that spreadsheet's first table, and repeat this step for each additional spreadsheet that you can open and view. Hint: It may save time to have a spokesperson from each team read the team data aloud, rotating to groups until all packet data has been provided. |
Using formulas
You can use the AutoSum feature to calculate the average number of times that each color occurs in all the packets.
1. | Highlight to select cells B2 to E2. |
2. | On the Standard toolbar, click the arrow next to AutoSum, and then click Average. The average will be automatically calculated and placed in the next adjacent horizontal cell. |
3. | Highlight from the cell with the first Average result to the last Average cell with an entry next to it, on the Edit menu, click Fill, and then click Down. The formula will automatically apply to and be calculated for all rows. |
4. | Adjust averages to whole numbers by moving the decimal point to the right, on the Standard toolbar, click Decrease Decimal. 
|
5. | On the File menu, click Save. |
6. | Save the document to the appropriate location on the school server. |
Creating a graph
You can use the Chart Wizard to represent the data in graph form.
1. | Highlight to select all cells created in the table. |
2. | On the Standard toolbar, click Chart Wizard. |
3. | In the Chart type list, click Column, and then click Next. |
4. | The information in the Data range box is correct, so click Next. |
5. | In the Chart title box, type the heading Jellybean Color Mix - Averages, and then click Next. |
6. | Click As new sheet, and then click Finish. |
7. | Your graph will now be created and stored as Chart2. |
At this point, you can experiment with color and fill effects by double-clicking on specific areas in your chart.
© 2004 Microsoft Corporation. All rights reserved.
This tutorial is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Companies, names, and/or data used in screens and sample output are fictitious, unless otherwise noted.
Microsoft and Encarta are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
