Scavenger Hunt – Week 05 – Excel, part 1 PDF

Preview

Summary

Scavenger Hunt notes...

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? These were relevant to the personal computer revolution because PCs (personal computers) would not be where they are today and what they are today without Lotus 1-2-3. XLSX- Excel file format “Killer app that ignited the personal computer revolution” -CFO Magazine

Unit 2: Excel Skills Note: You should be able to use all of the Excel skills presented in lectures 06.02 through 06.12 Download the sample spreadsheets and take the time to practice these skills. They’ll benefit you in your academic and professional lives. · What is a cell address? How is a cell’s address determined? A cell is the little boxes in the program. The spreadsheet is composed of a set of columns. The cell address is the combination of the column letter plus the row number. (Ex: cell D4.) · What is a range? How is it expressed in Excel? A range is simply a rectangular group of cells that is uninterrupted. (Ex: C5 : C9)

· Make note of all keyboard shortcuts in the lecture – they’ll save you time and some will be on the quiz

· Freeze Panes – what does this function do? how is it useful? How do you enable it in an Excel spreadsheet? Freeze panes (how it works)- you take your cursor and you put it immediately below the heading. Then go up to the view menu bar and click on freeze panes. · What does it mean when ########## is displayed in an Excel worksheet cell? When you enter dates into a cell and the data is too wide to fit within the column excel will show ##### This means the date information was too wide. · What power tip is offered for instantly and automatically setting a column width to the optimum size? You can double click the border between the two columns and excel will automatically pick the perfect column width. · Understand how to format cells: o text formatting like bold or italics You can use control (B) for bold and control (I) for italicize (you can use the shift key and hold it down while using the arrows to select multiple cells that you want to change the format of. o numeric data display formatting to set commas, decimal places, percentages, dates, etc. For dates you can click on the “date” tab to change the format. Go to the formatting area and click on comma o cell alignment – top, middle, bottom; left, middle, right: The top 3 alignment icons let you set the data in the top of the cell, the middle of the cell, or the bottom of the cell. You can do right, middle, or left justify as well. o merge cells, indent data within cells, set cell borders- Merge cells can take multiple cells and merge it into one super cell. · Understand how to do a multi-level sort: Select your range (ctl A to select the range), then click the sort box (you can sort by last name A-Z), then select a level to sort by · Understand the use of Remove Duplicates and how to avoid the inherent dangers: Remove duplicates by selecting the range then on the data bar there is an icon to remove duplicates, click on remove duplicates.

· Duplicate an entire sheet – within the same workbook or to a new one: On the home ribbon bar click the format button…

Copy it to save the data. (Right click- insert sheet) · Building formulas and importance of PEMDAS: P- Parentheses (please) is the very first thing that you when computing a formula is you look for parentheses. You do what's inside the parentheses first, then are there any exponents (do them), then multiply (*), then division, then addition, then subtraction. PEMDAS reminds us when we are creating a formula, Excel is going to do parentheses first… This is important for when you make complicated formulas. Force excel to do computations in a certain way. · Excel built-in functions o What is the standard Excel syntax for functions? The syntax starts with the equal sign, then you put the name of the function (ex:A), then use the inputs that change or compute the function (ex: +, *, /, -).

·

o Know how to create functions to… § Calculate sum of a range of cells: =SUM (A1:A5) § Determine maximum or minimum value in a range of cells: =MAX (insert range) =MIN (A1:C4) § Compute average of a range of cells: =AVERAGE (range) Copying formulas o Relative cell references- When you copy and paste a formula that has an address in it excel takes that address and looks at the relationship between the original cell and that

·

cell. You copy the relationship(excel will mess with cell addresses when you copy and paste) o Absolute cell references- You can force a cell to not mess with an address with a cell when it copies and pastes. You can do this by putting a dollar sign in front of the column letter or the row number when you do that excel doesn't mess with it it copies it. o How does each work? How do you specify an absolute reference? Specify an absolute reference with a $ (ex: =SUM (A1+ $H$3). Data Validation – be able to… o Configure data validation to require selection from a list of values. Click on data validation, then select the list of values and click on the range. o Configure data validation to require numeric input within a specified range. Click on the numeric option and type in the range. o Configure data validation to provide an input tip and error message. Click on the input and error options and provide a helpful message for viewers.

Unit 3: Formula Auditing ·

Prevalence of spreadsheets with errors o What percentage of all spreadsheets have errors? 88% of spreadsheets in the UK had errors in them. (Research done by F1F9.) o What percentage of those used by large companies have material defects? 50%

o What does F1F9 say is the main cause of spreadsheet errors? A lack of industry-wide standards in financial modeling was the main cause of these mistakes. o What spreadsheet error cost Fidelity Magellan Fund $2.45 billion? $76.6 million · What does Mr. Olson meant by a “sanity check”? Look at the answers that your spreadsheet gives you. See if the answers make sense. · Understand how the three tools below can be used to check formulas for correctness o Show Formulas: This will let you see the actual content of every cell (all the formulas) and when you click on a cell it will graphically show you where the data is. o Trace Precedents: (Follow the precedents backwards to see where the data comes from) If you click the trace precedents button excel will show arrows from where the data came from.

o Trace Dependents: “If this cell changes, what is the impact of that change” Ex: changing a percent you can see what numbers it will affect. If you click it twice it can show how the number affected then affects other numbers....