Lab 1: Excel Basics Simon Chapters 1 and 2

1. Lab 1: Excel BasicsSimon Chapters 1 and 2 URBPL 5/6010: Urban Research University of Utah Pam Perlich – BUC 103 Craig Forster – BEHS 101

2. Spreadsheet Files for this Lab • Files are found on • CD with the text • On class website • Simon, Chapter 1 • AnnualExpenses.xls • Simon Chapter 2 • Consolidate.xls • Outline.xls Introduction

3. Good Work Habits / Practices • Document • Data sources, dates, procedures • Back-up • At least 2 copies of electronic files • Never depend completely on network back-ups • Organize • File structure, file naming • Archive • Why? Replicate, defend/explain, reuse • Develop error checking procedures Introduction

4. Suggested Back-up Procedures for this Class • Although not required, it would be wise for you to invest in some type of magnetic media (memory stick or whatever) to transport your files to and from class. • Although less dependable, you can also create back-ups by emailing files to yourself. • Email is fast, but not always reliable. Introduction

5. MS Excel Help Facility • Take a few minutes and explore the help facility. • Notice that there are on-line resources at www.Mircrosoft.com Introduction

6. Functionality of Excel • Data entry • Junk in – junk out (error propagation) • Data analysis • Major focus of the course • Data / results display • Communicate findings • Simplification Simon, Chapter 1

7. Data Entry • Data lists • Group data for similar treatment • Data forms • Automate and validate data entry • Data from external sources • Import various file formats Simon, Chapter 1

8. Data Analysis • Formula creation • Computations, built-in functions • Macros • Automate repetitious tasks • Pivot tables • Create cross tabulations from data bases • Analysis tools • Add-ins: These include statistical functions Simon, Chapter 1

9. Results Presentation • Graphical presentation • Variety of chart types • Customization • “Worth a thousand words” – a good graph can communicate difficult concepts visually • Pivot Charts • Dynamic linkage to pivot table • Graph data • Visually inspect for errors Simon, Chapter 1

10. Data Types • Text or alphanumeric • Can be text only, numbers only, or a combination • Numeric • Numbers, dates, formulas Simon, Chapter 1

11. Data Types - Text • Letters and numbers combined in one cell • Maximum of 32,000 characters • Exceptions • Scientific notation • 1.45E+05 (1.45 times 10 raised to the 5th power or 145,000) • Single quote preceding a number => excel interprets this as text • Social security numbers => ‘000-00-0000 Simon, Chapter 1

12. Data Type - Numeric • Number: Variety of formats • Formulas • Dates and time • Fractions: Interpreted as dates => format • Numeric characters • 1 2 3 4 5 6 7 8 9 0 , % $ + - ( ) e E • 15 digits of precision • Truncates and converts to zero after this 35,555,545,365,875,922 35,555,545,365,875,988 both converted to 35,555,545,365,875,900 Simon, Chapter 1

13. Navigation and Data Entry in Excel • Open a blank Excel workbook • Put your cursor in Cell B3 (Column B, Row 3) • Enter the number 15000 • Right click on B3, select format cell. Explore formatting. • Put the cursor in a different cell • Enter ¾ • How does Excel interpret this? Explore formatting dates. • Enter =3/4 • How does Excel interpret this? Explore formatting percentages. Simon, Chapter 1

14. Cell Location : Sheet 1, Row 3 Column B Simon, Chapter 1

15. Select a Range = B3..D5 Select (left mouse click) cell B3 Hold down the shift key Select (left mouse click) cell D5 Simon, Chapter 1

16. Enter a Formula • Put your cursor in cell G2. • Enter a formula such as =10+5 • Put your cursor in cell G3. • Type the formula = G2 – 1 • Copy this formula from G3 paste it into G4, G5, G6 • What happens to the formula as you paste it? Simon, Chapter 1

17. Find a Value: Explore Search Options Open: AnnualExpenses.xls Simon, Chapter 1

18. Searching • Wild cards * and ? • Example: • Open AnnualExpense.xls from CD with text • Find *ber returns • September • October • November • December • Search and replace – practice this function Simon, Chapter 1

19. Name a Range • Select a range of contiguous cells (C2..C14) • Insert => Name => Define Simon, Chapter 1

20. Name a Range • Select a range of contiguous cells • Insert => Name => Define = Define Name • Use in formulas : =sum (groceries) Simon, Chapter 1

21. Automatically Name Ranges • Row and column headings will be used • Select A2..H14 • Insert Name Create Simon, Chapter 1

22. Excel Makes the Ranges Available Simon, Chapter 1

23. Copy, Paste, and Paste Special • Select the range of cells that you want to copy • Point to where you want to paste them • Paste special: • Values • Formats • Transpose • Etc. Simon, Chapter 1

24. Formats and Worksheet Protection • Formats • Auto Formats • Custom format – save to a template • Create named styles • Password Protection • Entire worksheet • Ranges • Once protected, you must “unprotect” in order to modify the protected area Simon, Chapter 1

25. Form for Data Entry • Make column headings • Select first cell that is the name for the first column • Data => Form • Answer “OK” to pop-up dialogue box Simon, Chapter 1

26. Sorting Data • Select data block • Data => Sort Simon, Chapter 1

27. Data Consolidation • Combine several identically formatted data tables into one summary table consolidated by titles in the first column • Open consolidate.xls • Data => Consolidate Simon, Chapter 2

28. Data Consolidation • Function => Sum (or whatever) • Specify ranges Simon, Chapter 2

29. Group and Outline • Outline.xls from book CD Simon, Chapter 2

30. Group and Outline • Auto Outline the balance of the data Simon, Chapter 2

31. Table and Print Instructions • The spreadsheet good_bad_tables.xls demonstrates table construction basics • Basic formatting • Correct titles, headings, and sources • Significant digits • Print procedure • Select print area • File  Print Area  Set Print Area • File  Print Preview  Set up • Select Page and Margin tabs to format for printing Project 1 Guidance

32. Working with Excel • New features and functions are added with each version. • Private vendors sell software that works with Excel to expand functionality • Statistical packages • Simulation packages • Accounting packages • Not as important to remember exactly how to do a function / operation as it is to remember that the function / operation exists. • You can use the help facility or reference materials to keep track of the exact procedure. Concluding Remarks