Document Sample

```					                                   Brief Overview of Excel®
Screen Layout                                 Math in Excel               Formulas
Common Formulas & Functions                   Cell References             Keyboard Shortcuts
More Help and Common Errors

What is Microsoft® Excel
Excel is a spreadsheet program that analyzes, organizes, solves, and charts information.
Excel’s most powerful features are the ability to create formulas, lists, and charts

Screen Layouti

Cell                The small rectangle container that can hold numbers, text, formulas, etc.
Active cell         Cell that is selected and has a border around it. Also called selected or highlighted
cell. In the above example, A1.
Cell reference      The cell address that is used in a formula to specify which cell(s) are used
Cell address         Identifies a cell’s location. The default style in Excel uses letters to denote
columns and numbers to denote rows. A1 refers to Column A and Row 1
Range               Two cell references that are separated by a colon. Example (B5:D13) will find all
cells between B5 and D13--including B5, B6,…B13, C5, C6, …C13, and
D5, D6, …D13.
Worksheet           The document area in Excel. A page of cells that are arranged in rows and
columns.Worksheets can contain text, charts, formulas, and multimedia (images,
sounds, movies, clip art) files. Default is three worksheets; the maximum number
of worksheets is limited to the computer’s memory.
Workbook            A collection of worksheets. File extension is .xls

1
Ways to Move in Excel
Arrow keys                          Move one character up, down,
left, or right
Enter                               Move from top to bottom
Tab                                 Move one cell right
Shift + Tab                         Move one cell left
Page Up or Page Down                Move one screen at a time
Ctrl + Home                         To the very top (A1)
Ctrl + End                          To most bottom cell with input

Mathematics in Excel
Operators
Symbol                   Symbol Name          Function                    Examples
*       Shift + 8     Asterisk         Multiplication         5*0.1=0.5    6*4=24
/                    Forward slash        Division             5/0.2=25      2/10=0.2
+                      Plus sign          Addition             8+2=10       0.1+0.3=0.4
-                     Minus sign        Subtraction             8-2=6      1/3-1/6=1/6
or Hyphen
^       Shift +6          Caret        Exponentiation         6^2=62=36     4^3=43=64
%       Shift +5        Percent            Percent             .25=25%      .025=2.5%
=, <,>,>=,<=,<>                       Logical comparison

Mathematics in Excel
Order of evaluation
1.       Equations are evaluated in the following manner:
First exponentiation
Second multiplication and division
2.       Parentheses () may be added to change the order of evaluation. Anything in parentheses is
evaluated first.
Examples
2+2/2+2^2 = 7
Order of evaluation:
1.      Exponentiation 22                      4
2.      Division 2/2                           1
4.      Result                                 7
(2+2)/(2+2)^2 = 0.25
1.     (2+2)=4 and (2+2)=4
2.     Exponentiation 42=16
3.     Division 4/16
4.     Result                                  1/4=0.25
2
Formulas
Formulas are instructions that perform calculations on values in a worksheet. A formula
starts with an equal sign (=). Formulas can be written in the formula bar as well as in a cell(s).
After you press Enter or move down, the formula will calculate the value

Formula bar

Formula written
in a cell

Definitions and Commonly Used Functions
fx             “Functions are predefined formulas that perform calculations by using specific
values, called arguments, in a particular order, or structure. Functions can be used to
perform simple or complex calculations.” (Microsoft Excel Help)ii.Common
functions include Autosum, average (arithmetic mean), sum, minimum, maximum,
and standard deviation

AutoSum        AutoSum uses different formulas to calculate results in a specific cell.
How AutoSum works:
1.  Click in a specific cell where result will appear
2.  Click AutoSum button and choose the formula you want to use: (Sum, Average,
Count, Max, Min, or More Functions)
3.  Excel proposes a formula on a selected group of cells (a blinking marquee surrounds
the selected cells) If it is correct, press either the Enter key or the AutoSum button.
4.  If not correct, select a different range by dragging until the correct cells are selected.
Array          “Collection of items operated on collectively or individually.” Arrays are enclosed in
{ }bracketsiii
Sum            Adds up all the numbers in a selected range
Minimum        Finds the smallest number in a selected range
Maximum        Finds the largest number in a selected range
Average        Returns the average (arithmetic mean) of a selected range
Excel Notation For the Above functions
Sum               =sum(a1:b4)
Minimum           =min (a1:a30)
Maximum           =max(a1:j1)
Average           =average(a1:a4)
Parentheses       Information within parentheses is called an argument. The character used to
separate arguments can be mathematical operators, commas, and colons.
Argument          =sum(a1:b4, j10) Will add the range of values between a1 through b4 with j10
3
Cell References
There are three types of cell references—relative, absolute, and mixed. Understanding the types of cell
references are important when troubleshooting problems.
Relative - Location relative to the selected cell. Most common cell reference style used in Excel.
In formulas, the cell reference changes relative to the cell selected. Allows cells to be cut,
copied, and pasted.
Absolute - Fixed location on the spreadsheet. Defined by the use of the dollar sign (\$).
The reference to the cell does not change when formula is copied to a different cell.
Often used when a specific cell address containing a table or lookup chart is used in a
formula or function.
Mixed-- A cell which contains a fixed row or column and a relative row or column reference.
Cell Reference Examples
Absolute           \$A\$1
Relative           B2
Mixed              \$B2

When Type of Cell Reference Makes a Difference in a Formula

4
What is a Template?
A template “is the basic structure for a document and contains document settings such as fonts, page
layout, special formatting, and styles.”

Getting to The Microsoft® Template Gallery
http://office.microsoft.com/templates/
The Microsoft Template Gallery is free. Hundreds of templates for personal finance, business owners,
teachers, and job seekers are available for Excel, Word, PowerPoint, and Access users. All these
templates are available if you or your organization owns a legally licensed copy of the Microsoft Office
product.
( Use of Netscape, Opera, or other browsers may not open the Template Gallery)
•   Go to http://office.microsoft.com/clipart/
Provides the latest clip art and multimedia files to spice up your document

•   Scroll down list and choose type of template
Excel Feature

“If you want to analyze data in the same cell or range of cells on multiple

worksheets within the workbook, use a 3-D reference. A 3-D reference
includes the cell or range reference, preceded by a range of worksheet
names. Excel uses any worksheets stored between the starting and ending
names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all
the values contained in cell B5 on all the worksheets between and including
Sheet 2 and Sheet 13.iii”

5
Keyboard Shortcuts
To find more, click on Help (Press F1) and type in the words keyboard shortcuts

Ctrl key
Save                           Ctrl + S
New window                     Ctrl + N
Cut                            Ctrl + X
Copy                           Ctrl + C
Paste                          Ctrl + V
Undo                           Ctrl + Z Undo works up to 20 times in Excel
Select All                     Ctrl + A
Toggle between formula and     Ctrl + ` (left quotation mark)
Ctrl+ spacebar                 Highlight column

Current date                   CTRL+; (semicolon)
Current time                   CTRL + shift +; (semicolon)

Alt key
Alt + enter                    Line break within a cell
Alt + page up                  Move one screen to the left
Alt + page Down                Move one screen to the right
Alt + =                        Apply AutoSum
Alt + F8                       Macros

Shift key
Shift + →                      All cells left of the current cell
Shift + ←                      All cells right of current cell

Function keys (May not work on a Mac)
F1                                Help
F5                                Go to
F7                                Spelling

F11                            Create chart of selected range
F12                            Save As

Right mouse button (On a Mac, hold the Control key down and click)
Get context sensitive frequently used commands

6
More Resources
http://www.scl.utah.edu/ess/person.xls
http://www.scl.utah.edu/ess/essexcel.xls
http://uuhsc.utah.edu/its/training/userguides/unblocked/excelxpintro.pdf
Microsoft Excel® Basics from Information Technology Services U of U Health Sciences Center
http://uuhsc.utah.edu/its/training/userguides/unblocked/excelint.pdf
Microsoft Excel® Intemediate Course Training Manual from Information Technology Services U of U Health
Sciences Center

http://medstat.med.utah.edu/ed/eduservices/handouts/Excel_Web/excel_handout.pdf
Part 1 from Spencer S. Eccles Health Sciences Library
http://medstat.med.utah.edu/ed/eduservices/handouts/Excel_Web/ExcelHandouts_PartII.pdf
Part 2 from Spencer S. Eccles Health Sciences Library

http://www.microsoft.com/office/excel/support/default.asp
Official Microsoft® site

http://j-walk.com/ss/
John Walkenbach’s, author of numerous books on Excel, web site.
http://www.exceltip.com
Mr. Excel on Excel
http://www.wopr.com/
Woody Leonhard is a respected author of several books on Microsoft products. Clearly explains
concepts and his criticism –his work has led to numerous improvements to the Office suite

http://www.fgcu.edu/support/office2000/excel/
Tutorial on Excel 2000 from Florida Gulf Coast University
www.uncc.edu/sysdev/HowTos/ExcelTipsRev.htm
Nice overview of Excel 2002
http://www.wellesley.edu/Computing/Office02/Excel02/excel02.html
Compares Excel 97 to 2002
http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/ExcelReview.htm
Great site from Duke University’s Business School
http://www.marshall.usc.edu/Web/Computing.cfm?doc_id=770#Excel

Magazines
Smart Computing in Plain English Available in paper at the Marriott Library
PC Magazine        Full-text available in EBSCOhost Business Source Premier
PC World           Full-text available in EBSCOhost Business Source Premier

7
Books
Excel 2002 for Dummies Quick Reference by John Walkenbach and Colin Banfield
Special Edition Using Microsoft Office 2000 by Woody Leonhard
Excel Charts by John Walkenbach
Excel 2002: The Complete Reference by Kathy Ivens, Conrad Carlberg.
http://site.ebrary.com/lib/utah/Doc?id=5008182
Excel 2002 from A to Z: A Quick Reference of More Than 300 Microsoft Excel Tasks, Terms and
Tricks by Stephen L. Nelson http://site.ebrary.com/lib/utah/Doc?id=5000107
Excel 2002 Bible by John Walkenbach and Brian Underdahl

EXCEL for Engineers and Scientists Book used at the U of U
http://sylvanbloch.hypermart.net/
Safari Tech Books Online http://proquest.safaribooksonline.com/?uicode=uutahsaltlake
Click on “Bookshelf” link to see books available from the University of Utah

Common Error Messages in Exceliv
######         The cell needs to be wider for the numbers to be displayed. Also, appears when formula
returns an invalid date or time.
#DIV/ 0        Formula attempts to divide by zero or by a blank cell which is impossible
#N/A           Formula references a cell that returns the “Not available” value.

#NAME? Formula appears to use a cell name but the cell name has unmatched quotes, is not
recognized or is misspelled.
#NULL           Formula attempts to provide value that doesn’t exist.
#NUM!           Formula attempts an impossible operation such as calculating the square root of a
negative value.
#REF!           Formula references a range that doesn’t or no longer exists. Often happens when you
a cell has been deleted from the worksheet.
#VALUE! Formula tries to use text in a mathematical operation.

i  Florida Gulf Coast University. (2002). Excel 2000 tutorial.          Retrieved April 25, 2003 from
http://www.fgcu.edu/support/office2000/excel/basics.html

ii    Microsoft® Excel [computer software]. (2002). Microsoft Office Help.

iii   Microsoft® Excel [computer software]. (2002). Microsoft Office Help.

iv    Walkenbach, John. (1999). Microsoft® Excel 2000 formulas. p. 315.

v     Walkenbach, John. (1999). Microsoft® Excel 2000 formulas. pp. 510-509.

Some of this material was adapted from Craig Bennion’s Excel handouts. Special thanks to Craig
Bennion for reviewing this material.
Alfred Mowdood 585-7125 or alfred.mowdoood@library.utah.edu Office in the MMC, 1st floor
Computer Lab 8/7/2005
8

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 34 posted: 7/18/2010 language: English pages: 8