Using a Spreadsheet to Manage Data Introduction to Excel

1

Using a Spreadsheet to Manage Data -
Introduction to Excel

• Jake Simons Steve Bonham
• Last revised 7/16/2002

2
What is a spreadsheet?

• Convenient way of storing, manipulating data
• Data is stored in cells on worksheets
• Cells are identified by their address
• Column letter
• Row number
• Cell ranges
• Referred to by upper left and lower right cells
  separated by a colon
• Ranges may be given names

C5
A2C5
3
Launching Excel

• Select Start gt Programs gt Microsoft Excel

4
Selecting a Worksheet

• Click the tab for desired worksheet

5
Navigating a Spreadsheet

• Tab moves one cell to right
• Shift-tab moves one cell to left
• Return/Enter moves one cell down
• Shift-Return/Enter moves one cell up
• Arrow keys may also be used (however this will
  deselect a selected range)

6
Updating a Cell Select Worksheet Template

• Select the cell to be edited
• Perform desired action
• Input data via keyboard
• Edit data via Edit Box
• Press enter or move cursor to another cell
• Remember Un-do button
• Select the cell B5 Correct my name to Bonham

Edit Box
7
Cells contain data

• Alpha (character) strings names, headings (cell
  A3 below)
• Numeric values 68, 68.3, 68.357 (cells A6 to D6
  below)
• Formulas (cell D6 below)
• Clearing contents Select cell gt delete
• or type to replace
• or edit via the edit box

8
Formatting Cells

• Select cell
• Accomplish action
• Text font, color, style, size
• Justification
• Format of contents
• Number format
• Background color (pattern)

9
Selecting a Cell or a Range

• Click cell C5 to select that cell
• Enter your first name
• Click-hold in cell B2 and drag down and over to
  cell E5 to select a range of cells

10
Updating or Entering Data in a Range

• Select the range where data is to be entered
• Within a Range Tabs will auto-return at the end
  of a line

• Select range D3 to F14
• Enter score as indicated by instructor
• Tab to move to next cell

11
Formatting ExercisesRight-click the cell
desired

• Select Format Cells from the pop-up menu

12
Formatting ExercisesSelect the Number Tab

• And the number category. Set decimal places to 5.
  Do not click OK yet!

13
Formatting ExercisesSelect the Font Tab

• change the font to Comic Sans, Style to Italic,
  Size to 14, and Color to blueDo not click OK yet!

14
Formatting ExercisesSelect the Patterns Tab

• Select a yellow background colorNow click OK!

15
Formatting ExercisesTruncing/Rounding off a
numeric value

• Reformat the data in cell C7 to show no decimal
  places. Your worksheet should look something like
  this now.

16
Formatting the Percentage Worksheet

• Lets make Aamanda Aamazing and Zzeke Zzero stand
  out from the crowd a bit.
• Select row 15 to 16 (click-drag the row headers)
• Right-click inside the selection and select
  format cells
• Make the text Red and Bold Italic

17
Using a Formula in a Cell on the Formats
Worksheet

• Now you will enter a formula in cell D7. Select
  cell D7.
• NO NOT type 60.33333333!!!!!
• Instead type the formula SUM(A7C7)
• This will add the values in each cell in this
  range
• Hit Tab or return/enter to see results
• Note that if you change any value in range A7C7
  the formulated value will update

Change any value here and watch cell D7 update!
18
Formatting the Spreadsheet

• Adjusting column width
• Select column or columns (click or click-drag
  headers)
• Drag intersection between headers)
• Autowidth (double-click intersection)

19
Formatting the Spreadsheet

• Inserting columns or rows
• Begin where you want first inserted column/row
• Highlight the number of columns/rows to insert
• Select Insertgt column or row
• Deleting columns or rows
• Highlight and delete

20
Formatting the Spreadsheet

• Locking Header Rows or Columns
• Select the cell below and right of the column/row
  to be locked
• Select Window gt Freeze Panes
• You can scroll down and still see the column
  header info. You can scroll right and still see
  names/depts.

21
Formulas

• Highlight cell where you want the result
• Enter formula
• Begin with equal sign ()
• Use addresses of cells to be used as inputs
• IMPORTANT! Enter formula in algebraic form
• Precedence of operations (), , /, -
• Please excuse my dear aunt Sally
• Example (B2C2)/2 results in 85 CORRECT!
• Example B2C2/2 results in 125!!INCORRECT!
• Result will be dynamically updated

22
Copying

• You can copy individual cells or ranges
• Formulas (not results) are copied unless you use
  Edit, Paste Special, Values

23
Sorting

• Select rows to be sorted
• Select Datagt Sort
• Select primary key (column on which to sort)
• Specify ascending or descending
• Select secondary, tertiary key if desired
• Click on OK
• Avoid sort errors by only selecting content to be
  sorted by row headers! Do NOT sort grade data by
  ranges!
• Tip! Use color coded rows to provide visual
  feedback

24
Summary-- We learned

• Terminology navigation
• Data entry/edit
• Cell/range format
• Locking rows columns
• Entering applying formulas
• Sorting techniques