Unlock the Power of Format Painter in Excel: Transform Your Spreadsheets with these 2 Super Tips!

Posted June 20, 2023

Do you find yourself spending hours perfecting the format of a single cell in Excel, only to realise that you need the same formatting applied to numerous cells or a larger range? This can be a tedious and time-consuming task if done manually. Fortunately, there is a solution in the powerful Format Painter in Excel. It functions like a digital paintbrush, enabling you to effortlessly copy and apply formatting from one cell to another with just a few clicks.

Assume you have a spreadsheet with a table that contains different product groups, current prices, and cells that calculate the price increases for each group. You want to highlight the product group headings and the cell showing the percentage increase to draw attention to them. Instead of manually formatting each cell, you can use Format Painter to quickly copy the formatting from one cell to another.

It’s like having a magic wand that instantly transforms your data into a visually appealing and consistent format.

In the upcoming sections, we’ll dive deep into the Format Painter feature and how it can help you in many ways. You’ll be able to copy formatting to multiple cells and use it with conditional formatting.

  1. Where is the Format Painter in Excel
  2. How to Use the Format Painter in Excel
  3. Copy formatting in Excel to Multiple Cells
  4. How to use Format Painter with Conditional Formatting in Excel

With the Format Painter, you’ll be able to save time and make your Excel spreadsheets look professional and stylish. This tool is an asset that will help you take your Excel skills to the next level.

1. Where is the Format Painter in Excel

In Excel, the Format Painter feature is a great time-saver as it allows you to quickly apply the same formatting, including font styles, cell borders, and fill colours, to multiple cells or ranges without having to manually format each one individually.

To access the Format Painter tool, head to the Home tab on the Excel Ribbon. You can find it in either the Clipboard or Clipboard/Clipboard group section. Look for the paintbrush icon that represents the tool and click on it to activate it.

Excel Ribbon on the Home tab with the Format Painter tool highlighted in a red box.

2. How to Use the Format Painter in Excel

1. To apply the desired formatting to another cell, select the cell that already has the formatting you want to use as a reference. This source cell will be the basis for the formatting you plan to copy. In the example below, we will select cell C4 as this cell has the formatting we want to copy.

Excel spreadsheet with callout pointing to cell C4 saying "Cell C4 is selected as it has the formatting we want to copy"

2. From the Home tab in the Clipboard group click the Format Painter button.

3. When you activate the Format Painter tool, the mouse pointer changes into a paintbrush icon. This signifies that the tool is ready to copy formatting from the source cell.

The mouse pointer with a paintbrush icon in Excel.

4. To apply the formatting, go to the target cell. Click on the cell where you want to apply the formatting, and it will automatically adopt the formatting of the source cell. In this example, we selected cell C9.

5. Once you release your mouse, the formatting will be applied.

Excel spreadsheet where cell C9 now has the same formatting as C4 with a callout saying "Cell C9 now has the same formatting as cell C4"

6. Once the formatting has been applied to the target cell, the tool is automatically turned off (deactivated). This indicates that the formatting from the source cell has been successfully copied and applied to the desired destination cell.

3. Copy formatting in Excel to Multiple Cells

Using the Format Painter to copy formatting to multiple cells at the same time can significantly save time and effort when working with large sets of data in Excel.

In the example below we want to continue copying the heading and input cell formats to additional product group headings in our data set.

Excel spreadsheet with a callout pointing to row 4 saying "Cells A4 to C4 have formatting we want to copy" and a callout pointing to rows 9, 15, 27, 31 and 36 saying "These are the cells we want to copy the formatting to"

1. To apply formatting to other cells, select the cells with the desired formatting. These cells will act as the source from which the formatting will be copied. In the following example, we are copying the format from cells A4 to C4.

Excel spreadsheet with cell A4 to C4 selected and a callout that says "Cell C4 is selected as it has the formatting we want to copy".

2. From the Home tab in the Clipboard group double-click the Format Painter button.

3. When you activate the Format Painter tool, you’ll see the mouse pointer transform into a paintbrush icon - a sign that the tool is ready to copy the formatting from the source cell.

The mouse pointer with a paintbrush icon in Excel.

4. To apply formatting, choose the cells or ranges you want to modify by clicking and dragging your mouse. Select the ranges of cells by dragging your cursor across them. In the example below we copied the formatting to cells A9 to C9.

Excel spreadsheet with the formatting from cells A4 to C4 now copied to cells A9 to C9 with a callout saying "The formatting from cells A4 to C4 has now been copied to cells A9 to C9"

5. After selecting the desired cells or ranges, simply continue to another set of cells that require formatting and select these cells. Repeat until all cells are formatted as required.

Excel spreadsheet with cells A, B and C in rows 4, 9, 15, 18, 27, 31 and 36 all with formatting applied

6. To turn off the Format Painter either press the ESC key or click the Format Painter button once again.

By following these steps, you can efficiently use the Format Painter to copy and apply formatting to multiple cells in Excel. This feature saves time and effort, especially when you need to maintain a consistent appearance across different sections of your spreadsheet.

4. How to use Format Painter with Conditional Formatting in Excel

Excel’s Conditional Formatting is a robust tool that automatically formats cells based on specific conditions or criteria. 

Let's say you have a sales data spreadsheet with a column that contains the total sales value for each sale made. You want to highlight the cells that meet a certain value threshold in a specific colour to draw attention to them. Conditional Formatting can help you achieve this.

To optimise the use of Conditional Formatting, you can combine it with the Format Painter tool, which efficiently applies and extends conditional formatting across multiple cells or ranges. 

Follow this detailed step-by-step guide to learn how to use the Format Painter with Conditional Formatting.

How to create a Conditional Formatting Rule in Excel

First, create a conditional formatting rule. In the example below we will highlight any sales that are greater than $4,700:

1. Select the range of cells containing the sales data.

Excel spreadsheet with cells B2 to B22 selected

2. Go to the Home tab in the Excel Ribbon and click on Conditional Formatting.

Excel Ribbon on the Home tab with the Conditional Formatting option shown in a red box

3. From the drop-down menu, choose Highlight Cells Rules and then Greater Than.

Conditional Formatting selected with Highlight Cells Rules selected then Greater Than... selected after that

4. In the dialogue box that appears, enter the threshold value that determines what constitutes a high sale. In our example, this will be $4,700.

Greater Than dialogue box with 4700 in the Format cells that are GREATER THAN: box

5. By default, the cells with sales higher than the threshold will be formatted with ‘Light Red Fill with Dark Red Text’.

Excel spreadsheet with cells B2 to B22 with Conditional Formatting on

6. To select a different formatting style to apply to the cells with sales higher than the threshold, click the drop-down arrow on the Format box to select a pre-set format or create a Custom format. A Custom format could be a specific font colour, cell fill colour, or any other formatting option.

Greater than dialogue box with a callout box pointing the the drop-down arrow saying "Click the drop-down arrow to reveal format options"

7. Click OK to apply the Conditional Formatting.

Conditional Formatting was applied from cell B2 to cell B22

After applying the Conditional Formatting, any cells in the selected range with sales higher than the specified threshold value of $4,700 will be automatically highlighted according to the chosen formatting style. This allows you to quickly identify and analyse the cells with the highest sales values in your spreadsheet.

Copying Conditional Formatting Rules in Excel

Now, if you enter new data outside of the range formatted with the Conditional Formatting rule, use the Format Painter to paint the rule over the new data. In the example below, new data has been entered into rows 23 to 26.

Excel spreadsheet with new rows 23 to 26 added with a callout that says "New data entered in rows 23 to 26. These rows do not have Conditional Formatting applied."

1. Select a cell that has the Conditional Formatting applied. In the example below, we selected cell B2 but any cells with the Conditional Formatting applied could be selected.

Excel spreadsheet with cell B2 selected

2. From the Home tab, click the Format Painter button. The mouse pointer will now change to a paintbrush.

3. Now, select the range of cells where you want to apply the same Conditional Formatting. You can click and drag to select multiple cells or click on individual cells while holding the left mouse button. 

Excel spreadsheet with cells B23 to cells B26 selected and Conditional FOrmatting has been applied

4. Excel will apply the Conditional Formatting rule from the original cell to the selected range of cells, extending the formatting to the new data.

5. By using the Format Painter, you can easily extend the Conditional Formatting rule to new data without manually setting up the rule again. In the example below, we selected cells B23 to B26 as these are the newly pasted cells with no conditional formatting applied.

By following these detailed steps, you can effectively harness the Format Painter with Conditional Formatting in Excel. This combination allows you to create complex formatting rules based on specific conditions and effortlessly apply them to different cells or ranges, saving time and ensuring consistent formatting throughout your spreadsheet.

Conclusion on the Format Painter in Excel

Excel’s Format Painter is a highly flexible tool that can help you save time and maintain consistency in formatting. It allows you to effortlessly transfer formatting styles from one cell to another, ensuring a professional-looking spreadsheet. Don’t be afraid to experiment with the Format Painter to improve your Excel skills and increase productivity. Take advantage of this powerful feature and unlock its full potential in your Excel workflow.


Was this blog helpful? Leave a Comment below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>