Format Painter works great with a small selection, but it doesn’t work so great with an entire row or column. Instead of Format Painter, use the following trick to copy formatting to an entire column or row:
- Select the cell that contains the format you want to copy.
- Press [Ctrl]+C to copy the cell’s content and formats.
- Press [Ctrl]+Spacebar to select the entire column. Or press [Shift]+Spacebar to select the entire row. If the source cell (step 1) isn’t in the same column or row, select a cell in the destination column or row before selecting the entire column or row.
- Choose Paste Special from the Edit menu.
- Click Formats in the Paste Special dialog box and click OK.
That’s all there is to it!