Welcome Excellers to another #formulafriday blog post. Today let’s take a look at how to quickly change your Excel formula without having to rewrite any of those formulas. I have found this extremely useful when a request for a spreadsheet solution can suddenly change at the last minute from a user or even your boss. That is correct you can use Find and Replace to update a large number of Excel formulas with not a lot of effort.
Using Find And Replace – A Recap
First, let’s step this back to basics and look at the Find and Replace features in Excel. After all, you don’t want to be scrolling through hundreds if not thousands of Excel cells. Let’s start with how to find characters, text, numbers or dates in a range of cells, worksheet or the entire workbook.
- First, select or highlight the range of cells you want to search in. Alternatively, to search the whole worksheet simply select any cell in the active worksheet.
- Open the Find and Replace Dialog Box. Do this with Ctrl+F for the keyboard shortcut fans out there. Or you can hit Home Tab | Editing Group | Find And Select.
Next, in the Find What box, you need to enter the data you want to locate. You can choose at this stage to select (optional) the Options button to expand the dialog box and specify any desired options.
Within: Search just the current worksheet or the entire workbook.
Search: Select whether to search first across the rows or down the columns.
Look In: Select whether you want to search through the values or formula results, through the actual formulas, or if you want to look in the comments.
Match Case: Check this box if you want your search to be case-specific.
Match Entire Cell Contents: Check this box if you want your search results to list only the items that exactly match your search criteria.
- Click Find Next. Excel will find and jump to the first match of your search. If this is not the entry you’re looking for, click Find Next again. Excel advises you if it doesn’t locate the data you’re searching for.
Let’s Work Through An Example
So, now we have recapped how easy it is to use Find and Replace with Excel formulas. let’s get it working hard and update a spreadsheet with a lot of formulas that need changing. The example I am going to use is a worksheet which is recused every year. Everyone is happy with it and we do not need to redesign it every year. We do, however, have to update the formulas every year with Sales Targets from a different worksheet. That’s easy!.
Here is a very small working example of this. I have sales targets for the North, South, East and West Regions. These are located in the following location ‘C:\Sales’ in an Excel worksheet called ‘Sales Targets.xlsx’ Every year a new worksheet is added with the relevant year’s targets.
Also in this folder is the Sales Performance Workbook. This is linked to these Sales Target Figures. Here is how quick it is to update the formulas that still point to the 2018 targets to those of the 2019 targets.
Setting Up the Find And Replace For Excel Formulas
Select the cell range that you want to update. I have chosen the cell range L3: L7 in my Sales Performance Workbook. (I told you this was a very small example). Currently, the formula in this workbook is using the targets data from the worksheet named 2018 in the Sales Targets workbook.
I need this formula to point to the 2018 worksheet. So, first, we need to select the range of cells to update. Open the Find And Replace dialog box either with Ctrl+H shortcut or you can hit Home Tab | Editing Group | Find And Select.
In the Find box type the part of the Excel formula that you want to find. In this case, it is ‘2018’ and we then want to go ahead and replace it with ‘2019’
Now, if you have hundreds or even thousands of cell with Excel formulas to update then this is a really fast way to update formulas. I have used this to change SUM to AVERAGE, AVERAGE To MAX and MIN. In addition pointing formulas to differently workbooks or worksheet,ts as in this example.
What’s Next?
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series. Click on the link below
How To Excel At Excel – Formula Friday Blog Posts.
