What is the fastest way to copy the format from one cell to multiple other cells?

What happens when you copy-paste, let’s say a column of information, in Excel? You get the values and the font and cell formatting; an exact duplicate. Today’s tutorial is about copying the format of one or more cells to other cells in the same worksheet, workbook, or even to other workbooks.

That brings us to the next question. What happens when you copy-paste a format in Excel? Let’s start with a single cell.

Focus on cell C5. Before copying the format:

After copying the format from cell C4:

What format gets copied? All formatting including font size and style, borders, color, etc will be copied to other cells.

What can you copy? You can copy formats of one cell to another cell, one cell to multiple cells, and multiple cells to multiple cells. If the selection you are pasting to, is larger than the copied selection, the format will keep pasting repetitively in a loop.

Where can you paste the format to? The format can be pasted to the same worksheet, other worksheets in the same workbook, and other workbooks. Let’s see an example to show you a situation where you will need to copy the format.

• Example
• Copy Formats Using Format Painter
  • Using Format Painter Multiple Times
• Copy Formats Using Paste Special
• Copy Formats Using Fill Handle

Example

This is the example we will be using for this guide to show you how to copy the format from one column to another. Copying the format of a single cell works the same way.

See the example below:

What we have here is the data entered first in column D looking, yes, a little out of place. In this case, you will need to copy the format from column C and paste it to column D so the values in the column are not overwritten. Let’s get format-copying!

Copy Formats Using Format Painter

The Format Painter can be used to apply the format of a cell to another cell or group of cells. The Format Painter is like the copy-and-pasting tool for formats and the steps are the same too; select cells, copy, select cells, paste. Below we show you how to use the Format Painter to copy a format.

• Select the cells from which you want to copy the format.

• Click on the Format Painter which is a little paintbrush icon in the Home tab, in the Clipboard. The cursor will change to a white cross with a little paintbrush, indicating that the format is ready to paste.

• Select the area that you want to paste the format to by clicking and dragging. When you release the mouse button, the format will have pasted and the cursor will return to normal.

Using Format Painter Multiple Times

Like mentioned earlier, the cursor reverts after the format has been pasted once. To continue pasting the format multiple times, all you have to do is double click the Format Painter button and you can continue to paste the format to multiple points. See how below:

• Select the cells for format copying.

• Double-click the Format Painter button in the Clipboard section in the Home tab. You know the format is ready to copy when the cursor changes to a white cross with a small paintbrush.

• Paste the format to the intended group of cells by click and drag.

• You will note that the cursor hasn’t changed back after using the Format Painter once. Continue to paste the format to other ranges.

• When done, press the Esc key or double-click the Format Painter button to exit Format Painter

The format has been pasted to multiple points:

Copy Formats Using Paste Special

While the Format Painter is a format copy-pasting tool, Paste Special literally copy-pastes formats. Pasting formats is one of the Paste Special options. The very job of Paste Special is to paste a certain feature of the copied object instead of pasting the object as is. Here we’ll show you how to use Paste Special for copying formats:

• Select the group of cells intended for format copying and press Ctrl + C to copy them.
• Right-click on the first cell of the range that you want to copy the format to.
• Go to the Paste Special options and select the Formatting option which is the first icon in Other Paste Options.

• Alternatively, you can use the keyboard shortcut Alt, E, S, T, Enter in sequence.
  • When you press Alt, E, S, the Paste Special dialog box opens.
  • The T key selects the Formats radio button in the dialog box.
  • The Enter key selects the OK command. This will close the dialog box and paste the copied format.

• Whichever method you choose for Paste Special; the right-click context menu or the keyboard shortcut, the format will be copied to the new range.
• Note that the marching ants line is still active, which means that the format can be copied to multiple points in the same way with Paste Special.

• Continue to paste the format to different ranges using Paste Special or by pressing F4.
• The F4 key repeats the last action which in this case is pasting the copied format.

Copy Formats Using Fill Handle

The Fill Handle is that tiny square at the bottom-right of every cell. Excel is quick to pick up what kind of data you’re working with and readies some of its features accordingly. Dragging the Fill Handle can help fill so many types of data and for now, we will use it to fill out formats.

The only requisite for using the Fill Handle to copy formats is that the data has to be in the adjacent rows or columns as the fill handle will ‘stretch’ the format onwards. Let’s see how that works:

• Select the range for copying its format.
• Hover the cursor to the Fill Handle of the selection (the Fill Handle shows on the last cell of the selection). When the cursor changes to a black cross, click and drag the Fill Handle to the range where you want the format copied. For our case, we will drag the Fill Handle rightward to copy the format to the next column.

• The column will have copy-pasted itself values included. That’s why we need to access the AutoFill options to only accept the pasting of the format of the selection.
• Click on the Auto Fill Options icon which is a little box on the bottom-right of the AutoFilled cells and select Fill Formatting Only from the options.

This option will revert the cells to their original values and only paste the formatting.

You may now have understood why the format needs to be copied to a successive range as the Fill Handle can only be dragged to the adjacent columns or rows to paste the format. If your dataset isn’t aligned together, you always have the other two options for copying formats.

And so we have copy-formatted our way to the end of the guide. We showed you some quick and easy options on copying formats in Excel without having to use the old copy-pasting, overwriting values. While you sync the formats on your sheets, we’re cracking on to bring more from Excel-dom and its Highness your way.

What is the fastest way to format cells in Excel?

Which of these is a quick way to copy formatting?

What is the shortcut to copy format in Excel?