Data Management in Excel

1
Data Management in Excel

• Disciplined use of Spreadsheets

2
Contents

• Cell References in Excel
• Copying to Adjacent Cells
• AutoFill Lists
• Creating a Custom AutoFill List
• References to other Sheets
• LISTS in Excel
• Forms

• Validation
• Auditing
• Types of Data
• Exchanging data with other applications
• Filters
• Using Labels for numeric data
• Sorting

3
Cell References in Excel

• Relative cell references describe the position of
  a cell relative to the current cell.
• Absolute references - such as A1 tell Excel the
  exact location of the cell.
• When copied, absolute references do not change
  relative references do.

4
Copying to Adjacent Cells

• You can copy a value to an adjacent cell by
  dragging the fill handle.
• You can drag in any direction (not diagonal) and
  all the cells covered will be filled with the
  value in the original cell
• If a formula includes relative references these
  references will change.

5
AutoFill Lists
6
Creating a Custom AutoFill List
7
References to other Sheets

• In formulae you can use references to cells on
  other sheets in the same workbook.
• The syntax for this is Sheet2!A1
• A 3-D reference is a range that spans 2 or more
  sheets in a workbook
• For example SUM(Sheet2Sheet6!A2C5)

8
LISTS in Excel
9
Forms

• When your data is arranged as a list you can
  create a form for data entry.
• The form can be used for
• adding new cases
• deleting cases
• finding cases

10
Validation
11
Auditing

• It is possible to post-validate your data by
  using the Auditing tool.
• Auditing causes a red circle to be drawn around
  values which dont adhere to the validation
  rules.
• Auditing only works on up to 255 cells at a time.

12
Types of Data

• Numerical data
• Be aware that Excel only handles dates after 1st
  January 1900
• Classification criteria are better entered as
  numbers.
• Character data
• Keep them aligned to the left
• Dont enter blanks as the first character in a
  cell.

13
Exchanging data with other Applications

• Excel files are easier to import if data are
  arranged as a list.
• Some packages only read from version 3 or 4.
• Keep variable names to a maximum of 8 characters
• Always check the data once it is transferred.

14
Filters

• Filters display a subset of cases in a list

15
Using labels for numeric data
16
Sorting

• When data are arranged in a list it is easy to
  sort cases according to the values in up to 3
  variables