Remove formatting, hyperlinks, and comments in Excel


Clearing out cells of font mismatches, fill colors, hyperlinks, comments, and conditional formatting doesn’t have to be tedious. There’s a much easier way.

First select the cells you want to clear. In Excel 2007, you need to go to the Edit menu and select the Clear command to access Clear Formats. In Excel 2010, Clear has moved to the default Home tab so it’s even easier to find Clear Formats:

clip_image001

Clear Contents leaves the formatting but empties the cells of data. You can save time by simply using the Delete key for this. Clear Comments will quickly remove all of your comments from selected cells.

Office 2010 has some additional functionality for removing hyperlinks. Clear Formats will clear the hyperlinks along with all formatting – but if you want to keep your formatting, choose Remove Hyperlinks instead. Or, if you remove hyperlinks from worksheets regularly, you could use this handy hyperlink removal macro that can be assigned to a keyboard shortcut.

Suzanne

Comments (10)

  1. Anonymous says:

    Great tip, wondered where that had moved too.

  2. Anonymous says:

    This helped me to reduce the size of my Excel that was once 3 MB, grew to 16 MB and now is 500 kb 🙂

  3. laura says:

    Awesome! Thanks, This whole ribbon thing is new to me!

  4. Wayne Riley says:

    Excel 2010 formats all of my hyperlinks with a strikethrough.  How do I change this?

  5. lh says:

    thanks for the help i really needed that at the time and it reduced all the headache

  6. Arg says:

    Didn't work, still getting the error

  7. bd says:

    Looked like exactly the instruction I needed to clear comments but it doesn't work – comments are still there

  8. OfficeChimp says:

    This worked, but it also removed my color fill!  🙁

  9. Boston Karen says:

    My "clear" doesn't show "remove hyperlinks" as an option.  Now what?

  10. mspey says:

    Comments are my only way to specifically notate my research on an item out of sometimes hundreds of rows of data.  When I am ready to submit a document to a client, I delete all the comments by first: go to the Review Tab, you can "Show All Comments", second: click on the upper left corner of the sheet to select it, and then third: go the Home Tab and under the Editing Panel:Clear, hit Clear Comments.  If you made a mistake, just "Undo" it!  I love Undo – don't you?  I have 2010, so an earlier version might not show Clear Hyperlinks under the Clear.