Lesson 11: Filtering Lists Part II

Filtering Lists

You can perform various operations with filtered lists
Eg : If you want to print a list of screens sold in 1994 :

Place the cursor in any cell on the list
Select the ‘filter’ option in ‘Data’ menu and select ‘Autofilter (Autofilter)
The arrows appear next to the field names
Choose ‘Screen’ from the ‘Product’ column
and select 1998 in the ‘Year’ column

In the preview you can see that only the filtered data will be printed.

Click on the “AutoSum”

Press Enter, and only the sum of the filtered data is calculated.
Once you remove the autofilter, the total is adjusted automatically.

The Sorting of a filtered list on the basis of the contents in the coloumns or rows:

Move the cursor to the “Quantity” column and click on the ‘Sort Ascending’ button
Immediately the entire list is sorted based on the content of the “Quantity” column

Sorting of multiple columns

Place the cursor anywhere in the list
Select ‘Sort’ (Sort) from the ‘Data’ menu
A dialog appears where you can specify three colums
Fill in the various sorting columns
Click Ok and sorting is performed

If you work with very large lists it is handy to give these lists a name

Select the whole list and click on the top left of the Name box to add a name to the list.

If you’re select in the name box of the list name, the entire list is selected

Managing lists with a database

EXAMPLE PRODUCTS LIST

You can manage database functions with a list or database.
In Column M3, we want to calcualte the average sales for the seller Janssens :

Action-the title is already filled
In the range L2 to L3, the criteria range is completed
In M3-calculate the average
Click on the ‘Wizard functions’ button
And in step 1 of 2, select ‘Database’ category
As a function name, choose ‘DAVERAGE’
Click OK
In step 2 of 2 fill the full range that is defined as a database
Since this database range name is “products”, here you can also enter the name
Press the Tab key
Here you enter the field that should be used in the function
So enter D1 (sales) in this field
Press the Tab key
The criterion for which the function is calculated, in this case, L2 to L3
Click OK
In the cell-M3, avarage sale for Janssens is calculated

If you type the name of another vendor, the average is adjusted accordingly

You can look up the values in a list from another worksheet using the vertical search and horizontal search function (look-up function).

First, create a spreadsheet with the different items and prices if you have not already done this.

Vertical search:

When I type the product name in cell L12, I want the price per item to be automatically populated in cell M12

Place the cursor in cell M12
Press the “Wizard features” button
In the category box, choose ‘Lookup and Reference’ (Lookup and Reference)
Select ‘Vertical Search’ (Vlookup) in the function name box
Click ‘Next’
In step two of two, place the cursor in the “Search Value” (Lookup_Value)
and here you must enter the cell value to compare – L12
Go to the tab of the list
Please give the full list

So-click on the ‘price’ worksheet
Select A2 to B9
It is important to first sort the column that we seek, in ascending order
Press TAB
In the ‘Column index number’ box, enter the column number that we need, in this case column 2 where the price per unit is entered.
Click ‘Finish’ to complete
You will see an error in cell M8 because we did not enter any product in cell L8.

Once you enter a product name, a price per piece automatically appears

You can see the function in the formula bar
If you change the relative cell reference for the table matrix to absolute cell reference by adding $ signs,
you can copy the function down and once you can enter product name in those cells and the correct price per unit is looked up from the table.

Awesome!
You've completed Lesson 11 START NEXT LESSON

Lesson 11

Office 97 - Excel