1 / 32

Lab 1: Excel Basics Simon Chapters 1 and 2

Lab 1: Excel Basics Simon Chapters 1 and 2. URBPL 5/6010: Urban Research University of Utah Pam Perlich – BUC 103 Craig Forster – BEHS 101. Spreadsheet Files for this Lab. Files are found on CD with the text On class website Simon, Chapter 1 AnnualExpenses.xls Simon Chapter 2

issac
Télécharger la présentation

Lab 1: Excel Basics Simon Chapters 1 and 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related