How to use Format Painter in Excel?

Format Painter in Excel stands as a testament to efficiency and aesthetic consistency in your spreadsheets, allowing you to effortlessly replicate formatting across multiple cells, ranges, or sheets. This tool not only saves time but also ensures that your data presentations are visually coherent and professional. Embrace the power and simplicity of Format Painter in Excel to transform your spreadsheet creation process, making it a seamless task to maintain uniformity and clarity in all your data-driven endeavors.

This quick video demonstrates how to use the Format Painter, Fill Handle, and Paste Special tools in Excel to duplicate formatting. These methods are compatible with Excel 2007 to Excel 365.

This Tutorial Covers:

1. What is Excel Format Painter
2. How to Use Format Painter in Excel
   • Using Format Painter from Ribbon
   • Using Shortcut
3. How to copy formatting to a range of cells
4. How to copy formatting down a column using the Fill Handle
5. How to copy cell formatting to an entire column or row
6. How to copy conditional formatting using format painter
7. How to copy shape formatting using format painter
8. How to keep format painter active for multiple use

1. What is Format Painter in Excel?

Use the handy Excel Format Painter tool to copy formatting from a group of cells and paste it in another location on the worksheet (or in multiple worksheets/workbooks).

Think about it! You receive some plainly unattractive data from a coworker, and you spend the next several minutes preparing it to make it look lovely. To do this, you might add borders, format the headers, change the column widths, take away the gridlines, etc.

While you’re patting yourself on the back for transforming ugly data into beautiful data, another file arrives that requires the same formatting.

Excel Format Painter can help in this situation and can save you a ton of time.

The formatting you have already done can now be readily copied and pasted into the new dataset.

The formatted data will be the same as if you had done everything manually, and woosh.

2. How to Use Format Painter in Excel?

To quickly copy all of the formatting from one cell to another, use the Format Painter. Without having to spend a lot of effort duplicating the formatting in each cell, this can assist maintain consistency.

Excel’s format painter can be used in two different ways. Below is a description of them.

• Using Format Painter from Ribbon:

Let’s use a straightforward example to explain Format Painter in Excel.

Let’s say I want to duplicate the formatting from cell A1 (which is displayed below) to cell B1.

The following are the steps to copy formatting in Excel using Format Painter:

Step 1: Choose the cell(s) whose formatting you want to replicate.

Step 2: Select “Format Painter” from the “Home” Tab’s “Clipboard” menu.

Step 3: The cell that you want to transfer the formatting to is selected. Note that when you click on the Format Painter button in the ribbon, the pointer changes and you can see a brush in it. The format painter is now in use, as evidenced by this.

The formatting from cell A1 would be replicated to cell B1 immediately (as shown below).

In the case above, format painter copied the following exactly:

• Cell Color
• Text formatting
• Text alignment

The value in the cell is not copied by Format Painter; just the formatting is.

Excel’s format painter can be used to:

• The same worksheet’s formatting should be copied.
• To another worksheet in the same workbook, copy the formatting.
• Formatting should be copied to another workbook.
• Using Shortcut:

The following are the steps to copy formatting using Format Painter in Excel with shortcut key:

Step 1: Choose the cell(s) whose formatting you want to replicate. To duplicate the format of the chosen cells, use Ctrl+C.

Step 2: Now click on the cells where the desired formatting will be pasted.

Step 3: Press the shortcut keys for Alt, H, F, and P on the keyboard, followed by the “Enter” key. The formatting from cell A1 would be replicated to cell B1 immediately (as shown below).

3. How to copy formatting to a range of cells?

The following are the steps to copy formatting to a range of cells in Excel using Format Painter:

 Step 1: Choose the cell(s) whose formatting you want to replicate.

Step 2: Select “Format Painter” from the “Home” Tab’s “Clipboard” menu.

Step 3: Drag the brush cursor over the cells you want to format after that.

The formatting from cell A1 would be replicated to cell B1, C1 and D1 immediately (as shown below).

4. How to copy formatting down a column using the Fill Handle?

The following are the steps to copy formatting down a column using the Fill Handle:

Step 1: Hover your mouse over the fill handle after choosing the properly formatted cell (a small square at the lower right-hand corner). The white selection cross will turn into a black cross as you proceed. Holding the handle down while dragging it over the cells you want to format:

Don’t worry; we’ll undo it in the following step. This will also replicate the value of the first cell to other cells.

Step 2: Release the fill handle, choose “Fill Formatting Only” from the Auto Fill Options drop-down menu:

That’s it! The cell values return to their initial values, and other cells in the column are formatted as desired:

Tip: Double-click the fill handle rather than dragging it, click AutoFill Options, and choose Fill Formatting Only to replicate the formatting along the column to the first empty cell.

5. How to copy cell formatting to an entire column or row?

Small choices work wonderfully with Excel Format Painter and Fill Handle. But how do you transfer a cell’s formatting to a whole column or row such that the updated formatting is applied to every single cell in the column or row, even empty cells? The answer is to use Excel Paste Special’s Formats option.

The following are the steps to copy formatting to an entire column or row in Excel:

Step 1: To copy the content and formats of a cell, select the cell with the desired format and press Ctrl+C.

Step 2: By clicking on its heading, you can choose the entire column or row that you want to format.

Step 3: Click “Paste Special” after doing a right-clicking on the selection.

Step 4: Click “Formats” in the “Paste Special” dialog box, then click OK.

The formatting from cell A1 would be replicated to entire Row 1 immediately (as shown below).

Alternatively, choose the “Paste Special” pop-up menu option for “Formatting”.

6. How to copy conditional formatting using format painter in Excel?

Copying conditional formatting is one of my favorite things to do with Format Painter.

Since conditional formatting enables you to declare several rules on a single data set, repeating the process for other data sets could take a while.

Consider the following dataset, where students’ grades are highlighted in red if they are less than 33 and in green if they are greater than 80.

Now, rather than repeating the conditional formatting process if I add a new column with marks in a new subject (Physics), I can just use the Format Painter to duplicate the cell formatting and conditional formatting rules.

The following are the steps to copy conditional formatting using format painter in Excel:

Step 1: Select the entire column B.

Step 2: Select “Format Painter” from the “Home” Tab’s “Clipboard” menu.

Step 3: Select the cell where you want to copy conditional formatting. It will immediately apply the format.

7. How to copy shape formatting using a format painter?

You may rapidly copy formatting from shapes and paste it into other shapes with Format Painter.

The following steps will show you how to use Excel’s format painter to copy formatting from one shape and paste it into another:

Step 1: Choose the shape whose formatting you want to duplicate.

Step 2: Click “Format Painter” under the “Clipboard” category on the “Home” tab.

Step 3: Wherever you wish to replicate the formatting, click on the shape. It will immediately apply the format.

8. How to keep format painter active for multiple uses?

In some circumstances, you might want to copy the formatting from a group of cells and paste it into a group of cells that are not all adjacent. These could be found on multiple worksheets or workbooks or on the same worksheet.

You can only copy and paste the formatting once when you select the “Format Painter” button in the “Home” tab.

The following are the steps to keep format painter active for multiple use in Excel:

Step 1: Select the entire column B.

Step 2: You must double-click on the Format Painter icon in order to copy and paste the formatting several times. This will enable you to copy from a group of cells and repeatedly paste that formatting (until you disable the Format Painter).

Step 3: Now, select the cells where you want to format. It will immediately apply the format.

Simply click the Format Painter button once again or use the Escape key to turn it off.

Application of Format Painter in Excel

1. Uniform Cell Formatting:
   • Quickly apply consistent text formatting (font type, size, color) and cell formatting (background color, borders) across various cells or ranges, ensuring your spreadsheet maintains a uniform and professional appearance.
2. Copying Number Formats:
   • Easily replicate number formats like currency, percentage, or date formats across different cells, saving time and preventing manual reformatting errors.
3. Aligning Text Consistently:
   • Apply the same text alignment and orientation (left, right, center, or justified) from a formatted cell to other cells, maintaining a consistent and organized look throughout your tables or data sets.
4. Transferring Conditional Formatting:
   • Use Format Painter in Excel to quickly copy conditional formatting rules from one set of cells to another, helping to highlight critical data points or trends across large datasets.
5. Applying Cell Styles:
   • Duplicate complex cell styles that involve multiple formatting options (like a combination of font style, cell borders, and fill color) to other cells or ranges, ensuring a cohesive design with minimal effort.
6. Replicating Data Bars or Color Scales:
   • Copy data bars or color scales from one set of cells to another, visually representing data comparison or trends efficiently and attractively.

Format Painter in Excel is a versatile tool that significantly enhances the visual appeal and consistency of your spreadsheets, saving time and ensuring that your data is presented in a clear and professional manner.

You may be interested:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts