Database Operations In Ms-Excel (2016, 2013, 2010, 2007) - Chapter 2: Part 1

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.

DATA

They are raw or unprocessed facts concerning individuals or business organizations.

CHARACTER

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, 2,…,9.

INFORMATION

It is also referred to as processed data. It is slightly different from data.

FIELD

This is a unit of information which forms the attribute of a record. It can also be referred to as Data Items.

FIELD NAMES

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, etc.

RECORD

This is the collection of related fields. It is commonly expressed in rows.

DATABASE

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.

SORTING FIELD

This is the field the sorting operation is based on.

SORTING ORDER

This is the order of the sorting operation it could be either in ascending or descending order  or group of colour range, etc.

DATA RANGE

This is the entire database you want to sort which includes all the records excluding the field names.

CRITERIA

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.

Sort dialogue

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.

Sort result

The first sort criterion (i.e. the first sort column) has precedence over the second and subsequent ones.

QUERYING A DATABASE

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.

METHODS OF QUERYING A DATABASE IN MS-EXCEL

There are two methods of querying a database in MS-Excel. They include:

• 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.

Simple filter

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.

NOTE:

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.

Advance filter 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.

Advanced filter dialogue

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:

a. Characters.

b. Field.

c. Field name.

d. Database.

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

Get this book (Kindle format): Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016.

Click Here to know more about the book.

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.