Lesson 18: Excel VBA Vocabulary to Filter and Sort Data

When Excel recognises you set of data as a database it offers you very powerful database functionalities like sorting and filtering.

The Excel Database Functionality

We are surrounded with  databases. All accounting programs, sales programs, inventory programs and other business programs include a database. There is the main database in your corporation, there are databases on the WEB  and all kinds of other departmental databases.

You can bring data from all these databases into Excel to analyse the data and create automated reports. You can also develop very useful databases with Excel. As a matter of fact if people  knew Excel better Access would not exist.

With Excel you can develop analyses and reports that would be impossible or unaffordable to develop even with very sophisticated database programs like PeopleSoft, JDE, Oracle and others. Corporations that can afford these million dollars systems rely often on Excel to analyse the data and design reports to support very important decision making processes. Some of the most powerful analysis tools in Excel are database functionalities like sorting, filtering, subtotals and pivot tables. There is even a form to enter data into an Excel database. It is there as soon as Excel recognizes your set of data as a database.

  

VERY IMPORTANT NOTE:

Excel needs to recognize your set of data as a database or you will not have access to any of the database functionalities from the "Data" menu item (the basic Sort and Filter or the more advanced Form, Subtotals and Pivot Table).

The DATABASE is a set of columns (called fields by the database people) that include a SINGLE title cell in each column. Select a different format for the title cells as oppose to the other cells of the table so that Excel understands that it is working with a DATABASE. I use bold font in the title cells and I add a border at the bottom of the cells. Use " Text Wrap" in " Format/Cells/Alignment" to write more than one line of text in one cell and use " Alt/Enter" to force a line break within the lines.

The database MUST be surrounded by empty rows (top and bottom) and empty columns (right and left). If you database starts in cell A1, no need to add an empty row at the top or an empty column on the left.

The Excel database goes from the row of title cells to the last row (called record by the database people) that carries at least one value in any of the fields.

If you want to make sure that your database is recognized by Excel, click anywhere in it and go to Edit/Go to/Specials/Current Region. What is then selected is your database.

Here are 4 examples of set of data not recognized as a database by Excel and one real database.

This is not a database recognized by Excel because there are two rows of title cells

This is not a database recognized by Excel because row 2 is not empty.

This is not a database because columns B, D, F and H are empty. Remove the empty columns and you have a single database recognized by Excel.

Here is a  database recognized by Excel. There are 5 fields (columns) (Date, Name, Product, Quantity, Amount) and 7 records (rows of data).  Column E is not empty, the title cell is there and row 4 is not empty there is a data in field 1. There can be many empty cells in an Excel database (except for title cells) but never any empty rows or columns.

Once you set of data is recognized as a database you can sort and filter data, you can use 

the form to enter new data and you can calculate subtotals and develop pivot tables.

___________________________________________________________

Deactivating filters

When you work in an Excel database you might want to make sure that all data filters are off. To this end you will start your procedure with two "If"statements. For example with a database starting in cell A1 here are the two sentences:

Range("A1" ).Select
           If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter

           If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

Sorting Data

Here is a simplified Excel macro to sort data using a criteria in one field. The following Excel macro will work with any size database starting in cell A1 and it will work in any version of Excel (1997 to 2010).

Sub proFilter()

Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

End Sub

Try the Excel macro above with a small table like the following (as you have leand how in the basic exercises for beginners):

Name	Number
Jones	1
Tom	2
Barry	3
Peter	4

Here is another simplified Excel macro sorting data using criteria in three different fields.

Sub proFilter()

Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
        "B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, _
        Header:=xlYes

End Sub

The code in the two procedures above is much simpler than the following recorded macro in Excel 2007 and 2010. This recorded macro will not work in earlier versions of Excel (1997 to 2006).

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:E7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With