=
Amherst College I.T. I.T. home. Amherst College.
I.T. home.
IT > Software > Excel >The Basics of Using Excel

The Basics of Using Excel

Calculating with Excel

Author: Andy Anderson
Date: March 2002

Previous: Editing Data in Excel

Contents

More: Graphing with Excel

The real power of Excel is in the general way it allows you to calculate new results from your data.

Getting Started

In this tutorial you'll be working on a specific set of data, so the first step is to make your own copy.

Excel files are called workbooks, and have a file type of .xls (the older format) or .xlsx (the newer 2007/2008 format).

Set Up: Starting Windows Excel and Getting the Tutorial Data

  1. Click on the menu Start Menu Icon Start.
  2. Click on the menu item All Programs > Folder Icon Microsoft Office 2013> Excel 2007 Icon Excel 2013.
  3. Once Excel opens, click on the menu File, then click on the menu item Open, then on Computer, and finally on FIle Open Icon Browse.
  4. In the dialog Open, in the field File name:, copy-and-paste (or type) the Internet address (URL) http://ats.amherst.edu/software/excel/calculating/global_temperature_anomalies.csv, and click on the button Open.
  5. The banner PROTECTED VIEW will display across the top of the file; click the button Enable Editing.
  6. Excel will download the file into a cache where it is read-only, so to create your own editable copy, again click on the menu File, and then click on the menu item Save As Button Save As, then on Computer, and finally on FIle Open Icon Browse.
  7. In the dialog Save As, navigate to one of these two locations:
    1. your network drive  U:, or
    2. the local hard drive  C:, e.g. onto your Desktop.
  8. Correct the file name, e.g. to stateinfo.xlsx. Then click on the button Save.

Set Up: Starting Mac Excel and Getting the Tutorial Data

  1. In the Mac Finder, open the folder Applications , then the folder Microsoft Office 2011, then double-click on Excel 2007 Icon Microsoft Excel.app.
  2. Once Excel opens, click on the menu File, then click on the menu item Open URL….
  3. In the dialog Open URL, in the field URL:, copy-and-paste (or type) the Internet address http://ats.amherst.edu/software/excel/calculating/global_temperature_anomalies.csv, and click on the button Open.
  4. Excel will download the file into a cache where it is read-only, so to create your own editable copy, again click on the menu File, and then click on the menu item Save As Button Save As.
  5. In the dialog Save As, navigate to one of these two locations:
    1. your network drive  Userfiles:, or
    2. the local hard drive  Macintosh HD, e.g. onto your Desktop.
  6. Correct the file name, e.g. to stateinfo.xlsx. Then click on the button Save.

 

Creating Formulas

Data from any set of cells can be easily linked together to calculate a new result.

  • Any cell can contain a formula, by beginning with an equal sign:

    = 1 + 1

    As with other editing within a cell, you save and exit the cell by clicking the Save icon or typing the Enter key.

    Then the cell shows the result of the calculation, while the Formula Field shows the actual formula (if the cell is selected):
     

     
    The usual arithmetic operations are available: add (+), subtract (–), multiply (*), divide (/), and exponentiation (^).
     
  • You can calculate with data in other cells by typing their cell reference at the appropriate point in the formula:


 
You can also click on a cell instead of typing its reference.

Iterating Cells

Excel makes it easy to extend a formula to other cells.

  • Once you have typed a formula, you can copy and paste it into other cells and its references will automatically update:
     

     
    Note that formula field now references row 3 rather than row 2.
     
  • You can iterate a formula more simply by clicking on the lower right corner of its cell and dragging down or to the right:
     

     
    If you already have the cells selected, you can also use the edit menu to "fill" them with iterated data:
     
      Menu: Edit Menu Fill Menu Down [or Right or Up or Left]

Functions

Excel supplies many mathematical and other functions that can be used in formulas.

  • Many nontrivial results can be obained with named functions, which combine numbers or cell references in an argument list separated by commas:
     
    = Average(B2, C2, D2, E2)
     
  • The arguments in this function could also be written:
     
    = Average(B2:E2)
     
    The argument is a range of cells in a single row, expressed in a compact form.
     
    One can also simply reference a range of cells in a single column, e.g. B2:B9.
     
    As when selecting single cells to reference them in a formula, you can reference a range of cells by clicking and dragging from the first to the last.

  • Note that even trivial functions can sometimes be expressed more simply and generally with ranges:
     
    = Sum(B2:E2)
     
    rather than
     
    = B2 + C2 + D2 + E2
     
    Then, if you add another cell (column) in this range, it is automatically included in the sum.
      
  • All of the common functions are available in Excel, e.g. statistics and trigonometry.
     
    Many other functions are available, too, and their usage can be learned through the Help system:

      Menu: Help Menu Contents and Index
      Click: MS Excel Help List Function Reference

     

     
  • For example:

      Click: MS Excel Help List Function Reference List Math & Trig
      Click: MS Excel Help List Function Reference List Math & Trig List CEILING

     

     

Excel has many ways to display data graphically, which you can learn about in Graphing with Excel.

Some additional examples of calculations with Excel can be found in Calculating Grades with Excel.

Previous: Editing Data in Excel

Contents

More: Graphing with Excel
Top of Page  | Using IT Documentation |  IT Home  |  IT Site Map  |  Search IT