Search Not Just Numbers

Tuesday 12 January 2016

Excel Tip: An easier way to change cell colours

Before I begin, I would like to wish a Happy New Year to all of my readers and subscribers - as this is my first post of 2016.

I am starting the year in humble mode, as I have had something very simple pointed out to me that I can't believe I had never spotted, despite it being right under my nose!

In my last post of 2015, I showed you some code to swap one cell colour for another throughout a worksheet. I then received a comment from a reader that I only know as Jim, pointing out that Excel already has a very simple way of doing this!

It can in fact be done using Find and Replace.

This amazed me because I use Find and Replace all of the time and didn't know you could do this!

It is simple as this:

  1. Highlight the range which you want to apply the changes to
  2. Click Find and Select (the binoculars icon) on the Home Ribbon and choose Replace from the dropdown menu
  3. Don't enter anything in the Find or Replace boxes, but click the Format button next to the Find box and on the Fill Tab, select the colour that you wish to change
  4. Click the Format box next to the Replace box and on the Fill Tab, select the new colour that you wish to swap for the colour you selected above
  5. Click Replace All
You can even start with the colour of an existing cell, using the dropdown on the format button, however be sure to remove any other formats other than Fill colour.

The thing that really shocked me is that these two big Format buttons have been right there in front of me every time that I've used Find and Replace since Excel 2003! And that's a lot of times!

In 2016, I am going to try to make sure that I constantly keep my eyes open for new ways to do things in Excel, instead of just relying on what I think I already know! And thanks again, Jim.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

7 comments:

  1. On my 2016 office 365 install I needed to click the options button to bring up the Format button beside both find and replace. This could be why I had not seen it before.
    Happy New Year
    Geoff

    ReplyDelete
    Replies
    1. I've not tried it in 2016, so I haven't got that excuse!

      Delete
  2. And of course it has much wider use than just for the colour of the cell. I like variance percentages to be shown with one decimal place but sometimes that gets missed. Using the format find and replace means that I can make sure an entire model has consistent formatting without have to go looking for the rogue ones and without messing up any other formats

    Thank you very much!

    ReplyDelete
    Replies
    1. Oh, I'm sure I'll use it for many things now I know it's there!

      Delete
  3. If you find yourself recoloring cells like that, perhaps it is time to explore another undervalued feature of Excel: Cell Styles!

    ReplyDelete
    Replies
    1. Thanks Jan. That was also mentioned in the comments on the last post. It's a great way to ensure that the problem doesn't happen, but we can't always be certain that a spreadsheet that we are trying to fix has been designed as well as that.

      Delete