Categories
Interior

How to search through comments in excel

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to filter cells with comments in Excel?

It is common for us to insert comments in worksheet which we can mark some important information or details. But, in certain condition, we would like to filter only the rows that contain comments and put them together for easily viewing. The Filter feature in Excel is not available for us to solve this job directly. In this article, you will learn how to filter cells with comments in Excel.

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Filter cells with comments by creating a helper column

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

To filter the commented rows only, you can identify the comment cells with User Defined Function first and then apply the Filter function.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: Identify the cells which contain comments

3. Then save the code and close the Module window, and go back to the worksheet, enter this formula =HasComment(B2) into a blank cell next to the comment cell,(B2 contains the value that you want to use) see screenshot:

How to search through comments in excel

4. And then drag the fill handle to the range cells that you want to apply this formula, and you will get TRUE or FALSE in the cells, TRUE stands for the cells which have comments and FALSE indicates no comments.

5. After identifying the comment cells, select the data range and then click Data > Filter, see screenshot:

How to search through comments in excel

6. Then click the drop down arrow beside the helper column, check TRUE option only, see screenshot:

How to search through comments in excel

7. And click OK button, then the comment rows have been filtered out as following screenshot shown:

How to search through comments in excel

8. At last, you can delete the contents of column C as you need.

Here’s a handy Excel macro that quickly searches and finds the comments you’ve posted in cells.
Bill Jelen

David H. asks,“Using the ‘Find’ feature in Excel works when you need to locate items quickly, but is there a way to use this, or another function, that will expand the search to include text in any comments within the worksheet?”

Cell comments are a great way to document a spreadsheet. But if you later need to search all cell comments that include particular text, it is fairly difficult.

One option is to use the Show All Comments icon on the Review tab in Excel. This will display all of the comments. If you have a small data set, then it is easy to read through the comments, looking for certain text. However, this solution is not perfect. If the text of the comment extends beyond the size of the comment, you will not notice text that extends beyond the comment.

How to search through comments in excel

The next option is a macro to search through comments. Follow these steps to add a macro to the workbook:

  1. Type Alt+T followed by M, S to check your macro security. If the setting is currently “Disable all macros without notification”, change it to “Disable all macros with notification”.
  2. Use File, Save As to save your file as either an XLSM or XLSB or XLS file type. The default XLSX file precludes macros.
  3. Use Alt+F11 to switch to VBA
  4. Select Insert, Module to insert a new module into your workbook
  5. Copy and paste the following into the blank module.

Dim c As Comment

Dim SearchFor As Variant

Dim TxtToSearch As Variant

Dim Ctr As Integer

Dim Msg As Variant

SearchFor = LCase(InputBox(Prompt:=”Text to find in comments?”))

For Each c In ActiveSheet.Comments

If InStr(1, TxtToSearch, SearchFor) > 0 Then

Msg = ” comments found:” & vbLf & c.Parent.Address

Msg = Msg & vbLf & c.Parent.Address

MsgBox x & ” not found in current worksheet comments.”

MsgBox Ctr & Msg

Then continue with these next three steps:

  1. Pre Alt+Q to close VBA and return to Excel.
  2. Press Alt+F8 to display a list of macros.
  3. Select SearchCommentsOnCurrentWorksheet and press Run

The macro will ask you for the text that you want to find.

How to search through comments in excel

Enter some text. Click OK. The macro will find all comments that contain the text. The comment will be shown for those cells and a list of the cells will appear in the dialog box, as shown in Figure 3.

How to search through comments in excel

To subsequently hide the comments, click the Show All Comments icon in the Review tab of the ribbon. Click this icon twice. The first click will show all comments. The second click will hide all comments.

You can also use this macro to find all comments created by one particular person. Here are all of the Joe Smith comments (see Figure 4).

How to search through comments in excel

Caution: if you regularly keep some comments displayed to provide instructions, you should not use this macro, as it will hide all comments.

If you find that this macro is useful and you will want to have it always available, you can move the code to your personal macro workbook and assign the macro to an icon on the Quick Access Toolbar.

CFO Contributor and Microsoft MVP Bill Jelen is the author of 34 books about Microsoft Excel, including Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved. Bill regularly selects reader questions from the Community Center (to the right of this article) to answer in his columns.

How to search through comments in excel

Comments in Excel are great for making notes about certain cells so you can keep track of your work. If you’ve added a lot of comments to your worksheets, it can get hard to remember where you put a specific note in a comment.

However, you can search through only the comments in your worksheet or workbook, with this advanced setting.

You can start the search from any cell in your worksheet. Excel will continue to cycle the search through the worksheet and search all the cells. Press Ctrl+F on your keyboard to open the Find and Replace dialog box with the Find tab active. Click “Options”.

How to search through comments in excel

To narrow the search to only comments, select “Comments” from the “Look in” drop-down list.

How to search through comments in excel

By default, Excel will only search the current worksheet. If you want to search through all the comments on all the worksheets in your workbook, select “Workbook” from the “Within” drop-down list. Click “Find Next” to start the search.

How to search through comments in excel

The first cell with a comment attached to it that contains the search term you entered is highlighted. The comment is not automatically shown. However, you don’t have to close the Find and Replace dialog box to view or edit the comment on the selected cell. The Find and Replace dialog box is a non-modal dialog box, which means you can work on the worksheet behind it while the dialog box is still open. If your comments are hidden, move your mouse over the selected cell to view the comment.

How to search through comments in excel

If you want to edit the comment, right-click on the cell and select “Edit Comment” from the popup menu.

How to search through comments in excel

The comment is shown (if it was hidden) and the cursor is placed within the comment, allowing you to edit it. When you’re finished editing the comment, click in any other cell. Your changes to the comment are saved and the comment is hidden again, if it was previously hidden.

How to search through comments in excel

To continue searching through your comments, click “Find Next” again on the Find and Replace dialog box to find the next occurrence of your search term. When you’re finished with your search, click “Close” to close the Find and Replace dialog box.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to highlight all cells with comments in Excel?

Comment is a useful feature for us to write some important information in Excel, after inserting the comment, there will be a small red indicator displayed in the cell. But sometime, the red indicators are not very striking, so we can highlight all the cells with comments with a specific color to make them more standout. Please read this article to know more details.

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Highlight all cells with comments with Go To Special function

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

In Excel, we can select all the comment cells first, and then give them a color we like, please do as follows:

1. Select the data range or the whole worksheet that you want to highlight the cells with comments.

2. Then click Home > Find & Select > Go To Special, see screenshot:

How to search through comments in excel

Tip: You can press shortcuts Ctrl + G to open the Go To dialog, and click Special to open the Go To Special dialog.

3. In the Go To Special dialog box, check Comments option, see screenshot:

How to search through comments in excel

4. Then click OK to close this dialog, and all the cells with comments have been selected in your selection, see screenshot:

How to search through comments in excel

5. After selecting the comment cells, then click the Fill Color icon under the Home tab, and choose one color you like, all the selected cell comments will be colored at once, see screenshot:

How to search through comments in excel

Highlight all cells with comments with VBA code

You can also highlight and color all the cell comments in the active worksheet immediately with following VBA code:

1. Activate your worksheet that you want to highlight the comment cells.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: highlight all cells with comments

4. Then press F5 key to run this code, and a prompt box will pop out to remind you to select the range you want to highlight the comment cells, see screenshot:

How to search through comments in excel

5. And then click OK, all the comments in your selection have been highlighted by color.

The old comments are now called “Notes”. The New Comments are actually discussion threads – like in Word. You can type a comments and others can reply to it. Spend 4 minutes to learn about the new comments.

New Comments look like this… a pink sign in the top right corner.

How to search through comments in excel

What happened to old comments?

Of course, the old comments will stay. Existing comments will work exactly the same way as before. They are still the same old Yellow color and show up as a red triangle in the right top corner of the cell.

The difference is that those are now called “Notes”. You get a new menu next to the existing Comments menu in the Review tab of the ribbon.

All the existing features have now moved to the Notes dropdown now. And of course, you can convert the older type of comments to newer type of comments.

How to search through comments in excel

What is the benefit of New Comments?

The benefit is collaboration. Earlier, each comment was separate. So if someone wants to comment on your comment, a new comment was added. This created too many comments. It was difficult to know which comment was added when and in what order.

Now, the New Comments feature makes this easier. The comment you add has a reply button. The reply also has another reply button.

Therefore, the comment becomes a base opinion followed by further discussion on that topic.

@mentions in New Comments

If you want specific comment from someone, you can specify @

How to search through comments in excel

How will that person know that you have added her name inside a comment? Don’t worry.. all of it is taken care of.

When you add someone as @mention, Excel will check if they already have access to the file. If not, you will be asked to Share the file with them.

How to search through comments in excel

What happens when you click Share and Notify? The same thing which would happen if you had gone to the regular Share button – chosen Specific People and given edit permission to the person. The person is given edit permission and receives a mail with link to the file.

Word and Excel as well

The New Comments feature is available in Word and PowerPoint as well. @mentions and associated sharing is also supported.

In case of Word, each comments has an additional button called Resolve. This is useful when someone has put comments in your file while reviewing it.

How to search through comments in excel

You are trying to go through the comments one-by-one and act upon each comment. When you finish working on the comment, you want to remember it. That is when you click the Resolve button. Now the comment becomes greyed out so visually indicate that it is resolved.

So start using the new comments and handle document collaboration more efficiently.

If your worksheet contains threaded comments or notes, you can print them.

Note: In Excel for Microsoft 365, there are two kinds of comments to choose from: threaded comments and notes. For more information, see the article Threaded comments and notes.

Select the worksheet that contains the comments that you want to print, then do one of the following:

To display an individual comment, right-click on the cell, and select Show/Hide Comments.

To display all comments in the worksheet, go to the Review tab > Show all Comments.

To move or resize any overlapping comments, click the border of the comment box so that its sizing handles appear.

How to search through comments in excel

You can resize the comment box by dragging any of its sizing handles, or move it by selecting any part of its border, then drag it where you want.

On the Page Layout tab, in the Page Setup group, click the flyout on the right to launch the Page Setup dialog.

How to search through comments in excel

Select the Sheet tab.

In the Comments box, choose either At end of sheet or As displayed on sheet.

How to search through comments in excel

Click Print, or Print Preview to see a preview of the comments.

Select the worksheet that contains the threaded comments and notes that you want to print.

On the Page Layout tab, in the Page Setup group, click the flyout on the right to launch the Page Setup dialog.

How to search through comments in excel

Select the Sheet tab.

In the Comments box, click At end of sheet.

Note: In Excel for Microsoft 365, you can’t print threaded comments as they’re displayed on a worksheet, but you can print notes. See the next section on printing notes in Excel for Microsoft 365.

Click Print, or Print Preview to see a preview of the comments and the notes.

You can print notes the way they appear on the sheet.

Select the worksheet that contains the notes that you want to print, then do one of the following:

To display an individual note, right-click the cell and click Show/Hide Note.

To display all notes, go to the Review tab > Notes > Show All Notes.

To move or resize any overlapping notes, click the border of the note box so that its sizing handles appear.

How to search through comments in excel

You can resize the note by dragging any of its sizing handles, or move it by selecting any part of its border, then drag it where you want.

On the Page Layout tab, in the Page Setup group, click the flyout on the right to open the Page Setup dialog box.

How to search through comments in excel

Select the Sheet tab.

In the Comments box, click As displayed on sheet (legacy).

Note: In Excel for Microsoft 365, you can’t print just notes at the end of the sheet. However, you can print both threaded comments and notes at the end of the sheet. See the previous section on printing threaded comments and notes in Excel for Microsoft 365.

Click Print, or Print Preview to see a preview of the notes.

Select the worksheet that contains the comments that you want to print, then do one of the following:

To display an individual comment, right-click on the cell right-click the cell and select Show/Hide Comments.

To display all comments in the worksheet, go to the Review tab > Show all Comments.

To move or resize any overlapping comments, right-click and select Edit Comment, and the border of the comment box will display sizing handles.

You can resize a comment by dragging any of its sizing handles, or move it by selecting any of its borders, then drag it where you want .

On the File menu, click Page Setup.

Select the Sheet tab.

In the Comments box, choose either At end of sheet or As displayed on sheet.

Select the worksheet that contains the threaded comments and notes that you want to print.

On the File menu, click Page Setup.

Select the Sheet tab.

In the Comments box, click At end of sheet.

Note: In Excel for Microsoft 365 for Mac, you can’t print threaded comments as they’re displayed on a sheet. But you can print notes as they’re displayed on a sheet. See the next section on printing only notes in Excel for Microsoft 365 for Mac.

You can print notes the way they appear on the sheet.

Select the worksheet that contains the notes that you want to print, then do one of the following:

To display an individual note, right-click the cell and click Show/Hide Note.

To display all notes, go to the Review tab > Notes > Show All Notes.

To move and resize any overlapping notes, right-click and select Edit Note, and the border of the note box will display sizing handles.

You can resize a note box by dragging any of its sizing handles, or move it by selecting any of its borders, then drag it where you want .

On the File menu, click Page Setup.

Select the Sheet tab.

In the Comments box, click As displayed on sheet (legacy).

Note: In Excel for Microsoft 365 for Mac, you can’t print just notes at the end of a sheet. However, you can print both threaded comments and notes at the end of a sheet. See the previous section on printing threaded comments and notes in Excel for Microsoft 365 for Mac.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

In this lesson, we’ll look at the difference between finding what is stored in a cell and finding what is displayed in a cell.

Let’s take a look.

When you use Find, there are three options for the “look in” setting on the Find tab: formulas, values, and comments. Comments are self-explanatory, so let’s focus on formulas and values. You can think of formulas as whatever is stored in the cell—the underlying formula—and values as whatever is displayed in the cell.

The naming is a little confusing, so let’s take a look at some examples.

Excel’s default setting for “look in” is formulas, and this makes sense in most cases. For example, we can easily search for and find names like Cecil. or Hana. Or, we can search for a certain order number, like 23464. But watch what happens when we search for $19.89, or the month March, abbreviated “Mar.”

In both cases, Excel finds no match, even though we can see the values in the table. This is because Excel is searching formulas—what is stored in the cell—and not the values as displayed.

If we look at the Tax column, we can see this is actually a formula. So $19.89 is not actually stored in the cell G11.

If we look in the Date column, we can see these dates are stored in day/month/year format without the month name. In other words, what is displayed in the Date and Tax columns is different from what is being stored. To search for what is displayed, we need to change the settings for “look in” from Formulas to Values.

Now Excel will search values as displayed. We can find $19.89 and dates in March without any problem.

I use this handy equation to search for a term inside of a cell in excel.

This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1.

All I’d like to do is search for more than one term at a time. Anyone know how to add an OR function into this so I can search for Gingrich OR Obama OR Romney etc. ?

How to search through comments in excel

4 Answers 4

Also, if you keep a list of values in, say A1 to A3, then you can use

The wildcards are not necessary at all in the Search() function, since Search() returns the position of the found string.

Try using COUNT function like this

Note that you don’t need the wildcards (as teylyn says) and unless there’s a specific reason “1” doesn’t need quotes (in fact that makes it a text value)

How to search through comments in excel

This will do it for you:

Given this function in the column to the right of the names (which are in column C), the result is:

How to search through comments in excel

In addition to the answer of @teylyn, I would like to add that you can put the string of multiple search terms inside a SINGLE cell (as opposed to using a different cell for each term and then using that range as argument to SEARCH ), using named ranges and the EVALUATE function as I found from this link.

For example, I put the following terms as text in a cell, $G$1 :

Then, I defined a named range named search_terms for that cell as described in the link above and shown in the figure below:

How to search through comments in excel

In the Refers to: field I put the following:

The above EVALUATE expression is simple used to emulate the literal string

to be used as input to the SEARCH function: using a direct reference to the SINGLE cell $G$1 (augmented with the curly braces in that case) inside SEARCH does not work, hence the use of named ranges and EVALUATE .

The trick now consists in replacing the direct reference to $G$1 by the EVALUATE -augmented named range search_terms .

It really works, and shows once more how powerful Excel really is!

Chandoo

Excel has a very useful feature called “cell comments” using which you can add comment to a cell. This is a very good way to gather remarks and review comments when a workbook is shared with colleagues and others. But what if you have typed a ton of cell comments and now want a way to extract them and do something with that data?

Well, no need to select each comment and copy the contents. You can use a simple user defined formula (UDF) to do just that. Here is a one line formula that I wrote.

To make it work,

To make the getComment() UDF work for you, you need to install it first. Here is a step by step guide if you dont know how to install user defined formulas in excel.

The simple and easy version:

  1. Copy the above vba code
  2. Open a new excel file
  3. Right click on any sheet tab name and select “view code” option.
  4. Don’t be scared of the VBA editor. Take a deep breath.
  5. Click on “Insert” menu option and select “Module”
  6. Paste the code in the new module
  7. Save the workbook and start using the getComment() formula.

The lengthy and more stable version:

  1. Same as first 6 steps above.
  2. Press ctrl+s to save the work book. Specify “excel add-in” as the file type.
  3. Now, go back to excel and install this new add-in
  4. That is all. Now you can use getComment() formula in your workbooks.