Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
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.
Format Painter in Excel (Table of Contents)
What is a Format Painter in Excel?
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 format painter in Excel.
- Using format painter tool of excel 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 this, 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 may be 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 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.
How to Use Format Painter in Excel?
Below are the examples to Format Painter in Excel.
Example #1 – Formatting the Cells in Excel
In this method, we will copy the formatting of range to another range.
Step 1: Select the range that is formatted.
Step 2: Go to the “Home “Tab. From the home tab, there will be the option of format painter.
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.
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.
The steps will be the same as explained above, but the only difference is this that in this case we also copy the conditional formatting of the cells.
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 “INSERT” Tab and choosing the required shape.
Step 2: Now select the shape that has all the formatting as we need and click on format painter.
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 #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.
Step 2: Select the worksheet that wants to be Formated then, it will be automatically applied.
Things to Remember About Format Painter in Excel
- While using the Excel format painter this should be remembered that all the properties/Format of one cell will be applied on 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 painter will disappear. To format more than one location we need to double click on 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 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.
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 –