Home // Video Tutorials // 3 - 6 (Intermediate) // 4 // Excel Find Replace Tool

Excel Find Replace Tool

Ways to use the Excel Find Replace Tool

Using the Excel Find Replace Tool

In this section you will learn to use the Excel Find Replace Tool, and in particular use it to find cells that contain external links.

Use it to find risky functions, and how to avoid making errors when you use the REPLACE feature.

The FIND/REPLACE feature in Excel, is another extremely useful tool that has been built in and existed in Excel for years. Most people associate this tool with the equivalent in Microsoft Word, which is literally used just to find words and replace them if necessary, however there are applications for FIND/REPLACE in Excel to find risky functions, and probably just as importantly to find those frustrating cells which contain the external links.

Firstly how to use the tool; in the traditional sense if you’d like to FIND or FIND and REPLACE something in your spreadsheet

  • you can just go to Edit,
  • and we’ll go to Replace
  • and depending on your version of Excel it will look something like this, where you have the ability to either just Find something first, or Replace it,
  • now what you might notice here is that there is an extra section here from what most people have,
  • if you click on the Options, this can be hidden and unhidden, and this provides a couple of new features especially in the more recent versions of Excel.
  • So for example for this Replace, you type in the word that you want to replace, lets say we’re interested in replacing Product,
  • you might mention what you want to replace it with,
  • and then there is a couple of choices you can make , if you want you can specify that only a certain format must be met,
  • also in the more recent versions of Excel a very, very interesting feature has been brought in. Traditionally FIND/REPLACE you could only search in the sheet you are currently looking in, but the newer version gives you the opportunity to search across a whole workbook. Which makes it a lot easier especially with workbooks getting bigger and bigger,
  • you can also specify how you want to search, whether by rows or columns
  • specify whether you want to Match the case, Match the entire contents.
  • you can look in Formulas, now if your doing Replace, it’ll only look in formulas,
  • however if you doing a Find, you can actually say look inside formulas itself, or
  • maybe look in the results of the formula or
  • look in the comments, so there’s quite a few nifty features here,
  • you can then decide to Replace all of them
  • and we can run that
  • and you’ll see it just mentions how many have been replaced
  • and you’ll see that it has replaced it here.

As with all Excel’s features, the unbelievable flexibility and automation they provide does have a risk, and the FIND/REPLACE feature is no different. It is important to understand what is actually happening when you do a FIND/REPLACE,

  • if you clicked on only one cell, and you do a FIND/REPLACE, what excel does is it looks through the entire spreadsheet and makes a replacement,
  • Alternatively if you highlight a section, what it’ll do is it’ll look through only that section and make the FIND/REPLACE.

The reason this is important is because errors can happen if you do not know what you are trying to replace. So for example in this sample spreadsheet, what we want to do is we want to just replace these B’s with their new name which is BB. So without thinking

  • we clicked on one cell at the moment
  • we go we Replace,
  • we say find all the B’s, Replace with the BB’s
  • without thinking we say Replace all,
  • there’s an external link here, which we are not too concerned about, we say cancel,
  • it warns you and tells that that Excel has completed and made 13 replacements
  • we say Ok that’s fine,
  • we close
  • and what you’ll see here it has correctly changed the B’s to the BB’s here, but for some reason where there was numbers previously here, we seem to have a bit of a problem,
  • and if you look into these cells you see that, suddenly as you go down, everything is referring to BB’s, and this is because previously these cells were referring to, B column B, but you have gone through and said replace all the B’s with BB’s, and therefore it has gone through and replaced all, including what is in the formula, with BB’s. This is quite a common problem and can happen if you do not think about what you are trying to Replace,
  • so if we undo this,
  • the solution to that is to emphasis if you know where the changes need to be made, highlight that area,
  • and do a Replace,
  • change the B’s to BB’s
  • you’ll replace them all,
  • you’ll see there’s only 2 replacements made,
  • you can say ok,
  • and close,
  • now only this has been changed,
  • Because of the nature of this spreadsheet the name is used and therefore it is going to another cell and looking for BB which doesn’t exist, hence these problems and you can sort these out in the other workbooks. But what hasn’t happen is your formulas haven’t changed and that is very important to remember

FIND/REPLACE can be used in a reviewing and auditing context in the following way; if you received a spreadsheet or you are looking at your own spreadsheet, you know from going through this course that certain functions are a little bit more risky than others. Now if it’s a big enough spreadsheet it will be difficult to find what functions are being used. But using the FIND it’s fairly easy,

  • so you’d click on Edit,
  • Find,
  • and for example we know that VLOOKUP poses a few problems, and if you just want to check those,
  • you go to Find what, you say VLOOKUP.
  • If you can if you do have the latest versions of Excel, specify it Look in the entire workbook,
  • You say Find next
  • and it’ll go through each cell that contains a VLOOKUP command, which you can then look at to make sure that it’s being doing it correctly.

Obviously if you have a tool like Spreadsheet Professional, you don’t need to go through this process, if you’re working on a machine without Spreadsheet Professional u can use this way of doing it.

Another useful benefit of the FIND/REPLACE is the ability to find which cells contain links to external spreadsheets, and in particular which links. So for example

  • this FIND/REPLACE if we look at the links, we’ll see there’s 3 external links used in the various cells.
  • the first one to worry about is this Colouring Cells Exercise,
  • so by coming to the Links feature
  • just look at the name; Colouring Cells
  • you say Close
  • go Edit
  • Find,
  • you don’t need to type in the entire name, just enough of the name to make it unique
  • when you say Find next,
  • Excel shoots through and finds that for whatever reason, there’s an obscure external link reference in column BJ
  • and you can then delete that,
  • getting back to the other Links,
  • looking here you can see that there’s links to Input Data Version 1 and Version 2,
  • what you can do then is say well I know Version 1 is incorrect, but let me see where it is looking at Version 1
  • so you close that,
  • you do a Find
  • in this case you’re looking for Input Data Version 1,
  • when you say Find next
  • it’ll point out that that cell there is looking at Version 1, so you know where to make the correction
  • you go to the next one
  • that one there is also looking at Version 1
  • and so you can quickly identify where the cells are that use external links.

For information purposes if you perform this, and if for whatever reason Excel says it cannot find the wording you use for the external link, there’s a very good chance that your external link is actually in a graph or in a named range, within the spreadsheet. So if that happens

  • find your graphs
  • right click on them
  • go to Source Data
  • and you’ll probably find that the graph itself, is going outside the spreadsheet