How to Highlight All Cells With Comments in Excel

The fastest way to highlight every cell with a comment

To highlight all cells with comments in Excel, you have two routes: Go To Special, which selects the cells so you can color them, or a macro that paints them for you. There's no conditional formatting rule for it, because Excel has no formula that returns "this cell has a comment."

Go To Special is quick but temporary. The second you click away, the selection is gone. A macro leaves a fill that stays put while you scroll and review. Here's the comparison.

Method	Applies color for you?	Stays visible after you click away?
Go To Special > Comments	No, you fill manually	Only the fill you add by hand
Conditional formatting	Not possible	N/A
Free macro	Yes, green fill	Yes

TL;DR: Key takeaways

• Excel can't highlight comment cells with a conditional formatting rule. No formula detects a comment.
• Go To Special (or Ctrl + Shift + O) selects every cell with a comment, then you apply a fill by hand.
• A free macro paints every commented cell in your selection green in one pass and reports the count.
• This targets legacy Notes (the yellow-triangle kind), not the newer threaded Comments.
• The highlight is a static fill, so rerun the macro after you add new notes.

Comments or Notes? Clear up the confusion first

This trips people up, so it's worth 30 seconds. Excel for Microsoft 365 split annotations into two things.

Comments are now threaded conversations. You reply, you @mention a colleague, it emails them. Notes are the old-school yellow boxes with the little red triangle in the corner, just a plain annotation with no reply box. Microsoft lays out the split in insert comments and notes in Excel.

Here's the part that matters: Go To Special, the Ctrl + Shift + O shortcut, and the macro below all target Notes, which older versions and VBA still call "comments." They don't select threaded comments. So if you're hunting for the yellow-box annotations an analyst left in a model, you're in the right place.

Method 1: Go To Special (built-in)

The native way selects every cell that has a note, then you color it yourself.

1. Select the range to scan, or press Ctrl + A for the whole sheet.
2. Go to Home > Find & Select > Go To Special.
3. Choose Comments, then click OK. Every cell with a note is now selected.
4. Apply a fill color from the Home tab.

The shortcut Ctrl + Shift + O does steps 1 to 3 in one keystroke. It works, but it's a manual, one-time action. If you add a note next week, you run the whole thing again. And you can't automate it with a rule, because conditional formatting can't see comments.

Method 2: Highlight cells with comments with a macro

To highlight all cells with comments in Excel in one click, use a macro. Download Highlight Cells With Comments. It's a free .xlsm file with one macro. No signup, and it runs offline, so your workbook stays on your machine.

First time running one of our macros? See How to run our tools for the 30-second setup.

1. Select the range you want to scan, including all the columns and rows that might hold notes.
2. Press Alt + F8, pick HighlightCellsWithComments, and click Run.
3. It paints every cell that has a note with a light green fill and reports how many it found.

The logic is a simple walk through your selection:

Sub HighlightCellsWithComments()
    Dim cell As Range
    For Each cell In Selection.Cells
        If Not cell.Comment Is Nothing Then
            cell.Interior.Color = RGB(198, 224, 180)   ' light green fill
        End If
    Next cell
End Sub

The cell.Comment check is what finds the notes. Cells without one are left untouched. The downloadable macro also darkens the font for contrast and counts the cells, so you get a tally of exactly how many notes are hiding in the range.

Why not conditional formatting?

This is the first thing most people try, so it's worth saying plainly: you can't. Conditional formatting rules run on formulas, and there is no worksheet function that tells you whether a cell has a comment or note. No HASCOMMENT(), nothing. It's one of the most common "why won't this work" questions on Excel forums.

That leaves two real options: Go To Special for a quick one-off, or the macro when you want a repeatable, one-click highlight across a whole model.

Example: auditing an inherited model

An analyst at a logistics company in Nagpur inherits a pricing model with about 4,000 populated cells across several tabs. The previous owner left notes all over it, assumptions, warnings, "don't touch this," but they're invisible until you hover on each red triangle one by one.

Finding them by hand would mean scanning every cell for a triangle, on a model dense enough that plenty would get missed. Go To Special would select them, but the moment she clicked a cell to read one, the rest of the selection vanished.

So she selects the used range and runs the macro. Every note cell turns green, 37 of them, and they cluster in two columns she now knows to read carefully before trusting the model. What could have been an hour of hovering becomes a green map in about two seconds. From there she can audit the rest of the inherited workbook with the annotations already in view.

FAQ

How do I highlight all cells with comments in Excel?

Select your range, go to Home > Find & Select > Go To Special, choose Comments, and apply a fill color. That's the manual route. For a repeatable one-click version that colors the cells and counts them for you, run a macro that fills every cell whose comment is not empty.

What is the shortcut to select all cells with comments?

Press Ctrl + Shift + O. It selects every cell in the sheet that has a note attached, the same result as Go To Special > Comments. Once they're selected, apply a background fill from the Home tab to make them stand out. The selection clears as soon as you click a single cell, though.

Does this highlight threaded comments or notes?

It highlights Notes, the classic yellow-box annotations with a red triangle. Go To Special and VBA's comment check both target notes, which older Excel called comments. Threaded comments, the newer reply-style conversations in Microsoft 365, are not selected by these methods, so this is for finding plain cell notes.

Can conditional formatting highlight cells that have comments?

No. Conditional formatting rules are built on formulas, and Excel has no formula that detects whether a cell has a comment or note. That's why there's no rule-based way to do it. Your options are Go To Special for a one-off or a macro for a repeatable, automatic highlight.

Will the highlight update when I add a new comment?

No. The macro applies a normal fill color, not a live rule, so a note you add later won't turn its cell green on its own. Just run the macro again after adding notes, and it repaints the full set. Because it's a plain fill, you can also clear it anytime with No Fill.

What to do next

Skip the hunt for red triangles. Color the whole set at once and read the model with the notes already in view.

Download the free Highlight Cells With Comments macro and run it on your own sheet. It's the quickest way to highlight all cells with comments in Excel, so every annotation stands out in green. When you're done reviewing, clear all the comments in one step or read the full guide to Excel data cleanup.