You will first need to download AdvancedExcel.xls to complete this teaching reference.
Click on Start | Programs | Microsoft Excel | File | Open.
|
|
|
Double-click on the tab Sheet 1. The word Sheet 1 becomes highlighted. Type 3rd Grade Reading. Double-click on the tab Sheet 2, and label this 4th Grade Writing.
|
|
|
Copy the table in 3rd Grade Reading sheet to 4 rows directly below the current table.
- To do this, highlight the entire table in 3rd Grade Reading Sheet, right-click on the highlighted area and select Copy.
-
- Select B11 below the selected table, and right-click on cell and select Paste. Do the same for the table in 4th Grade Writing Sheet. We are going to use the data and organization of these copied tables to determine percentages.
-
|
|
|
| Developing the Percentages | |
In the 3rd Grading Reading sheet, we are going to determine the percentage of students that performed at each level. Let's work with the new table and data from 1998.
- Select D12. Delete the number in D12 by pressing the backspace key.
- Type '=' to begin a formula in this cell. Select D5 in the Unsatisfactory column. To begin a division formula, type '/' after D5.
- Select C12 to place a denominator in the formula. Your formula will look like =D5/C12.
- Press the Enter key. The formula is now entered.
|
|
|
We are going to be using this formula to determine the other percentages in the 1998 class. We do not want the value of C12, the number of Total Students, to change in our formulas, so we must make this an absolute number.
- Select C12, and place a '$' in front of the C and 12 of C12. The formula will then look like =D5/$C$12. This makes the value of C12 absolute.
|
|
|
Select D12. With your mouse, click on the small black box on the lower right corner of the selected cell and drag the mouse to the end of the Proficient or Above column.
What happens to your data in the cells of 1998?
|
|
|
Highlight D12 through H12.
Select Format on the Menu Bar and select Cells. What does this area allow you to do to the entrees in the cell or cells?
Click on Percentage in the Category window. How many decimal places do you want the percentage listed? You can make this choice in this window, too.
|
|
|
|
Repeat the Percentage exercises (Activity 4 - 7) for 1999-2000 and with the data in 4th Grade Writing sheet.
|
|
|
| Graphing Levels by Data Range | |
View the 3rd Grade Reading sheet.
- Click on Insert in the Menu Bar and select Chart. What is the best Chart to view our information? The Chart Wizard will walk you through selection of Data, and labeling of data.
- In the data Range window, select the Insert button to insert the data. A Source Data window opens.
- Select G12 through G15. You will notice that the data is entered into the Source Data window.
- Click on the accept button in the Source Data window in-order for the data to be inserted in the data range.
|
|
|
| Graphing Levels by Series | |
For the Series in choice, Column needs to be selected because we want to compare Achievement level (column) by Years (rows).
- Click on the Series tab in the Chart Wizard window. Notice that in the window below Series there is a list beginning with Series 1. These are the labels for each achievement labels. We need to give each Series a name. Series 1 is highlighted and selected.
- Click on the insert button in the Name area. This will allow you to view your data and select D11 that contains Unsatisfactory - the title for Series 1 label.
- Click on the accept button within the Chart Wizard window. The label will be entered and Series 1 will be replaced with Unsatisfactory .
- Follow the same process for Series 2 through Series 4.
|
|
|
| Graphing Levels by Series Continued | |
At the bottom of the Series Window is an option to label the X axis - "Category (X) axis label."
- Select the insert button and the data will be in view.
- Select B12 through B14 to identify each year. The Cell Reference for this data will be Inserted into the Source Data window.
- Click on the accept button in the Source Data window.
- Click on the Next button in the Chart Wizard window.
|
|
|
In the Chart Options window, you have an opportunity to enter a Title for the Chart (Titles) and provide labels for the X-axis and the Z-axis (percentage). The other choices in Chart Option areas allows you to place axes information on the chart )Axes), add gridlines to the Chart (Gridlines), place the Legend at various locations around the Chart (Legend), display actual data points on the columns (Data Label), and place the data table close to the chart (Data Table).
After creating the chart, you have the option to edit these options by selecting the chart that you have made, and then select Chart on the menu bar.
You can then select Chart Options. You can also add additional data to the chart by selecting the chart, and then Chart on the menu bar. You can then select Add Data.
To compare your spreadsheet, download the finished version.
|
|
|
Feedback on this Workshop
Congratulations! We're Done!
For questions or comments about this workshop, please contact LoriReinsvold at University of Northern Colorado (lori.reinsvold ---at--- unco.edu)
|
