|
|
 |
|
| July 19, 2018 | |
| Hi fellow Excel Addict, | |
| Today's 'Excel
in
Minutes' tip 'How To Locate All Cells In A Workbook That Contain A Specific Worksheet Function' comes from a reader question.
I hope you find this tip helpful. If so, please be kind and share it with others who could use a little Excel help as well. Take care and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com  |
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
Quote of the Day "We realize the importance of our voices only when we are silenced." -- Malala Yousafzai -- |
|
|
If
you have a favourite quote, send it to me and I may post it in my
newsletter.
|
|
 |
|
| How To Locate All Cells In A Workbook That Contain A Specific Worksheet Function | |
Earlier this week Brian asked if there is a way to highlight all of the cells in a workbook that contain a specific worksheet function. The answer is: Yes, there is an easy way to locate all cells in a workbook that contain a specific worksheet function. But in order to select and highlight them on the worksheet you will need to perform the steps below for each sheet separately. 1) Press CTRL+F to open the Find & Replace Dialog; 2) Type the function name plus ( (i.e. an opening parenthesis) in the ‘Find what:’ field. You will need to include the opening parenthesis just in case some functions are within more complex formulas or if there is other text in the workbook that matches the function name. For example, finding the OFFSET function in =SUBTOTAL(9,D38:OFFSET(D44,-1,0)). Otherwise you could use = (i.e. an equal sign) plus function name. For example =SUBTOTAL; 3) Select Formulas in the ‘Look in:’ field; 4) Click the ‘Find All’ button;  All cells found containing the function name will be listed at the bottom of the dialog with the first one highlighted.  5) Press CTRL+A to highlight all items in the list. This also selects the corresponding cells on the worksheet.  6) Close the Find and Replace dialog and the 'found' cells will still be selected on the sheet.  At this point I often apply a different format (e.g. yellow background color) to better highlight these cells if I need to do further analysis. Using Find All for the Entire Workbook If you choose Within: Workbook in the Find and Replace dialog, clicking Find All will find and list the cells on all of the worksheets. However, selecting them from the dialog list allows you to select only cells from one worksheet. So, you need to repeat the above steps for each sheet in order to highlight all of the cells in the entire workbook. |
|
| Locate All Cells Containing A Specific Worksheet Function | |
| If you like this Excel in Minutes tip, please share it... | |
|
|
 |
|
|
|
|
|
|
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets
Tips From An Excel Addict" is a twice-weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |