Last month Microsoft released the beta version of Office 2010. The new version includes new versions of all the classics: Word, Powerpoint, Sharepoint and our favorite, Excel 2010!
Here is a short video from Microsoft about the new changes:
See What’s New in Microsoft Excel 2010

Excel has a strong and often missed feature, called Conditional Formatting, which allows you to format a cell depending on a rule [it's value]. This can be very useful for worksheets like Product Quantities and Dates. Let me show you what I mean.
Lets start off with a simple chart of products quantities in the warehouse.

We’ll [...]

The following routine will sort the sheets in a workbook alphabetically. The flag “Sort_Mode_Descending” can be switched between descending and ascending as required. The routine is case INSENSITIVE.
Sub Sort_Sheets()
Dim Sort_Mode_Descending As Boolean
Dim No_of_Sheets As Integer
Dim Outer_Loop As Integer
Dim Inner_Loop As Integer
No_of_Sheets = Sheets.Count
‘Change Flag As appropriate
Sort_Mode_Descending = False
For Outer_Loop = 1 To No_of_Sheets
[...]

The standard Excel “Column” Function returns the number rather than the letter of the column e.g:
Column(E4) – returns the number 5 rather than the letter E
Column(AD12) returns the number 30 rather than AD.
The following function returns the letter rather than the number of the column. So in the above two examples we have the letters [...]

The following function will extract the numerical portion from a string:
Function Extract_Number_from_Text(Phrase As String) As Double
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Length_of_String = Len(Phrase)
Temp = “”
For Current_Pos = 1 To Length_of_String
If (Mid(Phrase, Current_Pos, 1) = “-”) Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = “.”) Then
[...]

The following function will find the nth word in a string:
Function Find_nth_word(Phrase As String, n As Integer) As String
Dim Current_Pos As Long
Dim Length_of_String As Integer
Dim Current_Word_No As Integer
Find_nth_word = “”
Current_Word_No = 1
‘Remove Leading Spaces
Phrase = Trim(Phrase)
Length_of_String = Len(Phrase)
For Current_Pos = 1 To Length_of_String
If (Current_Word_No = n) Then
[...]

The following code works opening a workbook. It automatically adds a new sheet and labels it with the date. It also checks to see that the sheet doesn’t already exist – to allow for the possibility of it being opened more than once a day.
This code makes use of the Workbook Open Event and must [...]

The following code will prompt the user for the name of a workbook. It will then create and save a new
workbook with the same name.
Private Sub CommandButton1_Click()
Dim Workbook_Name As String
Dim New_Workbook As Workbook
Set New_Workbook = Nothing
Workbook_Name = InputBox(Prompt:=”Workbook Name.”, Title:=”Enter the WorkBook Name :”)
Set New_Workbook = Workbooks.Add
With New_Workbook
.Activate
.SaveAs Workbook_Name
End With

End Sub
To download the .XLSM file [...]

The following UDF will return the formulae for a given cell reference:
Function Show_Cell_Formulae(Cell As Range) As String
Show_Cell_Formulae = “Cell ” & Cell.Address & ” has the formulae : ‘ ” & Cell.Formula & ” ‘”
End Function
So if we have:

Where the cell B7 has the formulae : ‘=MAX(B5:G5) then we can type [...]

Imagine that we have the following 2 lists each consisting of 3 columns of data:

And we need to find those items that are in List 1 that are in List 2. However ALL 3 columns of data must match for items to be in both lists. One way would be to construct a comprehensive IF [...]