WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Format Painter in Excel

Format Painter in Excel

What is a Format Painter in Excel?

Format Painter in Excel is a tool which is used to copy the same format of a cell or a group of cells to the other cells. It is located on the home tab in the clipboard section, to copy the format the cells containing the format is to be selected first and then we click on the format painter button and then select the cells which we want to format.

We often copy the content of cells but ever wondered how we can copy only the formatting of the cell and that too without copying the value of the cell. Yes, this is possible to copy and paste only the formatting of one cell to another cell with the help of a format painter.

  • Using a format painter tool, we can copy the look of one cell or of the complete range of the cells and paste that onto other cells or range.
  • Format painter is the inbuilt tool of excel that belongs to the formatting class of functions. This tool can be used to copy the border, font style, the color of the cell, adjusting the width of the cell and all the interiors of the cells to another cell that is not at all formatted or formatted in a way that is not desired.
  • The great thing that comes with the format painter is that its use is not restricted to a single cell. We can use the format painter on the complete workbook to get it formatted like the other workbook.
  • The format of the worksheet maybe a border on the header, separate color on negative numbers, and much more. Now, if we have already created a workbook that is in the required format, we can always use the format painter tool and simply make the raw workbook as the formatted workbook with the help of a format painter.

So, now we do not need to spend time on formatting a workbook again and again as we have the format painter tool in excel.

Format Painter in Excel

How to Use Format Painter in Excel?

You can download this Format Painter Excel Template here – Format Painter Excel Template

Example #1 – Formatting the Cells in Excel

In this method, we will copy the formatting of the range to another range.

Step 1: Select the range that is formatted.

Format Painter Example 1

Step 2: Go to the “Home “Tab. From the home tab, there will be the option of format the painter.

Format Painter Example 1-1

Click on this option and then select the range where the format has to be applied. After the formatting is applied, we will get the range formatted as an original.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Format Painter Example 1-2

Example #2 – Formatting the Conditional Formatted Data

In this method, we can use the format painter to apply the conditional formatting from one range to another range of cells.

Step 1: Select the Range of data and Click on Format Painter.

Format Painter Example 2

The steps will be the same as explained above, but the only difference is that in this case, we also copy the conditional formatting of the cells.

Format Painter Example 2-1

Example #3 – Formatting the Shape of Object

In this method, we will format the object.

Step 1: Insert the shape.

A shape can be inserted by going to the “INSERT” tab and choosing the required shape.

Example 3

Step 2: Now select the shape that has all the formatting as we need and click on format painter.

Example 3-1

Step 3: Now select the shape that needs to be formatted in excel, and all the formatting will be copied on to the shape.

Example 3-2

Example #4- Format Painter on a Complete Worksheet

In this case, we will format the complete workbook instead of a single cell or range.

Step 1: Select the complete workbook that is already formatted or has the properties that are required and then click on the format painter option.

Example 4

Step 2: Select the worksheet that wants to be Formated; then, it will be automatically applied.

Example 4-1

Things to Remember

  • While using format painter, this should be remembered that all the properties/Format of one cell will be applied to the new location, and we do not have the option of choosing what format has to be applied or not.
  • After we have formatted the new location, the option of format the painter will disappear. To format more than one location, we need to double click on the format painter option as this will keep the function alive until we choose to turn it off by pressing key “ESC.”
  • If we are formatting a complete worksheet by using a format painter, then this should be remembered that both the sheets should have the same data in the same location. This means that if row 1st has 10 headers and another sheet has only 9 headers, then all 10 will be formatted. So we will have one Header extra that is formatted but will no data in it.
  • Format painter in Excel can also be used from paste specials options.

Recommended Articles

This has been a guide to Format Painter in Excel. Here we discuss how to use the Format Painter on cells, Shape of Object, Conditional Formatting along with practical examples and a downloadable template. You may learn more about excel from the following articles –

  • VBA Conditional Formatting
  • Custom Number Format in Excel
  • Pivot Table Conditional Formatting
  • AutoFormat Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Format Painter Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More