The Basics of Using Excel
Calculating with Excel
|
|
The real power of Excel is in the general way it allows you to calculate new results from your data.
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).
- Click on the menu Start.
- Click on the
menu item All Programs > Microsoft Office 2013> Excel 2013.
- Once Excel opens, click on the
menu File,
then click on the menu item Open, then on Computer, and finally on Browse.
- 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.
- The banner PROTECTED VIEW will display across the top of the file; click the button Enable Editing.
- 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, then on Computer, and finally on Browse.
- In the dialog Save
As,
navigate to one of these
two locations:
- your network drive U:,
or
- the local
hard drive C:,
e.g. onto your Desktop.
- Correct the file name, e.g. to stateinfo.xlsx. Then click on the button Save.
- In the Mac Finder, open the folder Applications , then the folder Microsoft Office 2011, then double-click on Microsoft Excel.app.
- Once Excel opens, click on the
menu File,
then click on the menu item Open URL….
- 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.
- 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.
- In the dialog Save
As,
navigate to one of these
two locations:
- your network drive Userfiles:,
or
- the local
hard drive Macintosh HD,
e.g. onto your Desktop.
- 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 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.
|