search
EXCEL-lence: Strategies for using Microsoft EXCEL

Introductory Activities
Learning Activities
Culminating Activity
Cross-curricular Extensions
Community Connections

Introductory Activities

On a sheet of paper, ask students to calculate the average of the following list of numbers: 12.3, 23.7, 35.8, 41, 67.13, 68.3, 74.759, 82.1, 89, 93.7, 99.99 (Answer: 62.525363)

Ask a student volunteer to describe the steps that one must take to calculate an average (or mean) from a series of numbers. Record student responses on a large instructional writing space. (Student responses will vary but should indicate comprehension by statements that include finding the sum of all numbers in a set or series of data, counting the number of values, data points, in the set then dividing the sum by the number of values.)

Learning Activities

Basic strategies for analyzing small data sets-Formulas and Charts

Copy the series of numbers into a column in EXCEL with each data point in a separate cell in a vertical column. In EXCEL, the column default label is alphabetical and the row default is numerical. The first column is labelled "A" and the first row is labelled "1". After entering the series, type "Average" in column A, row 15.

Move the cursor to column B, row 15. Click the EDIT FORMULA button at the top of the window ("=").

Direct students to choose the AVERAGE function. Provide students with a FOCUS FOR MEDIA INTERACTION by asking students to identify the location of the data to be analyzed. (Students should state the data is located in column A from rows 1 through 11. Explain to students that EXCEL uses the colon to establish the start and stop points as it reads data in a series.)

Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to verify that the average they calculated earlier is the same as the average calculated by the software. Point out to students that the data values appears parenthetically next to the Number1 field as a way of checking while constructing the formula to be used.

Ask students to graph the data using the "Chart Wizard" by clicking on the

Highlight the data points from A1 to A11 (A1:A11) and choose line graph. Change the way the graph looks by formatting the PLOT AREA and the CHART AREA using pre-set fill effects (example shown here is Desert) or colors.

Provide students with a FOCUS FOR MEDIA INTERACTION by asking students to perform the following analyses on the same set of data in column B: remind students to indicate the range of data points between A1 and A11 as A1:A11
• SUM-Find the sum of the values in the set.
• COUNT-Count the number of data points that are greater than 80 (>80).
• MEDIAN-Find the middle value within the set. (The MEDIAN function can be accessed in the drop down menu under "More functions".)
• MAX-Identify the largest number in the set.
Culminating Activity

 Advanced strategies for data analysis-Sorting and Table Organization Lead students in a discussion in challenges that might arise when trying to analyze data sets that contain more than eleven data points. (Student responses will vary but should indicate the challenges of data entry-possibilities of human error are introduced the more a person manipulates values.)

Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to review the Novell test data document being projected on overhead; the data was collected from the Novell training website. Students could log onto the site if an overhead of the document is not available. Ask the students to identify challenges that may be expected in the analysis of this data. (Student responses will vary but should indicate that if test data is to be analyzed, general organization is the most important characteristic of the information. Since most people want to compare test data by score, a better way to analyze the information might be to sort the data into

Copy the data from the table into EXCEL. Use the Crib sheet document to find keyboard shortcuts to speed up the process of copying the information (From the website, click on Ctl + A or highlight the table text using the mouse to copy and paste into a new document. Make sure to also copy the URL for proper citation and later reference). Paste the data into a new EXCEL worksheet. (Teachers can use the prepared Culminating Activity file - data is pasted into an EXCEL file).

In EXCEL, highlight the first eight columns of data (Column A through H) by dragging the mouse over the column labels at the top of the screen while holding down the left mouse button. Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to identify the cut score and time for the "CNI Service and Support" test. (Cut score: 686; time: 90 minutes)

Provide students with a FOCUS FOR MEDIA INTERACTION by asking them to consider how the information could be organized to give information about the "type of test" being administered. (Student responses will vary but make sure to point out that there are two types of test-adaptive (A) and traditional (P). Indicate to students that while raw data is similar to what is shown here is easier to process if the key information is aggregated or lumped together to form fewer points of distinction.)

The ideal method used to showcase data is called a pivot table. Log onto the census data websites (http://www.census.gov/epcd/mwb97/us/us.html) or (http://factfinder.census.gov/servlet/QTTable?
_bm=y&-geo_id=86000US10001&-qr_name
=DEC_2000_SF1_U_QTP2&-ds_nam
e=DEC_2000_SF1_U&-_lang=en&-_sse=on
) to identify the characteristics of good data organization. Point out to students that both tables are organized to summarize large sets of data by categories of interest. Advanced students can use the "pivot wizard" to create various data displays. Students whose skills are still emerging, can practice analysis functions conducted in earlier parts of the lesson.

Cross-Curricular Extensions

Create an electronic financial journal. Use EXCEL software to create a spreadsheet of spending data for yourself. Analyze your average spending for a week. Identify the most amount of money you spent for a lunch meal or the least amount of money you spent on entertainment expenses in a month.

Log onto the Academic Techology website of Drew University to practice basic chart building or progress toward more advanced techniques useful for presentations.