In this article, we are going to see how to Replace or Change Names in Formulas with Cell Reference in Excel Office 365 using Kutools. Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Note: kutools for Excel has more than 180 features which are used to complete the difficult task with several clicks. If you want to use Kutools, you need to install it from Excel’s official website.
Replace Names within Formulas with Cell References in a Range:
If you want to replace names within formulas with cell references in a range, do as below steps.
- First, you need to select the range which contains formulas in the Excel spreadsheet.
- Go to the Kutools Tab, select the Name Tools option, then choose the Convert Name to Reference Range option from the menu list.
- It will open the Convert Name to Reference Range Dialog Box.
- In this dialog box, you will see all formulas with names of the range listed under the Range Tab.
- You need to check the formula that you want to replace names with actual cell references in the list.
- Then, click the Replace button.
- It will display a prompt box to tell you how many formulas have been replaced with cell references.
- Then, click the OK button to close the prompt box.
- At last, you will see that the selected range names have been replaced with cell references as shown in the below image.
Change Names within Formulas with Cell References in a Worksheet:
To change names within formulas with cell references in a worksheet, follow the below steps.
- you need to open the worksheet or workbook which contains formulas.
- Go to the Kutools Tab, select the Name Tools option, then choose the Convert Name to Reference Range option from the menu.
- It will open the Convert Name to Reference Range Dialog Box.
- On the Worksheet Tab , you need to select the All Worksheets option in the Base Worksheet drop-down list.
- Now, you need to check the formula that you want to replace names with cell references.
- Then, hit the Replace option.
- It will display a prompt box to tell you how many formulas have been replaced with cell references.
- Then, click the OK button to close the prompt box.
- Finally, all names within formulas have been replaced by the cell references as shown in the below screenshot.
Only Replace Specified Names within Formulas with Cell References:
If you want to replace all formulas which are used a specific name in the worksheet, then follow the below steps.
- Open the sheet that contains formulas.
- On the Kutools Tab, select the Name Tools option, then choose the Convert Name to Reference Range option from the menu.
- It will open the Convert Name to Reference Range Dialog Box.
- Under the Name Tab, you can list all formulas according to specific names by using the Base Name drop-down list.
- You need to check the Sheets that contain the specific formula which you want to replace. Here we choose Quantity.
- Then, hit the Replace option.
- It will display a prompt box to tell you how many formulas have been replaced with cell references.
- Then, click the OK button to close the prompt box.
- At last, the range name you have specified has been replaced with the cell references as shown in the below screenshot.
Note:
- View cell – This option will help you to view the cell when you click the item in the Convert Name to Reference Range Dialog box.
Verdict:
In this article, you can clearly understand the steps to Replace or Change Names in Formulas with Cell Reference in Excel Office 365 using Kutools. ** Drop your **feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!
Top comments (0)