How to Use the Format Painter in Excel

How to Use the Format Painter in Excel
Coming up next: Wrapping Text in Excel: How to Wrap Text within Cells

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 The Format Painter Tool
  • 1:30 Format Painter with…
  • 3:18 Format Painter with…
  • 5:17 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed
Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

This lesson explains the Format Painter tool in MS Excel. The command works similarly to the Copy/Paste command; however, it only copies the formatting of the cell, for instance numerical formatting, highlighting and borders.

The Format Painter Tool

Formatting data in a spreadsheet not only makes it easier to read and understand, but it also makes it easier for the reader to actually find the data they want to see. It helps to draw attention to data such as text, numbers and totals.

Imagine you created a spreadsheet to track expenses for the month. You formatted everything nicely, and it looks just the way you want it. When you started, this was just for the month of January, but after looking at it, you have decided to track your expenses for the entire year. This requires adding the other 11 months. However, you don't want to re-create the formatting for the borders, highlight colors, numbers, etc.; you just want to add the numbers and have the formatting already in place.

This is where the format painter comes in! If you are familiar with the Copy/Paste command, where you can copy the data from cell to cell, you will find the Format Painter command is similar. The difference? The Format Painter copies only the formatting of the cells and leaves the data empty for you to enter.

This lesson will review two operations using the Format Painter tool. The first will be how to select a range of cells and copy the formatting applied to other adjacent cells. The second is how to copy the formatting from one cell and apply the formatting to a non-adjacent cell or cells.

Format Painter with Adjacent Cells

So, let's take a look at the Format Painter with adjacent cells. Adjacent means lying near, close or adjoining. This topic will demonstrate how to copy the formatting for a range of cells (that are adjacent) and apply the formatting to another range of cells (that are adjacent as well). For instance, A3 through B5 would include all cells with that range (A3, A4, A5, B3, B4 and B5). The cells are connected; therefore, the cells are adjacent to each other.

If we take a look at our expense tracker, you can see we have formatted everything, including the expense column for the month of January:

Spreadsheet for example
example spreadsheet

Now we need to add the other 11 months and apply the formatting to the cells where we enter the dollar amounts. So, here are the steps:

  1. Add the desired formatting to a cell or cells.
  2. Select the cell or range of cells to copy.
  3. Go to the Home menu in the ribbon.
  4. Look in the Clipboard grouping of commands.
  5. Click on the Format Painter command (it's like a paint brush icon in the lower, right-hand corner).
  6. Select the cell or range of cells to copy the formatting to.

You are all set. Now you can add data, and the formatting will be applied automatically. This feature is very helpful when you want to extend a spreadsheet to accommodate new data, and you need to format the new cells to match the original data area.

Format Painter with Non-Adjacent Cells

So, now let's take a look at the Format Painter with non-adjacent cells. You might have an occasion where you want to copy the formatting for one cell to cells that are non-adjacent. For instance, you may want to copy the formatting to C19, E19 and G19. None of these cells are connected or joined and are, therefore, non-adjacent to each other.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support