Excel Range Names + Find & Replace (Fast Navigation)

Key Takeaways

Range names are meaningful labels that you can assign to individual cells or cell ranges. You can use a range name anywhere you would use a cell or range reference. For example, you can use a name such as clients to describe a range of cells rather than referencing cells A1 to A15 directly. To apply a cell name or range name, first use your cursor to select the cells that you want to name. Next, type the name that you would like to use into the name box. Pressing enter will apply this name. From then on, you'll be able to select this range by clicking the name box drop-own menu and clicking on the range name that you set. You will look at cell and range names in more detail later in the course. You should note that range names are not case-sensitive, cannot contain spaces, and cannot start with a number. Excel offers a number of tools to help you find values, objects, and formatting in your worksheet and replace those items as necessary. Thomas Bradshaw's manager at Westcal Publishing noticed that the naming of some items in the company accounts workbook is incorrect because the name of the client has recently changed. Thomas needs to be able to find multiple occurrences of this name throughout the workbook and change them all. One of Thomas's colleagues mentioned that there was a quick way to do this using the find and select feature in Excel. Let's explore what this feature has to offer and how to use it. The find command is used to locate information or data within the current workbook. A single cell value or multiple cells with the same value can be replaced with an updated value if required. To access the find and replace feature, click find and select then find or press control and F. Either action will display the find and replace dialogue box with the options hidden by default. The find and replace dialogue box has lots of options on the find tab to help with this task. Let's have a look at the commands in this dialogue box. The find what drop-own menu allows you to display any recently searched items. The options button will expand this dialogue box and show you more search options such as matching text case. The find all button will execute the search command and look for all instances within the worksheet. The find next button, on the other hand, will allow you to search for the next occurrence of the search term. Clicking it again will highlight the next occurrence and so on. If you click the options button, you have more choices to refine your search. You can now choose to search formatting within a sheet or workbook, in rows or columns, or by matching the case, all by making selections in the drop-own boxes. The find and replace dialogue box contains some powerful advanced search options that you can use to find exactly what you looking for in a workbook. Clicking the format button will open the find format dialogue box. Here you can specify the exact format type that you would like to search for. The format drop-down menu allows you to choose a format to look for based upon an existing cell. The within drop-own menu allows you to choose between searching the current worksheet or the entire workbook. The search drop-down menu is used to specify if the search should be performed by row or column. The lookin drop-own menu is used to specify if you want to search formulas, values, or comments. The match case checkbox allows you to specify whether you want the results of your search to match the exact text case of your search terms. Similarly, the match entire cell contents checkbox will only return results that are the exact and complete characters of the search terms. If you click the replace tab in the find dialogue box, you have the option to search for a term and replace it with something else. You also can jump directly to this tab by pressing controll and h or by clicking find and select then replace. If any instances of the search criteria entered in the find what text field are found, they can be replaced with whatever is in the replace with text field. All you have to do is click the replace button each time Excel finds something. If you click the replace all button, Excel will replace every instance of the word or number it finds with the replacement term without waiting for you to click replace for each one. The options button will show additional controls and commands that you can use to provide more specific detail for your replace action. Finder and place also lets you search for and replace certain types of formatting. In the second half of the dialogue box, there are options to search worksheets, workbooks, and formulas. Once you're happy with your options, click replace to update the format one cell at a time, or click replace all to perform all of the replacements at once. The go-to command enables you to locate and navigate to a named cell or cell range within your workbook. You can also use certain criteria to navigate to specific areas of a workbook. This can be useful if you have a large workbook with multiple worksheets and you need to quickly navigate to specific data ranges or named areas. To view the goto window, click the home tab, then find and select, then go to. You can also directly open the go-to dialogue box by pressing controll and g on your keyboard. Using this dialogue box, you can quickly go to any named range within your workbook. For example, here you can see the headers range that we can quickly go to and select. Clicking the special button will display the go-to special dialogue box that allows you to select cells based on chosen criteria. For example, you can choose blank cells, cells that contain comments, and much more. You can display this dialogue box directly by clicking find and select, then go to special.