Learning how to use Find and Replace in Excel 2010, or Excel 2007, can save you a great deal of time if you have to change a lot of values or formulas on a worksheet. Learn how to use the Find and Replace function in Excel with this guide and never waste time making changes to a worksheet again…
What is Find and Replace
The Find and Replace function in Excel does exactly what it says, it finds a value, text string or formula and replaces it with a different value, text string or formula as specified by you the user.
For example you may have a worksheet that contains raw employee data with one of the fields being the team of each employee. If one, or more, of those team names suddenly get renamed by the business then using Find and Replace in Excel can save you the hassle of manually amending all the cells individually, you can just get Excel to do the hard work and change all occurrences of Team X to Team Y.
How to access the Find and Replace function in Excel
This method works the same for Excel 2010 and Excel 2007, even earlier versions should work similar if you are still working on Excel 2003 for example.
There are 2 ways to access the Find and Replace function in Excel, the first is by means of the Excel ribbon on the top of the screen.
To access the Find and Replace function using the Ribbon:
1) Make sure you are on the Home tab
2) Left-click on the “Find & Select” option
3) Left-click on the “Replace” option
That will now bring up the Find and Replace function which looks like this:
Alternative method (Best way)
The alternative method is far quicker and utilises Excel’s shortcut commands. To open the same Find and Replace function as above simply press and hold the CTRL key and the letter H on your keyboard (CTRL+H).
Extra tip: This shortcut command also works in Microsoft Word documents to bring up that version of Find and Replace.
How to use the Find and Replace Function
Now you know how to access the Find and Replace function the next step is to learn how to utilise its power for any changes you might have to make, this can be broken down into basic changes and advanced changes:
If like in the previously mentioned example you just want to change a team name within a dataset, i.e. change “Marketing” to “Marketing and Advertising”, you can simply enter the change on the basic view of the Find and Replace Function like so:
Once you have entered the text you can choose to either “Replace All”, which will tell Excel to go through the worksheet and replace all occurrences of “Marketing” with “Marketing and Advertising”, alternatively you can select “Replace” and it will go through each occurrence one by one. The latter method is much slower if there are lots of changes to make but can be useful if you have a situation where you do not want to change every occurrence as you will have the option to tell Excel to skip changing a particular cell.
To complete more advanced changes then you need to look into the options part of the Find and Replace function, do so by left-clicking on the “Options” button:
The view will change to the following:
As you can see there are now many more options for how the Find and Replace is approached and implemented by Excel.
Advanced Options Explained
· Formats: In the top right of the Find and Replace function window there is now an option button for “Format”. This can be used if you want to only search for a particular format, or if you want to replace using a particular format. Examples of this might be changing certain text to a different colour or to a different font style, like italic or bold.
· Within: The drop down box next to “Within” allows you to change whether Excel looks at the active worksheet (as identified by “Sheet”) or you can also change this to “Workbook” if you want Excel to Find and Replace throughout the whole workbook.
· Search: This drop down box allows you to change the way Excel searches from “By Rows” to “By Columns”. It’s data dependent on what is best but usually you can leave this set to “By Rows” as Excel will still search the entire worksheet or workbook.
· Look in: This is always set to “Formulas” making it kind of redundant, it just means Excel will look in the formula as well as just text strings or numerical values when it completes the Find and Replace.
· Match case: If there are times where you need Excel to be case specific, i.e. only change “MARKETING” and not “Marketing” then check this box.
· Match Entire Cell Contents: Again this is another very specific option which tells Excel to only Find and Replace cells where the entire cell matches what it is trying to find. For example if you want to only find “Marketing” and not cases where the cell is “Marketing team” then you would check this box.
· Use the Find and Replace function in Excel where you have multiple changes to make or have large datasets that require changes, it will save you loads of time rather than manually hunting down words, numbers or formulas.
· Access the Find and Replace function from the “Home” tab or use the shortcut command CTRL+H.
· Basic changes, i.e. changing one word to a different word, can be made on the initial view of the function, click “Replace All” to make changes to every occurrence in one go, or “Replace” to go through them one-by-one.
· Advanced changes, i.e. searching for certain formats or exact matches can be made by setting additional options in the Find and Replace function.
· As always I would recommend completing any changes in a copy of your workbook first so if you make any mistakes you can revert back to the original.
That is really all there is to the Find and Replace function in Excel 2010, or Excel 2007. It is an amazingly powerful tool which all analysts and Excel users should understand as it can enable you to make multiple changes to an Excel file in no time at all…you can use that extra time to grab yourself a nice cup of coffee!