Calculating with Excel

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

Creating Formulas

Iterating Cells

Functions

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 Fill 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 Contents and Index
  	Click:	MS Excel Help Function Reference

  
   
  
   
  

• For example:
  
  

  	Click:	MS Excel Help Function Reference Math & Trig
  	Click:	MS Excel Help Function Reference Math & Trig 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.