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  Consolidate.xls

 Outline.xls

Introduction

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

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

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

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

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

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

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

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

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 5 th 145,000) power or Single quote preceding a number => excel interprets this as text  Social security numbers => ‘000-00-0000 Simon, Chapter 1

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

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

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

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

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

Find a Value: Explore Search Options Simon, Chapter 1

Open: AnnualExpenses.xls

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

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

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

 Automatically Name Ranges Row and column headings will be used   Select A2..H14

Insert  Name  Create Simon, Chapter 1

Excel Makes the Ranges Available Simon, Chapter 1

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

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

  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

Sorting Data  Select data block  Data => Sort Simon, Chapter 1

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

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

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

Group and Outline  Auto Outline the balance of the data Simon, Chapter 2

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  File  Print Area  Print Preview Set Print Area  Set up  Select Page and Margin tabs to format for printing Project 1 Guidance

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