Excel: Convert an entire spreadsheet to proper case in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, how do I convert an entire spreadsheet into proper case (first character in each word to uppercase and the rest to lowercase)?

Answer: This can be done with a macro.

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we have two sheets called Sheet1 and Sheet2.

We've placed a button on Sheet1 that when clicked will move through each sheet in the spreadsheet updating each value to proper case. This macro will then leave you positioned on the last sheet in the spreadsheet.

This is what your spreadsheet will look like after you've run the macro:

You'll now be positioned on Sheet2 and all of the values should now appear in proper case.

To view the macro, press Alt-F11 and double-click on the module called Module1 in the left window.

Macro Code:

This macro code looks like this:

Sub ConvertToProper()

    Dim ws As Object
    Dim LCell As Range

    'Turn off screen updating to increase performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'Move through each sheet in your spreadsheet
    For Each ws In ActiveWorkbook.Sheets
        On Error Resume Next
        ws.Activate

        'Convert all constants and text values to proper case
        For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)
            LCell.Formula = StrConv(LCell.Formula, vbProperCase)
        Next
    Next ws

    'Turn screen updating back on
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub