5.12 Database management

5.12.8 Filtering Records in a Worksheet

In the technique talked above, you were having a look at the records one by one and there was no specific sequence or pattern on which they were appearing.

However, if you wish to see records of only one particular department or a particular designation, you will have to set in filter conditions on which records of only a particular criteria appear and rest do not turn up.

To do so, you will give Microsoft Excel instructions of choosing Filter and then AutoFilter from the Data Menu. The steps listed below will help you achieve this task.  

1.    Click on the Data menu. In the pull-down menu, first select Filter, followed by AutoFilter. Microsoft Excel displays down arrows for each field .

2.    Click the down arrow for the DEPTT. field. Microsoft Excel displays a list of the unique entries in the DEPTT. field – ADMIN, EDUCATION, R&D, SALES and SYSTEMS – as well as three other options – (All), (Top 10…) and (Custom…).

3.    From the unique entries select EDUCATION. Moment you select EDUCATION, Microsoft Excel filters four records that have DEPTT. as EDUCATION. They are that of ANKITA, MANSI, POOJA and ROOHI. These records hide all other records.
Take a note that Microsoft Excel retains the original record numbers and changes the colour of the down arrow for the DEPTT. field to indicate which column is being used for filtering. Microsoft Excel displays the results of the filter operation – 4 of 12 records found – in the status bar.

4.    Change the filter conditions and see different categories or records getting filtered-like click on ADMIN or SALES or any other entry.

5.    In order to remove filter conditions, click on DEPTT. down arrow and select (ALL).
Alternatively, you can remove all the filters you have set up so far by choosing Filter and then Show All from the Data menu. You can then apply fresh set of filters to the entire list.  

5.12.8.1 Customizing Filters

In the following section you will customize filters and see how they can act as per your information requirement. Suppose you want to see only the records with salary over Rs 19000/-. To filter out these records, you can use the Custom option on the drop down list. To do so, take up the steps listed below:  

1.    Click the down arrow for Salary field and select (Custom…).
Custom AutoFilter dialog box gets onscreen. In this dialog box, you can use operators and the And or buttons to set criteria for the SALARY field.

2.    Click the down arrow to the right or the “equals” to display a list of operators and then select “is greater than”.

3.    Next click the down arrow to the right of the criteria box and click on 19000. Alternatively you can press the Tab key and type 19000.

4.    Click OK. Microsoft Excel displays the results.

5.12.8.2 Using Multiple Filters

Take up the steps listed below to set up multiple filters:

1.    Once again click on down arrow of SALARY field and select (Custom…).
In the Custom AutoFilter dialog box, leave “is greater than” as the operator and value as 19000 as the first criteria.

2.    Select the And button, followed by “is less than” as the operator and type or select 26400 as the second criteria.

3.    Click OK to display results. See the output has only those records where salary is greater than 19000 and is less than 26400.

4.    In order to remove filter conditions, click on the down arrow of SALARY and select (All). Alternatively, you can remove all the filters you have set up so far by choosing Filter and then Show All from the Data menu. You can then apply different filters to the entire list.

5.12.9 Summarizing Data

Quite often, you may require to summarize data in a list in some way. For instance, you may want to see what is the total salary disbursed in the worksheet that you have built. Microsoft Excel’s new PivotTable Wizard permits you to do just that. The Wizard guides you through the steps of creating a PivotTable with the type of summary calculation you specify. After you create the PivotTable, you can reformat it by “pivoting” rows and columns on the screen to provide different views of data.

You will be able to create the PivotTable with the help of the Query And Pivot toolbar. To display this toolbar take up the steps listed below:  

1.    Right-click anywhere on the toolbar to display the toolbar shortcut menu and choose PivotTable.

2.    Microsoft Excel displays a floating PivotTable toolbar.

3.    Drag the toolbar up until its outline is over the name box and the left end of the formula bar. When you release the mouse button, the toolbar gets housed in that location.

4.    We suggest that you take a minute time ff and point to each button so that ToolTips can give you an idea what the buttons do.

5.12.10 Creating the PivotTable

The steps listed below will help you create a PivotTable that will summarize the salary total department wise. The steps listed below will help you do so:  

1.    Click the PivotTable Wizard button to display the first dialog box.

2.    Click on the Next > button to create a PivotTable from the EMPLOYEE list. Microsoft Excel displays the dialog box for the next step. You will see that Microsoft Excel has already entered the range containing the employee information - $A$4:$F$16 – in the Range edit box.

3.    To proceed further, click on the Next > button. It will display Step 3 dialog box, where you set up the PivotTable layout by dragging the necessary fields to the appropriate areas.

4.    Drag the DEPTT. field button to the ROW area. Next drag the SALARY field to the DATA area. By default Microsoft Excel will calculate the Salary total data.

5.    Click on the Next > button to display the Step 4 dialog box. Click on the Existing worksheet button. In the background scroll the worksheet so that cell A18 is visible. Microsoft Excel enters an absolute reference to cell A18 of Sheet1 in the edit box.

6.    Click on the Finish button to accept the default options and scroll to see the new PivotTable.  Microsoft Excel has totalled the salaries disbursed department-wise.

Before you end this lesson, it is recommended that you close the PivotTable toolbar. To do so, right click on this toolbar. A drop down menu shows PivotTable as selected.  Click on the PivotTable option again. The option gets de-selected and closes the PivotTable toolbar.

In this chapter, you learnt about database and the technique to manage them. The lesson covered the techniques involved to sort the database, find records in it, add, delete and filter records in a Worksheet. Finally, you learnt to summarize data and create the PivotTable.

Copyright © 2001 Selfonline-Education. All rights reserved.