While collecting and analyzing data in Excel, you’ll frequently format it (for example, by changing font styles, font color, cell background color, text alignment, number format; being it currency, percentage or a general number, or font characteristics such as bold, italics and underline) to make it stand out.
And, rather than doing it manually for a large number of cells or a range of cells, you can do it once and copy & paste the formatting.
I’ll show you how to copy formatting in Excel in this guide. It’s simple to do with the Format painter option, the Fill handle, or the Paste special.
How to use the paste special to copy formatting in excel
How do you replicate the format of a single cell to a whole column or row, including blank cells, so that the new format is applied to all cells in the column or row?
When you copy and paste cells in Excel, you’ve probably seen that there are various paste options available, such as Paste text, Paste values, and so on. The solution is to use Excel Paste Special’s Formats option. This helps because It does not overwrite the values in the new cell.
- Select the cell in which you want to copy the formatting and right-click
- Select Copy from the drop-down menu or use the keyboard shortcut CTRL+C
- Select the formatting option from the paste special dialogue box
The results are displayed below. We can see that column B has taken the formats of column A which are the highlighted columns.
Or you can use this alternative;
- Select the cell with the desired format by clicking and dragging and pressing Ctrl+C to copy its content and formats.
- Select the entire column or row that you want to format by clicking on its heading.
- Right-click the selection, and then click Paste Special.
- In the Paste Special dropdown, click Formats, and then click OK.
The results are the same as those displayed above in this diagram.
For those who normally love to work from the keyboard, these are a list of shortcuts that will help you get your desired results without leaving the keyboard
- Select the cell from which you want to copy the format.
- Press Ctrl + C to copy the selected cell to the Clipboard.
- Select the cell(s) to which the format should be applied.
- In Excel 2016, 2013, or 2010, press Shift + F10, S, R, and then click Enter.
For those still using Excel 2007, press Shift + F10, S, T, and press Enter.
This key sequence executes the following commands:
- Shift + F10 displays the context menu.
- Shift + S selects the Paste Special command.
- Shift + R chooses to paste only formatting.
How to use the fill handle To Copy formatting In Excel
The fill handle is the small black cross that is seen when the cursor is positioned at the right bottom corner of a cell. It is represented by a dotlike symbol as seen below
This cursor helps you to copy a cell or a group of cells. In addition to copying the cell values, the fill handle allows you to copy every format feature attached to the cell. Let’s see how this works below.
For instance, we have a list like the one shown below and we want to apply the format to the rest of the cells in the A column.
The steps below will guide you to apply the format to the rest of the cells in the A column.
Position the cursor in the right bottom corner of a cell from which you want to copy the formatting (A1) which is color, Bold, and Italics until the black cross (fill handle) appears;
Drag the fill handle down to the end of the range that we want to format (A7). If you want to copy the cell to the end of the range (until the first blank cell in the range), just double-click the fill handle.
When you drop the cursor, by default both content and formatting will be copied to the range. Now, you need to click on the AutoFill Options icon next to the end of the range and choose Fill Formatting Only.
Now, you can see that contents of the cells are not changed, while the formatting is copied to the whole range.
This method is only applicable when the data is in the same row or column. This can’t be used to copy formatting and apply it to a different worksheet or another workbook.
How to Copy the formatting to a range of cells using the Format Painter
You can transfer the formatting from one cell to another, and you can copy it to a range of cells as well. In this situation, you must first select a range of cells to which the format painter will be applied.
- To copy formatting to several adjacent cells,
- select the sample cell with the desired format
- Click the Format Painter button, and
- drag the brush cursor across the cells that you want to format.
Unlike the fill handle which can only apply formats in the same column or row, the format painter can apply formats to adjacent cells as well.
Copying the formatting of one column to another column
- Select the heading of the column whose formatting you wish to replicate
- click Format Painter, and then
- click the heading of the target column to swiftly copy the format of the entire column.
Copying the formatting of on cell to non-adjacent cells
Instead of single-clicking the Format Painter button, double-click it to copy formatting to non-contiguous cells. The copied formatting will be done to all cells and ranges that you click/select until you press Esc or click the Format Painter button one last time.
Copy the formatting to a single cell
- Choose a prepared cell with the formatting you’d like to copy (A2 in our example)
- In the Home tab, select Format Painter. The cursor will be transformed into a paintbrush with a (+) icon.
- Click on a cell where you want to copy a format (B2)