Thanks for joining me again for another time-saving tip that I hope you will find use for in your work. On Tuesday I showed you how you can sort by color in Excel, for versions 2007 and later.
Today I am going to show you how to 'find and replace' or 'find and select' specific colors, or any other cell formatting you choose, in all the cells of you worksheet using just a few clicks.
I hope you find it useful.
Please feel free to share this with anyone else you think it may help.
Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
'Finding And Replacing' or 'Finding and Selecting' All Cells with Specific Formatting
You probably apply font or background colors to your worksheets for various reasons, such as green to indicate that an amount has been checked or maybe red to indicate that a value needs to be reviewed.
Such was the case for a reader, who asked me how she could remove the green fill colors from hundreds of cells throughout her workbook without having to do them manually and without messing up the other formatting such as borders and fonts in the same cells.
Thankfully, Excel provides an easy way to quickly find and replace cell formatting.
Here's how to find cells with a specific Fill color and replace with another color;
1) Press CTRL+H to open the Find & Replace dialog (or from the Home tab click Find & Select then Replace);
2) If there are any values in the 'Find what' or 'Replace with' fields from a previous search, clear them;
3) If you don't see the Format... button, you'll need to click the Options>> button to expand the dialog box;
4) If the small boxes to the left of the Format... buttons do not say 'No Format Set', click the small arrow on the right of the Format... button and choose Clear Find Format or Clear Replace Format;
5) Click the Format... button to the right of the 'Find what' field to open the Find Format dialog;
6) Click the Fill tab and choose the Fill color you want to replace and click OK;
You will now see a 'Preview' of the color you chose in the small box to the left of the Format... button;
7) Next, choose the color you want to replace the 'found' color with by clicking the Format... button to the right of the 'Replace with' field. Click the Fill tab in the Find Format dialog, choose a replacement Fill color and click OK. In this example, the cells containing green fill color will be replaced by a yellow fill color;
8) Click the Replace All button and all of the cells with the green Fill color will be replaced with yellow Fill without affecting any of the other cell formatting such borders, font size, font color, etc...
As you've probably already realized, you can utilize this same technique to easily replace any other cell formatting attribute or multiple formatting attributes that can be found in the Format Cells dialog ( CTRL+1).
Notice that there is a Choose Format From Cell... option on the Format... button. This is useful when you want to use all, or multiple parts of a cell's formatting as your search criteria. Also, if you want to find a color but you're not exactly sure which color is in the cell, this option can help. In that case, use the 'Choose Format From Cell' option to select a cell containing the color. Since this option includes all formatting attributes of the selected cell, you may need to clear any unwanted attributes from the criteria by clicking the Clear button on each tab (i.e. Number. Alignment, Font, Border, Fill, Protection) in the Find Format dialog.
Select All Cells Containing A Specific Format
If you just want to 'select' the cells on the worksheet and not necessarily replace them, here's how...
Instead of clicking the Replace All button in Step 8 above, click the Find All button. All of the 'found' cells will be listed at the bottom of the Find & Replace dialog. At this point, press CTRL+A to 'select' all of the items in the list. You will notice that all of the cells have been selected on your worksheet. Click the Close button and you will be returned to your worksheet with all of the cells containing the color selected.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630