Excel Spreadsheet Formulas by ner17598

VIEWS: 333 PAGES: 88

More Info

Sen Zhang
     What is a spreadsheet?
• Spreadsheets have been used for many,
  many years in business to keep track of
  information such as expenses, help make
  decisions and for other calculation

• Spreadsheets allow you to organize
  information in tables (which are composed
  of rows, columns and cells).
     The formal definition of the
      traditional spreadsheet.
• A piece of paper with rows and columns
  for recording financial data for use in
  comparative analysis.
    If using paper and pencil
The math that goes on behind the scenes on the paper
spreadsheet can be overwhelming.
If you change the loan amount, you will have to start the
math all over again (from scratch), and you probably will
make mistakes somewhere even with the aid of calculators
and you have to start again.
What is an Excel spreadsheet?
• Simply put, it is the computer equivalent of
  a paper-based spreadsheet, processed by
  Microsoft Excel software.
• More than simulating the paper-based
  spreadsheet, Excel spreadsheet, however,
  is more powerful than the simple
  spreadsheet for its added bonus of
  automatic mathematics.
What is an Excel spreadsheet?

• Excel is an environment that can make
  number manipulation easy and somewhat
  less painfull than otherwise.

• Excel eliminates the tedious recalculations
  required by manual methods.
                   If using Excel
•All you need to do is to design your formula precisely
once, then formulas will do all the calculation for you,
always precisely and correctly!
•The best thing is that you can experiment with
numbers without having to RE-DO all the calculations.
  What makes up a spreadsheet in
• Spreadsheets are made up of
  – COLUMNS. Letters are used to designate each
    COLUMN'S location.
  – ROWS. Numbers are used to designate each
    ROW'S location.
  – CELL is defined as the space where a specified
    row and column intersect. Each CELL is
    assigned a name according to its COLUMN
    letter and ROW number. When referencing a
    particular cell, you should put the column first
    and the row second.
 More about names (addresses or
• In computing world, everything is modeled an
  object, and every object has a name. (That is
  why computers can do things precisely.)
• Operating system manages data in different
  files, folders, drives, computers or network
  domains etc.
• In Excel, every object here stands for a cell, a
  column, a row, a worksheet or a workbook file.
             Locate a cell
• For example, the cell in the uppermost left
  corner would be "A1." The current cell(s)
  will always be listed in the "Name Box,"
  which appears on the left below the
  standard and formatting toolbars (you can
  drag it to different places.)
 Navigating the Spreadsheet
• You can use the "Up," "Down," "Left,"
  "Right," to move (one cell at a time)
  throughout the spreadsheet. You can also
  simply click the cursor into a cell). The
  "tab" button will move one cell to the right.
  The "Enter" button will confirm the entered
  information and move one cell down.
 What can you do with those cells?
• A cell is the basic unit of a worksheet into
  which you enter data or information.

• In each cell there may be the following
  types of data
   – text (labels)
   – number data (constants)
   –formulae (mathematical equations that
     work on number data, always being
     prefixed by an assignment sign, “=”.)
• They are entries that have an equation
  that calculates the value based on the
  values of other cells or constants.
• Formulas reflect business rules.
• In formulas cells, we DO NOT type in the
  numbers; we type in the equation,
  because the values of these cells are
  supposed to be derived from the values of
  other cells.
• This cell reference can either be a
  relative or an absolute reference
Use formulas as much as possible!
• When we are entering formulas into a
  spreadsheet we want to make as many
  references as possible to existing data.

• If we can reference that information we
  don't have to type it in again.
 Intelligence need to be designed.
• Excel is good at repeating and precise
• But Computer is not as smart as human
  beings. It is your responsibility to precisely
  tell the Excel what you want it to do by
  defining right formulas in proper cells.
  How can Excel make calculation
• Excel will keep track of numbers you place
  in cells. Furthermore, if you have defined
  formula cells to refer to each other, any
  changes made in one cell will be reflected
  in these referring cells.
• It sounds a bit complicated, but Excel
  makes it all a breeze.
                  Two stages
• Design stage. Implement all business rules
  using formulas. You are the designer, not
  necessarily to be the final user.

• Production stage. you give your well-designed
  Excel workbook which consists of pre-allocated
  blank cells and preset formula cells to the final
  users, who might or might not be yourself.
• Layout design,
  – text, label and row header and column header
  – Numerical data directly input from users.
  – Derived data defined by formulas (including both
    straight formulas and predefined functions.)
• Format and lighten up the dry data appearance
• Generate charts
• Web support (two ways)
  – Some data could be retrieved from Web.
  – Save as webpage
                       An example
• This is what a basic spreadsheet may look like, keeping track of the
  grades for five students. As you'll notice, numbers automatically
  align to the right, while text automatically aligns to the left. Room has
  been allowed at the top and the left for column and row headings,
  which have been placed in bold.
• We will show grade 1, 2, 3 can be generated easily.
           Simple Formulas:

• "92.67" was not entered as the contents for cell
  "E2." The "formula bar" has the following
  entered into it:
• =(B2+C2+D2)/3
• If you wanted to do the same for students
  2 through 5, you would enter in similar
  formulas for each cell from "E3" to "E6"
  replacing the column letters and row
  numbers where appropriate.
   Copying by dragging fill handler
• An easy method to replicate formulas is to select
  the cell which contains the original formula ("E2"
  in this case), click the bottom right corner of the
  selection box, and drag down several rows (to
  "E6" in this example). The formula will be copied
  down in each cell, and will change itself to reflect
  each new row.
      Relative addresses used in
• Cells information is copied from its relative
  position. In other words in the original cell (e2)
  the equation was =(B2+C2+D2)/3. When we
  paste the function it will look for the three cells to
  the left. So the equation pasted into (e3) would
  be =(B3+C3+D3)/3. And the equation pasted
  into (e4) would be =(B4+C4+D4)/3.
  Absolute address in formulas
• Sometimes it is necessary to keep a
  certain position that is not relative to the
  new cell location.
• This is possible by inserting a $ before the
  Column letter or a $ before the Row
  number (or both). This is called Absolute
              Absolute address

                   If we were to fill down with this
  A B     C        formula we would have the
1 5 3 =$A$1+$B$1   exact same formula in all of the
2 8 2 =$A$1+$B$1   cells C1, C2, C3, and C4. The
3 4 6 =$A$1+$B$1   dollar signs Lock the cell
4 3 8 =$A$1+$B$1
                   location to a FIXED position.
                   When it is copied and pasted it
                   remains EXACTLY the same
                   (no relative).
     Relative position & Absolute

• Relative position, AB12
• Absolute position, $AB$12
• Mixed reference $A2 with only one dollar sign
  before either the column or the row .
• Unless you use absolute positions in your
  formula, the smart Excel will take it for granted
  that the reference is relative, that means they
  will change positions based on its intelligence!
   More examples about relative
        position in formula
• D5 cell contains a formula, which is d4+c4.
• If this formula is copy to d8 what is the
• D7+c7, why.
• D5=d4+c4
• What is the relative position.
• Compared with d5, d4 means same
  column, denoted 0; one row above
  denoted as -1.
• Compared with d5, c4 means one column
  left, denoted -1; one row above denoted
  as -1.
• This relative information will be maintained
  in new cell
• D8=D7+C7
  Absolute position in formula
• D5=$d$4+$c$4
    What are Excel functions?
• An Excel function is a predefined formula.
• Sum, max, average etc.
• Many more
          A function example
    Generate a random value between two limits

• Enter the following values in cell A1 and A2:
  65, 84 (these values will act as the lower and
  upper bound limits)
  Enter the following formula in cell B1:
  A random number between 65 and 84 will be
• Different views ctrl+ ` (accent symbol)
      A new Excel Workbook
• Microsoft Excel will automatically open
  with a blank spreadsheet spanning many
  columns and rows. You will notice a
  number of toolbars with many more
  options included.
        A Workbook window
• Title bar
• Menu bar, tool bars, floating and docking
  at top or bottom of the window.
• Status bar
• Worksheet windows
        Excel Fundamentals
                Worksheet Layout

                                   Standard Toolbar

Cells           C6                          Toolbar

          B12                                 Bar

            Excel Fundamentals
                          Worksheet Layout

  • Worksheet Layout, continued              Formula Bar



              Excel Fundamentals
                          Excel Capabilities

   Use a function to analyze the data ...
                Formula Bar                    AutoSum Tool

Formula in

            Printing Worksheets

• Select File, then Print
• Always a good idea to select Print Preview
                                               Select Printer

Selection                                         Number of

 Preview                                               ?
                  General Options

• Select Tools, then Options

   Manage                           Set the number
  “Recently                          of blank
used file list”                      worksheets
                                    to start with
Set Standard
    Font                             Set File
                                    Set User Name
               Custom Formats
• Select Format, then Cells
• Select the Number Tab, then Custom. Type the format in the box


             Excel Fundamentals
                           Entering Data
  Enter and edit some data ...

 Text is

   Linking Worksheets, identifying
     cells across multiple sheets

                             Cell D4

• One of Excel’s powerful features is its ability to
  sort, while still retaining the relationships
  among information. For example, let’s take our
  student grade example from above. What if we
  wanted to sort the grades in descending order?
  First, let’s select the information we want to
• Now let’s select the “Sort” option from the
  “Data” menu.
• A new window will appear asking how you
  would like to sort the information. Let’s sort
  it by the average grade, which is in
  Column E; be sure to set by “Descending”
  order. If there were other criteria you
  wished to sort by as secondary measures,
  you could do so; let’s select “Then by” as
  “Grade 3” just for the practice of doing so
  (“Descending” order, as well).
• Excel will sort your information with the
  specifications you entered. The results
  should look something like this:
             Dry spreadsheets
• Spreadsheets full of numbers can be pretty dry,
  so we need some tools to dress them up a little.
• You can add bells and whistles to not only
  decorate the appearance of your sheets, but
  also improve their and readability.
• We can use most of the tricks in our word
  processor to do the formatting of text. We can
  use : bold face, italics, underline, change the
  color, align (left, right, center), font size, font, etc.
               Design for Looks
• Here is some (fictitious) data from an author’s book tour.
• When unformatted, the table is hard to read and understand
          2001 Book Tour Results
          City      Date       Fee       Attendance Books Sold
          New York 23-Jan-01      $1,180        205        147
          Boston     25-Jan-01      $821        385        146
          Washington27-Jan-01     $1,724        499        151
          January Total           $3,725       1089        444
          Miami       4-Feb-01      $757        221         41
          Memphis     6-Feb-01      $910          93        45
          Atlanta     8-Feb-01    $1,880        149         95
          February Total          $3,547        463        181
          Los Angeles 5-Mar-01      $941        265         47
          San Francisco             $904        171        145
          Seattle     9-Mar-01      $934        279        109
          March Total             $2,779        715        301
          Grand Total            $10,051       2267        926
           Design for Looks
• The same data, with some formatting.
• Give this a try!

            2001 Book Tour Results
         City             Date      Fee    Attendance Books Sold
New York                23-Jan-01 $ 1,180      205       147
Boston                  25-Jan-01 $    821     385       146
Washington, D.C.        27-Jan-01 $ 1,724      499       151
          January Total           $ 3,725      1089      444
Miami                   4-Feb-01 $     757     221        41
Memphis                 6-Feb-01 $     910      93        45
Atlanta                 8-Feb-01 $ 1,880       149        95
         February Total           $ 3,547      463       181
Los Angeles             5-Mar-01 $     941     265        47
San Francisco           7-Mar-01 $     904     171       145
Seattle                 9-Mar-01 $     934     279       109
            March Total           $ 2,779      715       301
Tour Grand Totals                 $ 10,051     2267      926       16
            Cell Formatting
• You may have noticed that, by default, Excel
  will leave as many decimal points as possible
  within the cell’s width restraints; as you
  increase the cell’s width, the number of
  decimal points increases.
• Select “Cells” from the “Format” menu. A new window will
  appear with a wide variety of ways in which to customize
  your spreadsheets.
• For example, if we wanted to set the percentages fixed to
  only two decimal points, you can make this selection
  under the “Number” category within the “Number” tab.
  You can also set the formatting for things such as the
  date, time, currency, etc.
• The “Font” tab will also allow you to change the default
  font used on the spreadsheet. The other tabs provide
  even more ways to customize your spreadsheet and its
  appearance; experiment with the settings to see what
  works best for you.
     A picture is worth a thousand
• People communicate all the time in graphical
• In many cases, you can get a message across
  more quickly with graphs, charts, or maps than
  with words or tables of numbers.
• Excel allow the users to make information.
  graphically articulate.
• Not only chart, but you can also add picture, art
  work and so on.
            Chart Wizard
• Excel allows you to create basic – to –
  intermediate charts based of information
  and data within your spreadsheets.
• Let’s create a column chart from the
  student grade data from before. First,
  highlight the data.
• Next, select “Chart” from the “Insert”
• A new window will appear asking which
  type of chart you would like to create. For
  this example, let’s do a basic pie chart.
  Select “Column” from the “Chart Type” on
  the left side, and pick the first sub-type on
  the right (a normal, 2D column chart).
• Click “Next.” In this window, you’ll be asked to
  select your “data range”; this is the area of your
  spreadsheet that you wish to generate a chart
  from. Since you’ve already selected the area
  before, it should already be entered into the
  appropriate area. “Series in” allows you to
  choose by which value you want to arrange the
  chart. Let’s arrange it by rows; this will break it
  down by “Grade” (such as Test 1, Test 2, etc.)
  and comparing the student scores next to each
• Click “Next.” In step three you can give the
  chart a name (“Chart Title”), label the X
  and/or Y axis, etc.
• Click “Next.” The final step will ask
  whether you want the chart as an object in
  your current spreadsheet or in a new one;
  generally, you will place it within the same
• Click “Finish,”
  and your chart
  will appear in
• The completed chart can be placed to enhance presentation, yet
remains linked to its data source. If the data changes, the chart
will too. In orther words, if you change values in the data source, Excel
automatically updates the chart to reflect the change.
                                              Quarterly Sales




            $ Millions




                               NORTH   EAST                     SOUTH   WEST
• Pictures
• Cliparts
• borders and shading
             Web Feature
• Save as a webpage
• More importantly, it can receive alive data
  online through web services. (Hot!)
             Web queries
• Grab dynamic data from the Internet
  through web service support.
• Can retrieve information from the Internet.
• Data -> import data …
       Save as web pages
• Static
• Dynamic
       Orientation & preview
• Orientation
  – Portrait
  – Landscape
• Preview
  Some unexpected symbols?
• #### Excel use this string of symbols to
  represent a value that is so large that it
  cannot be displayed within the width of the
  cell. To view the value in the cell, you must
  either increase the width of the column or
  hover your mouse pointer over the cell.
• Something starts with a # sign
• #REF! indicates that there is an invalid cell
  reference in the formula
            Recognizing Errors

•Excel error messages begin with “#”
•Common Error Messages

   •   #####         Cell isn’t wide enough to show the data
   •   #VALUE!       Wrong type of data for a function
   •   #DIV/0!       Tried to divide by zero
   •   #NAME?        Cell name not defined or (usually) misspelled
   •   #REF!         Cell reference is not valid
   •   #NUM!         Function requires a number
   •   #NULL!        Called a non-intersecting range of cells

• If you enter text or numbers that span further
  than the column allows, simply place your
  cursor on the line dividing two columns next to
  their respective letters, and drag to the right or
  left until the desired width is achieved. You can
  also double-click this dividing line to have
  Excel automatically choose the best width.
        Conditional function
• =IF(B2>90,"a", IF(B2>80,"b", "c"))
           Conditional Formatting
• Excel can be set to “watch” for certain values in your spreadsheet
• It responds to the values by changing the cells to a format you specify

     Conditional Formatting, user
        friendly appearence
• Apply conditional formatting to “Forecast Example.xls”
• Highlight cells to be formatted (C3 to O26)
• Set values between 0 and 5 to be filled with red, 6 to 10 yellow, and
   11 and above green

• When additional information is necessary in a worksheet, a
    comment can be inserted
• Select Insert, then choose Comment
• A cell with comments is marked by a red triangle at the upper
right corner of the cell

• What does a green triangle mean in
• Answer: Error
• Ctrl + W = close current window
  Ctrl + P = print current document
  Ctrl + F = find certain text in current
• Alt + F4 = quit current program
• Tab = moves selection between address bar
  and links on current page
• F3 = find certain text in current document
• F5 = refresh current page (also Ctrl + R)
  F6 = set text input to address bar (also Ctrl +
• F11 = switch to full-screen mode
• Ctrl + D = add current page to favorites list
• F4 = expand address bar downwards to view list
  of recently visited sites
•   Ctrl + D = fill up
•   Ctrl + G = go to some position
•   Ctrl + H = find and replace
•   Ctrl + R = fill right
•   Ctrl + 1 (one) = format cells
•   F7 = spelling and grammar
•   Ctrl + K = insert a hyperlink
• Renaming worksheet name
• Adding new worksheet
• Options & customization

• After completing this lecture you will be
  able to:
  – Do math by typing simple formulas to add,
    divide, multiply, and subtract.
  – Use cell references in formulas, so that Excel
    can automatically update results when values
    change or when you copy formulas.
  – Use functions (prewritten formulas) to add up
    values, calculate averages, and find the
    smallest or largest value in a range of values.
  Excel Exam Study Outline
• Spreadsheet (Functional part)
• Charts and Graphs
• Formatting
• Change print alignment
• Enter text
• Cell alignment (right, center, left)
• Indenting text in cells
• Formatting cells (percent, currency, decimal places, font, text wrap,
  fill, etc.)
• Merge and Center command
• Formulas
    – Multiplication, subtraction, division, etc. of cells with each other and/or
      by constants
    – Copying formulas using Relative, Mixed and Absolute cell reference
    – Writing complex formulas involving more than one calculation.
• Adjusting column width
• Using the AutoSum feature
• Using common functions
          Charts and Graphs
• Accenting data in multiple rows or columns.
• Building a chart using the Chart Wizard
• Changing appearance
  –   Move legend
  –   Change colors
  –   Change title
  –   Change background
  –   Change font, etc.
• Data labels
• Embedded chart resizing
   Entering multiple lines of Text
            within a Cell
• Alt+enter
• After you try Excel, you'll never
  go back to a calculator, pencils
  or paper.

To top