Using the Format Painter in Excel

excel logoThe format painter is one of the best-kept Microsoft Office secrets. Alright, maybe not a secret, but it is certainly under utilized. This is such a great time-saving device that someone should be yelling from the rooftops 'Hear ye, hear ye, it has been decreed that all ye users of PC's shall from this day forward, under penalty of death, dismemberment, and most painful and unpleasant torture, use the benevolent and majestic device known in the common language as 'Ye Great Format Painter'. OK, a little over the top again. Still, it's a tool that should definitely be in your arsenal of time-saving tricks.

While the format painter is available in all of the standard Microsoft Office applications, I think that it's in Excel that it really shines. Let's open up a spreadsheet and try it out:

You can download the xls file used in this example here (right click, then select 'Save link as...' or 'Save target as...')


excel format painter 01


Before we even get started let's resize all of the columns to the minimum size for the data (click here if you don't know how):


excel format painter 02


Now, let's start by reformatting the latitude and longitude columns so they go to 3 places after the decimal. Do this by highlighting the two columns, then right click and select 'Format Cells...' from the drop down menu:


excel format painter 03


From the Format Cells dialog select Number from the Category list, then increase the Decimal places from 2 to 3:


excel format painter 04


Select OK to make the change. Now let's format the Population cell, again right click on the column, select Format Cells..., again select Number from the Category list, change the decimal place from 2 to 0, and this time click the check box next to Use 1000 separator (,). The Format Cells dialog should look like this when you've got it set:


excel format painter 05


Click OK to make the change. The final change is to format the final two columns to US currency. Highlight both cells, the get the Format Cells dialog as previously described. Select Currency from the Category list, and leave it at the defaults (yours may look different, depending on your local currency settings):


excel format painter 06


Click OK to make the change. Now we want to spread these changes down the entire sheet, which is where we'll use the format painter. The first thing I'm noticing here is that I don't have the icon on my toolbar. In Excel 2003 your toolbars update dynamically based on what tools you use and the width at which you have the window set. As you add tools or decrease the size of the window the tools that you don't use as frequently will drop off the toolbar. To get them back click on the toolbar options at the end of the toolbar, and in this case select the Format Painter icon:


excel format painter 07


Now highlight the first data row (A2:J2) and click once on the format painter icon. When you do you'll get the 'marching ants' highlight around the cells:


excel format painter 08


Now click and drag from cell A3 over to cell J3, then drag down to the bottom of the data. Notice the mouse pointer changes to a plus sign with a format painter icon:


excel format painter 09


This will copy the formatting down the sheet. You may find some cells that have outgrown their width because of the change, so you can't see the data anymore:


excel format painter 10


You can fix this easily to automatically resize to the correct width by double-clicking on the column separator between the columns:


excel format painter 11


Let's look now at a couple of additional neat tricks you can do with the format painter. Let's put a border around all of the cells, and change the background color for every other row so that it look like a greenbar report. Highlight rows 2 and 3, and place a border around the cells:


excel format painter 12


Now select row 3 and change the background color to a light green:


excel format painter 13


Now select rows 2 and 3, click on the format painter icon, and starting from row 4 drag down to the bottom of the sheet. You should get something that looks like this:


excel format painter 14


OK, looks good so far, let's perform just one final trick. Let's highlight all of the data from Garden City. Start by highlighting the first row with the information that we want and change the color to a light yellow:


excel format painter 15


Now this time I want you to double-click on the format painter icon. This will lock it on so you can copy the formatting where you need it. You can copy the formatting to the entire row by clicking on just the first column. To do this click just once on the A column in rows 12 through 21:


excel format painter 16


You can also use the format painter between different sheets, and even between different workbooks. If you highlight a block of cells and select the format painter all you need to do is to select the upper left hand cell for the target block and the entire block will be formatted.