Excel tips and tricks

Note: this Excel howto applies to Excel 2003. Other versions may be different.

How to search backwards in Excel

Suppose you maintain a list of expenses, something like this:

Date	Expense	Cost	Where
...	...	...	...
2009-06-01	haircut	40	Fancy Saloon
2009-06-05	groceries	200	Filthy Bazaar
2009-06-07	pet food	100	We R Pets
...	...	...	...
2009-07-03	haircut	8	Cheapo Cuts
2009-07-07	oil change	40	Slimy Boys

It's August now, and you need a new haircut. You want to search backwards in your spreadsheet and find out when and where you had your last haircut. How do you do that? There are two ways:

• manually, you can press Ctrl+F, type "haircut" and hold Shift while clicking "Find Next". This undocumented behavior in Excel 2003 will find the Previous occurrence instead.
• with the little Visual Basic macro below, you can just press a shortcut key of your choice and Excel will automatically search backwards for the content of the cell you're on, in its column:

Sub Search_cell_backwards()
'
' Search for the contents of the current cell in the cells above it. Only considers the first cell of
' the selection, because the .Find only takes into account the first cell of multi-cell selections
'
    With Selection.Worksheet
        ' Select the range ending just above the current cell
        Set targetColumn = .Range(.Cells(1, Selection.Column), .Cells(Selection.Row - 1, Selection.Column))
    End With
    Set c = targetColumn.Find(Selection.Value, LookIn:=xlValues:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
    If c Is Nothing Then
        MsgBox ("Not found: " & Selection.Value)
    Else
        c.Select
    End If

End Sub

To install this macro, press Alt+F11 in Excel. This will display the Visual Basic code for any macros you may have defined. If so, find your macros and paste the one above in the same place. If not, then right click on your worksheet in the list at the top left, and choose Insert -> Module.

To assign a keyboard shortcut to a macro, go in your worksheet to Tools -> Macro -> Macros (Alt+F8), select the macro, then click "Options".

Now you can place the cursor on a cell and press a hotkey to search backwards for that cell's contents.

Even better: multi-cell backwards search in Excel

The Find method only cares about the first cell in a selection. In other words, if you select the two cells haircut | 8, you'll find the haircut cell that is followed by 40 (even if the range was extended to the width of the selection). This is not what we want. The macro below searches for all the cells in a selection, including for a single cell, so it completely supersedes the previous macro.

Sub Search_selection_backwards()
' Search backwards for all the cells in the current selection
'
    With Selection
        For rw = .Row - 1 To 1 Step -1
            entireSelectionFound = 1
            For c = 1 To .Columns.Count
                If .Worksheet.Cells(rw, .Column + c - 1).Value <> .Cells(1, c).Value Then
                    entireSelectionFound = 0
                    Exit For
                End If
            Next c
            If entireSelectionFound = 1 Then
                .Worksheet.Range(.Worksheet.Cells(rw, .Column), .Worksheet.Cells(rw, .Column + .Columns.Count - 1)).Select
                Exit For
            End If
        Next rw
        If entireSelectionFound <> 1 Then
            MsgBox ("Couldn't find selection starting with: " & .Cells(1, 1).Value)
        End If
    End With
End Sub

How to enter a line break in a cell

Simply press Alt+Enter.

How to prevent the first row/column from scrolling (freeze it in place)

To keep C columns and R rows from scrolling when you scroll the worksheet, click on row R+1 and column C+1 and choose Window -> Freeze panes

Troubleshooting

Ctrl+A does not select the entire sheet; it stops at blank rows.

Solution: to select all cells in the sheet, click the top-left-most cell, above 1 and to the left of A.

 Privacy policy