Learning Center
Plans & pricing Sign in
Sign Out

Home Business Excell Spreadsheet Template Free - PDF


Home Business Excell Spreadsheet Template Free document sample

More Info
									                                   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

                                       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
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
                Third addition and subtraction
2.       Parentheses () may be added to change the order of evaluation. Anything in parentheses is
         evaluated first.
                 2+2/2+2^2 = 7
                 Order of evaluation:
                      1.      Exponentiation 22                      4
                      2.      Division 2/2                           1
                      3.      Addition                               2
                      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
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
                                        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

                                                 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
 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
   •   Use Internet Explorer as your web browser.
       ( Use of Netscape, Opera, or other browsers may not open the Template Gallery)
   •   Go to
       Provides the latest clip art and multimedia files to spice up your document

   •   Click on any link
   •   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”

                                     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

                                           More Resources
Microsoft Excel® Basics from Information Technology Services U of U Health Sciences Center
Microsoft Excel® Intemediate Course Training Manual from Information Technology Services U of U Health
Sciences Center
Part 1 from Spencer S. Eccles Health Sciences Library
Part 2 from Spencer S. Eccles Health Sciences Library
Official Microsoft® site
John Walkenbach’s, author of numerous books on Excel, web site.
Mr. Excel on Excel
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
Tutorial on Excel 2000 from Florida Gulf Coast University
Nice overview of Excel 2002
Compares Excel 97 to 2002
Advanced Excel Tutorial
Great site from Duke University’s Business School
From USC’s Business school

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

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.
Excel 2002 from A to Z: A Quick Reference of More Than 300 Microsoft Excel Tasks, Terms and
Tricks by Stephen L. Nelson
Excel 2002 Bible by John Walkenbach and Brian Underdahl

EXCEL for Engineers and Scientists Book used at the U of U
Safari Tech Books Online
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

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 Office in the MMC, 1st floor
Computer Lab 8/7/2005

To top