Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Use the Format Painter Excel Feature? — 3 Bonus Tips

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

Note: This guide on how to use the Format Painter Excel feature is suitable for all Excel versions including Office 365. 

In this tutorial, I’ll walk you through how to easily use the Excel format painter feature.

You’ll learn:

Table Of Contents
  1. What is the Format Painter Excel Feature?
  2. How to Use the Format Painter in Excel? 
  3. Copy Conditional Formatting Using the Format Painter in Excel  
  4. Duplicate Shape Formatting Using the Excel Format Painter
  5. Useful tips while using the Format Painter in Excel
  6. FAQs
  7. Closing Thoughts

Related:

How to Superscript in Excel? (9 Best Methods)

How to Indent in Excel? 3 Easy Methods

How to Enable Excel Dark Mode? 3 Simple Steps

What is the Format Painter Excel Feature?

It is always a good practice to format your Excel sheet before sharing it with others. This improves not only the aesthetics but also the usability of your worksheet. 

Though formatting tasks like applying borders, headers, alignment, etc. look simple, they can become very daunting if you have to do them repeatedly. 

Let us say, for example, you have spent a decent amount of time carefully editing and formatting an Excel spreadsheet. What if you come across another spreadsheet that requires the same formatting to be applied? Do you begin all over from scratch?  

The format painter in Excel solves this very problem by quickly copying the previous formatting to your new spreadsheet. This will save you a lot of time and effort when working with multiple worksheets. 

The format painter copies:

  1. Font size, colour, font type, etc.
  2. Text Alignment ( Left, right & centre)
  3. Cell borders
  4. Cell Background
  5. Font Style ( Bold, Italics, Underline, etc.)
  6. Number format type ( Number, currency, accounting, text, etc.)
  7. Conditional Formatting. 

Without further ado, I’ll show you how to easily use the format painter in Excel. 

How to Use the Format Painter in Excel? 

To use the format painter to copy formatting follow these simple steps: 

  1. Select the cell or range of cells whose formatting you want to copy. 
Format Painter in Excel - Select the range of cells
Select the range of cells whose formatting you want to copy
  1. Click on the Format Painter button in the Clipboard section of the Home tab. 
Click on the Format Painter button in the Clipboard section of the Home tab
Click on the Format Painter button in the Clipboard section of the Home tab
  1. Go to the destination cell or range of cells where you want to apply the formatting and click on it. 
Go to the destination cell or range of cells and click on it to apply the copied formatting
Go to the destination cell or range of cells and click on it to apply the copied formatting

Excel will instantly apply the same formatting of your selected cell to the destination cell range. 

Please keep in mind that it will not copy original values with the formatting. 

You can use it to copy formatting within a worksheet, between worksheets, and between workbooks. In the following section, I’ll show you some interesting examples. 

Also Read:

How to Add Error Bars in Excel? 7 Best Methods

Easily Make a Bullet Chart in Excel—2 Examples

How to Shade Every Other Row in Excel? (5 Best Methods)

Copy Conditional Formatting Using the Format Painter in Excel  

This is one of the best use cases for the format painter. Conditional formatting requires a lot of work while setting up. Doing it again from scratch is a waste of time. 

You can instantly apply conditional formatting to other cells, worksheets, or workbooks simply by copying it using the format painter option. 

Copy conditional formatting using the Format painter in Excel
Copy conditional formatting using the Format Painter Excel Feature

What’s more interesting? You can copy conditional formatting from one workbook to another. 

This will immensely help you save your precious time. So keep this in mind the next time you use conditional formatting. 

Duplicate Shape Formatting Using the Excel Format Painter

The format painter does not only apply to normal cells. It also works with shapes in Excel. Let us say for example you have to add buttons for actions items in your workbook. For this, you need to create many rectangular boxes with text inside them. 

Copy Shape formatting using the Excel format painter
Copy Shape formatting using the Format Painter Excel Feature

You can instantly copy the formatting from the first shape to other shapes using the format painter in Excel. 

Useful tips while using the Format Painter in Excel

These are some important things to remember while using the format painter. 

  1. When you want to copy a formatting style over a range of cells, simply drag the cursor over the range of the destination cells after clicking on the Format Painter button. 
Drag the format painter cursor over a range of cells to apply the copied formatting
Drag the format painter cursor over a range of cells to apply the copied formatting
  1. When you want to copy a formatting style over many separate ranges of cells, double click on the Format painter button. This will lock the format painter option. You can now apply the formatting to all the ranges, without clicking the format painter each time. 

Once you are done, press Esc or once again click on the Format Painter icon to unlock it. 

Locking the Format Painter in Excel
Double click on the Format Painter button to lock it. Once it is locked, you can apply the copied formatting to as many separate ranges of cells as you want.
  1. You can copy the formatting of entire rows or columns to other rows or columns simply by selecting the row or column headers while using the format painter. 
You can save time by just selecting the row and column headers to copy the formatting
You can save time by just selecting the row and column headers to copy the formatting

Suggested Reads:

How to Autofit Excel Cells? 3 Best Methods

The FORMULATEXT Excel Function – 2 Best Examples

How to Apply the Accounting Number Format in Excel? (3 Best methods)

FAQs

What is the difference between the Format Painter Excel feature and the Copy feature?

The ‘Copy’ feature in Excel is an all-around tool that copies everything inside a cell including formulas, formatting, values etc. You can then use the various types of the ‘Paste’ feature to paste only values, formats, formulas etc.

The Format Painter is a simplified copying feature that applies only to the formatting. It lets you copy formatting from one range of cells to another range of cells ( including, shapes & graphs) with just a few clicks.

What is the shortcut key for Format Painter in Excel?

You can use the keyboard shortcut Alt+H+FP to access the Excel Format painter, instead of clicking on the icon.

Closing Thoughts

In this short tutorial, I have covered all the important details about the format painter in Excel. You can start using these tips right away in your next Excel worksheet. 

You can find more high-quality Excel guides here. 

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. 

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats. When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Use ANOVA in Excel: 4 Simple Steps

How to Enter Credit Card Transactions in QuickBooks 2019

Manage Customers and Jobs in QuickBooks Pro 2017 Part 2

How to Reduce Excel File Size? 7 Proven Ways

How to Stop Excel From Rounding? 4 Easy Ways

How to Use Guided Edit Mode in Adobe Photoshop Elements 2019

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)