10 advanced formatting tricks for Excel users

Thanks to Susan Harkins of Tech Republic!

Most Excel users know how to apply basic numeric and text formats. But users who push beyond the basics will create more readable and effective sheets. In addition, knowing how to apply just the right formatting to specific cells quickly and easily helps users work more efficiently. These 10 tips will help users get more out of Excel’s formatting features.

1: Use the fill handle to copy formatting

The fill handle is a versatile and powerful tool. Besides copying formulas and creating series, the fill handle can copy formats with just a few quick clicks:

1. Select the cell that contains the formatting you want to copy. In Figure A, I’ve selected A2 to copy the bold font and gray fill color to the remaining cells in column A.
2. Double-click the cell’s fill handle. The fill handle’s series behavior has kicked into gear by overwriting the TOTALS label with January. Don’t worry about that, you can undo that next.
3. Click the resulting AutoFill Options control to display the list shown in Figure B.
4. Select the Fill Formatting Only option.
   

Figure A

Select the source cell — the cell containing the formats you want to copy.

Figure B

Choose the Fill Formatting Only option to cancel the series overwrite and still copy the formats from the source cell.

You can see that the fill handle canceled the series values in Figure C. In addition, the action reverted to the original data and applied the formats from A3 to the destination range. This method isn’t superior to Format Painter, but you won’t have to select the target range, which can be awkward with a large one.

Figure C

Excel’s fill handle copied only the source cell’s formats.

2: Use Paste to copy formatting

Another quick copy trick utilizes the Paste feature. Again, the Format Painter works great with a small range, but this trick is helpful when copying formats to an entire column or row:

1. Select the source cell and press [Ctrl]+C.
2. Click anywhere inside the destination column or row.
3. Press [Ctrl]+[Spacebar] to select the entire column or [Shift]+[Spacebar] to select the entire row. (This works only with a blank data range.)
   2010: With the column or row selected, choose Formatting from the Paste drop-down (in the Clipboard group).
   2007: Choose Paste Special from the Paste drop-down and click Formats in the Paste section.
   2003: Right-click a selected cell and choose Paste Special from the submenu. In the resulting dialog, click Formats in the Paste section.
4. Using Live Preview, you can see what the applied formats will look like. Click OK if you decide to apply them.
   

You can also format a new chart using Paste. Select the source chart and press [Ctrl]+C. Select the destination chart and choose Paste Special from the Paste drop-down. Choose Formats and click OK.