How to Copy a Format for Excel Worksheet Columns and Rows

Formatting allows viewers to distinguish values by type and even sections. Applying the right formatting is critical to readability, and most users learn how to format cells and regions early on. After learning the basics, users quickly move on to using Format Painter to copy existing formats to other cells or even entire regions. You’ll work more efficiently using Format Painter.

In this article, you’ll learn how to use Format Painter with cells. Then, we’ll move on to using Format Painter to quickly format entire rows and columns. You’ll even learn a trick for formatting entire rows and columns without using Format Painter.

How to use Format Painter

Format Painter lets you apply the same set of formats from a cell or group of cells to another cell or group of cells. To illustrate, let’s use Format Painter to fix the formatting in the first date column shown in Figure A. As you can see, different formats are in use. In this situation, you’ll want to apply the same format to all of the cells with dates.

Now, let’s use Format Painter to copy the formatting from B31 to the rest of the column:

1. Select cell B31.
2. Click Format Painter in the Clipboard group on the Home tab.
3. Select the rest of the column, B32:B39.

As you can see in Figure B, B32:B39 now shares the same format as B31. It’s important to note that this feature copies all formatting, not just the date format.

When you need to copy formatting to a number of cells or regions that aren’t contiguous, you can lock the feature. Doing so lets you format until you turn the feature off. Let’s use the locking feature to reformat the dates in C32 and C36:

1. Select C31, or any cell with the correct formatting.
2. Double-click Format Painter.
3. Click C32 (Figure C).

4. Click C36.
5. Click Format Painter to turn it off.

You can continue to click cells until you click Format Painter to turn it off.

How to use Format Painter with rows and columns

Now that you know the basics, it’s time to learn how to use Format Painter with entire columns and rows. It’s simple, really; the routine is the same as reviewed above. The only difference is that you click a row or column header. A header cell is the gray cell to the left or the row or above the column.

SEE: Explore these Excel tips every user should master.

Let’s suppose you want to create a new table of values using the same header format in row 29. You’ll do so as follows:

1. Click the header cell for row 29 to select the entire row.
2. Click Format Painter.
3. Scroll down to row 52 and click that row’s header cell (Figure D).

You just copied an entire row’s formatting to another. Although it’s not readily noticeable, that means you copied A29 to A52, B29 to B52, C29 to C59 and so on.

You might notice that there’s no formatting to the right of column G. That’s because there’s no formatting to the right of column G in row 29.

You can do the same with columns: To copy the formatting from one column to another, do the same thing, but select column headers.

If you can’t click the header cell

While it’s not likely to be unable to click the header cell, it can happen if the workbook’s creator turns off the header cells. With the header cells off, it’s easier to visualize how this next tip can come in handy. However, you can use the Name Box to display the current cell.

Let’s turn off the header cell display as follows:

1. Click the File tab.
2. Click Options or More and then Options.
3. Click Advanced in the left pane.
4. In the Display options for this worksheet, uncheck the Show row and column headers option (Figure E).

Now, let’s illustrate this next technique by expanding the format in G51 to the remaining cells in row 51:

1. Select the cell you want to copy. In this case, click G51.
2. Click Format Painter.
3. Click Shift + Spacebar to copy the format from G51 to the entire row (Figure F).

ACADEMY: Master Microsoft Excel with these 16 courses in this bundled deal.

To copy the formatting from a cell to an entire column, repeat the above steps, but press Ctrl + Spacebar to select the entire column in step 3.

How to copy formatting to rows and columns without Format Painter

Alternatively, here’s how to copy formatting to an entire row or column without using Format Painter. We’ll work with row 51 again, having pressed Ctrl + Z to undo the last copy:

1. Select the cell you want to copy. In this case, click G51.
2. Press Ctrl + C to copy the cell’s content and formats to the Clipboard.
3. Press Shift+ Spacebar to select the entire row. Or press Ctrl + Spacebar to select the entire column.
4. Choose Paste Special from the Edit menu.
5. Click Formats in the Paste dropdown (Figure G).

Keep in mind that paste options (step 5) will have different results. You must choose Formats to paste only the formatting.

Formatting entire rows and columns

Regardless of what section of the sheet you’re working on, row and column headers are usually available, making it easy to format the entire row or column using Format Painter. In a pinch, you can copy the format to the Clipboard using Ctrl + C and then use shortcuts to select the entire row or column before pasting the format from the Clipboard.