Excel 2007 can sort and filter using the cell color (colour). By using a UDF you can do this in earlier version as well. VBA can get the interior color of the cell and report it as an index number from 1 to 56. Add this code into the VBA editor and add a column to you data with the formula =CellColor(A1).

Function CellColour(cell As Range)
CellColour = (cell.Interior.ColorIndex)
End Function


You can now sort or filter using this data. It’s not quite as clever as the latest version but you can still use colours to sort and filter.

You might find the following table helpful to understand the colours used in Excel. Thanks to www.mvps.org for the table.

This is one of my favourites.
Returns a subtotal in a list or database. However this does not just add, it can count, average, min max and more.

SUBTOTAL(function_num, ref1, ref2, …)

Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.

Ref1, ref2 are 1 to 29 ranges or references for which you want the subtotal.

If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal only nonhidden numbers in a list.

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

U.S Patent 7,475,086 granted to IBM: Method of automatically removing leading and trailing space characters from data being entered into a database system.

A computer Implemented method of automatically removing space characters from data being entered into a database system are provided. When a user creates a table in a database system into which data having leading and/or trailing character spaces may be entered, the user may specifically instruct the database system to remove any character spaces before entering the data into the table.

Consequently, when a piece of data is being entered into the table, the database system will determine whether the piece of data includes leading and/or trailing character spaces. If so, the database system will automatically remove the character spaces from the piece of data before it is entered into the table. Hence, the use of TRIM functions when retrieving data from the database system will be obviated.

It took three people from Texas to come up with that idea, and they thought it was so brilliant that it deserved to be patented.

Does this mean that the following VBA procedure violates the patent?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
On Error Resume Next
For Each cell In Target
If Not cell.HasFormula Then cell = Trim(Target)
Next cell
End Sub