lab 1 excel basics simon chapters 1 and 2 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lab 1: Excel Basics Simon Chapters 1 and 2 PowerPoint Presentation
Download Presentation
Lab 1: Excel Basics Simon Chapters 1 and 2

Loading in 2 Seconds...

play fullscreen
1 / 32

Lab 1: Excel Basics Simon Chapters 1 and 2 - PowerPoint PPT Presentation


  • 148 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Lab 1: Excel Basics Simon Chapters 1 and 2' - issac


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lab 1 excel basics simon chapters 1 and 2

Lab 1: Excel BasicsSimon 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
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
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
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
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
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 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
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
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
Data Types
  • Text or alphanumeric
    • Can be text only, numbers only, or a combination
  • Numeric
    • Numbers, dates, formulas

Simon, Chapter 1

data types text
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

data type numeric
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
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

select a range b3 d5
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
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
Find a Value: Explore Search Options

Open: AnnualExpenses.xls

Simon, Chapter 1

searching
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
Name a Range
  • Select a range of contiguous cells (C2..C14)
  • Insert => Name => Define

Simon, Chapter 1

name a range20
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
Automatically Name Ranges
  • Row and column headings will be used
  • Select A2..H14
  • Insert Name Create

Simon, Chapter 1

copy paste and paste special
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 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
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
Sorting Data
  • Select data block
  • Data => Sort

Simon, Chapter 1

data consolidation
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 consolidation28
Data Consolidation
  • Function => Sum (or whatever)
  • Specify ranges

Simon, Chapter 2

group and outline
Group and Outline
  • Outline.xls from book CD

Simon, Chapter 2

group and outline30
Group and Outline
  • Auto Outline the balance of the data

Simon, Chapter 2

table and print instructions
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

working with excel
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