Blank Price Charts by knl21081


More Info
									                                   Using Lists…

Further Adventures in Excel 2000                  1
     Using Lists

          This lesson will cover:
             • What is a List?
             • How to:
                  • Sort Data in a List
                  • Find Data in a List…the Art of Filtering
                  • Create List Subtotals
                  • Summarize List Data…Pivot Tables

              These topics are from Chapter 8 of the text...

Further Adventures in Excel 2000                               2
     What is a List?

A List is a series of rows that contain similar data…we’ve
been using lists in many of the sample worksheets. Each row
is like a record in a database; as the data in each row pertains
to a specific item. Therefore, rows in a list are called records
and each column is called a field, just as with databases.

Lists are used as simple databases in Excel and can be
manipulated in several ways such as by sorting, filtering and
summarizing (as with Pivot Tables). The next slide shows
some of the guidelines that Microsoft suggests when using a

Further Adventures in Excel 2000                                   3
Guidelines for Creating a List
* According to the Excel help system

    Use only one list per worksheet - Some list features, such as filtering, can be used on
    only one list at a time.
    Put similar items in one column - Design the list so that all rows have similar items in
    the same column.
    Keep the list separate - Leave at least one blank column and one blank row between the
    list and other data on the worksheet. Excel can then more easily detect and select the list
    when you sort, filter, or insert automatic subtotals.
    Position critical data above or below the list - Avoid placing critical data to the left or
    right of the list; the data might be hidden when you filter the list.
    Show rows and columns - Make sure any hidden rows or columns are displayed before
    making changes to the list. When rows and columns in a list are not showing, data can be
    deleted inadvertently.
    Use formatted column labels - Create column labels in the first row of the list. Excel
    uses the labels to create reports and to find and organize data.
    Use cell borders - When you want to separate labels from data, use cell borders — not
    blank rows or dashed lines
    Avoid blank rows and columns - Avoid putting blank rows and columns in the list so
    that Excel can more easily detect and select the list.

  Further Adventures in Excel 2000                                                                4
                                   Unique column names
                                   contained within a single cell
                                   (No merged cells) in the first

                                   No Empty Cells…

                                   No Hidden rows or columns

                                   Only One List per worksheet

                                   A series of rows with similar
                                   data…this must be a list.

Further Adventures in Excel 2000                             5
     Sorting a List

When you sort a list, Excel rearranges rows according to the contents of a
column you choose. The main types of sorting are:

Ascending sort - To arrange a list alphanumerically using the data in one
column, you can specify an ascending sort order (0 to 9, leading spaces,
punctuation, then A to Z).

Descending sort - To sort a list in reverse order, use descending (Z to A,
punctuation, leading spaces, then 9 to 0) sort order.

For example, to sort a list of sales in order from the highest to the lowest
value, you would sort the Sales column in descending order.

Further Adventures in Excel 2000                                               6
      Sorting a List
      Default sort orders

Excel uses specific sort orders to arrange data according to the value, not the format,
of the data. In an ascending sort, Excel uses the following order. (In a descending
sort, this sort order is reversed except for blank cells, which are always placed last.)
Numbers - Numbers are sorted from the smallest negative number to the largest
positive number.
Alphanumeric sort - When you sort alphanumeric text, Excel sorts left to right,
character by character. For example, if a cell contains the text "A100," Excel places
the cell after a cell that contains the entry "A1" and before a cell that contains the
entry "A11."
Text and text that includes numbers are sorted in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C
Logical values - In logical values, FALSE is placed before TRUE.
Error values - All error values are equal.
Blanks - Blanks are always placed last.

 Further Adventures in Excel 2000                                                          7
                                   To sort a list, select the Sort
                                   command from the Data

Further Adventures in Excel 2000                                8
                                   This brings up the Sort dialog
                                   box. Here you can add the
                                   conditions to your list
                                   sort…this example selects a
                                   list sort by “Boat Size” in
                                   Ascending order…no other
                                   criteria were set…

Further Adventures in Excel 2000                             9
                                   Remember you can use the
                                   What’s this? help to explain
                                   any confusing items…here I
                                   selected the help for the “My
                                   List has” sort option…it
                                   seems a bit confusing, but the
                                   help explains it well, we don’t
                                   need to change this…since
                                   this list has a Header row.

Further Adventures in Excel 2000                             10
                                   When you click OK on the
                                   Sort dialog with the options
                                   set to sort by the Boat Size
                                   column in Ascending order,
                                   you get a list that is now
                                   ordered just that way…from a
                                   smaller Boat Size to a larger
                                   one as you move down the
                                   list…you can always Undo
                                   the sort by using the Undo

Further Adventures in Excel 2000                           11
                                   Now we can try a sort with a
                                   few more criteria…using the
                                   list as it was previously
                                   sorted, we now select sort by
                                   Year Built (descending) and
                                   Price (descending)…the first
                                   criteria Boat Size (ascending)
                                   was already selected for us…

Further Adventures in Excel 2000                             12
                                   Clicking the OK button now
                                   sorts the list:
                                   • Boat Size (ascending)
                                   • Year Built (descending)
                                   • Price (descending)

                                   Look at the rows in the 44’
                                   (foot) column to see how each
                                   row was sorted…

                                   …first the rows were sorted
                                   by Year Built, and then within
                                   each Year by Price, both in
                                   descending values…

Further Adventures in Excel 2000                            13
     The Art of Filtering…

Filtering is a way to find and work with a subset of data in a list. A filtered
list displays only the rows that meet the criteria you specify for a column.
Unlike sorting, filtering does not rearrange a list. Excel provides two
commands for filtering lists:

•AutoFilter - is a filter by selection, for simple criteria. Only data that
matches the criteria is displayed, the rest of the list is hidden.
•Advanced Filter - uses more complex criteria and can actually copy the
results of the filtering process to another location.

When Excel filters rows, you can edit, format, chart, and print your list
subset without rearranging or moving it.

Further Adventures in Excel 2000                                            14
                                   To use the AutoFilter feature
                                   select the Filter command
                                   from the Data menu, and then
                                   the AutoFilter command…

Further Adventures in Excel 2000                           15
                                   The AutoFilter command places
                                   dropdown list boxes next to all
                                   the column headers. These
                                   dropdown lists are for selecting
                                   the filter criterion. For the first
                                   demonstration we’ll select 1981
                                   from the Year Built column…

Further Adventures in Excel 2000                                 16
                                                   This criterion “filters out” all
                                                   records that do not have a Year
                                                   Built equal to 1981. The rest of
                                                   the records are temporarily

                                   Notice that there were 30 records
                                   found with the Year equal to 1981 out
                                   of the total of 59 records.

Further Adventures in Excel 2000                                               17
                                   To remove the AutoFilter
                                   uncheck the box next to the
                                   command, or, to restore all the
                                   records and use another filter on
                                   them, all click the Show All

Further Adventures in Excel 2000                                18
                                   After returning to the original
                                   recordset (or list) by selecting the
                                   Show All option, we’ll try a
                                   Custom AutoFilter…select
                                   Custom from the dropdown
                                   menu in Price…

Further Adventures in Excel 2000                                 19
                             Using the Custom AutoFilter dialog you can select
                             from all of the the comparison operators on the left…

Further Adventures in Excel 2000                                               20
                         …and select the valid values for comparison from the list
                         on the right…

Further Adventures in Excel 2000                                              21
       Here the completed Custom AutoFilter says:

       Select all of the records with Prices that are less than
       $260,000 and greater than $65,000…

Further Adventures in Excel 2000                                  22
                             The filter is complete (38 records selected), but it is
                             hard to see that the records were selected as mentioned
                             in the previous slide…

Further Adventures in Excel 2000                                                23
                                   …so we sort the records…now
                                   you can see that the lowest Price
                                   is $69,900 which is the first Price
                                   greater than $65,000…the highest
                                   Price is not displayed in the
                                   window, but trust me it is

Further Adventures in Excel 2000                                  24
     Creating List Subtotals…

Excel can automatically summarize data by calculating subtotal and grand
total values in a list. To use automatic subtotals, your list must contain
labeled columns and the list must be sorted on the columns for which you
want subtotals. Basically you need to have a correctly designed list that is
sorted according to your desired “sub” categories.

For example, if you have a list of sales by salespeople that have a region as
a field in each record; and, you want the region’s sales subtotals with a
listing of the salespeople you would sort by region before applying any
subtotals. This groups all of the records by region and is necessary for
Excel to provide subtotals.

When you insert automatic subtotals, Excel outlines the list by grouping
detail rows with each associated subtotal row, and grouping subtotal rows
with the grand total row.

Further Adventures in Excel 2000                                          25
                                   …so we first need to sort the
                                   records…for this demonstration
                                   will we use the yacht’s
                                   location…it doesn’t matter for the
                                   subtotals, but we will sort in
                                   ascending order.

Further Adventures in Excel 2000                                 26
                                   …now that the records are sorted
                                   (or grouped) we can apply the
                                   subtotals to them…from the Data
                                   menu select the Subtotal
                                   command…this displays a dialog
                                   box like the one to the left.

                                   We need to subtotal by Location,
                                   so we select that for the “At each
                                   change in” option (which means
                                   subtotal when there is a new

                                   Then we want to see the Sum
                                   (“Use function”) of Prices (“Add
                                   subtotal to”)…now click OK…

Further Adventures in Excel 2000                                27
                                   …the subtotals have been added,
                                   but the worksheet layout has a
                                   slight problem with the column
                                   widths, however…

Further Adventures in Excel 2000                             28
                                   …which is easily
                                   remedied…now you can see
                                   there are subtotals at the end
                                   of every change in the yacht
                                   location groupings…at the
                                   end of the list there is also a
                                   grand total. To view a brief
                                   listing of locations and their
                                   subtotals click on the 2
                                   button in the upper left

Further Adventures in Excel 2000                             29
           …now you have a brief listing…subtotals by location…you
           can expand this listing to the full version by clicking the 3
           button to expand all the locations or just expand one location
           at a time by clicking the + buttons to the left of each location.

           To view the grand total only, click the 1 button…

Further Adventures in Excel 2000                                               30
                                   …this is the highest level of calculation, thus
                                   the number 1 designation on the button, each
                                   lower level shows more detail…clicking on
                                   the + button expands the detail to the next
                                   level (again)…

Further Adventures in Excel 2000                                              31
                                   …and back and forth you can go…a subtotal
                                   view can be printed if need be, and makes a
                                   good Ad Hoc report…

Further Adventures in Excel 2000                                                 32
                                   …to remove the subtotals, select
                                   Subtotals from the Data menu
                                   and click the Remove All button
                                   at the bottom of the dialog box…

Further Adventures in Excel 2000                              33
     Summarizing List Data…
     Pivot Tables
A PivotTable report is an interactive table that you can use to
quickly summarize large amounts of data. You can rotate its
rows and columns to see different summaries of the source
data, filter the data by displaying different pages, or display
the details for areas of interest. You can create a PivotTable
report from an Excel list, an external database (Access or
Oracle, for example), multiple Excel worksheets, or another
PivotTable report.

But you really need to see one to understand it…

Further Adventures in Excel 2000                              34
                                   …to create a Pivot Table
                                   select the Pivot Table and
                                   PivotChart Report
                                   command from the Data
                                   menu…this action will
                                   display the Pivot Table
                                   and PivotChart Wizard as
                                   shown here…for this
                                   example we’ll choose to
                                   use an Excel List or
                                   Database as the source
                                   data…and select the
                                   PivotTable option for the
                                   “Kind of Report”…then
                                   we click Next…

Further Adventures in Excel 2000                      35
                                   …Step 2 of the wizard
                                   asks for the data to be
                                   analyzed…since it has
                                   already selected all of our
                                   list (note the list data is
                                   surrounded by the dotted
                                   line) we click Next…

                                   …selected data lies
                                   within this boundary…

Further Adventures in Excel 2000                       36
                                   …Step 3 of the wizard
                                   asks for the location of the
                                   new PivotTable…a new
                                   worksheet is always a safe
                                   bet…so, we click Finish to
                                   display the PivotTable
                                   …there are other features
                                   that can be accessed from
                                   this step (such as Layout
                                   and Options), but they are
                                   not needed for this
                                   example, and do require a
                                   little more skill to use

Further Adventures in Excel 2000                       37
                                   …we now have been moved to a
                                   new worksheet (named Sheet1)
                                   that has a PivotTable layout on it.
                                   The PivotTable toolbar is now
                                   displayed…from the toolbar you
                                   drag fields to the appropriate
                                   locations on the PivotTable’s
                                   layout…for example dragging
                                   the Boat Size field to the “Drop
                                   Column Fields Here” cell and
                                   the Location field to the “Drop
                                   Row Fields Here” cell will
                                   change the layout to…

Further Adventures in Excel 2000                               38
                                   …a skeleton of the PivotTable…
                                   you can tell from this layout that
                                   the table rows will represent the
                                   Locations data and the columns
                                   will represent Boat Sizes data.
                                   What we are setting up is a view
                                   of the list’s data from another

Further Adventures in Excel 2000                               39
                                   …the arrows (which look suspiciously
                                   like AutoFilter arrows) are actually
                                   AutoFilter dropdown lists, just as are
                                   displayed when you use the AutoFilter
                                   feature…you select and deselect those
                                   field entries you want to view in the
                                   PivotTable. Here we have a list of
                                   every unique Location entry…

Further Adventures in Excel 2000                                   40
                                   …now drag the Price field to the
                                   corner cell between the Location
                                   and the Boat Size…once you
                                   have dropped the Price field into
                                   place you have a Pivot table that
                                   shows the Prices of each Boat
                                   Size by Location, or you could
                                   look at it as what Boat Sizes are
                                   at what Location and what are
                                   their Prices…it all depends…

                                   …but the number format is not
                                   right, so we need to format the

Further Adventures in Excel 2000                              41
                                   …as usual there is more than one
                                   way to access the Field Settings
                                   dialog box…

                                   …you could right-click on the
                                   cell (Sum of Price) and select
                                   Field Settings from the shortcut

Further Adventures in Excel 2000                              42
                                   …or click the PivotTable button
                                   on the toolbar…either way you
                                   get the Field Settings dialog

Further Adventures in Excel 2000                             43
                                   …from here you can change the
                                   settings of the currently selected
                                   field…here the Sum of Price
                                   field was selected so you can
                                   change the calculation for that
                                   field…say from Sum to
                                   Count…or Average…you can
                                   also change the number format,
                                   which really needs to be done for
                                   this example…to do this click
                                   the Number button to see the
                                   Format Cells “Number” dialog
                                   box…change the format to
                                   currency in dollars and its

Further Adventures in Excel 2000                              44
                                   …you can add fields (like
                                   the Year Built field) to add
                                   new dimensions to the
                                   table…pay attention to the
                                   layout lines…they tell you
                                   whether the field you are
                                   placing will be a row or
                                   column item…Year Built
                                   has been added here as a
                                   new row.

                                      These blue lines are the
                                      table layout borders…

Further Adventures in Excel 2000                          45
                                   …selecting a cell that is a
                                   header for a group of cells
                                   and clicking the Hide Detail
                                   button on the PivotTable
                                   toolbar will cause the detail
                                   lines to condense into one
                                   summary line for the

Further Adventures in Excel 2000                          46
                                   …as shown here.

                                   There are many ways to
                                   view the data with
                                   PivotTables, and some are
                                   worthless, just as with
                                   charts. The trick is to know
                                   what ideas you want your
                                   data to convey, and create a
                                   table to that end.

Further Adventures in Excel 2000                         47

To top