Power-formatting Excel spreadsheets

Power-formatting Excel spreadsheets

May 1, 2001
Jeff Davis
© 2002 TechRepublic, Inc.

Are you always looking for a great Excel tip to save time for yourself, or for your end users? Recently, one of my coworkers pronounced me her "new hero" for showing her a formatting shortcut that will literally save her hundreds of hours of work over the course of a year. Here's the scoop.

The challenge: Adding stripes takes too long
This user was working on a spreadsheet like the one shown in Figure A, only her file contained dozens of columns and hundreds of rows. Her end users complained that it was difficult to follow a single row of data.

She had the brilliant idea to use the Formatting toolbar's Fill Color tool to shade every other row in the spreadsheet as light green, creating a "green-bar" stripe effect. The result was that the spreadsheet, both on-screen and in printed form, was easier to read.

Unfortunately, the user was formatting those rows the slow and difficult way. She was selecting every other row, one at a time, and clicking the Fill Color tool on each row.

Figure A

Some readers have trouble keeping track of rows and columns of data in densely packed spreadsheets like this one.

The solution: The intelligent Format Painter
I started by showing the user how to save time by using the Format Painter. We formatted the first row as light green, selected that row, and double-clicked the Format Painter. (It's the icon in the Standard toolbar that looks like a paintbrush.) Then, to "paint" additional rows, all we had to do was click on the row's number, and the Format Painter copied the "light green fill" attribute onto the target row.

After the user gushed for a bit about how cool she thought the Format Painter was, we kicked it up a notch. Because the Format Painter is smart enough to recognize patterns when you've selected two or more rows, we were able to format every other row in her spreadsheet with one swipe of the mouse.

First, we selected two rows—the first, plain row of data and the row that had been formatted with a light green fill. Next, we clicked the Format Painter once, and clicked on the first row number below our selection, and dragged down the column. Figure B shows what the sheet looked like while we were copying. (Notice the paintbrush icon near the bottom of the range we were formatting.) Figure C shows the final result.

Figure B

When you select two or more rows and click the Format Painter, the program copies the formatting from each row.

Figure C

When you use the Format Painter to apply formatting from two or more rows, Excel formats subsequent rows using the same patterns. (In this case, the pattern is one plain row followed by one shaded row.)

Works for columns, too
Of course, this same shortcut applies to formatting columns. Suppose you want to format every four columns of data using the same color scheme, like we did in Figure D.

To copy the attributes associated with two or more columns in a row, just select them and click the Format Painter. Then click and drag on the letters of the target columns. When you release the mouse, Excel will format those columns using the same pattern as the one whose attributes you copied.

Figure D

You can also use the Format Painter to copy formatting to two or more columns at a time.

Color our world

To comment on this tip, post a note below or follow this link to write to Jeff.