Using Excel to manage lists by LondonGlobal

VIEWS: 38 PAGES: 26

									UCL
EDUCATION & INFORMATION SUPPORT DIVISION
INFORMATION SYSTEMS




                                           Excel 2003

                                           Using Excel to
                                           manage lists




Document No. IS-022 v4
Contents
Introduction .............................................................................................................................. 2
Entering and manipulating data ................................................................................................ 1
   AutoFill                                                                                                                                   1
Sorting data ............................................................................................................................... 3
   Lists                                                                                                                                      3
   Sorting data                                                                                                                               4
Subtotals ................................................................................................................................... 7
Outlining ................................................................................................................................... 8
   Displaying and hiding detail data                                                                                                          8
   Outlining data                                                                                                                             8
Views........................................................................................................................................ 10
   Defining a view                                                                                                                          11
   Creating a custom view using outlining                                                                                                   11
Conditional formatting ............................................................................................................. 12
Filtering a list ........................................................................................................................... 13
   Filtering a list using AutoFilter                                                                                                        13
   Custom criteria                                                                                                                          14
   Advanced filtering                                                                                                                       16
   Copying filtered data                                                                                                                    18
Data forms ............................................................................................................................... 19
   Moving through the records                                                                                                               19
   Adding records using the data form                                                                                                       19
   Searching for specific records                                                                                                           20
Learning more .......................................................................................................................... 21




UCL Information Systems                                                                                  Using Excel to manage lists
Introduction
This workbook has been prepared to help you use Excel to manage your data, in much the same way as
you would use a simple database. It is aimed at those who have a good understanding of the basic use
of Excel for entering data. It assumes knowledge about moving around a worksheet, formatting cells
and controlling worksheet display and printing. These topics are all covered in the Getting started with
Excel course.
This guide can be used as a reference or tutorial document. To assist your learning, a series of practical
tasks are available.

Training files
If you wish to attempt the exercises contained in the exercise document and you are not using a
training account, it is necessary to download the training files used in this workbook from the IS
Training web site at: www.ucl.ac.uk/is/training/exercises.htm
Full instructions on how to do this are provided there.




Introduction                                                                    UCL Information Systems
Entering and manipulating data
AutoFill
As explained in the Getting started with Excel course, the fill handle can be used automatically to fill a range
of cells. To use AutoFill:
1. Key your data into the first cell of the range.
2. When you select that cell, a fill handle will appear in the bottom right-hand corner.
                                                                                                      Fill handle
3. Click on it and drag it down, or to the right, over the range of cells you want to fill.
4. The data in the first cell will be copied into the other cells.
If the first cell contains numeric data, or days, months, etc, AutoFill will take over and
extend the series across the selected range of cells. If the cell contains a mixture of text
and numbers, AutoFill will extend the numeric component, while copying the text
component.
For example, Month 1 will be copied to Month 2, Month 3, etc.
See the examples below:




If you need to fill cells with numbers you may need to key in the first two numbers as shown below.
Select both cells and then drag over the required range to use AutoFill.




Helpful hints:
If you want to copy data rather than extend the series, hold down Ctrl as you drag the fill handle.
If ever you find that the fill handle is missing, go to Tools|Options and select the Edit tab. Make sure Allow
cell drag and drop is selected.
If the entire row or column is selected, the fill handle is displayed at the beginning of the row or column.




UCL Information Systems                                1                      Entering and manipulating data
Create a custom AutoFill list
You can create a custom AutoFill series from existing items you've listed in a worksheet, or you can
type the list from scratch.
If you've already entered the list of items you want to
use as a series, select the list, go to Tools|Options
and select the Custom lists tab. Existing lists are
displayed in the Custom lists box on the left.
To use the selected list, click the Import button.
The new data will be added to the lists on the left,
and listed in the List entries box.
Click OK to accept the new Custom list.




Creating a new list from the Custom Lists tab
1. Select Tools|Options then select the Custom Lists tab.
2. Click into the Custom lists box on the right.
3. Key in the new list items, beginning with the first entry. Press Enter after each entry.
4. When the list is complete, click the Add button.



Editing or deleting a custom AutoFill list
1. Select Tools|Options and select the Custom Lists tab.
2. In the Custom lists box, select the list you want to edit or delete.
3. To edit the list, make the changes you want in the List entries box and then click Add.
4. To delete the list, click Delete.
You cannot edit or delete the built-in lists for months and days.




Entering and manipulating data                         2                      UCL Information Systems
Sorting data
Lists
Excel’s tabular layout makes it an ideal tool to use for managing lists of data. There are many
commands that you can use to manipulate list data.
An Excel list can be used to:
Search or query to find specific data.
Sort data alphabetically or numerically by rows, in ascending or descending order.
Search for matching criteria with a filter and copy the results to a different part of the worksheet.
Perform statistical calculations on the data for analysis and decision-making.
Print data organised for specific purposes.
It is important, however, that your lists are keyed in properly if these features of Excel are to work.

Guidelines for creating lists
Make the first row of your list the column headings that describe the data.
Never:
use ambiguous words as column headings (e.g. SUM, SUBTOTAL) as Excel may confuse them with
   functions or names that you have defined,
use punctuation or spaces in column headings,
leave a gap between the column headings and the first row of data,
leave blank rows between list data.
Do not mix text and numbers in a column – the data must belong to the same category of information
and, therefore, should be the same data type.
Do not use spaces in front of column entries. Use alignment buttons instead if you need to move data
away from the column edges.
Do not put more than one list in a worksheet. If you want more than one list in a workbook, place
each list on a separate sheet. Filtering can only be used on one list at a time.
Leave at least one blank row and one blank column between the list and other data on the worksheet.
Place additional data diagonally below and to the right of your list. This ensures that data will not be
affected when you filter the list.


Terminology
The data on each row is referred to as a Record.
The data in each column is referred to as a Field.
Each Record in a database must have the same number of Fields.
Each Column must contain the same type of data, e.g. numbers, dates, or text.




UCL Information Systems                              3                                         Sorting data
Column labels
A label is the title at the top of each column, describing the category of information which it contains.
Each label name must be unique and made up of text rather than numbers or calculations.
The column names must appear directly above the list information – they may not be separated from
the rest of the list by a blank row.
Format your column labels to distinguish them from the list data.
The column labels at the top of the list can be two rows providing the labels are formatted differently
to the list as regards font, alignment, colour etc.


Sorting data
Although not confined to database information, the sorting facility in Excel is particularly appropriate
for changing the order in which records are listed. It is a good idea to save the file prior to sorting.
If you are likely to need to restore the original record order, it is a good idea to include a column of
record numbers before sorting the data. This can be achieved by adding a column with a suitable
heading, and using the fill handle or the data series command to enter consecutive numbers adjacent to
each record.


Simple sort using the toolbar
Sorting rows
To sort rows in ascending or descending order based on the contents of one column:
1. Click in the column you want to sort on. (Do not
   select anything.)
2. Click the Ascending or Descending sort button
   on the toolbar.
The data will be sorted based on the active column, i.e.,
the data in each row will stay together. In the example
opposite, Frank McBride will still be the Sales Person
for New York after the City column has been sorted.

Sorting one column only
You can sort data in a list in ascending or descending order. The Standard toolbar has buttons that you
can use to specify the sort order, but you need to specify which column contains the data that you want
to sort by.
To sort one column only (leaving the other columns where they are):
1. Select the data in the column you want to sort.
2. Don’t include the column heading.
3. Click the ascending or descending sort button on the toolbar.
The column will be sorted without moving any of the data in the
other columns. In the example opposite, after the City column is
sorted, it could end up in a different county!




Sorting data                                         4                         UCL Information Systems
Sorting selected data
Select the data you want to sort.
Do not include the column headings!
The active cell will be visible within the selection as the white cell.
Move the active cell across to the column that you want to sort by
using the Tab key. If you need to move back, use Shift+Tab.
To sort the selected rows in ascending order using the active cell column, click the Ascending
button on the Standard toolbar.
Or
To sort the selected rows in descending order using the active cell column, click the Descending
button on the Standard toolbar.
Only the selected rows will be sorted according to the data in the active column.


Sorting by more than one column
Where you have lists with lots of columns and rows, you
may need to sort by more than one column. For example,
the list of cars shown here has been sorted by Make, within
the Make grouping you may want to sort by Model and
then by Mileage.


To sort on more than one column:
1. Click in the middle of the list you want to sort.
2. Select Data|Sort. The Sort dialog box will appear.
3. Excel recognises the column headings from your list and allows you to
   pick them from drop-down list boxes in the dialog box. Choose the
   main sort order for your data by clicking the drop-down list arrow to
   the right of the Sort by list and picking the column you want to use.
4. Set Ascending or Descending order for the sort by clicking the
   relevant option.
5. Choose the next column that you want to sort by from the Then by
   lists and click the Ascending or Descending option.
6. Click OK to perform the sort.
When using the Sort tools, Excel can sort using up to three columns in any one sort operation. If your
data is very complex, you could carry out a sequence of sorts starting with the least important column
and ending with the most important one to achieve the desired results.




UCL Information Systems                                5                                   Sorting data
Custom sorting options
The ascending and descending sort orders rearrange your list by alphabetical, numerical, reverse
alphabetical or reverse numerical order. For some types of data, such as months, this may not be the
order that you need to use. You can use one of the custom sort orders provided with Excel to
rearrange your data in chronological order by day of the week or by month.
To use a custom sort:
1. Place the active cell within the list.
2. Select Data|Sort.
3. From the Sort by drop-down list, select the column by which you want
   to sort.
4. Click on Options.
5. From the First key sort order drop-down list, select a custom sort
   order.
6. Click on OK to return to the Sort dialog box and OK again to perform the sort.


Helpful hint:
You can also sort a list according to a custom list that you have created yourself. See page 2 for details of
creating custom lists.




Sorting data                                           6                           UCL Information Systems
Subtotals
Excel can automatically add subtotals to a list of data. Your subtotals can sum numeric data or find
highest and lowest values and averages. You can also count the number of rows in a group.

Organising the list for subtotals
When you issue the subtotals command Excel will
need to know where to put the subtotals. In this
example, if you wanted to see subtotals for the Sales
for each Country, you would first need to sort the
list into Country order. Similarly, if you wanted to
see how much each sales person had sold, you
would sort the list by Sales Person before adding the
subtotals in.

Adding subtotals to a list
Once the list is sorted, choose Subtotals from the Data menu.
In the Subtotal dialog box, click the drop-down list arrow to the right of
the At Each Change in: box to display your column headings. Pick
the heading according to the column you have used to group the data.
Click the drop-down list arrow to the right of the Use Function box to
display a list of functions you can use to summarise data in your list.
Click the function that you want.
Use the up and down arrows in the Add subtotal to list to set which
columns you want to add the subtotals to. Check the box next to the
column to subtotal. You can add subtotals to more than one column.
Set options for the new subtotals using the check boxes at the bottom of the dialog box.
Replace Current Subtotals ensures that the new
subtotals overwrite any existing ones.
If you want each subtotalled group on a separate
page, check Page Break Between Groups.
Summary Below Data adds subtotals and an over-
all total below rather than above the data groups.
Click OK to apply the subtotals.
In this example the list was first sorted by Country.
Subtotals was selected from the Data menu and the
details in the Subtotal dialog box were completed as shown above.
When the OK button was clicked, the subtotals were added for each Country as shown.




UCL Information Systems                              7                                         Subtotals
Outlining
An outline is worksheet data in which rows or columns of detail data are grouped so that you can
create summary reports. The outline feature can summarise either an entire worksheet or a selected
portion of it.
When working on large worksheets, it is useful to hide the parts of the worksheet that you are not
currently using. The outline feature enables you to hide parts of a worksheet to make it easier to see the
more important parts. This is especially useful when different users need to see different information.
When data is in list form, Excel can create an outline to let you hide or show levels of detail with a
single mouse click. An outline lets you quickly display only the rows or columns that provide
summaries or headings for sections of your worksheet, or display the areas of detail data adjacent to a
summary row or column.
Note: Only one outline can be created for each worksheet.


Displaying and hiding detail data
An outline can have up to eight levels of detail, both
horizontally and vertically, with each inner level providing
details for the preceding outer level. In the following example,
the row containing the grand total of all the rows is level 1, the
rows containing totals are level 2, and the detail rows for the
countries are level 3.                                                  Outline
                                                                        symbol
When an outline has been created various buttons and bars appear on the worksheet.
The Show Detail symbol indicates hidden rows or columns. Clicking on the button will expand the
levels.
The    Hide Detail symbol hides rows or columns in an outline level when clicked.
Row and column level buttons,            , indicate the number of levels in an outline. When clicked on,
they display a specific level.


Outlining data
There are various ways to create an outline in Excel.
Outlining a worksheet automatically
This is the easiest way to create an outline. As long as the data is
organised so that summary rows refer to cells above or to the left, and
you have summarised data by using formulae that contain functions,
e.g. SUM, MAX, etc., the Auto Outline can be used. (A summary
row is one that contains formulae, e.g. Totals or Subtotals.)
To create an Auto Outline:
Select Data|Group and Outline|Auto Outline.




Outlining                                             8                        UCL Information Systems
Outlining a worksheet manually
If the data is not organised so that Excel can outline it
automatically, you can create an outline manually. For example,
you will need manually to outline data if the rows or columns of
summary data contain values instead of formulae, such as in the
example opposite. If you want to hide the detail rows for January
and February, you can do so by outlining the list manually.
Because the totals in this list were typed as numbers, not
calculated as formulae, automatic outlining would not work.
To create a manual outline:
1. Make sure that the summary rows or columns in all areas that you want to outline are located in the
   same direction relative to the detail data. That is, all summary columns must be either to the right
   or to the left of the detail data, but not mixed. All summary rows must be either below or above,
   but not mixed.
2. Select the rows or columns that contain detail data.
3. Detail rows or columns are usually adjacent to the row or column that contains the related
   summary data. For example, if Row 6 contains totals for Rows 2 – 5, select Rows 2 – 5.
4. Select Data|Group and Outline|Group.
5. Select either Rows or Columns and click OK.
6. The outline symbols appear beside the group on the screen.
7. Continue to select detail rows or columns and click the Group command
   until you have created all of the levels you want in the outline.

Changing the direction of the summary group
Excel assumes by default that summary rows are below detail rows and summary columns are to the
right of detail columns. If the summary rows or columns are otherwise,
you can change this setting before you outline the data.
1. Select Data|Group and Outline|Settings
2. Change the check boxes in the Direction group and click OK.

Display or hide outline symbols without removing the outline
1. Select Tools|Options, and then click the View tab.
2. To display the outline symbols, select the Outline symbols check box.
3. To hide the symbols, clear the check box.
If you hide the outlining symbols, you will need to use the options available
on the Data|Group and Outline menu to do any further outlining in your
worksheet.
Helpful hints:
The keyboard shortcut Ctrl+8 can be used to toggle the outlining symbols on and off.
When you print a view created with the outline feature, the outlining symbols do not print.

Clearing an outline
To remove an outline from your worksheet:
Select Data|Group and Outline|Clear Outline.
Warning
You will not be able to use the Undo feature to restore your outline. You will need to recreate it if you change
your mind. If in doubt, hide the outline as explained above, rather than remove it.

UCL Information Systems                                9                                             Outlining
Views
Creating Custom Views allows you to save different display and print settings, and impose them quickly
and easily on the worksheet at any time. The settings which can be saved include print settings, row
heights and column widths, display settings, selected cells, window size and positions, settings for panes
and frozen titles. This can be advantageous when dealing with large worksheets where switching from
one area to another might otherwise be awkward. It also allows a number of different print settings –
including print areas – to be saved as part of the same worksheet file.


Example of views
In this example, Income, Costs, Salaries, Total Costs
and Profits data can be seen at the top of the screen,
with an embedded column chart underneath. There is
also an embedded pie chart, which, at the moment,
can only be seen by scrolling down the spreadsheet.
It would be useful to be able to "swap" between the
column chart and the pie chart whilst still being able
to see the spreadsheet figures. It may also be helpful
to define different page settings, depending on
whether the column chart or pie chart is being
printed. By defining different spreadsheet views, it is
possible to toggle between the different charts and
keep the data on the screen at all times. It is also
possible to print a different header when a different
type of chart is displayed.




                                                  Notice in this view of the data, rows 7 – 22 are
                                                  hidden.




Views                                               10                         UCL Information Systems
Defining a view
Before defining a view, you should ensure that the display options, zoom percentage, print settings etc.
are as you wish to record them.
1. Select View|Custom Views. The Custom Views dialog box
   will appear:
2. From the Custom Views dialog box select Add.
3. Choose whether or not to include print settings or hidden
   rows and columns as part of the view by checking the
   required options.
4. Enter the name under which this view is to be saved and click OK.
Once a view has been defined, the display and print settings of
the worksheet can be changed (for example, in our typical model
you may want a view to display the pie chart next to the data
with an appropriate header when printing). You can then set up
a View that would save those settings.


Showing a view
Having defined as many views as are needed for the current worksheet, you can switch between them.
Select View|Custom Views, and then highlight the name of the desired view.
Click on the Show button. If the Print or Print Preview commands are executed, the correct settings
(including the header) will be applied to each view.


Deleting a view
Select View|Custom Views, and then highlight the name of the view you want to delete.
Click the Delete button.


Creating a custom view using outlining
If you want regularly to view you data with certain levels hidden or displayed, you can use the Outlining
feature and create a Custom View. (See page 8 for details of the Outlining feature.)
1. Display your data as you want to view it, i.e., create your outline, and collapse or expand the
   relevant groups.
2. Select View|Custom Views and then click the Add button.
3. Type Outline view and ensure the Print settings and Hidden rows boxes are checked.
4. Click OK and save your amended document.




UCL Information Systems                             11                                               Views
Conditional formatting
There will be times when you want to use formatting (i.e. the appearance of text in a cell) to highlight
values in a particular range. For example, you might want to show an overdrawn balance in a financial
worksheet in red, or you might want to highlight failed exam marks by displaying them in bold. Excel
allows you to do this using conditional formatting in which cell values are checked automatically
against user-defined criteria.
1. Select the cell(s) to which you wish to apply the conditional formatting.
2. From the Format menu, choose Conditional Formatting. The Conditional Formatting dialog box
   displays.




3. In the Condition 1 area you can specify that the cell value must satisfy one of a
   range of conditions using the drop-down list.
4. Enter the required value(s) in the box(es). Note that you can use greater than, less
   than etc. with character as well as with numeric data. Greater than “E” would
   return strings of text starting with F or later in the alphabet.
5. To define the format, click the Format button to display the standard Format Cells dialog box, and
   choose an appropriate font colour, style and cell background.
6. Click OK to return to the Conditional Formatting dialog box. A preview of the formatting choices
   you have made will appear.
7. If you want to define more conditions click Add>> to insert another row. You can add up to three
   conditions to the same cells.
When you have defined all the conditions you require, click OK to apply the conditional formatting
rules. You should test the formatting by entering an appropriate value into the relevant cell.




Conditional formatting                              12                          UCL Information Systems
Filtering a list
Filtering is a quick way to find records in a list that match search criteria. Only the rows that match are
displayed.
There are two ways to filter a list, AutoFilter for simple searches and the Advanced Filter. When a
list is filtered, the worksheet is placed in Filter mode. When in Filter mode, the list can be edited,
formatted, made into a chart and printed without rearranging or moving it.
When you use the AutoFilter command, drop-down list arrows are displayed
next to each of the column labels in the list. When you open a drop -down
list, a list of all the unique entries for that column is displayed. By selecting
one of the entries, called a filter criterion, you instruct Excel what to search for. Then Excel filters the list
so that only the sets of data that contain the entry you selected will be displayed.
When Filter mode is active, arrows for the columns with filter criteria selected appear in blue on the
worksheet, row numbers appear in blue and the Status Bar displays either the number of rows that
meet the criteria, or the text Filter mode. The sets of data that do not meet the criteria remain in the list
but they are hidden.
If you select a single cell in the list before choosing Data|Filter|AutoFilter, drop-down list arrows
are applied to all of the column labels in your list. If you select multiple column labels before choosing
Data|Filter|AutoFilter, drop-down list arrows are displayed only for the selected columns, thus
restricting which columns you can apply filters to. In either case, the entire list is filtered. You can
filter only one list at a time in a worksheet.


Filtering a list using AutoFilter
1. Place the active cell anywhere within your list.
2. Select Data|Filter|AutoFilter. Your lists column labels will appear with drop-down list arrows to
   the right.
3. Click the drop-down arrow for the column by which you want to filter, to
   display the unique values from that column. Select the value you are filtering
   for.
4. Repeat step 3 until you have set filter criteria for all columns that you wish to
   filter by.
The list will show only those rows that match your criteria.
If you print the worksheet whilst a filter is active, only visible rows will be output, so you can print out
multiple views of your data from a single list.

Removing a single column filter
You can see which columns have filter criteria active because the drop-down list arrows
are blue. Click the drop-down list arrow for the column whose criteria you wish to
remove.
Select All at the top of the list.

Removing all column filters
Select Data|Filter|Show All.




UCL Information Systems                                13                                         Filtering a list
Custom criteria
The AutoFilter only allows you to select one filter criterion at a time. The Custom filter criterion
enables you to filter a list to display sets of data that contain either of two unique entries in a column.
This creates an Or or an And condition.
Or – to meet the filter criteria, a set of data must meet either the first or the second filter criterion.
And – to meet the filter criteria, a set of data must meet both the first and the second filter criteria.


Either/or custom criteria
1. Click on the AutoFilter drop-down for the desired column.
2. Select Custom.
3. In the Custom AutoFilter dialog box, from the first criterion
   drop-down list, select one of the filter criteria. (The default
   operator is equals).
4. Click on Or or And.
5. From the second operator drop-down list, select a
   comparison operator.
6. From the second criterion drop-down list select the other filter criterion.
7. Click OK. The filtered list shows the sets of data that meet either the first or the second specified
   criterion for the column.


Using custom criteria to find a range of values
You can also use the custom criterion option to find values that fall within a range. When you specify
custom criteria, select a comparison operator from the drop-down list and then either type in a value or
select it from the criteria drop-down list.
When you use custom criteria, you need to understand the comparison operators that Excel offers you.
These will be shown as text, but in advanced filters you will need to work with symbols. The table
below outlines these:
Operator           Meaning
=                  Equal to
>                  Greater than
<                  Less than
>=                 Greater than or equal to
<=                 Less than or equal to
<>                 Not equal to
1. Click the drop-down arrow for the column label whose range of values you want to filter by. This
   will typically be numbers or dates.
2. Click Custom. From the resulting dialog box, select the comparison operator to control the lower
   limiting value, for example less than or less than and equal to.
3. From the first criteria drop-down list, select a value or type the value in and click OK.
     Or     Choose an upper limiting value by selecting a comparison operator from the second
            operator drop-down list and selecting a second criterion from the second criterion drop-
            down list, or typing one in.
4. Click OK.

Filtering a list                                      14                           UCL Information Systems
Wildcards
You can use wildcards to search for sets of data in a list that have certain text in common within the
unique entries, even though the entire entry might not match. For example, searching for all of the sets
of data that have entries in the last-name column that begin with “A” might display two Andrews
(where the entire entry matches) but might also display Andrews and Aston (where the entire entry
does not match).
Wildcard            Finds                                                  Example
* asterisk          Any set of characters that are in the same position    And* finds Andrews and
                    as the asterisk                                        Anderson
? question mark     Any single character that is in the same position as   Sm?th finds Smith and
                    the question mark                                      Smyth
~ tilde             A question mark or an asterisk                         Who~? finds the text
                                                                           “Who?”




Filtering a list using wildcards
Wildcards only work when filtering columns containing text.
1. With AutoFilter active, select the drop-down list
   arrow to the right of the column you want to use to
   filter the data and select Custom.
2. Ensure that the operator is set to equals.
3. Type the pattern of letters you are filtering by with
   the asterisks and/or question marks inserted in the
   appropriate positions.
4. Click OK.


Switching off AutoFilter
When you no longer need to filter your data, you can switch the AutoFilter off in the following way:
1. Select Data|Filter.
2. The AutoFilter option on the submenu will appear ticked showing that AutoFilter is currently
   active. Select AutoFilter to remove the tick and deactivate the AutoFilter.




UCL Information Systems                              15                                   Filtering a list
Advanced filtering
Sometimes, the filter criteria that you specify with AutoFilter will not yield the necessary results. For
example, you cannot use AutoFilter to filter a list to display the more complex criteria of two separate
AND conditions combined with an OR
condition. To do this, you must use the
Advanced Filter option. This relies on
you setting up and defining a Criteria
Range in the worksheet where the data
to be matched can be entered.


Set criteria
The Criteria Range has to be at least one
column and two rows in size. The first
row must contain the column labels (field names) in any order. It is easier to copy the original labels so
that no mistakes are made. It is not necessary to include all the column labels in the Criteria Range. It
could be restricted to only those labels on which you wished to search, and those labels could be
displayed in a different order. Usually, however, the names are copied complete from the top of the
list.
The second/third blank rows will contain the required match information.
To define the Criteria Range:
1. Copy the required column heading as explained
   above.
2. Select the copied set of column labels and the
   blank row immediately beneath them and name
   the range using the Name|Define option on
   the Insert menu.
3. Name the range Criteria and press Enter.
You do not have to name the cells with the range
name Criteria, but it will ensure that Excel
automatically treats the correct group of cells as the
criteria-carrying cells whenever you use the
advanced filter.


Running an advanced filter
1. Enter criteria in the blank row of the Criteria Range under the
   appropriate column label(s).
2. Ensure that a cell in the main list is selected.
3. Select Data|Filter|Advanced Filter. Excel will highlight the main list and enter the cell
   references into the appropriate boxes.
4. Click OK to accept the ranges, and Excel will hide all the records not matching the search settings.




Filtering a list                                      16                        UCL Information Systems
Removing a filter
To remove the filter, select Filter from the Data menu and then Show All.


Entering search criteria
At the most basic level, either text or numeric data can be entered so that Excel will find those records
where the relevant field exactly matches what you have entered. Excel is not case-sensitive when
matching criteria, so text can be entered in upper or lower case.
It is important to remember to delete old criteria before applying a new filter. Otherwise the
true results of a filter will not be shown.
Excel will find records matching text information entered in the Criteria Range and records where the
initial letters match the specified data. When working with a user-defined Criteria Range, if you wish to
confine filter results to only those records where, for example, the first name is Chris, it would be
necessary to enter the formula ="=Chris" in the Criteria Range under the appropriate column label.
 Criteria entered      Results matched
 Chris                 Chris, Christine, Christopher
 =Chris                Chris


Finding ranges of records
Relative or comparison operators such as < or > can be entered with data. For example, <25 entered
in an age field would limit the view to only those persons whose age was less than 25.
Relative operators     Definition                  Relative operators    Definition
         =             Equal to                            <>            Not equal to
         <             Less than                            >            Greater than
        <=             Less than or equal to               >=            Greater than or equal to

Comparison operators may also be used on fields containing text information. Entering <C in a
surname field would limit the view to only those persons whose name began with either A or B.
Entering the = symbol on its own in a field would allow Excel to find those records where that field
was blank. The <> symbol on its own in a field would identify records where that field was not blank.
Date searches may also be carried out using either exact matches or in combination with comparison
operators. To find records dated earlier than 20th January 2004, you could enter <20/1/04.


Multiple criteria
Hitherto, the Criteria Range has been described as a copied set of field names followed by a blank row,
into which you may enter search specifications under the appropriate column names. You may choose
to enter criteria in the blank row under more than one field name. Entering Warm as the Climate and
Sm?th as the Advisor, for example, would find only those records that met both criteria.
Putting multiple criteria on the same row dictates that the first specification AND all other
specifications must be met in order for Excel to find the record.


Using multiple rows in the Criteria Range
There may be situations where you wish to set more than one criteria, e.g., you may wish to find
Climates that are either Warm OR Hot. In such an instance the Criteria Range can be extended to
include a second row into which you may enter specifications.

UCL Information Systems                             17                                      Filtering a list
Extending the Criteria Range for OR criteria
1. Delete the current Criteria Range name. From the Insert menu, select Name, and then Define.
2. Select Criteria from the Names list inside the dialog box and click Delete.
3. Highlight the entire region to be redefined as the Criteria Range – i.e. the copied set of column
   names and the two rows (or more) immediately below.
4. Select Insert|Name|Define.
5. Type the word Criteria into the name box. And then press Enter.
Entering search specifications in all rows within the range will allow Excel to identify all records
meeting the specifications in either the first OR the second row etc.
The Criteria Range may be extended to include three or more rows of user defined search criteria if
required.
IMPORTANT
To return to using just one row of user defined information in the Criteria Range, select the area to be
included and redefine the Criteria Range. This is important because searching for data when a row in the
Criteria Range has been left blank, will result in Excel finding every record in the database. In effect, you
have asked Excel to find all records where the contents of any field can be anything at all.


Checking the Criteria Range
If you are getting surprising results when you filter your data, it may be because your Criteria Range
contains unlabelled cells or extra rows that you thought you had removed from the range.
It is easy to double-check the currently defined Criteria Range at any time by making use of the range
name which Excel applies to it. Pressing the F5 function key displays the Go To dialog box showing all
the currently named ranges on the worksheet. Click on the name Criteria and choose OK. The area
defined by that name will be highlighted. You may choose to alter the selection and redefine the
Criteria Range to adjust it.


Copying filtered data
You can use the Data|Filter|Advanced Filter command to copy the sets of data that meet the
criteria in the Criteria Range to another location on the worksheet.
1.   Set the Criteria Range.
2.   Place the active cell within the list.
3.   Select Data|Filter|Advanced Filter.
4.   In the resulting dialog box, choose Copy to another location.
5.   In the Copy to text box, enter a worksheet cell that represents the top left-hand corner of where
     you would like the results and click OK.
If you want to copy only certain columns from the matching sets of data, enter the column labels
exactly as they appear in the list, in the location you want to copy to. When you run the filter, set the
Copy To range reference to the cells where you have typed the column labels.

Unique records
There is a check box [ ] allowing you to select unique records only. This may be useful if, for example,
the Copy to range does not include all the column labels. There may be several records where the
Product and Customer are the same. If the Quantity, Date and Salesperson are not included in the
Copy to range, this could result in several seemingly identical records being extracted. Checking the
Unique records only check box before choosing OK would result in Excel extracting only the first
record in each instance.
Filtering a list                                       18                           UCL Information Systems
Data forms
In Excel, a data form is a dialog box that enables you to view,
change, add, and delete records in a list or to find specific
records based on information required. Working with a data
form can be time-saving when maintaining data in lists.
To open the data form, click on a cell within the list and, select
Data|Form.
A dialog box will appear on screen, displaying the name of the
worksheet containing the list in the title bar. Each field name
(column) will be listed down the left hand side of the dialog box,
and the details for one record will be displayed. The data is
displayed one record (row) at a time and gives access to all the
records in the list. The data in the text boxes can be edited.


Moving through the records
There is a vertical scrollbar which can be used to move up and down through the available records. The
current record number will appear at the top right of the data form, and will adjust as you move
through the records.
A series of buttons appears down the right-hand side of the data form. These can be used to perform a
number of different operations on the data.


Adding records using the data form
1. Click on the New button to add a new record to the database.
2. Key in your new data using Tab or Shift+Tab to move from field to field.
3. Click on the New button again to add a new record to the database.
4. The record number display will change to New Record, and the fields beside each fieldname will
   be cleared, waiting for you to input more information.


Deleting records using the data form
1. Move to the record you want to delete.
2. Click on the Delete button in order to delete the record currently
   on display.
3. The message opposite will be displayed.
4. Click OK.


Editing or restoring records
You may click in any of the text boxes and edit the information it contains.
Computed fields (those calculated by Excel) cannot be edited.
If you click on the Restore button before moving away from the record displayed, the changes will be
undone and the original information in that record will be restored.

UCL Information Systems                              19                                    Data forms
Searching for specific records
Data form criteria options allow you quickly to find specific information. To search for matching
records using the data form, select Form from the Data menu, and then click the Criteria button.


Entering search criteria
As with the advanced filtering feature, either text or numeric data can be entered so that Excel will find
those records where the relevant field exactly matches what you have entered. Excel is not case-
sensitive when matching criteria, so text can be entered in upper or lower case.
See page 17 for details on entering search criteria.


Multiple criteria using the data form
A combination of different specifications may be entered in different fields and Excel will find only
those records, which meet all specifications. For example, if you enter Warm as the Climate and
Smith as the Advisor, when the Find Next or Find Prev button is clicked, Excel will find only those
orders which fulfil both criteria.
To use the criteria feature:
1. Click the Criteria button.
2. A blank form will display. Use the Tab key to move to the text box for the field where you want
   to set the criteria and type it in.
3. Click Find Next to jump down to the first matching record in the list.
   Clicking Find Next will subsequently allow you to move through the matching records one by one
      until Excel beeps, signalling there are no more matches.
   Click Find Previous to jump to matching records in an upwards direction if necessary.

Clear criteria
When you click the Criteria button, Excel offers you a dialog box where you can set criteria. Your last
criteria will have been stored and remembered and will still be visible in the relevant text boxes.
Click the Clear button to delete them.


Returning to the data form
Click on the Form button to return to the data form.




Data forms                                             20                      UCL Information Systems
Learning more
Central IT training
Information Systems runs courses for UCL staff, and publishes documents for staff and students to
accompany this workbook as detailed below:
Getting started with Excel        This 3hr course is for those who are new to spreadsheets or to Excel, and
                                  wish to explore the basic features of spreadsheet design. Note that it does
                                  not cover formulae and functions.
Getting more from Excel (no       This 3hr course is for users of Excel who wish to learn more about the
formulae or functions)            non-mathematical features of Excel and to work more efficiently.
Using Excel to manage lists       This 3hr course is for those already familiar with Excel who would like to
                                  use some of its basic data-handling functions.
Excel formulae and functions      This 3hr course is aimed at introducing users, who are already familiar with
                                  the Excel environment, to formulae and functions.
More Excel formulae and           This 3.5hr course is aimed at competent Excel users who are already
functions                         familiar with basic functions and would like to know what else Excel can
                                  do and try some more complex IF statements.
Advanced formulae and             This 3.5hr course is aimed at competent Excel users who are already
functions                         familiar with basic functions. It aims to introduce you to functions from
                                  several different categories so that you are equipped to try out other
                                  functions on your own.
Excel statistical functions       This course aims to introduce you to built-in Excel statistical functions and
                                  those in the Analysis ToolPak. The course covers major descriptive,
                                  parametric and non-parametric measures and tests.
Excel statistical formulae        This course covers best practise in constructing complex statistical
                                  formulae in spreadsheets using common statistical measures as example
                                  material.
Excel tricks and tips             This is a 2hr interactive demonstration of popular Excel shortcuts. It aims
                                  to help you find quicker ways of doing everyday tasks. This fast-paced
                                  course is also a good all-round revision course for experienced Excel users.
Pivot tables                      Pivot tables allow you to organise and summarise large amounts of data by
                                  filtering and rotating headings around them. This 2hr course also shows
                                  you how to create pivot charts.
Advanced Excel – Data analysis    This course aims to help you learn to use some less common Excel features
tools                             to analyse your data.
Advanced Excel – Setting up and   Would you like to customise and automate Excel to perform tasks you do
automating Excel                  regularly? If you are an experienced user of Excel, then this course is for
                                  you.
Advanced Excel – Importing        Do you share workbooks with others? Would you like to see who has
data and sharing workbooks        updated what? Do you know how to import data from text files or
                                  databases? This course aims to show you how.


These workbooks are available for students at the Help Desk.




UCL Information Systems                              21                                         Learning more
Open Learning Centre
The Open Learning Centre is open every afternoon for members of staff who wish to obtain training
    on specific features in Excel on an individual or small group basis. For general help or advice, call in
    any afternoon between 12:30pm – 5:30pm Monday – Thursday, or 12:30pm – 4:00pm Friday.
If you want help with specific advanced features of Excel you will need to book a session in advance at:
    www.ucl.ac.uk/is/olc/bookspecial.htm
Sessions will last for up to an hour, or possibly longer, depending on availability. Please let us know your
    previous levels of experience, and what areas you would like to cover, when arranging to attend.
See the OLC Web pages for more details at: www.ucl.ac.uk/is/olc


Online learning
There is also a comprehensive range of online training available via TheLearningZone at:
www.ucl.ac.uk/elearning



Getting help
The following faculties have a dedicated Faculty Information Support Officer (FISO) who works with
faculty staff on one-to-one help as well as group training, and general advice tailored to your subject
discipline:
Arts and Humanities
The Bartlett
Engineering
Life Sciences
Maths and Physical Sciences
Social and Historical Sciences
See the faculty-based support section of the www.ucl.ac.uk/is/fiso Web page for more details.
A Web search using a search engine such as Google (www.google.co.uk) can also retrieve helpful Web
pages. For example, a search for “Excel tutorial” would return a useful selection of tutorials.




Learning more                                        22                           UCL Information Systems

								
To top