Search Backwards in Excel

Excel Tip #022

Search Backwards in Excel

Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it.

Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select, then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one.

But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values?

You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time.

1. Select a cell below the range you want to search (CTRL+END is a shortcut you can use to quickly get to the bottom of your worksheet)
2. Press CTRL+F to open the Excel's Find & Replace dialog, then enter the value you are searching for
3. Now hold down the SHIFT key and click Find Next. If there is a matching value in the worksheet, you will be taken to the last one on the sheet
4. Subsequently, each time you click the Find Next button while holding down the Shift key, you will be searching backwards from the bottom to the top of your worksheet, finding each matching value until it finally reaches the first matching value

Pressing CTRL+F, then SHIFT+FIND NEXT starts searching at the 'active cell' and goes upward. When it reaches row 1, it jumps back to the bottom of the worksheet and continues searching upward until it gets back to the original active cell or the first matching value if there is one. So, you start from row 1, it will immediately jump to the bottom.