Using Microsoft Excel

Information presented here focuses on uses of Excel that may be helpful in completing assignments. More detailed information on Excel is available through Element K, an on-line tool that can be used as a reference and source of comprehensive, self-paced tutorials.

How to get started:

  1. Call the HelpDesk and request the enrollment key for "Package 1" of Element K
  2. See instructions at http://elementk.wlu.edu/enroll.html on how to enroll and access Element K material.

Frequently Asked Questions about Excel

 

Excel's Statistical Features

Excel's statistical analysis tools:

Microsoft Excel provides a set of data analysis tools - called the Analysis ToolPak - that you can use for statistical analysis: descriptive statistics, regression, variance (anova), correlation, covariance, F-Test, t-Test, etc. You provide the data and parameters; the tool uses the appropriate statistical macro functions and then displays the results in an output table. Some tools generate charts in addition to output tables.

To use these tools, you must be familiar with statistics and the analysis tools must have been installed on your computer. To see if the analysis tools are installed, open the Tools menu and look for Data Analysis on the menu. If the Data Analysis command is not on the Tools menu, you will need to install the Analysis ToolPak.

Installing the Analysis ToolPak:

If you are using your own computer for your analysis, you may install the Analysis ToolPak by clicking on the Tools menu and selecting Add-Ins. If the Analysis ToolPak is not listed in the Add-Ins dialog box, click Browse and locate the drive, folder name, and file name for the Analysis ToolPak add-in, Analys32.xll - this is usually located in the Microsoft Office\Office\Library\Analysis folder - or run the Setup program if it isn't installed (you will probably need your Microsoft Office CD-ROM for this).

Select the Analysis ToolPak check box and click OK.

Using the Analysis ToolPak:

Arrange the data you want to analyze in columns or rows on your worksheet. This is your input range. On the Tools menu, click Data Analysis. In the Analysis Tools box, click the tool you want to use. Enter the input range and the output range, and then select the options you want.

 

Importing to Excel

Data is available on the Internet in many different formats, and some formats are easier to work with than others. Some files will be available in Excel or Lotus format - simply open these files in Excel to view them. However, if the online data is in a plain text or HTML format, then you will need to use Excel's Import Wizard. Here is an overview of the kinds of files that will need to be imported by Excel before they can be viewed:

  1. HTML Tables -- If the data is presented in a table on a Web page, save the whole page as an HTML file and open this file in Excel. Tables are not always obvious, since they may not have borders. In the table below, the text looks very much like the "fixed width text" in the example below. Checking the source code will help determine the format of the data. There are instructions if you need help saving a file in Netscape or Internet Explorer.
    Example:
      Fiscal Year End
    12/1997
    12/1998
      Assets ($ mil)    
      Cash & Equivalent
    3.5
    1.9
      Receivables
    546.3
    452.4
    source code for this data
  2. Delimited Text -- these are plain text files that are intended to be imported into spreadsheets or databases. The columns are separated by a character (usually a tab or comma). Using the Import Wizard, Excel can split the data into rows and columns. You should save this file as plain text and use Excel's Import Wizard.
    Example:
      Fiscal Year End","12/1997","12/1998"
      "Assets ($ mil)",,
      "Cash & Equivalent",3.5,1.9
      "Receivables",546.3,452.4
  3. Fixed-Width Text -- These files use spaces to force the plain text to line up into columns. Excel can also import this data if you save the file as plain text. Here is an example of fixed width text:
  Fiscal Year End     12/1997    12/1998

  Assets ($ mil)
  Cash & Equivalent       3.5        1.9
  Receivables           546.3      452.4
                    
source code for this data

 

Using Excel's Import Wizard

Excel's Import Wizard automatically analyzes files you attempt to open to assist you in your attempt to open files that are not native Excel files. Learning to use the Import Wizard makes life much easier than converting data manually.

First, you will need to open the text file in Excel:

  • Start Excel
  • Click the open icon Open Button or select "Open" from the File menu
  • In the Open dialog box, change the Files of type field to Text Files
  • Locate and select the data file
 

File Type and Name

  • Click on the Open button.

The "Text Import Wizard" window should open automatically.

Import Wizard Step 1

The Import Wizard should identify whether the file is delimited or fixed width, but make sure it is correct. Fixed width files are aligned in columns manually by using spaces in a fixed font, as in the example above. Check the setting and be sure the file type has been properly identified.

Files that begin with a large amount of text will often confuse the Wizard, and it may be easier to import the data separately first by setting the "Start import row" field to the row that begins the actual data. Later you can open the text file in Word and cut and paste the text into your spread sheet as needed.

Click the Next button to proceed to step 2 of the Import Wizard. The Import Wizard will display different versions of Steps 2 and 3, depending on which file type you chose. Please pick the correct instructions below:


Importing Fixed-Width Files

Import Wizard Step 2

  • Step 2 of the Wizard allows you to set column breaks.
  • Column breaks are displayed as solid lines with an arrow head pointing to the ruler at the top of the Data Preview section of the window. Use the scroll bars to examine the data and be sure the column breaks are located where they belong. If necessary:
    • Drag the breaks where they belong
    • Insert new column breaks by clicking at the desired locations
    • Delete unwanted column breaks by double clicking on the unwanted column breaks
  • Click on the Next button when column breaks are set correctly.

Import Wizard Step 3

  • Step 3 of the Wizard allows you to set the data format of each column or skip columns in the importing process. This is generally unnecessary and this step can usually be skipped if the data is not going to be used in a database. Any formatting required can be done later if it is required. If you have data that requires special attention, such as dates, or if you wish skip columns, use the arrow keys, or they tab key, to move from one column to the next to change the "Column data format." Use the General setting for text or numerical data.
  • Click the Finish button when you are finished setting the format and identifying columns that are not needed.

The data should then open in a spreadsheet, but some clean up work and formatting may still be required.

You may also want to see the instructions for importing delimited files.


Importing Delimited Files

  • Step 2 of the Wizard allows you to set column breaks.

Delimited File Import 2

  • Column breaks are displayed as solid lines in the Data Preview section of the window. Use the scroll bars to examine the data and be sure the column breaks are located where they belong. If the column breaks are not correct, it is usually because the selected delimiter is not correct. If this does not fix the problem, then you probably don't have a delimited file and you should back up to step on and try importing the file as a fixed-width file.
  • Click on the Next button when column breaks are correct to advance to step 3.

Delimited File Import 3

  • Step 3 of the Wizard allows you to set the data format of each column or to skip columns in the importing process. This step is generally unnecessary and can usually be skipped if the data is not going to be used later in a database. You will need to format the spreadsheet later anyway, and the data format can be altered at that time if required. If you have data that requires special attention, such as dates, or if you wish skip columns, use the arrow keys, or they tab key, to move from column to column and change the "Column data format" as needed. Use the General setting for text and numerical data.
  • Click the Finish button when you are finished setting the format and identifying columns that are not needed.

The data should then open in a spreadsheet, but some clean up work and formatting may still be required.

You may also want to see the instructions for importing fixed-width files.

 

© Copyright 2007 Washington & Lee University
This website is provided by the Leyburn Library and University Computing
Website design and implementation by Jack Jeong , Class of 2007