Data Sort

Data Sort

Sorting can be done in ascending/descending order.  You can sort maximum three fields in Excel database.

How to sort data:

1. Place your cursor on any cell in the above database.
2. Go to data>sort
3. Sort dialog box will appear
4. Every field’s name is in the short by list box
5. Select your desired field for example you may choose to sort by department, so select department
6. Select the order from ascending/descending option button
7. there is another two then by boxes; if you want to sort by two or more criteria select those. Now select position in the then by field
8. If in my list has area, header row is not selected then select it
9. now press ok
10. now your data appears as those in the figure 3

Figure 3

How to filter data

On many occasions you may need to see data from specific category. For that purpose you need filter.

Filter can be applied to Excel database in two ways

AutoFilter

Advanced Filter

Data filtering through auto filtering

For example you may want to see data for executives from the ProjectDay4 database

Then you need to follow the following steps:

1. Place the cursor on the database
2. Go to data>filter>autofilter
3. Observe that now there is down arrows in your field names
4. Click on the right side arrow (drop down control) of position
5. Select executive
6. Go to data menu again and select show all to remove filter.

Advanced filter

Autofilter and advanced filter perform the same task. There are more options available in advanced filter than in auto filter. The advantage of using advanced filter is that you can transferred the filtered data to another part of the worksheet.

How to do it?

1. first of all copy the field names of the database to another place in the database and the field on basis of which you want to filter, type the condition below that field. The header row alongwith the condition is referred to as Criteria.
2. place your cursor on database
3. go to data>Filter>advanced filter
4. In the criteria range box type the copied field name alongwith the condition
5. If you want to have the filtered data in another place of the worksheet then select copy to another location radio button and type in the copy to box the cell range where you want to have your data
6. Click ok

Class exercise:

You want to move the data relating to officers to another place of the worksheet. How will you do it?

Form

Like database program you can also see the form of the database record for Excel. You can also view the previous/next record. You can also input new record and edit previous record.

1. Place the cursor on database
2. Choose form from data menu

To input a new record in the form:

1. click on the New button
2. Enter accurate data in different field
3. Click on new to add another new record

Edit data in the form:

Use the scrollbar to select the desired record.

Edit the data in the field

Click New to update the data

Search data in worksheet:

In the criteria of form you can search for different data

Click on criteria. All data will be vanished and only criteria is there

Type the field on the basis of which you want to search the data

If for example you want to see data for officers then write in the position box”officer”(there must be officer field in the database and data about the officers.)

Click on find previous/next

Subtotal

In an organization you frequently need to present the information in your workbook to others. In this situation, it is rationale to represent it as a report. Subtotal can help you in this regard.

Preparing summary report with the help of subtotal

1. Place your cursor on the database and sort it on the basis of position.
2. From data menu select subtotal

The dialog box will appear as follows:

1. at each change in select position
2. Ensure that in use function box sum is selected
3. select salary field in the add sub total to box
4. Click Ok

How to remove subtotal :

1.                  from data menu select subtotal

2.                  then click remove all in the sub total dialog box.