Introduction to Spreadsheets



Introduction to Spreadsheets

(Excel)

Objective:

The objective of this document is to serve as a basic guide to the use of Excel for the manipulation, analysis, and presentation of data. For some users, much of this material will be old hat and for others it may be too advanced. My hope is that this document will be useful as a brief introduction to Excel and also provide examples of some useful functions that Excel contains. This guide will not likely serve as a stand-alone guide but should be used in conjunction with the help that is available in Excel. To facilitate the use of the help, I have tried to use the terminology and spelling used by Excel.

Excel: The Basics

Organization:

To minimize confusion, you should be aware of the levels of organization within Excel. I will introduce you to the levels of organization by starting with the smallest level and working up. The first level would be the cell. When you enter data into Excel, you place numbers or letters within a box called a cell. There are many cells located on each sheet (also called a worksheet). The best way to think of a sheet is simply as a page within a larger workbook (or simply book). When you save a file in excel, you are saving a workbook.

Entering Data:

One of the most common ways of entering data into Excel is to use the keyboard to enter the data, by cells, into a sheet. To enter data in this way, select the cell where you want to place the data and type in your data. To move to another cell, hit enter (to go to the cell below), use the arrow keys (to move any direction), or select another cell with the mouse. If you make a mistake in your data entry, select the cell and reenter the correct data.

Importing Data:

Excel can also import data from other formats. This is especially handy if you want to use Excel to work with exciting data sets. One of the most common formats that you will encounter will be text files (also called ascii files). To import a text file into excel, select “file”, then “open”, then navigate to the location of the file and select the text file. If the file you wish to open is not displayed, make sure that you have the appropriate file type selected in the “open” dialog box. If you are not sure of the file type, select “all files”. When Excel tries to open the file, it will detect that it is not an Excel file and will open the “Text Import Wizard”. One of the first things you will need to do is specify whether the file is delimited or is simply fixed-width (Step 1). Delimited data has something “delimiting” the columns of data. For example, many times data sets will have commas between the columns of data. If there are delimiters, select “delimited”, otherwise select “fixed width”. You also have the option to start importing data at row 1 or at another row of your choosing. When you import your data, you want to make sure that you import only the data and not a title or other text that is not in the column format. Select a row other than row 1 if you have a title. Select next to move to step 2 of the Import Wizard. Step 2 indicates where the Import Wizard will put column breaks. You will want to check to see that all of the columns of data are completely separated by column breaks. If not, follow the instructions to edit the breaks. Step 3 controls the column data format. This is where you tell Excel what type of data is in each column. In most cases, you will want to stay with the default format, which is “general”. This causes Excel to import numbers as numbers and text as text. One helpful tip is that you can select columns that you DO NOT want to be imported into the final worksheet during this step. This is especially helpful if you have a number of columns of data that you do not need.

Manipulating Data in Spreadsheets

One of the strengths of using spreadsheets is the ability to easily manipulate data. Manipulating data can take many forms, from simply rearranging data to performing calculations on many rows or columns of numbers.

Sort:

Many times a user will want to sort a sheet of data by values in one or more columns. For example, you may want to order a set of data based on dates or scores on exams, etc. To sort a set of data, select “data” and “sort”. A word of caution, be sure you know what you are sorting! If you select only one column (have only one column highlighted) only that column will be sorted. This will result in that column no longer being aligned with the other columns in the data set. Be careful, there is no good way to realign the data other then the undo command.

Copy and Paste:

Copy and paste in Excel is similar to the copy and paste in a word processor. These commands are helpful in moving data around so that it is organized in a useful way. Paste special is a very useful command in Excel. When you copy or cut a selection you have the option to paste or paste special (under the edit command on the toolbar). The difference is that when you paste special, you can alter what is being pasted. For example, you can transpose a column or row of data (turn a column into a row and vice versa) and paste the results (values) of a formula without the associated formula.

Functions:

Functions are predefined formulas that perform calculations. For example, the SUM function adds values of a range of cells and the AVERAGE function returns the average (mean) of a range of cells.

Formulas:

Formulas are equations that perform calculations on values in your worksheet. You can create a formula that performs arithmetic operations, such as adding numbers together. Or you can create a formula that returns the result of a complex equation. The following table contains some of the arithmetic operators used by Excel.

|+ (plus sign) |Addition |3+3 |

|– (minus sign) |Subtraction |3–1 |

| |Negation |–1 |

|* (asterisk) |Multiplication |3*3 |

|/ (forward slash) |Division |3/3 |

|% (percent sign) |Percent |20% |

|^ (caret) |Exponentiation |3^2 (the same as 3*3) |

To calculate a formula, select the cell in which you want the results of the calculation displayed. Select the “=” sign from the top of the screen, and enter the appropriate equation.

Example: Add a row of numbers and determine 20% of the total value. Perform this operation on the entire sheet of data.

In this case, you can sum the contents of each cell of row “a” and multiply that value by 0.20. The formula would look like this: =(A1+B1+C1+D1+E1)*0.20 where A1 represents the value of row “1” and column “a”, B1 represents the value of row “1” and column “b”, etc. This seems fairly time consuming but the benefit is that you can copy and paste the equation and it will calculate the same equation for each row of data in the table. For example, the equation in row 3 would be: =(A3+B3+C3+D3+E3)*0.2. In this way, you can perform complex calculations on very large tables (sheets) of data in a very short time. Additionally, if the equation is correct for the first row, it will be correct for all of the rows (reduces the chance for human error).

NOTE: You can also embed functions in your formulas, which will reduce the amount of time it takes to write equations.

Basic Statistics in Excel

Excel can calculate many types of commonly used statistics (mean, standard error, standard deviation, variance) and run some statistical tests (regression, analysis of variance (ANOVA), t-tests, correlations). To access these statistics you must have the “Analysis ToolPak” installed. Unfortunately, this is not installed during a standard installation on a pc (it is on a Mac). To check to see if the “ToolPak” is installed, go to the “tool” tab and look for “Data Analysis” in the drop-down menu. If it is not there, the “ToolPak” needs to be installed.

Installing the “Analysis ToolPak”:

Under the “tool” tab, select “Add-ins”. Select “Analysis ToolPak” in the dialog box and select “ok”. You may or may not be prompted for the Microsoft Office installation disk, depending on how the software was initially installed. Follow directions and “Data Analysis” will be available when you select the “tool” tab.

Using Data Analysis:

Select “Data Analysis” from the “tool” tab. You will see a dialog box that offers a number of statistical tests. Select the appropriate statistics and follow the instructions. For example, if you want descriptive statistics (such as mean, median, mode, variance, standard error) for a column of data, select descriptive statistics. You will be prompted to select an input range (cells you want the statistics calculated on) and you can select an output range (cells where you want the answers to go). If you fail to select an output range it will put the results on a new worksheet by default. You will also need to check the box for summary statistics (see pg 7 – “Instructions for using Excel to calculate descriptive statistics…” for additional help).

|Graphing Data |

| |

|Excel can be used to create different types of graphs including histograms and scatter plots. |

| |

|To create a graph, select the Chart Wizard icon or select chart from the Insert menu. |

| |

|[pic] |

| |

|Select the type of graph (chart type) from the Chart Wizard then click on “Next” |

| |

|[pic] |

|Select the data range (see #4 pg 7) and click on “Next”. |

|Label axes appropriatey and click on “Next”. |

|Continue through the Chart Wizard and click “Finish” |

|The graph will appear as a window in the worksheet. |

| |

|The graph can be copied and pasted into Word or printed from Excel. Note: Many parts of the graph can be edited by simply activating |

|the component by double-clicking. Give it a try; double click on the background, an axis label, or a bar in the bar graph. |

| |

|These instructions are designed to help you get started using Excel. You will need to spend some time playing with Excel to get your |

|graphs to look the way you want. |

Instructions for using Excel to calculate descriptive statistics using “Data Analysis”

Descriptive statistics:

1. Enter data in columns

2. Access the data analysis tool by selecting “Data Analysis” from the Tool menu

Excel should look like this:

[pic]

3. Calculate descriptive statistics (mean, range, variance, standard deviation and more) select descriptive statistics from the analysis tools

|[pic] |

|The input range should indicate the cells (column) that contains the data you wish to use for the calculations. In the above example, the statistics would be |

|calculated for the unmated male data in column A. You can indicate what data to use by simply selecting this button and then use the mouse to select the data. |

|Indicate where you would like to output (summary statistics) to be placed in the output range. This is done the same way that you selected the input range, however, |

|you only want to select one cell (which will be the upper left cell of the output). |

|Read the data in the output (see below). |

|[pic] |

-----------------------

Version 1, July, 2002

Raymond S. Matlack

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download