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
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:
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 chúng tôi example if you want to only find “Marketing” and not cases where the cell is “Marketing team” then you would check this box.
Access the Find and Replace function from the “Home” tab or use the shortcut command CTRL+H.
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 chúng tôi 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 chúng tôi can use that extra time to grab yourself a nice cup of coffee!