Microsoft optimize

Quickly format cells with related attributes

When formatting cells with a common attribute, use this quick trick to select all the cells so you can format them as a block instead of individually.

It's common to apply the same format to cells that are related in some way. For instance, many spreadsheets use color to denote cells that contains formulas.

You could spend a lot of time applying the format manually to individual cells, or you could do it the easy way. Use [F5] to select all the cells that contain the same attribute. Then, apply the format to all of the cells at once. For instance, to color cells that contain formulas, you'd do the following:

  1. Press [F5].
  2. Click Special (at the bottom right of the dialog box).
  3. Select formulas and check all the options that apply to your situation.

  1. Click OK to return to the spreadsheet. The appropriate cells will be highlighted.

With the appropriate cells selected as a block, apply the format as you normally would. In this case, choose a color from the Fill Color palette. That's all there is to it. Using [Goto] to select cells with a common attribute, you save time and eliminate the possibility that you might miss cells.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
cdaaawg
cdaaawg

It would be nice if you could provide the files used in your tips for download. It would make practice for us newbies much easier. Thanks for the great tip!

ssharkins
ssharkins

I try to use the same example files all the time for that reason. I'll check with TR and see if it's possible to upload example files.

michael.rattenne
michael.rattenne

I love cool Excel shorts like this and already have applied this to several reports. The thing is I tried to show this to one of my team leads and couldn't reverse it. Also, I wanted to show these colored cells but not print; any way to do this to certain cells but not all? Thanks for the tip.

ssharkins
ssharkins

Pressing [Ctrl]+z should undo any format you apply, as long as you do it right away.