What you will do: ü
Gather real-world data from different
sources including text files, databases, and the Web ü
Use PivotTables and PivotCharts to explore
questions using complex data ü
Save your analysis as an interactive Web
page
Analyzing Data with Excel 2002
In many learning situations, students
are presented with data in the form of published studies, textbook examples,
and media presentations. Although students may absorb some information from
reading someone’s analysis of data, they are likely to become even more engaged
in the process of collecting and analyzing data on their own. Microsoftâ
Excel 2002, along with the other programs in Microsoft Office XP, facilitates
active, collaborative learning and critical thinking by providing tools that
empower students to collect information, look at it in different ways, and
share it with others.
Everyone knows that Excel is a “spreadsheet” software program that is used to record data; however, Excel is also a tool for analyzing that data. Perhaps you are already using a spreadsheet to keep track of your students’ grades. You might even be analyzing patterns in those grades. Excel 2002 makes it possible to perform the same kinds of analyses on data from all kinds of sources, including text files, databases, and the Web. Excel’s PivotTable features enable you to analyze large amounts of complex data in a flexible manner and summarize your findings in a graphical format.
You will follow along with a group of social-science students and their instructor as they collect and analyze data about students planning to major in math and science. They will import data from a text file, query a Microsoft Access 2002 database, and gather data from the Web. Next, they will analyze the data by using PivotTablesâ and looking at it in PivotChartsâ. Finally, they will share their analysis and continue their research by using interactive Web pages. Sample data files are provided for you to use.
If
you are new to Excel 2002, you may want to read the documentation that came
with your Office XP software. Additionally, all copies of Office XP purchased
through a retail store or volume licensing includes the Microsoft Press Step by
Step Interactive CD. Step by Step Interactive provides an excellent
introduction to the basic functions of Excel 2002. If Excel was provided to you
through a volume purchase, discuss with your administrator how to access the
Step by Step Interactive program.
To install Excel 2002, you will need:
· Personal or multimedia computer with a Pentium or higher processor (Pentium II recommended)
· 32-megabytes (MB) of RAM (minimum)
· 295-MB hard disk
· CD-ROM or DVD-ROM drive
· VGA or higher-resolution video adapter (Super VGA, 256-color recommended)
· Microsoft mouse, Microsoft IntelliMouse®, or compatible pointing device
Some activities also require:
· Network connection and modem
· Access to the Internet through a service provider
If you have not already done so, make sure that you download the following sample data files that accompany this tutorial:
· analyzing_data.xls
· major_database.mdb
· text_file.txt
These sample files can be found on the Microsoft in Education Web site at the following URL (type analyzing data in the Search field):
http://www.microsoft.com/education/
The directions given in this tutorial use the following conventions:
· To “click,” use the mouse to point to an area on the screen and press the left (primary) mouse button. To “double-click,” press the left mouse button twice, quickly. To “right-click,” press the right (secondary) mouse button. (You can also customize the mouse so that the right button is primary and the left button is secondary.)
·
You can also select a different Office Assistant and set
it to operate so that it fits your personality and the way you work. For
example, if you prefer to use the keyboard, you can have the Office
Assistant display tips on shortcut keys. Because all Office programs share
the Office Assistant, any options you change will apply to the Office
Assistant in your other Office programs as well. To customize or turn off
the Office Assistant, click it and then click Options.
Items such as menus or buttons that you click or select to carry
out a command are bold.
For example, "click Print"
means you should click the Print
command on the File
menu.
· Information that you are to type exactly is shown in bold.
For assistance with Excel 2002, you can use the Office Assistant. To open the Office Assistant, on the Help menu, click Show the Office Assistant. You can then type your question in the text box provided, and then click Search.
Column heading Formula bar Name box
Before using Excel 2002, you will want to be familiar with its
features. The following illustration shows a workbook that contains data.
Active cell Select all Task Pane Row Headings Menu bar
Worksheet
tab Status bar
Words to know: Cell
reference. Identifies a cell or a range of cells on a worksheet and
tells Microsoft Excel where to look for the values or data you want to use
in a formula. Function.
A predefined calculation that may be included in a cell and does a specific
manipulation of data. Operator.
A symbol or other character that specifies the type of calculation you want
to perform on the elements of a formula. PivotTable.
A special type of worksheet used to summarize and analyze data. Query.
To search a database for records that meet specific criteria. Range.
Two or more cells on a sheet. The cells in a range can be adjacent or
nonadjacent. Result
set. Data that is returned by a query, usually a subset of the
original database. Source
data. The data used to create a PivotTable, or the data imported
into Excel through a database or Web query. Value.
Converts a text string that represents a number into a number.
The basic Excel document is a worksheet. Several
worksheets can be saved together as a workbook. When you start Excel, a blank
worksheet opens. You can then complete the following tasks:
· Add data manually. Type text and numbers in cells.
· Create formulas. Calculate results from the data by applying formulas to cells. Formulas can be up to 1,024 characters and can contain operators, cell references, values, text, and functions.
· Navigate. To change the active cell, use the arrow and PAGE UP and PAGE DOWN keys, click a new cell, or drag the scroll bars.
· Select cells. Click a cell and drag the mouse pointer to select a range of cells. To select nonadjacent cells, hold down the CTRL key and then click on the cells you want.
· Add a new worksheet to form a workbook. Click Insert and then click Worksheet, or right-click the tab of the active worksheet to open the context menu, click Insert, and then click Worksheet.
· Rename the worksheet and color the name tabs. Double-click the Sheet1 tab to select it, and then type the new name, or right click Sheet 1 and select tab color to color code the tabs for easier navigation between worksheets.
· Format the worksheet. Change or apply font styles, colors, patterns, borders, and cell alignment to make your worksheets more attractive and easier to read. Select the cells that you want to format, and on the Format menu, click Cells.
· Embed charts and pictures. To create a new chart within Excel, on the Insert menu, click Chart. To insert another file, such as clip art or a scanned image, on the Insert menu, point to Picture, and then click a command.
Before getting started with this tutorial, it would be helpful for you to spend some time exploring Excel. One of the easiest ways to become familiar with a new program is to view the menus, see what some of the buttons do, and explore Help.
To view Help
1. On the Start menu, point to Programs, and then click Microsoft Excel.
2. On the Help menu, click Microsoft Excel Help.
3. Type the question How do I save a workbook? in the text box provided, and then click Search.
4. You can view the topic that is displayed in the right pane, or select one of the additional topics for more information.
Now that you are familiar with some of the basic features of Excel 2002, you are ready to find out how you can use Excel to facilitate engaging, collaborative learning activities with students.
Collecting real-world data is an important part of making
learning relevant and engaging. To learn how students can collect their own
data through Web surveys, see the Microsoft in Education tutorial Gathering
Information Online.
In a social science class, students were asked to pick a
current issue of interest and then compare the attitudes of their peers with
those reported in a national poll. The students were then required to share
their research with others. One group chose to look at students planning to
major in math or science. With Excel 2002, the students are able to gather data
from several locations, analyze it to answer questions of interest to them, and
then share what they discover.
There are many sources of public domain data on the Web.
Try the United States Census Bureau at http://www.census.gov/
and the National Science Foundation at hppt://www.nsf.gov/
The
social science students have found several data pools in their research that
they want to bring together and analyze. Before they can analyze data, they
have to get it into a format Excel can use. This usually means typing or
importing data into a worksheet. There are several ways to do this, including:
· Type data manually
· Generate data by using forms or macros
· Import data from another file, such as a text file
· Import data from a database
· Import data from the Web
You can also import text files as Fixed Width files, in
which the text fields are aligned in columns with spaces between each
field.
Importing data from text files
The social science students found some statistics about student ethnicity and the selection of science majors that they thought would be important for their study. However, the information wasn’t in an Excel file. Excel can’t read every type of file that exists, but most programs will allow you to save data as a delimited text file that can then be imported into Excel. Delimited means that each section of data is separated by a special character, typically a tab, comma, quote, or space. The students saved the file as a text file (text_file.txt) and are now ready to import it into Excel.
When working with text files, use a non-proportional font
such as Courier New so that your fields of text line up.
To import a delimited text file
1. On the File menu, click Open.
2. Select the folder where you downloaded the sample data files, and double-click analyzing_data.xls.
3. Click the Sheet1 tab to select it.
4. Click Data, point to Import External Data, and then click Import Data.
5.
If the file you want to import contains tables, convert
the tables to text before saving the file as a text file.
Switch to the folder from which you opened the workbook, and
change the Files of type
to Text Files. Select
the text_file.txt file(located in the sample_data folder that you downloaded
with this tutorial), and then click Open.
The Text Import Wizard opens.
6. Verify that Delimited is selected, and then click Next.
7. Verify that Tab is selected, and then click Next.
8. Verify that General is selected, and then click Finish.
9. Verify that Existing worksheet is selected, and then click OK.
10. On the File menu, click Save.
Later, if the data in the text file changes, you can update your Excel worksheet without importing the text file again. To do so, on the Data menu, click Refresh Data. Select the text file, and then click Import. Excel automatically updates the worksheet with any new data that is in the text file.
Importing data from a database
The social science students also conducted their own survey of college freshman at their school. They used a Web-based form created in Microsoft FrontPage® 2002 and arranged for students from several English 102 classes to fill out the form online. They collected the results of their survey in an Access 2002 database.
In order to analyze and compare their survey with the national statistics, the group needs to import some of the survey data into Excel. They can query the database to isolate a subset of the data collected in the survey. Excel has a Query Wizard that makes it easy to retrieve data stored in an external database.
To query a database
1.
Microsoft Query may need to be installed on your system.
To do this you will need Microsoft Office XP software, or ask your network
administrator.
In the analyzing_data.xls
spreadsheet click the Sheet2 tab to select it.
2. On the standard toolbar, click Data, point to Import External Data, and then click New Database Query.
3. On the Databases tab, click MS Access Database, click Use the Query Wizard to create/edit queries, and then click OK.
4.
Locate the major_database.mdb database file
(located in the sample_data folder that you downloaded with this tutorial) in
the Select
Database dialog box, and then click OK.
The Query Wizard opens.
The Choose
Columns Query Wizard prompts you to select the columns that you want to
include in your query. The list on the left shows the tables and columns
available from the database that you queried. The list on the right contains
the columns that you want to include in your Excel worksheet.
5. From the list on the left, click Declared major by gender and ethnicity, and then click the right arrow. The columns from the database table are now listed on the right.
6.
If you want to remove any columns from the list,
select the heading and click the left arrow. For this query, you probably don’t
have any use for the arbitrary ID number that was assigned to each record.
Click Student ID #, and
then click the left arrow.
You can also preview the data in a column. Select age from the list on the right, and then click Preview Now. You will see the
numbers 17 through 21 listed in the Preview Data list. These are the possible
ages of the students who were surveyed. Click Next to continue.
If you want more information about the Filter Data dialog box,
click the help button in the lower left corner of the dialog box.
7. The Filter Data Query Wizard allows you to filter the data that you are querying directly from the database. For example, if you wanted to include the records only of minority students, you could do so here. The social science students want to see all the data in Excel, so they chose not to filter data at this point. Click Next.
8. The Sort Order Query Wizard allows you to sort the data that you are querying from the database by ascending or descending order. The students might want to sort their data later in Excel, but choose not to do so here. Click Next to continue.
9. Click Return Data to Microsoft Excel, and then click Finish.
10. Verify that Existing worksheet is selected, and then click OK. Notice that you can also create a PivotTable Report directly from a database query. PivotTables are discussed later in this tutorial. The data from the database opens in Sheet2. The External Data toolbar also appears.
When collecting information from a website that changes
frequently, such as weather or stock market information, create a
refreshable query so that the spreadsheet will always have the most recent
data.
Importing data from the Web
With Excel 2002, you can use data that you find on the Web. Conducting a Web query is very similar to conducting a database query. Excel 2002 remembers where the data came from, so that you can refresh your query if the data changes. The social science students want to compare their data with historical statistics on college graduates from the US Census Bureau. Census Bureau reports are located on the Web at http://www.census.gov/population/.
To create a Web query
1. In the analyzing_data.xls spreadsheet, click the Sheet3 tab to select it.
2. Click Data, point to Import External Data, and then click New Web Query.
3. In the Address box, type the address of the Web page. In this case, type http://www.census.gov/population/socdemo/school/tabA-6.txt
4. Select the yellow boxes next to the portion of the page you want to reference in your query, and then click Import.
5. Verify that Existing worksheet is selected, and then click OK. The census data is imported into the Excel worksheet.
After
you have gathered your data into Excel, you can perform many kinds of analyses,
from simple filtering to advanced “what-if” analysis. By using the powerful
PivotTable feature of Excel 2002, you can accomplish this without creating a
single formula.
The social science group has brought data together from several sources into the Excel workbook analyzing_data.xls. They are now ready to find out what their data means. They will first perform a simple analysis by filtering a list. Next they will do a more complex analysis of the data by using PivotTables and looking at the results of the analysis in PivotCharts.
Filtering a list is a method of analysis in which you hide rows in the list that do not meet some specified criteria. For example, you could filter a grade-book list to show only those students who did not declare a major. You could then perform a mail merge with the results and mail notices only to those students.
The social science students hypothesize that participation rates of females in science and engineering in 1998 is greater than participation rates of minorities. To explore this hypothesis, students used the following procedure and the filter_list worksheet in the analyzing_data.xls sample file. This worksheet contains national data about the numbers of minority students intending to major in science and engineering. It is similar to the Excel worksheet created from importing the text file earlier in this tutorial.
To filter a list by using AutoFilter
1. In the analyzing_data.xls spreadsheet, click the filter_list tab to select it.
2. Click cell A3 in the worksheet to activate the cell.
3. Click Data, point to Filter, and then select AutoFilter. Drop-down arrows appear next to the field names in the header row.
4. You can filter the list by values in a single column or in multiple columns. For example, click the drop-down arrow on the 1998 field and then click (Top 10). The Top 10 AutoFilter dialog box opens.
5. You can change the parameters to return any number of the top or bottom items or percentages. For this exercise, leave the filter parameters at Top 10 items.
6. Click OK. All rows are hidden except the ten with the highest number of freshmen intending to major in science and engineering in 1998. To filter the list further, you can select an additional year, or you can click the drop-down arrow for the 1998 field and click (All) to remove filtering of the column.
7. To turn off AutoFiltering and return to the complete list, click Data, point to Filter, and then click AutoFilter to clear the check box.
8. You can start filtering the list from any row. If you want to see results only for minorities intending to major in science and engineering, click row 21, click Data, point to Filter, and then click AutoFilter. The drop-down arrows appear at line 21 instead of at the top of the list.