Lists

1
Lists

• Things to do

2
A List

• A List
• A collection of similar data stored in a
  structured manner (rows / cols)
• Typically
• A row of data contains a single entry / record
• Should not be confused with a database
• Although similar to database in the types of
  information stored

3
How to Design a List

• Construct a data definition table
• Serves to document the spreadsheet
• Serves to clearly define the data itself
• Should occur in a separate documentation sheet

4
List from Data Definition

• Each field name in the data definition becomes a
  column header
• Column headers should be formatted reasonably
• List must have empty cells around the edges
• Except if at top and/or fully to the left
• This allows Excel to automatically compute data
  range
• Each row is a record of data

5
Identify the elements of an Excel list

• An Excel list is a collection of rows and columns
  that contain similar data.
• In a worksheet, each column represents a field of
  data and each row represents a record of data.
• The first row of the list always contains the
  name of the fields and is called a field header
  row.

6
An example of an Excel list
7
Freeze rows and columns

• Scrolling through large amounts of data in a
  worksheet can move data off the screen.
• Portions of the data can be always displayed
  column heading for example. This is called
  freezing.
• Can freeze a header
• Select View tab and then freeze panes.
• Select the Freeze top row
• Can also freeze left column
• Can split the view into sub-windows
• Select View tab and then Split option
• The window will split into four panels at the
  active cell point

8
Sorting

• When sorting
• Entire records are sorted based on a key (or
  multiple keys)
• Only the range selected is included in the sort
• Must always be careful to select all columns that
  are part of a record

9
Consider

• Sort the vehicle table by year by odometer
  reading.
• What happens in the following case (Select column
  e)?

10
List Filtering

• A range can be converted to a list
• Select range
• Choose Filter
• Drop-down menus are created to
• Sort (ascending / descending)
• Show top N items
• Filter based on criteria

11
List Filtering
12
Filter data in a list using AutoFilters

• Sometimes you will want to see a portion of the
  records instead of all of them.
• The processing of displaying only those records
  that meet some criteria is called Filtering.
• When data in the list is filtered, records that
  do not meet your criteria are hidden.
• These records are not removed from the list and,
  therefore, can be redisplayed by removing the
  filter
• Simple filters can be specified by clicking the
  list arrow on any field name cell.
• More complex filters must be created using the
  Custom AutoFilters option.

13
Autofilter

• Autofilter shows only those records matching the
  selected criteria
• Each columns criteria are ANDED together
• Example Show all ford trucks with over 50000
  miles on the odometer

14
Apply conditional formatting to a range

• There are times when you will want data to have a
  different appearance if it meets some criteria.
• For example, you might want data to appear in
  red, if the data is more than six months old
• Or, you might want a value to be black if it is
  positive and red if it is negative
• This kind of formatting is called conditional
  formatting.
• You specify the condition under which you want
  the formatting to take place and what the
  formatting should be.

15
Lookup Tables

• A lookup table is related to a list
• A lookup table contains data that is accessed by
  some key
• Use the following table for C-S 001
• John Bunyan gets a 73.5 in the course, what
  grade does he receive?
• What is the key?
• What is the data to be accessed?

Image from http//www.skeletonkey.org/skeleton-ke
y/images/skeleton_key.jpg
16
Lookup Tables
What does this table mean? Any score between 0
up to 60 receives an F Any score between 60 up
to 70 receives a D Any score between 70 up to 80
receives a C Any score between 80 up to 90
receives a B Any score above 90 receives an
A Even though the number 73.5 doesnt appear in
the table, it is in the table implicitly since
each key entry represents a range of values.
17
Lookup Tables
What does this table mean? Any department
between MIS up to C-S has REMUS as a chair? This
table doesnt have ranges but exact
correspondences
18
Lookup Example

• Complete columns
• D Compute total course average
• E Figure out the students grade from the Grade
  Policy information

What to enter into D15?
AVERAGE(B15C15)
What to enter into E15?
19
VLookup

• The VLOOKUP function is used to find data in a
  table
• Uses key data to find other data
• VLOOKUP(lookup_value, table, col_index,
  range_lookup)
• Lookup_value The value which is used as the KEY
  into the table
• May be a reference or value
• An error if this number is smaller than the
  smallest table entry
• Table The data table itself. Must be at least
  two columns wide and sorted!.
• Col_index The column in the table containing the
  data of interest
• Range_lookup A value indicating how to interpret
  the table. True for range lookups and false for
  exact lookups
• If range_lookup is TRUE or omitted, an exact or
  approximate match is returned. If an exact match
  is not found, the largest value that is less than
  lookup_value is returned.

My head is spinning
20
Example

• Use VLOOKUP
• If range_lookup is TRUE or omitted, an exact or
  approximate match is returned. If an exact match
  is not found, the next largest value that is less
  than lookup_value is returned.

What to enter into E15?
VLOOKUP( D15, B7C11, 2, TRUE)
VLOOKUP( D15, B7C11, 2)
21
Example

• The Grade Policy Table should be given a name!

What to enter into E15?
VLOOKUP( D15, GRADE_POLICY, 2)
22
Another Example

• An employer wants to maintain information about
  health care costs
• Various group plans available. Each plan
• Has a base cost (paid by employee)
• Has a percentage cost (percent of salary which is
  paid by employee)
• Has a percentage case which is paid by the
  employer

23
VLookup Example
24
Create 3-D cell references and workbook references

• Think of the collections of worksheets in a
  workbook as a third dimension.
• You know that you can reference rows and columns
  in a worksheet.
• You can also reference worksheets. You can,
  therefore, have a reference in a worksheet that
  pertains to a cell in another worksheet.
• The reference Sheet3!A5 is a reference to cell A5
  on Sheet3
• You can place the reference in any cell on any
  worksheet within the workbook.

25
The three dimensions of a workbook
SUM(Sheet1Sheet4!B18)
26
Link workbooks to summarize data

• You can summarize data from several workbooks by
  creating links between them.
• To create a workbook reference
• Click the tab for the workbook that will be the
  destination workbook
• Click in the cell that will receive the data, and
  enter an equal sign (), but do not press the
  Enter key
• Switch to the target workbook, click in the cell
  containing the data to be linked, and press the
  Enter button on the Formula bar
• The formula referencing the source workbook will
  appear in the destination cell

27
Link Workbooks by specifying source and
destination files
28
What Links Look Like

• Formulas with links to other workbooks are
  displayed in two ways, depending on whether the
  source workbook is open or closed.
• When the source is open, the link includes
• the workbook name in square brackets, followed by
  the worksheet name, an exclamation point (!), and
  the cells of the formula.
• For example, the following formula adds the cells
  C10C25 from the workbook named Budget.xls.
• SUM(Budget.xlsAnnual!C10C25)
• When the source is closed, the link includes the
  entire path.
• SUM('C\Reports\Budget.xlsAnnual'!C10C25)

29
Create a workbook template

• In business, you often create workbooks that have
  common elements such as invoices, expense
  statements, etc.
• Using a template makes this process easier
  because the elements are already in place all
  you do is fill them in.
• You can use any of the templates supplied with
  Excel or you can create your own.
• When you design your template, you can include
  formatting and calculations.
• When you open a new workbook with the template,
  the formatting and calculations will be built
  into the workbook.

30
Use pre-built templates
31
An Excel template
32
Store and access templates

• To save a template, use the Save As option on
  the File menu and then change the File Type to
  template.
• When you save a template, it must be saved in the
  Templates folder.
• This makes it possible for Excel to locate the
  template when you are ready to use it again
• You usually don't see the Templates folder in
  Windows Explorer because it is a hidden folder
• Once the template has been saved, it will be
  listed as an icon in the Templates dialog box.

33
Worksheet with formatting and formulas but no data