State University of New York at Morrisville

Dr. Walid H. Shayya
School of Agriculture, Business, and Technology



Using MS Excel 2003 to Analyze Data: An Introductory Tutorial

Developed By

Dr. Walid H. Shayya


Table of Contents

Introduction
Overview
Creating a Worksheet
Entering Formulas and Functions
Formatting Your Worksheet
Embedding a Chart
Getting Help
Concluding Remarks

 


Introduction

A spreadsheet is the computer equivalent of a paper ledger sheet.  It consists of a grid made of columns and rows.  It is an environment that can make number manipulations easy and straightforward.  Microsoft (MS) Excel is a spreadsheet application that is part of Microsoft Office. It enables the calculation and display of complex mathematical formulas (functions) with a facility for extensive formatting. Functions are predefined calculations that may be included in any given Excel cell to perform specific manipulation of data. Using MS Excel, data could be imported from a variety of sources. Analyzing data is a very important skill of any professional, especially those who work in the fields of agriculture and natural resources where data in its raw collected state have very little use without some sort of processing. As a student and a professional, MS Excel can assist you in the analysis of data.  This tutorial focuses on introducing the basic features of MS Excel 2003 to analyze general data.  It will cover the basic steps of creating a spreadsheet, using formulas and basic formatting, and creating charts.

Top of the Document


Overview

Before you start using this MS Excel tutorial, it is very beneficial for you to become familiar with the basic features of MS Excel workbooks and worksheets.  In MS Excel, a workbook is the file in which you work and store your data.  Because each workbook can contain many sheets, you can organize various kinds of related information in a single file.  Worksheets are used to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets.  When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet.  The names of the worksheets appear on tabs at the bottom of the workbook window.  To move from one sheet to another, click the sheet tabs.

As you can see, we have already used the terms "spreadsheet" and "worksheet".  Although people generally use the two terms interchangeably, the term worksheet refers to the row-and-column matrix sheet on which you work upon while the term spreadsheet refers to this type of computer application.

As mentioned earlier, the workbook can contain worksheets and chart sheets.  The following illustration shows a new worksheet in an Excel workbook:

Excel Window

As you can see in the illustration, worksheets are made up of columns and rows.   In a worksheet the "cell" is defined as the space where a specified row and column intersect. Each cell is assigned a name according to its "column" letter and "row" number.  In each cell there may be the following types of data: text (labels), number data (constants), and formulas (mathematical equations that do all the work).

Spreadsheets have many mathematical functions built into them.  The most basic operations are the standard multiplication (*), division (/), addition (+), and subtraction(-).  There is an order of operations when you are evaluating a formula.  Formulas are evaluated from left to right.  Expressions enclosed in parentheses are evaluated first followed by exponents, multiplication and division (same level), and addition and subtraction (same level).  MS Excel has many more operators and mathematical operations may also be performed using "functions" (e.g., the "SUM" function).  A brief description of the latter and relevant common functions for summarizing data can be found here.  Advanced MS Excel functions on correlation and regression analysis are also available. 

Selecting cells is a very important concept of a spreadsheet. We need to know how to reference the data in other parts of the spreadsheet.  When entering your selection you may use the keyboard or the mouse.  We can select several cells together by specifying a starting and a stopping cell.  This will select "all" the cells within this specified block of cells.

Depending on the task you want to perform in MS Excel, you can use either relative cell references (which are references to cells relative to the position of the formula) or absolute references (which are cell references that always refer to cells in a specific location).  If a dollar sign precedes the letter and/or number, such as $A$1, the column and/or row reference is absolute.  Relative references automatically adjust when you copy the cells while absolute references do not.

Some of the basic functions of MS Excel will be demonstrated in this tutorial where a worksheet that includes student information and grades are first created and then manipulated.  Ultimately, the new worksheet is formatted followed by producing a graphical display of student data.

Top of the Document


Creating a Worksheet

This section describes how to create a worksheet and modify it to suit your needs. You will then use formulas and formatting as well as embed a chart.  To insert a new worksheet, follow the following steps:

  1. Start MS Excel.  The application starts with several worksheets (usually 3).
  2. To insert a new worksheet, click "Worksheet" from the "Insert" menu.
  3. Right-click on the tab for the new worksheet and select "Rename" from the shortcut menu (alternatively, you could double-click the tab to accomplish the same).
  4. Type in "Grade Book" and press "Enter" to save the change.
  5. Key in the text and data into the worksheet as shown in the picture below.  You will notice that your data is displayed in two areas while you are typing it in a cell.  Text is displayed in the active cell within the workbook and it is also displayed in the "Formula Bar".  The "Formula Bar" is activated as soon as you begin typing in a cell.  At the far left of the "Formula Bar" is the reference section, which will show the reference of the active cell.
  6. To change the width of any column, position the pointer between the column headings for two columns (the pointer should change shape to show a double-headed arrow as you position the pointer between the two column headings). When the pointer changes shape, you can change the width of the column by pressing the left mouse button and dragging to the right or left.  Press the left mouse button and drag to the right until the width of the column fits the text.

    Excel Figure 1
     
  7. Once you have all the data in, save your work by clicking "Save" from the "File" menu.  Alternatively, you could click on the "Save" button on the toolbar.  Given that this the first time you are attempting to save a new workbook, MS Excel will display the "Save As" dialog and will prompt you to enter the file name for your new workbook.  You also have many other choices including the choice of file folder where your workbook ought to be saved.  Give your file a descriptive name before pressing "Save".

Top of the Document


Entering Formulas and Functions

All formulas in MS Excel must begin with an equal sign (=).  When a formula is entered into a cell, the formula itself is displayed in the "Formula Bar" when that cell is highlighted, and the result of the formula is displayed in the actual cell.  The following steps demonstrate how to enter formulas and functions in a cell:

  1. In cell "H3" type in "Total Score".
  2. You will now type in the formula for calculating the total score for the student.  In cell "H4" type in "=(D4+E4+F4+G4)/4" and press "Enter".

    Excel Figure 2

  3. Position your mouse cursor over the fill handle (the small black box in the lower right hand corner of the active cell).
  4. Click and hold, drag down to cell "H9" and release. This replicates the formula for the rest of the students in the list. If you have more students, simply drag the mouse down to the last row that has a student and release there.
  5. To calculate the average for each graded item, select cell "D10" and from the "Insert" menu click "Function".  In the "Insert Function" dialog will appear.  You could "Search for a function" if you know the function's name or "Select a function from a list".  If you do not see the function you want, select a different category (including "All" which will display all available functions) to display the pertinent functions in the "Select a function" box.
  6. Select the "All" category and highlight the "AVERAGE" function from the "Select a function" box.  Then, click "OK".
  7. The range you wish to use should already be entered.  If so, please click "OK".

    Excel Figure 3

  8. Again drag the fill handle to the last filled-in column (cell "H10") and release. Notice the value in cell "F10 is "#DIV/0!". This is due to the fact that there are no data in the column that are being averaged.
  9. Enter 100 for all the "Final Exam" grades in order to provide data for the function in cell F10 to calculate properly.

Excel Figure 3a

Top of the Document


Formatting Your Worksheet

Several formatting features are available within MS Excel to effectively display your data.  Text and individual characters can be formatted to make them stand out (you can format all of the text in a cell or only selected characters).  You may also rotate text (in a column for example) as well as add borders, colors, and patterns to distinguish among different types of information in a worksheet.  Also, you can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance.  Finally, you can format cells and lists quickly using auto-formats, styles, and the "Format Painter" button.

To demonstrate how to go about auto-formatting a worksheet, please follow the following steps:

  1. Select all the cells that encompass your title (i.e., "A1") and click the down arrow to the right of the "Fill Color" button. Select the desired color.
  2. Select your grade book by clicking and holding in cell "A3" and dragging to cell "H10" and releasing.
  3. Select "AutoFormat" from the "Format" menu and select the desired format from those provided and click "OK".
  4. In cell "C10" type "Average".
  5. Format cells D10 to H10 by first selecting these cells.  On the "Format" menu, click "Cells", and then click the "Number" tab.  In the Category list, click "Number" and leave the default of two decimal places.
  6. Save your work by clicking "Save" from the "File" menu.  Alternatively, you may click on the "Save" button on the toolbar to accomplish the same result.

Excel Figure 4

Top of the Document


Embedding a Chart

In MS Excel, you can create either an embedded chart or a chart sheet.  The steps involved are the same except for the last step when you could choose one or the other.  In order to create a chart, you need to first select the cells that contain the data you want to appear in the chart.  If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.  Then, you may click the "Chart Wizard" tool and follow the instructions of the wizard.

To demonstrate how to go about embedding a chart in your worksheet, please follow the following steps:

  1. Highlight cell "C3" to "H9".
  2. Click "Chart" from the "Insert" menu and select the "Column" chart type from the list provided.
  3. Click "Next" to advance to the subsequent step and click "Next" again.
  4. Type in the title "First Semester Grades" in the "Chart Title" box and type in "Student ID" for the "X axis" label.
  5. Click "Finish" to complete the wizard and position the chart as necessary.
  6. Save your work.

Excel Figure 5

Top of the Document


Getting Help

At any time while you are using MS Excel, you can get help by clicking the "Microsoft Excel Help" on the "Help" menu. To access online help, click "Microsoft Office Online" on the "Help" menu.

Top of the Document


Concluding Remarks

Before you end your MS Excel session, you should save your workbook if you intend to continue working on it in the future or want to save a copy for future reference.  To do so, click on the "File" option from the top menu bar and select the "Save" option from the pull-down menu.  The next time you want to work on this workbook (or any other workbook), choose the "File" option from the top menu bar and select the "Open..." option from the pull-down menu. To end your MS Excel session, choose the "Exit" option from the same pull-down menu.

I hope that you find this introductory tutorial to MS Excel informative and useful.  As you may have noticed, there are numerous features and tools that were not demonstrated in this tutorial.  You can see the functions of these tools by just trying them, or can click on the "Help" option on the top menu bar and get help.  The help screens provide a good overview of MS Excel.