How to Filter Cells with Notes in Excel?

Notes are used in Excel to make notes or annotations about the data, like in the example below:

A small red triangle in the top right corner of the cell indicates a note. To open the note, hover the cursor over the cell with the red triangle.

When working with large datasets, filtering cells with notes can be a useful way to focus on specific content.

Sometimes you may want to filter cells with notes in Excel so that, for example, you can quickly find and review the notes added by a colleague. 

This tutorial shows three techniques for filtering cells with notes in Excel.

Note: In the Excel versions before Excel 365, Notes were called comments.

Method #1: Use the Go To Dialog Box

We use the Go To dialog box in Excel to quickly navigate to a specific cell or cell range within a worksheet.

For example, we can use the dialog box to select all cells that contain notes in a dataset. We can then apply a fill color to the selected cells and finally filter the colored cells with notes.

Suppose we have the following dataset with notes in cells D3, D5, and D6.

We can use the following steps to filter the cells with notes:

Step #1: Use the Go To Dialog Box to Select Cells With Notes

Below are the steps to select all the cells with notes (called comments in the older versions of Excel) in them

1. On the current worksheet containing the dataset, press F5 to open the Go To dialog box. Click the Special button on the dialog box.

2. On the Go To Special dialog box that appears, select Notes and click OK.

All the cells with notes are selected:

If you’re using an older version of Excel, you will see the Comments option instead of Notes in the Go To Special dialog box. Follow the same steps and select the Comments option instead.

Step #2: Apply a Fill Color to the Selected Cells

Use the following step to apply a fill color to the selected cells:

1. On the Home tab, open the Fill Color drop-down on the Font group and click the color of your choice. In this example, we have chosen yellow.

The selected cells which contain notes now have a yellow fill color:

Step #3: Filter the Colored Cells With Notes

Use the following steps to filter the colored cells with notes:

1. Select the cell range D1:D6:

2. On the Data tab, click the Filter button on the Sort & Filter group.

3. Click the Filter drop-down in cell E1, hover the cursor over the Filter by Color option, and choose the yellow fill color on the submenu.

The colored cells with notes are filtered.

When using this method, make sure you choose a color that has not already been used in the column. This way, when you filter the column, it will only filter cells that have notes in them.

Also read: VBA Macro Codes to Filter Data In Excel

Method #2: Use the Find and Replace Dialog Box 

While the usual use of the Find and Replace feature in Excel is to help us find cells with specific text, numbers, or formatting and replace them with something else, you can also use it to select all cells containing a particular author’s notes.

This can be useful when you want to filter all the cells that contain comments inserted by a specific person. The author’s name in the comment is automatically taken from the system’s settings.

Once we have found and selected these cells, we can then apply a fill color to the selected cells and finally filter the colored cells with notes.

Let’s consider the following dataset with notes in cells D3, D5, and D6. Christopher Sirali has authored all the notes.

We can use the following steps to filter the cells with notes made by Christopher Sirali:

Step #1: Use the Find and Replace Dialog Box to Select Cells With Notes Made by a Particular Author 

Below are the steps to find all the cells with comments

1. Press Ctrl + F to open the Find and Replace dialog box.
2. Do the following on the Find tab of the Find and Replace dialog box:

• Type “Christopher Sirali” in the Find what field.
• Open the Look in drop-down and choose the Notes option.
• Click the Find All button.

3. Press Ctrl + A to select all the cells found. The cells’ references are highlighted in blue at the bottom of the Find and Replace dialog box.

And the cells in the dataset are selected:

4. Dismiss the Find and Replace dialog box by clicking the Close button.

Pro tip: In case you want to find and then filter all the cells with notes, irrespective of the author name, enter * (asterisk symbol) in the Find what field in Step 2. When you use an asterisk, it is going to find and select any cell that has a note with any text in it

Step #2: Apply a Fill Color to the Selected Cells

We use the steps below to apply a fill color to the selected cells in the dataset:

1. On the Home tab, open the Fill Color drop-down on the Font group and click the color of your choice. In this example, we have chosen light green.

The selected cells, which contain notes made by Christopher Sirali, now have a light green fill color:

Step #3: Filter the Colored Cells With Notes 

We use the following steps to filter the colored cells with notes:

1. Select the cell range D1:D6 (or you can select any cell in the dataset)

2. On the Data tab, click the Filter button on the Sort & Filter group.

3. Click the Filter drop-down in cell E1, hover the cursor over the Filter by Color option, and choose the light green fill color on the submenu.

The colored cells with notes are filtered.

Method #3: Use a User Defined Function (VBA)

We can apply a user-defined function created in Excel VBA to filter cells with notes in Excel.

Let’s consider the following dataset with notes in cells D3, D5, and D6.

We want to create a user-defined function and use it to filter the cells with notes.

Step #1: Create the User Defined Function

We use the following steps:

1. On the worksheet containing the dataset, press Alt + F11 to launch the Visual Basic Editor.
2. Click on the Insert option in the menu and then click on the Module option to insert a new module.

3. Copy the following function procedure and paste it into the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function WITHNOTE(k As Range)
Application.Volatile True
WITHNOTE = Not k.Comment Is Nothing
End Function

4. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).

Note: In the VBA code, we have used the comment property because comment means note in Excel 365. A note, in this case, is the same as a comment in earlier versions of Excel.

If you’re interested, here is a line-by-line explanation of the code:

• Line 1: Function WITHNOTE(k As Range) – This line declares a function called WITHNOTE and defines the argument “k” as a range.
• Line 2: Application.Volatile True – This line makes the function volatile, meaning that it will recalculate automatically whenever any of the cells it references are changed.
• Line 3: WITHNOTE = Not k.Comment Is Nothing – This line defines the return value of the function as the logical value of the statement “k.Comment is not nothing”. This statement returns true if the range “k” has an associated comment and false if it does not.
• Line 4: End Function – This line marks the end of the function.

Step #2: Use the User Defined Function in Helper Column

We use the following steps to apply the user-defined function to filter cells with notes in the example dataset.

1. Add a helper column to the dataset:

2. Select cell E2 in the helper column and type in the formula below:

=WITHNOTE(D2)

3. Drag or double-click the fill handle feature to copy the formula down the column.

The WITHNOTE user-defined function returns TRUE if the target cell contains a note; otherwise, it returns FALSE.

4. Select the cell range E1:E6 for filtering.

5. On the Data tab, click the Filter button on the Sort & Filter group. This will apply filters to all the column headers.

6. Click the Filter drop-down button in cell E1, deselect FALSE, leave the TRUE option selected, and click OK.

The cells with notes are filtered as seen below:

Once you have the filter data, you can select the cells in column D (where it only shows the cells that have notes in them), and do whatever you want (delete the notes, modify them, or highlight the cells)

This tutorial has shown three techniques for filtering cells with notes in Excel.

The methods involve using the Go To dialog box, applying the Find and Replace dialog box, and using a user-defined function created in Excel VBA.

I hope you found this tutorial useful. Do leave your thoughts in the comments section below.

Other Excel articles you may also like:

• How to Delete a Comment in Excel (or Delete ALL Comments)
• How to Print Comments in Excel?
• How to Remove Dashes (-) in Excel?
• How to Count Filtered Rows in Excel?
• How to Get Rid of Green Triangles in Excel?