How to Find and replace data in Excel 2010

Many a times while working with a worksheet in excel, you might have huge amounts of data placed in your excel spreadsheet. It might thus be necessary at different points to replace various numbers or names etc in the given rows and columns. To make things easier, MS Excel provides you with the option of finding and replacing data in workbooks and spreadsheets to make editing information easier.

Follow this step by step tutorial to learn How to find & replace in excel.


Step # 1 – Finding words

In case you have a large amount of data on your worksheet in excel and you want to search for a specific word or phrase etc, press the “Ctrl + F” key. A search box will appear.
In the “find what” field, type in the word or phrase that you are searching for. Now, press the “Find next” button and this will direct you to the cell that contains the searched item. In this tutorial, we have searched for the letters “ca”. The letters “ca” is a short form for “California” and so when we press the “find next” button, Excel searches for all the words that starts with “ca” such as “caroline”.

Search for the words

Step # 2 – Using the “match entire cell contents” option

Once the above steps have been completed, click on the “options” button and select the “Match entire cell contents” option. This will allow only the cells which have “ca” in them to be searched for.

Select match entire cell contents option

Step # 3 – Using the “Replace” option

In order to find & replace in excel, a specific word or phrase, go to the “editing” group and click on the “find and select” button. A drop down menu will appear from where you can select the “replace” option.

Select the Replace option

Step # 4 – Finding all the words

In the “replace with” box, we have entered the word “California”. Click on the “find next” button and this will allow the next word starting with “ca” to be searched for. You can also click on the “find all” button in order to search for all the words starting with “ca” to be found in one go.

Search for all the words

Step # 5 – Replacing the words

In order to proceed, click on the cell link given at the bottom after which both the link and the cell will be highlighted. You can now click on the “replace” button in order to replace the chosen word or click on the “replace all” button to replace all the selected words in one go.

Change the words