Return to IFS 105 Notes!

Excel
Project 3

What-If Analysis, Charting, and
Working with Large Worksheets

Enhancing a Worksheet and Chart

• Drawing toolbar - draws shapes, arrows, and drop shadows around cells
• Assumptions - are cells whose values can change to determine new values for formulas

Rotating Text and Using Fill Handle to Create a Series

• rotate text - use alignment tab in Format Cells dialog box
• Fill Handle - can create a series of numbers, dates, or month names
• To create a nonsequential series - enter 1st number of series and second number of series in an adjacent cell, select both cells and drag fill handle across paste area
• CTRL-key and drag fill handle to copy exact value and not create the series

Note: Copies the format of 1st cell in series

Copying a Cell’s Format Using Format Painter Button

• Format Painter - shows as paintbrush on Standard toolbar
• Copy formats to nonadjacent ranges - double-click Format Painter button and then, one by one, drag through the ranges

Copying Range of Cells to a Nonadjacent Paste Area

• Copy Button or Copy Command on Edit or Shortcut menu - places entries on Office Clipboard
• Office Clipboard - can collect up to 24 items
  • Clipboard task pane - displays when 2nd item collected with icons representing the different items
• Paste Button or Paste Command on Edit or Shortcut menu - copies newest item on Office Clipboard to the paste area
• Undo Paste command - on Edit menu can be used for mistakes

Drag and Drop Method
Move and Copy Cells

• Select copy area and point to border of range
  • mouse pointer changes to block arrow
• Move - drag selection to new location
• Copy - hold down CTRL key while dragging release left mouse button before the Ctrl-key
• Alternative Move - use the Cut button on Standard toolbar or Cut command on the Edit or shortcut menu (uses Clipboard)

Inserting Rows
(2 Ways)

1. Rows command on Insert menu
2. Insert command on shortcut menu

Primary difference

• Must select entire rows to insert rows using the Insert command on shortcut menu
• Rows command on Insert menu only requires you to select a cell in a row

Inserting Columns
(2 Ways)

1. Columns command on Insert menu
2. Insert command on shortcut menu

Primary difference

• Must select entire column to insert columns using the Insert command on shortcut menu
• Columns command on Insert menu only requires you to select a cell in a column

Inserting Rows and Columns

• Inserted rows use the format of row above the insert
• Inserted columns use the format of the column to left of the insert
• Excel pushes rows down or columns to right
• Recommended that you insert only entire rows or entire columns

Deleting Columns and Rows

• Delete command on Edit or shortcut menu (includes data and format)
• Clear command clears data from cells
• Delete command removes the cells
• Excel does not adjust cell references to the deleted row or column in formulas
• Error message #REF (meaning cell reference error)
• Recommended delete entire rows and columns

General Terms

• Format symbol - Excel immediately applies format to cell (% , $)
• Freeze Panes command on Window menu (drag split bars too)
  • useful in large spreadsheet beyond range of window
• Unfreeze Panes command on Window menu to undo Freeze Panes command

Displaying System Date

• date stamp - system date usually current date
• NOW function - returns decimal number in range 1 to 65,380 (representing dates Jan 1, 1900 and Dec 31, 2078) and time of day

Example: Excel treats date as number (35038.5)

35038 - number of days since Dec 31, 1899
.5 - time of day is 12:00 noon

Absolute Referencing

• Excel keeps cell reference in a formula the same when a copied cell is pasted (useful when copying Assumption's area constants)
• indicated by $ prior to row or column reference

Example : $B$15

• Mixed cell reference - cell reference with one $ sign before either a row or column ($B15)

Making Decisions
IF Function

• General Form:
  =IF(logical-test,value-if-true,value-if-false)
  • logical-test - made up of two expressions and a comparison operator
    • expression - cell reference, number, text, function, or formula

Example:
=IF(B4>=$B$24,$B$19,0)

Comparison Operators

• < Less than
• = Equal to
• > Greater than
• >= Greater than or equal to
• <= Less than or equal to
• <> Not equal to

Display Drawing toolbar
(4 Ways)

1. Point to toolbar, click right mouse button, choose Drawing toolbar from shortcut menu
2. View menu choose Toolbars, select Drawing toolbar
3. Click Drawing button on Standard toolbar after double-clicking the move handle to display it in its entirety
4. In Voice Command mode say "View, Toolbars, Drawing"

Moving and Docking Toolbar

• Floating Toolbar - in middle of screen can be moved
  • Drag borders to resize, click Close button on title bar to hide
• Move toolbar - point to blank area of toolbar and drag to new position
• Toolbar dock - drag toolbar close to edge of window
  • (Four toolbar dock areas along the four edges of window)
  • To change a docked to floating toolbar double-click in blank area

Pie Charts

• Pie chart - is used to show the relationship or proportion of parts to a whole
• chart sheet - the chart appears on a different worksheet from the numbers
• Exploded pie chart - pie chart with one or more slices offset
• category names - identify slices of pie chart
• data series - data used to represent the slices in the pie chart
• 3-D View command on Format or shortcut menu - allows control of rotation angle, elevation, perspective, height, and angle of axis

Splitting Window into Panes
2 Methods

1. Split command on Window menu
2. a) Vertical split box - splits window vertically
   creates vertical split bar (up and down middle of screen)

     b) Horizontal split box - splits window horizontally
     creates a horizontal split bar (across middle of screen)

Remove Split from Window
(2 ways)

1. Unsplit command on Window menu
2. Position mouse pointer at intersection of horizontal and vertical split bars, double-click

What-If Analysis

• Sensitivity analysis - scrutinize impact of changing values in cells that are referenced by a formula in another cell
• Goal seeking - used if you know result you want formula to produce to determine the value of a cell on which formula depends
• Goal Seek command on Tools menu

In the Lab 3 (Extra Credit #1)

• Pages Ex 218-221 extra credit
• Project #3 Awesome Intranets' Six-Month Financial Projections pages Ex 150-208 (in chapter)

End Excel Project 3

Computer Assignment 6

Return to IFS 105 Notes!