It is always advisable that you get used to some MS Excel spreadsheet or database operations like sorting a worksheet, querying a database using the various available methods and knowing some commonly used terms in Excel database operations. This tutorial has three parts and this is the first Part (Part 1). It explicitly explains all about database and its operations and manipulations using any version of MS-Excel. I will also provide some exercises to test your understanding.
Part two explains explicitly CHARTS AND CHART OPERATIONS IN MS-EXCEL (2016, 2013, 2010, 2007)
Part three explains explicitly MANIPULATIONSON SMARTART, DUAL-AXIS CHARTS, SHAPES AND MATHEMATICAL EQUATIONS IN MS-EXCEL
SORTING A DATABASE
This is the arrangement of records either in ascending or
descending order for easy accessibility and retrieval of information. Sometimes
there might be need for you to arrange records alphabetically, and then you
sort the database. You can sort by any column of your choice.
DEFINITION OF SOME DATABASE TERMS
These are some of the database terms we will be using in
this chapter and also used generally when sorting a database both in MS Excel or Access.
They are raw or unprocessed facts concerning individuals or
This is the smallest entry in a database. A collection of
characters is called a field or data item. For example A, B, C,…, Z or 0, 1,
It is also referred to as processed data. It is slightly
different from data.
This is a unit of information which forms the attribute of a
record. It can also be referred to as Data Items.
This is the column title or heading that specifies the type
of data to be stored in a column or field. For example FIRST NAME, SURNAME,
This is the collection of related fields. It is commonly
expressed in rows.
This is a collection of related records that contain
information of individuals or entities which are organized structurally to
enhance easy access, update, maintenance and storage with minimum redundancy.
This is the field the sorting operation is based on.
This is the order of the sorting operation it could be
either in ascending or descending order
or group of colour range, etc.
This is the entire database you want to sort which includes
all the records excluding the field names.
These are the conditions you use to tell Excel how it will
query or sort your database.
HOW TO SORT A
WORKSHEET OR DATABASE
To sort a Database:
1. Open the records or database you want to sort.
2. Click the Data tab and click Sort under Sort & Fill group. This prompts the sort dialogue box.
3. If your data has a header row, check the button at the top that says “My Data has a header”. Specify the sorting column by clicking the “Sort by” drop down menu under Column. You can sort by more than one column by clicking the “Add level” tab. This will add a second sort level. In the figure shown below, there are two levels: sort by NAME and then by AGE.
4. The “Sort On” drop down menu has many options such as values, cell colour, font colour, cell icon. Specify the sort criteria by clicking the drop down menu under “Sort On”. The default is Values.
5. Then specify the sort order in the “Order” drop down menu. The options see is determined by the option you chose under the “sort on” drop down menu. For example if you chose to sort by values, you can either sort in ascending or descending order. If you chose to sort by cell colour, you will see a drop down menu of all the cell colours you used.
6. If you want some advanced options like sorting from left to right i.e. row wise or case sensitive sorting, click the “Options” button and check the two desired buttons. Below is a figure of a sort result.
The first sort criterion (i.e. the first sort column) has
precedence over the second and subsequent ones.
This operation is used to extract or search for specific
information from a large volume of records in a database based on the specified
criteria. This operation is very useful especially if there are so many records
in a database and you wish to only the records that met the specified criteria.
To achieve such tasks manually would take much of your time because you have to
skim and scan through the record. But with the query or filter operation in
MS-Excel, the task will be achieved within few minutes.
QUERYING A DATABASE IN MS-EXCEL
There are two methods of querying a database in MS-Excel.
• SIMPLE METHOD – Auto filter command
• ADVANCED METHOD – Advanced filter command
SIMPLE METHOD – Auto filter command
This is the simplest and automatic method of querying a database in Excel.
To filter or query a database using Auto filter command:
1. Prepare and open the database you want to query or filter.
2. Position the cell pointer anywhere within the data range.
3. Click the Home tab.
4. Click the Data tab and click Filter under Sort & Fill group. Excel will automatically insert drop down arrow on each column title as shown below.
Click on any drop down arrow to specify your conditions or criteria. As you set these criteria, excel automatically removes the records that did not meet the criteria, leaving only those that met it.
This method allows you to set only one criterion at a time. If you set more than one criteria, Excel will only list the records that met the last criterion you set.
THE ADVANCED METHOD – Advance filter command
This is the best method of querying a database because Excel allows you to specify more than one criterion at the same time. In this method three ranges of data must be prepared. They include:
• LIST/INPUT RANGE
This range specifies the location of the entire database which includes all the records as well as their column or field names.
• CRITERIA RANGE
This range is where you specify the conditions which MS-Excel will use to search for the records that in the list/input range that met the specified conditions.
The first row of this range contains copies of the field names from the input range and is about one to two blank rows from the output range.
• OUTPUT RANGE
This range is where Excel pastes copies of records in the input range that met the criteria specified in the criteria range.
The first row of the output range contains copies of the field names from the input range and then several rows of result records. The worksheet in the figure below specifies the three ranges.
To filter or query a database using Auto filter command:
1. Prepare the three ranges of data as shown above.
2. Type in the conditions under the criteria range.
3. Position your cell pointer anywhere within the input range.
4. Click the Data tab and click Advanced under Sort & Fill group. This prompts the advanced filter dialogue box.
5. Click on the collapse button at the right end of the list range text box. This takes you to your worksheet.
6. Specify the list range by highlighting the entire input range i.e. the database or entire record and then click on the icon again to go back to the dialogue box.
7. Specify the criteria range by highlighting the entire criteria range and then click on the collapse button again to go back to the dialogue box.
8. Check the “Copy to another location” bullet under “Action”.
9. Specify the output range by highlighting the entire output range and then click on the collapse button again to go back to the dialogue box.
10. Click OK.
Excel now copies the records that met the criteria you set in the criteria range to the output range.
PRACTICE EXERCISES UNDER EXCEL DATABASE OPERATIONS
Attempt these sample questions:
1. Explain briefly the following with good examples:
c. Field name.
e. Sorting field.
f. Data range.
2. How can the following be achieved:
a. Sorting a database.
b. Filtering a database using Advance Filter Method.
This is the end of the part 1 of this tutorial.
Recommended MS Excel Textbook
Now go over to Part 2 - CHARTS AND CHART OPERATIONS IN MS-EXCEL (2016, 2013, 2010, 2007)
Hope you learnt a lot from this
article? Comment below if you have any confusion. Don’t forget to share this
article with your friends. Also subscribe to get our latest posts.