Format Painter in Excel

Copying the formatting is one of the most important features in Microsoft Excel that is often neglected or underestimated. Microsoft has benefited its users by providing an amazingly easy method to copy the formatting settings of one cell to another. You might already have guessed that I am talking about the Format Painter feature of Excel that easily, within a single click, copy the formatting of a selected cell and apply it to another.

Format Painter is not limited to this. Therefore, this tutorial will explore the most efficient methods to utilize Format Painter in your worksheets and cover plenty of other techniques to copy formatting in Excel.

Excel Format Painter

Format Painter is an amazing tool in Excel that empowers the users to copy formatting from a single cell or range of cells and applies it to a different cell or range of cells present somewhere else in the same worksheet or other worksheets or even different workbooks.

Without any doubt, Format Painter is one of the most significant and underused tools in Excel. With just a few clicks, it helps you to replicate most, or it won’t be wrong if we say all of the formatting settings, including:

• Number formatting (General, Percentage, Decimals, and many more)
• Line and Paragraph spacing, Decrease and Increase Indent
• Font size, font color and font face
• Text highlight color and Text Effects
• Font highlights unlike bold, italic, and underline
• Text alignment, Text direction and Text orientation

Let’s understand it using an example:

You get a plain-featured messy Excel datasheet from a colleague, and you spend the next 10-15 minutes beautifying the sheet by applying some formatting to it. It includes borders, highlighting the headers, formatting the font face, font size and color, setting column widths, wrapping the text, eliminating gridlines, etc.

And while you’re patting your back for changing a messy excel sheet to an organized one, your colleague bombarded two more files that need to be formatted the same way.

This is where Excel Format Painter will work like magic for you and will copy the formatting for the data from previous Excel sheets to the new one.

Just a couple of clicks will get converted into the same formatted data.

Where is the Format Painter present in Excel?

The Excel Format Painter feature is present on the Home tab, in the Clipboard section (the first section of the home tab), to the right of the Paste option. The location of Format Painter is the same for all Excel versions.

Step to use Format Painter tool in Excel

Follow the below steps to use the Format Painter tool and copy the cell formatting of one cell to another cell.

1. Place your cursor and select the cell from which you would like to copy the formatting settings.
   
2. In the Excel Hometab, go to the Clipboard group, click on the Format Painter icon as shown below.
   
3. You will notice that the cursor will automatically change to a paintbrush once you have selected the Format Painter button.
   
4. Now, move your cursor (resembles a paintbrush) to the cell where you wish to copy the cell formatting and click on it.
   

That’s it! Within a couple of clicks, the formatting of your previous cell is copied to your new cell.

Excel Format Painter tips

Though in the above section, we learned how to use the Paint Format tool. But what if you want to copy the formatting non-continuous group of cells or a range of adjacent cells? Indeed, copying the formatting one by one for each cell would be very tedious and time-consuming. Therefore we came up with some Excel Format Painter tips that will help you speed up things.

1. Copy the formatting settings to a range of adjacent cells.

In Excel, if you want to copy the formatting of a selected to the targeted contiguous cells, do the following:

Select the initial cell(s) from which you wish to copy the formatting, click on the Format Painter icon, and drag the mouse pointer (resembling a paintbrush) across the range of cells.

2. Copy the formatting settings to non-adjacent cells.

In Excel, if you want to copy the formatting of a selected to the targeted non-contiguous cells, do the following:

It will turn on the Excel Format Painter tool and lock it. Select the initial cell(s) from which you wish to copy the formatting and double-click on the Format Painter tool rather than single-clicking it. Now copy the formatting to as many cells as you want just by clicking on the cell. Once done, press the Esc key or click on the Format Painter tool one last time to turn it off.

3. Copy the formatting settings of one column to another column

We can easily copy the formatting of one column, and paste it to another column using the Format Painter tool.

For this firstly select the column heading and then click on the Format Painter tool. You will notice your cursor will change to a paint brush. Select the heading of the target column where you want to copy the formatting including the column width (in a row-row order). As shown in the below screenshot, as you will notice the formatting of the entire column is applied to the target column.

Though in the above section we learnt how to copy the formatting of a column but in the similar way you can copy the formatting for one entire row to another target row.

Copy formatting settings in Excel using the Fill Handle feature

Fill handle is another excel tool often used to copy formulas from one cell to another cell(s) or auto-fill cells containing data. But to your surprise, it can also copy the formatting settings to a cell within a couple of clicks. Let’s learn how we can do that:

1. Do the formatting of a cell and select

2. Now, hover your mouse pointer to the right lower edge of the rectangular selection box, and you will notice your cursor will change to a black cross. Hold the black cross and drag the icon over the cells until you want to apply the respective formatting.

3. Although the above method will copy the data along with the formatting, don’t worry in the next step; we will learn how to undo the copied data so that only the cell formatting remains.

4. The next step is to release the fill handle icon. You notice on the right the bottom side of your last dragged there is an Auto Fill Options icon. Click on it.

5. A drop-down menu will appear. From that menu, select the Fill Formatting Only option (refer to the below screenshot).

Eureka! The copied cells will revert to the initial values, and the selected formatting setting is also applied to targeted cells in just a few clicks.

Microsoft Excel Format Painter tool shortcut

Suppose you are using the Microsoft Format Painter tool regularly. Clicking the Format Painter button on the ribbon tab, again and again, would be tedious and time-consuming. Instead, you can use the keyboard keys to utilize the same. However, Excel doesn’t directly provide any keyboard shortcut to utilize the Format Painter tool. However, you can use the Excel hotkeys features to apply the same. Therefore, if you are a professional Excel user and prefer to use the keyboard the most, this is the best option to copy the formatting.

1. Place your mouse pointer and select the cell from which you want to copy the formatting.
2. Next, from your keyboard, press the Alt, H, F, P keys.
3. Click on the target cell(s) where you wish to copy the cell formatting.

Note: Please remember hotkeys are different from shortcut keys. Unlike shortcut keys, they won’t work if they are pressed at once. To activate the Format Painter tool in your worksheet, press the keys one by one.

The hotkeys will work in the following sequence:

• Alt key will help you to activate the keyboard shortcuts for Microsoft Excel’s ribbon tab.
• H key is used to select the Home tab on the ribbon.
• F, P is the shortcut key to select the Format Painter tool.