How to Filter Cells with Notes in Excel for Microsoft 365?
For users of Excel for Microsoft 365, you may notice that comments are now different from the ones in earlier versions of Excel, like Excel 2021. In Excel 365, Comments are threaded and used for discussions with other people. And Comments come with a Reply box. Looking for the old Comments? They are now called Notes. Notes work the same as Comments in earlier Excel versions. You can add annotations and reminders in cells with Notes. So here comes the question: how do you filter cells with notes in Excel for Microsoft 365? In the tutorial, we will show you one quick way to get the job done.
Filter Cells with Notes by Creating a Helper Column
To filter only the rows with notes, you can identify the Notes cells with User Defined Function first and then apply the Filter function.
1. Press 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 notes
Function HasComment(r As Range)
'Update 20220716
Application.Volatile True
HasComment = Not r.Comment Is Nothing
End Function
3. Save the code and close the Module window.
4. Go back to the worksheet. Please copy the formula below into cell C2, then press the Enter key to get the result.
=HasComment(B2,C5)
5. Select this result cell and drag its autofill handle down to get the rest of the results. TRUE stands for the cells which have notes and FALSE indicates no notes.
6. Select the data range, then click Data > Filter.
7. Click the drop-down arrow of the helper column, and check TRUE option only. Then click the OK button to finish the filtering.
8. Now you can see the filtered results of rows with notes only. You can delete the helper column C as you need.
Notes:
- In the VBA code of Step 2, comment means note in Excel for Microsoft 365 because a note here is the same as a comment in other versions of Excel. The formula in Step 3 is likewise.
- If you want to filter cells with comments in earlier versions of Excel, this method also works.
How To Add A Note In An Excel Chart?
For example, you have created a chart in Excel, and now you want to add a custom note in the chart, how could you deal with it? This article will introduce an easy solution for you.
How To Auto Add Date And Time To Comment In Excel?
This will help you to quickly identify the time you created the comments. This article, I will introduce some tricks for you to deal with this problem in Excel.
How To Convert Cell Contents To Comments In Excel?
Maybe you have a range of cells filled with some information, and now you want to convert those cell contents to comments in a worksheet. Is it possible for you to finish this task in Excel? Here are some quick tricks to solve this problem.
How To Quickly Insert A Comment To Multiple Cells In Excel?
In a worksheet, to insert a comment in a cell is very easy, but when you want to insert the same comment into multiple cells at the same time, how could you do?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
