State University of New York at Morrisville

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



Using MS Excel 2013 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 may 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 2013 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 desired sheet's tab.

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 MS Excel 2013 workbook:

MS Excel Introductory Tutorial By W.Shayya - MS Excel Application 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 relative cell references (which are references to cells relative to the position of the formula), absolute references (which are cell references that always refer to cells in a specific location) or mixed references (which are references that have mixed relative and absolute cell references -- relative column and absolute row references, or absolute column and relative row references).  If a dollar sign precedes the letter and number, such as $A$1, the column and row reference is absolute.  If a dollar sign precedes the letter or number, such as A$1 or $A1, the column or row reference is absolute while the other is relative.  Relative references automatically adjust when you copy the cells while absolute references do not.

Some of the basic functions of MS Excel 2013 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 and a graphical display of student data produced.

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 2013 and select to open a blank workbook.  The application starts with some worksheets (usually one in MS Excel 2013).  Please note that the "Ribbon" has been available in MS Office since the 2007 version of the software (earlier versions of MS Excel included pull-down menus and toolbars).

    MS Excel Introductory Tutorial By W.Shayya - Figure 0

  2. To insert a new worksheet, click on the "Insert Worksheet" Tab from the "Worksheet Navigation" bar (or press Shift+F11).
  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.

    MS Excel Introductory Tutorial By W.Shayya - Figure 1

  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. 
  7. Once you have all the data in, save your work by first clicking the "File" tab from the ribbon and then choosing "Save".  Alternatively, you could click on the "Save" button on the "Quick Access Toolbar" (the bar to usually above the "File" and "Home" tabs).  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 (e.g., GradeBook") 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".

    MS Excel Introductory Tutorial By W.Shayya - 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.

    MS Excel Introductory Tutorial By W.Shayya -  Figure 3

  5. Next, we will need to calculate the class average for each completed assignment.  Before we do so, however, we should enter a title for these calculations in row 10.
  6. In cell "C10" type "Average".
  7. To calculate the average for each graded item, select cell "D10" and from the "Formulas" tab select "Insert Function".  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.
  8. Select the "All" category and highlight the "AVERAGE" function from the "Select a function:" box.  Then, click "OK".

    MS Excel Introductory Tutorial By W.Shayya -  Figure 4

  9. The range you wish to use should already be entered.  If so, please click "OK".

    MS Excel Introductory Tutorial By W.Shayya -  Figure 5

  10. Again drag the fill handle (with cell D10 being selected) to the last filled-in column (cell "H10") and release. Please note that the value in cell F10 is "#DIV/0!" if you have not already entered any numbers in cells F4 to F9. This is due to the fact that you have not yet been instructed to enter data in column F that may be averaged.
  11. Enter 100 for all the "Final Exam" grades (cells F4 to F9) in order to provide data for the function in cell F10 to calculate properly.

    MS Excel Introductory Tutorial By W.Shayya -  Figure 6

Top of the Document


Formatting Your Worksheet

Several formatting features are available within MS Excel 2013 to effectively display your data (we are going to only with some basic ones which were also available in the previous versions of MS Excel).  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 the "Cell Styles" button from the "Styles" group of the "Home" tab.  Cells could even be formatted as tables using the "Format as Table" button from the "Styles" group.

To demonstrate how to go about formatting a worksheet quickly, 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 from the "Font" group. Select the desired color for the background.
  2. Select the "Cell Styles" button from the "Styles" group and choose the "Title" style from the "Titles and Headings" section.
  3. Select the heading row in your grade book by clicking and holding on cell "A3" and dragging to cell "H3" and releasing.
  4. Again select the "Cell Styles" button from the "Styles" group and choose the "Heading 3" style from the "Titles and Headings" section.
  5. Select the last row in your grade book by clicking and holding on cell "A10" and dragging to cell "H10" and releasing."
  6. Again select the "Cell Styles" button from the "Styles" group and choose the "Total" style from the "Titles and Headings" section.
  7. Format cells D10 to H10 by first selecting these cells.
  8. Click the "Show Dialog Button" of the "Number" group to open the "Format Cells" dialog (click on the "Number" tab if not already selected).  In the "Category:" list, click "Number" and leave the default of two decimal places.
  9. Repeat step 8 above after selecting cells H4 to H9.  Your screen should look the same as the following figure.

    MS Excel Introductory Tutorial By W.Shayya -  Figure 7

  10. Save your work by clicking the "Save" button on the "Quick Access Toolbar".  Alternatively, you may click the "Ctrl" and "S" keys simultaneously to accomplish the same result.

Top of the Document


Embedding a Chart

In MS Excel, you can easily create an embedded chart.  After the chart is created, it could be readily modified and even saved as a chart sheet.  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 "Insert" tab and then choose a desired chart type by clicking on its corresponding button from the "Charts" group.  The steps to follow are straightforward.

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

  1. Highlight cell "C3" to "H10".
  2. Choose the "Insert" tab and click the "Column" button from the "Charts" group.
  3. Click the "Clustered Column" option from the "2-D Column" section to create the column chart.  Alternatively, you could have pressed the "F11" function key after step 1 to create a similar chart but as a separate sheet.

    MS Excel Introductory Tutorial By W.Shayya -  Figure 8

  4. Modify the size of the chart and have it fit under the table.
  5. Once a chart is selected, a contextual set of tabs appears to enable you to alter the chart "Design" and "Format".  The "Design" tab can be readily used to (among other things) select a layout using the "Quick Layout" command.  Alternatively, you could enter a title for the chart (e.g., "First Semester Grades"), x-axis label (e.g., "Student ID"), and y-axis label (e.g., "Grade").  You may also format any of the axis as you see fit (see the example figure provided below).

    MS Excel Introductory Tutorial By W.Shayya -  Figure 9

  6. This concludes the introductory tutorial on MS Excel.

Top of the Document


Getting Help

At any time while you are using MS Excel, you can get help by clicking the "Microsoft Office Excel Help (F1)" icon on the "Tab" bar. This includes access to online help which may readily be selected from the "Excel Help" dialog.

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 your created Workbook in the future or want to save a copy for future reference.  The next time you want to work on this workbook (or any other workbook), choose the "Open" command from the "File" tab. To end your MS Excel session, choose the "Exit" command from the same tab.

I hope that you find this introductory tutorial to MS Excel informative and useful.  As you 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 using "MS Office Excel Help".  The help screens provide a good overview of MS Excel.