Add Change Case Buttons to the Microsoft Excel 365 Ribbon

If you work with both Microsoft Word and Microsoft Excel, you may have noticed that Microsoft Word has a “change case” button in the Font section of the ribbon which allows you to quickly capitalize or lowercase sections of your document. While Excel does not come with such a button by default, it is possible to add buttons to set the case of the highlighted cells.

Step 1: Add New Macros to Your Personal Macro Workbook

The personal macro workbook allows you to store macros that you would like to use in multiple workbooks. Once we’ve added macros to change case to the personal macro workbook, we can add buttons for them on the ribbon. Ensure the developer tab is enabled on the ribbon. Then, to make sure you have a personal macro workbook, go to Developer > Record Macro and record a macro. Stop recording, and open the Visual Basic editor from the same tab. In place of the recently recorded macro, add the following code:

Sub UpperCase()
'
' UpperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = UCase(cell.Value) ' Capitalize the text
        End If
    Next cell
End Sub

Sub LowerCase()
'
' LowerCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = LCase(cell.Value) ' Lower case the text
        End If
    Next cell
End Sub

Sub ProperCase()
'
' ProperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = StrConv(cell.Value, vbProperCase) ' Proper case the text
        End If
    Next cell
End Sub

Step 2: Add Change Case Buttons to the Ribbon

Right click the ribbon, and click “Customize the Ribbon…”. On the left, select Macros and move the macros into the desired area of the ribbon. Use the “Rename…” button to name the buttons and select an icon if desired.

That’s it! Because these macros are associated with your personal macro workbook, you can use the new buttons any time you are using Excel.