350 likes | 471 Views
Creating Worksheets for Decision Makers. Chapter One. Spreadsheet Applications. Computer program Analyze numeric information Help make meaningful business decisions. Dan Bricklin and Bob Frankston Invented in 1979 VisiCalc Sold and developed into Lotus 1-2-3. Titles. Column Headings.
E N D
Creating Worksheets for Decision Makers Chapter One
Spreadsheet Applications • Computer program • Analyze numeric information • Help make meaningful business decisions • Dan Bricklin and Bob Frankston • Invented in 1979 • VisiCalc • Sold and developed into Lotus 1-2-3
Titles Column Headings Borders Data Calculations Typical Spreadsheet
Application Window Workbook Window Minimize Restore Close
Title bar Formula Bar Name Box Active Cell Menu bar Task Pane Standard Tool bar Formatting Tool bar Status bar Active Worksheet Sheet tabs Anatomy of the Excel Window
Cell Pointer Columns Rows B 3 Basic Vocabulary Cell Address Worksheet
Moving Around a Worksheet Using the Keyboard Keystroke Action Up Arrow Moves up one cellDown Arrow Moves down one cellLeft arrow Moves left one cellRight arrow Moves right one cell PgUp Moves active cell up one screenPgDn Moves active cell down one screen Home Moves active cell to column A of current row Ctrl+Home Moves the active cell to cell A1Ctrl+End Moves to the lower, rightmost active corner of the worksheet F5 (function key) Opens the Go To dialog box in which you can enter any cell address
Moving Around a Worksheet Using the Mouse • Action • Click the cell • Click on scroll bars to reveal cells not showing • Mouse pointer • Indicates current position of mouse • Changes shape to indicate what action can be performed • Arrow: select item from menu • I-beam: type text in formula bar • White plus sign: as move over worksheet surface
Worksheet Limits IV Last cell in a worksheet! Cell address of IV65536 65536
Right one sheet Rightmost sheet Leftmost sheet Left one sheet Workbooks Maximum number of Workbooks is 255. Initial number loaded can be setby user. Worksheet tabs
Click on the Open Icon on the Standard Toolbar Choose Open from File Menu Use the Shortcut Key combinationof holding down the Control key and thenpressing the O (letter not zero) key Opening an existing workbook
Information about worksheet’s designer, use, creation date, modification dates, and brief commentsabout input fields and output. Documentation Sheet
Excel Data Types • Three different types of data • Label or text • Value • Formula • Function – Special prewritten formula
Text • Set of characters not interpreted as a value • Cannot be used for calculations • Left justified in cell (default setting) • Examples: • telephone number 227-1240 • security number 372-70-9654 • column headings
Cell still not large enough, but adjacent cell is empty Text Within a Cell Cell not large enough, but adjacent cell is filled
Value • Numbers, dates, times • Can be used for calculations • Right justified in cell (default setting) • Examples: • 378 11/29/94 4:40:31 (9876) • Recognized as number if begins with • + = @ # $ or any digit Number Date Time Negative Number
Formula • Specifies calculations to be performed • Begins with an equal sign (=) • Can contain cell references, arithmetic operators, values and built-in functions • Recalculates when cell reference changes • Example: = A1 + B2 Note: If forget the equal sign then it becomes a label (no math done!)
Surrounds math operations and indicates that they should be done first. Exponentiation done first of the math operators Multiplication and Division are done next in the order that they appear left to right Addition and Subtraction are done last and in the order that they appear left to right Arithmetic Operators • Parentheses ( ) (5+3)/2 4 • Exponentiation ^ 5^2 25 • Multiplication * 5*2 10 • Division / 5/2 2.5 • Addition + 5+2 7 • Subtraction - 5-2 3
Formula bar reveals cell’sunderlying formula Value (calculated results) .10 5 900 Creating Formulas = times times = 450
Functions • Built-in formula that provides shortcut for complex calculations • Start with function’s name followed by set of parentheses • Parentheses enclose the argument list • Cells or other expressions needed for the calculations
B3:D8 A11:B14 Argument List Cell Ranges • More than one cell or a block of cells • Always rectangular or square in shape • Specify by naming the upper left cell, a colon, and the lower right cell • SUM function example =SUM(A11:B14)
Editing Cell Entries • Select cell • Click in formula bar or press F2 • Correct mistake • Type in change • Backspace key (removes character on left) • Delete key (removes character on right) • Highlight by dragging over characters to change, then type correction (will replace what is highlighted) • Press Enter
Three ways to Save • Button (Icon) • Save in Menu • Shortcut Key Ctrl + S Saving a File • File Menu • Save: Replaces workbook with newer version • SaveAs: Creates workbook under a new name(Makes a Copy)
Folder where file will be saved File name you choose for document Type of file: The format the file will be saved as Saving a Workbook Windows Save As Dialog Box
Topics Highlighted topic explanation Getting Help Chose the Help buttonon theStandard toolbarorChose Helpfrom theHelp menuto get theHelp DialogBox shown
Shortcut menu thatappears with a right click Clearing Cells • Other methods: • Select cell with mouse and press the Delete key • From the menu bar chooseEdit and then click on Clear,and then Click Contents
What-if Analysis • Classic use of spreadsheets • Involves modifying values and reviewing their affect on other values • Recalculation automatically allows review • Assumption cells • Cells upon which other formulas depend • Values changed to observe effect
Headers/Footers • Header • Three sections: left, center, or right • Always appears at top of worksheet • Footer • Always appears at bottom of worksheet
Ability to enlarge sheet to view details Ability to adjust the page margins Locate where the page breaks occur Direct access to the Print Dialog Box Direct access to the Page Setup Dialog Box Print Preview Print Preview Provides:
Can also specify how manycopies and in what order theworksheets should be printed. Can send to a specified printer or to a file. Print all pages or specifically name page to start and to stop Choose just a selection of cells (those highlighted), the entire workbook (all worksheets), or just theactive worksheet. Another way to navigate to the Print Preview Screen Printing
Closing a Workbook • File menu • Choose Close • Title bar • Click on the Close button • “Save before exiting” dialog box may appear if any work was done since last save
Summary • Open a workbook • Type into worksheet cells text, values, formulas, and functions • Edit and clear cell entries • Save a workbook • Adding a header and a footer • Previewing output • Close a workbook