Note: The info from this page is also published on MSDN
Working with the Compatibility Checker in Excel 2007 and Excel 2010
http://msdn.microsoft.com/en-us/library/gg132972.aspx


When you Save an Excel 97-2003 file(xls) that you have open in Excel 2007 or Excel 2010 or use Save As to save an Excel file that is open in Excel 2007 or Excel 2010 as Excel 97-2003 file (xls) it is possible that the Compatibility Checker dialog pops up.

Note: You only see this dialog if there are features in the workbook that are not supported in Excel 97-2003. The Compatibility Checker scans your workbook for Compatibility issues and displays warnings if they can be found in your workbook. These features may be lost or degraded when you continue to save the workbook in the Excel 97-2003 format.



You can click the button named “Copy to New Sheet” to copy the summary of issues
on a new worksheet or read the about the issues in the dialog.
In the report you can find one or both of the following two sections with every issue:
 
Minor loss of fidelity
If there is a Table in the workbook it is possible that you will see this error message: A Table style is applied
in this workbook. Table style formatting cannot be displayed in earlier versions of Excel.
 
Significant loss of functionality
Like the example in the dialog above: You will see this when you use a formula in the workbook that is added
in Excel 2007 (for example SUMIFS)
 
Now you have information about the compatibility issues that are present in the workbook you can make a decision what to do, Save as Excel 97-2003 file or not.
 
Issues in the Minor loss of fidelity section
 
Like it says “Minor”, most likely it is no problem to save the file as Excel 97-2003 file and share it with an Excel 97-2003 user. You can uncheck the checkbox named “Check compatibility when saving this workbook” if you not want to let the Compatibility Checker check your workbook anymore when you save the file.

You can change this setting in the user interface like this:
Excel 2007: Office Button>Prepare>Run Compatibility Checker
Excel 2010: File>Info>Check for issues>Check Compatibility

 
Issues in the Significant loss of functionality section
 
When you have Issues in this section continue saving the workbook is most of the time not a good option because you will lose data/formulas or specific things will not work correctly anymore.
One option is not to use formulas that are new in 2007-2010.
For example =SUMIFS() and =COUNTIFS() could be replaced by an equivalent =SUMPRODUCT().
See this site for more information: http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And not use columns/rows with data outside 256(IV) columns by 65536 rows. These are just two examples
of issues that you can have. So looking for a workaround is maybe an option for you.
 
If you only want to show the data in your workbook to an Excel 97-2003 user there are two options.
 
1: Save your workbook as PDF file
This option is installed by default in Microsoft Office 2010 and in Microsoft Office 2007 Service Pack 2
If you do not wish to install Microsoft Office 2007 SP2, you can install just the add-in.
 
You can download it here:
2007 Microsoft Office Add-in: Microsoft Save as PDF
 
 
2: Send your workbook in the Excel 2007-2010 format to the Excel 97-2003 user.
And let the Excel 97-2003 user install the Excel Viewer.

You can download it here:
Microsoft Excel Viewer
 
You can also download the Microsoft Office Compatibility Pack at the Microsoft site, with this Microsoft Office Compatibility Pack you can Open/Edit/Save files that are in the 2007-2010 format in older Excel versions, but it will not for example display columns/rows with data outside 256(IV) columns by 65536 rows. Also when there are formulas in the workbook that are new in 2007-2010 they will display a #NAME error when you recalculate the workbook. You see that the Viewer or saving as PDF is a better option if you want to show Excel 97-2003 users the data in your 2007-2010 workbook.
 
 
Compatibility Checker and VBA code
 
 
You see that in the Compatibility Checker dialog there is a checkbox named “Check compatibility when saving this workbook” to turn on or off this setting, with VBA code you can do it like this:
 
ActiveWorkbook.CheckCompatibility = False
 
Or turn it on again
 
ActiveWorkbook.CheckCompatibility = True
 
You can change this setting in the user interface like this:
Excel 2007: Office Button>Prepare>Run Compatibility Checker
Excel 2010: File>Info>Check for issues>Check Compatibility
 
 
 
Save one 2007-2010 worksheet as Excel 97-2003 workbook
 
You can use the macro below in your 2007 workbook to create an Excel 97-2003 workbook of the active sheet and avoid the CheckCompatibility dialog. Be sure that there are minor or no compatibility issues when you use this macro.

Sub Save_2007_WorkSheet_As_97_2003_Workbook()
'Avoid CheckCompatibility dialog when you copy a WorkSheet
'from a 2007-2010 file with compatibility issues to a new
'workbook and save this workbook as a 97-2003 workbook
    Dim Destwb As Workbook
    Dim SaveFormat As Long
    Dim TempFilePath As String
    Dim TempFileName As String

    'Remember the users setting
    SaveFormat = Application.DefaultSaveFormat
    'Set it to the 97-2003 file format
    Application.DefaultSaveFormat = 56

    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
    Destwb.CheckCompatibility = False

    'Save the new workbook and close it
    TempFilePath = Application.DefaultFilePath & "\"
    TempFileName = "Excel 97-2003 WorkBook " & Format(Now, "yyyy-mm-dd hh-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & ".xls", FileFormat:=56
        .Close SaveChanges:=False
    End With

    'Set DefaultSaveFormat back to the users setting
    Application.DefaultSaveFormat = SaveFormat

    MsgBox "You can find the file in " & Application.DefaultFilePath

End Sub

I use ActiveSheet.Copy but you can also use Sheets("Sheet5").Copy
It doesn't have to be the active sheet used at that time.
 
 
 
Save one 2007-2010 worksheet as Excel 97-2003 workbook and mail it
 
Note: The example below will create an Excel 97-2003 workbook of the Active sheet and display a mail
with this workbook attached if you use one of the following mail programs:

Outlook Express
Windows Mail
Windows Live Mail
Outlook

Be sure that there are minor or no compatibility issues when you use this macro.

Sub Mail_ActiveSheet_As_97_2003_Workbook()
'Working in 2007-2010
    Dim SaveFormat As Long
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim I As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Remember the users setting
    SaveFormat = Application.DefaultSaveFormat
    'Set it to the 97-2003 file format
    Application.DefaultSaveFormat = 56

    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
    Destwb.CheckCompatibility = False

    'Save/Mail the new workbook and close it
    TempFilePath = Application.DefaultFilePath & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " _
                 & Format(Now, "yyyy-mm-dd hh-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & ".xls", _
                FileFormat:=56
        On Error Resume Next
        For I = 1 To 3
            .SendMail "", _
                      "This is the Subject line"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & ".xls"

    'Set DefaultSaveFormat back to the users setting
    Application.DefaultSaveFormat = SaveFormat

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

For more mail examples and tips how to change the code visit my mail page
http://www.rondebruin.nl/sendmail.htm