ISM3004- Scavenger Hunt Week 5 PDF

Preview

Summary

Professor Olson
Unit 1: Introduction (01.01)
Unit 2: Excel Skills (05.02-05.11)
Unit 3: Formula Auditing (05.12)...

Description

Scavenger Hunt – Week 05 – Excel Part 1

Unit 1: Introduction •

In what way were spreadsheets – and Lotus 1-2-3 specifically – relevant to the personal computer revolution? [05.01] o Spreadsheets § Paper & Pencil § Rows & Columns of Numbers § Math errors § Recalculation speed o Spreadsheet Applications § VisiCalc (“visual calculator”) § Lotus 1-2-3 • Spreadsheet • Graphics • Data Management * ”killer app that ignited the personal computer revolution” – CFO Magazine § Microsoft Excel o ISM3004 and Excel § Windows: Excel 2016 or Excel 2013 § Macintosh: Excel 2016 § Keyboard Shortcuts o Excel file Formats § XLSX • Excel Workbook • 2007+ § XLS • Excel Workbook • 1997-2003 format

Unit 2: Excel Skills Note: You should be able to use all of the Excel skills presented in lectures 04.02 through 04.11 Download the sample spreadsheets and take the time to practice these skills. They’ll benefit you in your academic and professional lives. The items below are intended to draw extra attention to some ideas; don’t ignore other techniques and ideas in the lectures! • What is a cell address? How is a cell’s address determined? [05.02] o Cells § Cells • one of the boxes in the Excel Program § Cell Addressing • Column letter plus row number (e.g. D4, A1) § Naming a Cell • Highlight Cell (click on the cell), Click on the Name Box (top left corner), then type name o Sheets § Sheet size § Sheet naming • Double Click “Sheet _” at bottom and type name

•

What is a range? How is it expressed in Excel? o Range § What is a range? • Rectangular group of cells that is uninterrupted § How are they used? • Example- C5:C9 (Hold down shift key plus arrow keys then let go), A4:C10 § Naming ranges • Highlight, click name box, then type name

•

Make note of all keyboard shortcuts in the lecture – they’ll save you time and some will be on the quiz o Navigation Shortcuts § Function • Move to boundary o They take you to the boundary between data and emptiness § Windows • Ctrl ← → ↑ ↓ • End ← → ↑ ↓ § Macintosh • Command ← → ↑ ↓ • Move to… o Home= Top Left o End= Bottom Right § Windows • Ctrl Home • Ctrl End § Macintosh • Command Home (Home = FN Left-Arrow) • Command End (End = FN Right-Arrow) • Move up/down one sheet § Windows • Ctrl PgUp • Ctrl PgDn § Macintosh • Command PgUp (PgUp = FN Up-Arrow) • Command PgDn (PgDn = FN Down-Arrow)

•

Freeze Panes – what does this function do? how is it useful? how do you enable it in an Excel spreadsheet? o Freeze Panes § Problem: Disappearing Headings • Row • Column § Solution… Freeze Panes (can see the headings and not have to shift between Home and End) § Take cursor, put immediately below the headings , View- Freeze Panes • If headings are in Row 3, put cursor on A4 (Command Home would take you to A4) § If data doesn’t fit horizontally (e.g. Keep Columns A through C and Rows 1 and 2)

•

Move cursor just to the right of the stuff I’m keeping and just below the stuff I’m keeping (e.g. D3) and click Freeze Panes (Command Home would take you to D3)

•

What does it mean when ########## is displayed in an Excel worksheet cell? [05.03] o Numbers (Right-Justified) § Don’t type formatting ( $ or , ), only type digits (1,2,3, etc.) • General Formatting (1300.5) • Comma Formatting (1,300.50) • Currency Formatting ($1,300.50) • Percentage Formatting (98%) § Don’t enter extra zeroes (1.56000) (instead just type 1.56) § Auto-Fill • In A3 (or whatever cell) type the beginning number (for example, 1), move cursor on top of fill handle (bottom right hand corner of cell), and drag down to copy or fill data o Text (Left-Justified) § Just type • Numbers- start with ‘ (single quote character) § Column Headings… double-click to resize (double-click border between 2 columns) § Multi-line textual data • Word Wrap- automatic, based on column width (Wrap Text) • Manually force new line by typing… (only works with text, not numbers) o WIN: Alt-Enter o MAC: Ctrl-Command-Enter o Dates § Entering dates (works for time too) • 7/4/2020 (choose date formatting you want in Excel) § Formatting problems…. (#######) • Sometimes when you enter dates, the data is too wide to fit within the column • To fix: double-click borders between columns to autofit column width (don’t want too narrow of column) § AutoFill • Highlight cell, pull down with fill handle (bottom right hand corner of cell) § Under the hood… (each date is a number) • 1/1/1900 ~ 1 • 5/1/2018 ~ 43221

•

What power tip is offered for rapidly/efficiently entering a range of data? o Entering a Range § Tab key (moves to the right of the cell) § Enter key (moves to beginning of next row) [05.04] o Edit a Cell’s Contents § Double-click cell § Click formula bar § Press § F2 (Windows) § Ctrl-U (Mac) o Clear Cell Contents

§

o

o

o

o

•

Delete key § Cell § Range § Right-click | Clear Contents § Clear Button (Home ribbon bar) Delete Cells § Nature abhors a vacuum § Cells destroyed § Contents lost § Something has to fill the emptiness § Procedure § Highlight cell or range § Right-click | Delete or Delete Button on Home ribbon bar Moving Cells § Select cell or range § Windows § Ctrl-X § Ctrl-V § Mac § Command-X (Cut) § Command-V (Paste) Copying Cells § Select cell or range § Windows § Ctrl-C § Ctrl-V § Mac § Command-C (Copy) § Command-V (Paste) § Danger, Will Robinson! § Be careful to know how big the range is that you’re copying or cutting because when you paste it, that “paste” could overwrite data, wiping it out To err is human… § Undo is divine (only while in file, can’t undo after you save and exit file) § Windows: Ctrl-Z § Mac: Command-Z § Undo and unfo? Redo! § Windows: Ctrl-Y § Mac: Command-Y

What power tip is offered for instantly and automatically setting a column width to the optimum size? [05.05] o Resizing § Click on the boundary and drag § Home Ribbon Bar -> Format dropdown menu • Note: if multiple rows/columns are selected then all will get same height/width • Tip: Press Ctrl-A twice to select all columns o Mac… Command-A o Automatic Resizing § Double-click method • Column- boundary to right of column letter

• Row- boundary below column number Home Ribbon Bar • Note: if multiple rows/columns are selected then all will be automatically resized o Inserting § Right-Click Col Letter § Right-Click Row Number § Home Ribbon Bar o Moving § (1) Select row/column § (2) Cut- menu or Ctrl-X § (3) Select destination § (4) Paste- menu or Ctrl-V o Copying § (1) Select row/column § (2) Copy- menu or Ctrl-C § (3) Select destination § (4) Paste- menu or Ctrl-V **Moving/Copying Warning: Destination cell contents and formatting are replaced** o Insert Copied Cells § (1) Select row/column § (2) Copy- menu or Ctrl-C § (3) Right-click destination, choose “Insert Copied Cells” • Note: New column/row inserted at paste point; existing content shifts right/down o Insert Cut Cells § (1) Select row/column § (2) Cut- menu or Ctrl-X § (3) Right-click destination, choose “Insert Cut Cells” • Note: New column/row inserted at paste point; existing content shifts right/down • Note: Cut column/row is deleted o Hiding § Right-click column letter § Right-click row number §

[05.06] o Character Appearance § Ribbon Bar § Mouseless • Windows o Ctrl-B o Ctrl-I o Ctrl-U • Mac o Command-B o Command-I o Command-U o Numeric Format § General, Number, Currency, Acounting, Short Date, Long Date o Data Alignment § Top, Middle, or Bottom of cell § Right-justified, Centered, or Left-Justified § Indent functions

•

•

§ Wrapping § Merging o Borders § Right-click range of cells, Format Cells, Border, Presets (None, Outline, Inside), Line Style Understand how to do a multi-level sort [05.07] o Sorting Data o Range § Manually select § Auto-select on current cell o Sort Command § Data Ribbon Bar o Multi-Level Sort § Ctrl-A (Command-A) of range, Sort, Sort by Last Sort on Values Order A to Z, Add Level -> Then by First Sort on Values Order A to Z Understand the use of Remove Duplicates and how to avoid the inherent dangers o Removing Duplicates § Range • Manually select • Click one cell § Remove Duplicates Icon • Ribbon Bar • Data Tab § Select Cells • E.g. Remove Duplicates for Customer and Product for Customer Calls Example o Don’t sort just by Customer because it takes away all the products they called about! (by removing duplicates- we destroyed data- it’s gone) o Danger Will Robinson- make a copy of all this data so original source data is presvered and you have the manipulated data (data is valuable)

•

Duplicate an entire sheet – within same workbook or to a new one [05.08] o Manipulating Worksheets § Moving- drag/drop § Renaming § Inserting (Right-click) § Deleting (Right-click) o Duplicating Sheets § Method #1 • Home Ribbon • Format • Move or Copy Sheet § Method #2 • Right-Click Tab for Sheet • Move or Copy Sheet § Move to Book: Same workbook or New book

•

Building formulas and importance of PEMDAS [05.09] o Creating Basic Formulas § Always start with = § Operations + - * / ^ o Truth is stranger than fiction…

§

“…you should never use a calculator to determine a mathematical output and type it into the cell location of a worksheet.” - Joseph Manzo, How to Use Microsoft Excel o Order of Operations § Please Excuse My Dear Aunt Sally • Parentheses • Exponentiation • Multiplication • Division • Addition • Subtraction •

Copying formulas [05.10] o Relative cell references “the relationship” § Example 1: (Horizontally) • A1: 1, B1: 2, C1: 3, D1: 4, E1:5 • In A2, put =A1, and paste it across to E5 “equal the cell immediately above me” o Have same number above it (A2: 1, B2: 2, C2: 3, D2: 4, E2: 5) § Example 2 • In B4, put =B2+B3 o Get Total Sales and paste across § Example 3: (Vertically) • =C5/B5 “one to the left divided by two to the left” • Paste down below o Absolute cell references § $ • Example from Above: in A2, put =$a$1 • Paste that across and every number will be “1” (whatever is in A1) • Example 2 for multiplication: =B4*$A$5 o Paste across the row o “one up times A5” • Example 3: Business Application • In F4, put =E4*$I$1 • Command Shift Up highlight whole range, then Paste (Command-V) § Windows Mouseless… F4 o Mixed Relative/Absolute § Example: Relative Row with Absolute Column • In B5, put =B$4*$A5 • Copy and Paste within the range, so F9 will say, =F$4*$A9 o How does each work? How do you specify an absolute reference? § See Definitions and Examples above***

•

Data Validation – be able to… [05.11] o Consistent Data § Problem: Inconsistent data entry § Solution: Data Validation § Adding new options to the list • Insert new row in the middle of the range (In middle of Boat Type) o Configure data validation to require selection from a list of values. • List of acceptable data values • User can select from pull-down

o

o

o

o

o Select range of cells on “Boat Rental Log” for Boat Rented, Data, Data Validation, List, Highlight list from “Boat Rental Info” Configure data validation to require numeric input within a specified range. • Values within limited ranges o (Example: Number of Passengers) Data Validation: Whole Number, between, Min: 1, Max: 3 o (Example: Total Fare) Data Validatio, Decima, Greater than, 0 Configure data validation to provide an input tip and error message. • Input tip o Title: , Input Message: Select the boat type from the menu. • Error Messages o Style: Stop, Title: Oops!, Error Message: Invalid boat type. Try again. Valid Data § Is it the right type of data? § Are the values reasonable? (Select range of data first) • Number of text characters o Data Validation: Text length, equal to, 1 • Dates in the future… or past… or within a range o Data Validation: Date • Custom formulas Fixing Invalid Data § Circle Invalid Data- to find and repair data validation problems • Used after you inputted data validation

Unit 3: Formula Auditing •

Prevalence of spreadsheets with errors [Spreadsheet Blunders costing Business Billions] o What percentage of all spreadsheets have errors? § 88% o What percentage of those used by large companies have material defects (significant)? § 50% o What does F1F9 say is the main cause of spreadsheet errors? § a lack of industry-wide standards in financial modeling o What spreadsheet error cost Fidelity Magellan Fund $2.45 billion? § Omission (left out; excluded) of a minus sign

•

What does Mr. Olson mean by a “sanity check”? [05.12] o Sanity Check § Does the output of your formula make sense? § If the formula is built incorrectly, it’ll give incorrect answers at lighning speed! Understand how the three tools below can be used to check formulas for correctness o Show Formulas § Formulas Ribbon Bar § Show Formulas § Click cells to graphically see the formula’s inputs • Example: fix to (B5-B4)/B4 o Copy formula o Command Dwn arrow (to move to bottom of row) o Shift Command Up arrow (to highight) o Command-V (paste) • Example 2: fix B9 to say =B5+B6+B7+B8

•

o Trace Precedents § Arrows show how data flows into a formula • (1) Click the cell containing the formula • (2) Formulas Ribbon Bar • (3) Trace Precedents o Trace Dependents § Arrows show how changing one cell affects others • (1) Click the source cell • (2) Formulas Ribbon Bar • (3) Trace Dependents...