Format Painter and other ways to copy formatting in Excel

This short tutorial shows how to copy formatting in Excel using Format Painter, Fill Handle and Paste Special options. These techniques work in all versions of Excel, from 2007 through Excel 365.

After you've put a lot of effort in calculating a worksheet, you would usually want to add some finishing touches to make it look nice and presentable. Whether you are creating a repot for your head office or building a summary worksheet for the board of directors, proper formatting is what makes important data stand out and convey the relevant information more effectively.

Fortunately, Microsoft Excel has an amazingly simple way to copy formatting, which is often overlooked or underestimated. As you have probably guessed, I am talking about the Excel Format Painter that makes it really easy to take the formatting of one cell and apply it to another.

Further on in this tutorial, you will find the most efficient ways to use Format Painter in Excel, and learn a couple of other techniques to copy formatting in your sheets.

Excel Format Painter

When it comes to copying formatting in Excel, Format Painter is one of the most helpful and underused features. It works by copying the formatting of one cell and applying it to other cells.

With just a couple of clicks, it can help you reproduce most, if not all of the formatting settings, including:

  • Number format (General, Percentage, Currency, etc.)
  • Font face, size, and color
  • Font characteristics such as bold, italic, and underline
  • Fill color (cell background color)
  • Text alignment, direction and orientation
  • Cell borders

In all Excel versions, the Format Painter button is located on the Home tab, in the Clipboard group, right next to the Paste button:
Excel Format Painter button

How to use Format Painter in Excel

To copy cell formatting with the Excel Format Painter, just do the following:

  1. Select the cell with the formatting you want to copy.
  2. On the Home tab, in the Clipboard group, click the Format Painter button. The pointer will change to a paint brush.
  3. Move to the cell where you want to apply the formatting and click on it.

Done! The new formatting is copied to your target cell.
Using the Format Painter to copy formatting to a target cell

Excel Format Painter tips

If you need to change formatting of more than one cell, clicking each cell individually would be tedious and time consuming. The following tips will speed things up.

1. How to copy formatting to a range of cells.

To copy formatting to several adjacent cells, select the sample cell with the desired format, click the Format Painter button, and then drag the brush cursor across the cells that you want to format.
Using the Format Painter to copy formatting to a range of cells

2. How to copy format to non-adjacent cells.

To copy formatting to non-contiguous cells, double-click the Format Painter button instead of single-clicking it. This will "lock" the Excel Format Painter on, and the copied formatting will be applied to all cells and ranges that you click/select until you press Esc or click on the Format Painter button one final time.

3. How to copy the formatting of one column to another column row-by-row

To quickly copy the format of the entire column, select the heading of the column whose formatting you want to copy, click Format Painter, and then click the heading of the target column.

As demonstrated in the following screenshot, the new formatting is applied to the target column row-by-row, including the column width:
Apply the formatting of one column to another column row-by-row

In a similar manner, you can copy the format of the entire row, column-by-column. For this, click the sample row heading, click Format Painter, and then click the heading of the target row.

As you have just seen, the Format Painter makes copying format as easy as it can possibly be. However, as is often the case with Microsoft Excel, there is more than one way to do the same thing. Below, you will find two more methods to copy formats in Excel.

How to copy formatting down a column using the Fill Handle

We often use the fill handle to copy formulas or auto fill cells with data. But did you know that it can also copy Excel formats with just a few clicks? Here's how:

  1. Format the first cell the way you want.
  2. Select the properly formatted cell and hover over the fill handle (a small square at the lower right-hand corner). As you do this, the cursor will change from the white selection cross to a black cross.
  3. Hold and drag the handle over the cells where you want to apply the formatting:
    Copy formatting using the Fill Handle
    This will also copy the value of the first cell to other cells, but don't worry about that, we will undo it on the next step.
  4. Release the fill handle, click the Auto Fill Options drop-down menu, and select Fill Formatting Only:
    In the Auto Fill Options drop-down menu, and select Fill Formatting Only.

That's it! The cell values revert to the original values, and the desired format is applied to other cells in the column:
The desired format is applied to other cells in the column.

Tip. To copy the formatting down the column until the first empty cell, double-click the fill handle instead of dragging it, then click AutoFill Options, and select Fill Formatting Only.

How to copy cell formatting to an entire column or row

Excel Format Painter and Fill Handle work great with small selections. But how do you copy the format of a specific cell to an entire column or row so that the new format is applied to absolutely all cells in a column/row including blank cells? The solution is using the Formats option of Excel Paste Special.

  1. Select the cell with the desired format and press Ctrl+C to copy its content and formats.
  2. Select the entire column or row that you want to format by clicking on its heading.
  3. Right-click the selection, and then click Paste Special.
  4. In the Paste Special dialog box, click Formats, and then click OK.
    In the Paste Special dialog box, click Formats, and then click OK.

Alternatively, select the Formatting option from the Paste Special pop-up menu. This will display a live preview of the new format, as shown in the screenshot below:
Select the Formatting option from the Paste Special pop-up menu.

Shortcuts to copy formatting in Excel

Regrettably, Microsoft Excel doesn't provide a single shortcut that you could use to copy cell formats. However, this can be done by using a sequence of shortcuts. So, if you prefer working form the keyboard most of the time, you can copy format in Excel in one of the following ways.

Excel Format Painter shortcut

Instead of clicking the Format Painter button on the ribbon, do the following:

  1. Select the cell containing the required format.
  2. Press Alt, H, F, P keys.
  3. Click the target cell where you want to apply the formatting.

Please note, the shortcut keys for Format Painter in Excel should be pressed one by one, not all at once:

  • Alt activates the keyboard shortcuts for ribbon commands.
  • H selects the Home tab on the ribbon.
  • F, P select the Format Painter button.

Paste Special formatting shortcut

Another quick way to copy format in Excel is by using the keyboard shortcut for Paste Special > Formats:

  1. Select the cell from which you want to copy the format.
  2. Press Ctrl + C to copy the selected cell to the Clipboard.
  3. Select the cell(s) to which the format should be applied.
  4. Press Shift + F10, R and then click Enter.

This key sequence does the following:

  • Shift + F10 displays the context menu.
  • Shift + R chooses to paste only formatting.

These are the fastest ways to copy formatting in Excel. If you've accidentally copied a wrong format, no problem, our next article will teach you how to clear it :) I thank you for reading and hope to see you on our blog soon!

Table of contents

31 comments

  1. My modification to copy Width (Columns) and Height (Rows) Selected Ranges

    Public Sub copyWidthHeightSelectedRanges()
    Dim sourceRange As Range
    Dim DestinyRange As Range
    Dim xColumn As Double
    Dim yRow As Double

    On Error Resume Next
    Set sourceRange = Application.InputBox("Select a range:", "Source selection cells", Application.Selection.Address, Type:=8)
    On Error GoTo 0
    On Error Resume Next
    Set DestinyRange = Application.InputBox("Select a range (Top Left a Cell):", "Destiny selection cells", Application.Selection.Address, Type:=8)
    On Error GoTo 0

    If Not (sourceRange Is Nothing) Then
    If Not (DestinyRange Is Nothing) Then
    Application.StatusBar = "" 'only for visualizing progress SET START
    Call defreeze(False) 'Application.ScreenUpdating = False
    On Error Resume Next
    For xColumn = 1 To sourceRange.Columns.Count
    Sheets(DestinyRange.Worksheet.Name).Cells(1, DestinyRange.Column + xColumn - 1).ColumnWidth = Sheets(sourceRange.Worksheet.Name).Cells(1, sourceRange.Column + xColumn - 1).ColumnWidth
    Call showStatusBar(xColumn, sourceRange.Columns.Count, " Process Running 1/2: ") 'only for visualizing progress
    Next xColumn
    On Error GoTo 0
    On Error Resume Next
    For yRow = 1 To sourceRange.Rows.Count
    Sheets(DestinyRange.Worksheet.Name).Cells(DestinyRange.Row + yRow - 1, 1).RowHeight = Sheets(sourceRange.Worksheet.Name).Cells(sourceRange.Row + yRow - 1, 1).RowHeight
    Call showStatusBar(yRow, sourceRange.Rows.Count, " Process Running 2/2: ") 'only for visualizing progress
    Next yRow
    On Error GoTo 0
    Application.StatusBar = "" 'only for visualizing progress RESET END
    Call defreeze 'Application.ScreenUpdating = True
    End If
    End If
    End Sub

    'to fast work
    Sub defreeze(Optional status As Boolean = True) 'disabled by default, True
    With Application
    If status Then
    .ScreenUpdating = True
    .EnableEvents = True
    Else
    .ScreenUpdating = False
    .EnableEvents = False
    End If
    End With
    End Sub

    'progressbar, only for visualizing progress
    Sub showStatusBar(current As Double, total As Double, topic As String)
    'Application.StatusBar = "" 'SET START
    'Call showStatusBar(current, total, " Process Running: ")
    'Application.StatusBar = "" 'RESET END
    Static pctDone As Long
    Dim numberOfBars As Long
    Dim tmpPctDone As Long
    Dim currentStatus As Long
    Dim fast As Boolean

    numberOfBars = 50
    'Application.StatusBar = "[" & Space(NumberOfBars) & "]"

    ' Display and update Status Bar
    currentStatus = Int((current / total) * numberOfBars)
    If currentStatus > numberOfBars Then currentStatus = numberOfBars
    tmpPctDone = Round(currentStatus / numberOfBars * 100, 0)
    With Application
    If pctDone tmpPctDone Then
    pctDone = tmpPctDone
    If .ScreenUpdating = False Then
    fast = True
    .ScreenUpdating = True
    Else
    fast = False
    End If
    .StatusBar = topic & " [" & String(currentStatus, "|") & _
    Space(numberOfBars - currentStatus) & "]" & _
    " " & pctDone & "% Complete"
    If fast = True Then .ScreenUpdating = False
    Else
    If pctDone = 0 And .StatusBar = "" Then .StatusBar = topic & " [" & String(currentStatus, "|") & _
    Space(numberOfBars - currentStatus) & "]" & _
    " " & pctDone & "% Complete"
    End If
    End With
    ' Clear the Status Bar when you're done
    ' If Current = Total Then Application.StatusBar = ""
    End Sub

  2. Do you know of a way to copy just the color versus the full cell format? Kind of a pain to have to open the fill color, find the RGB or hex to set the cell fill color the same. Tks.

    • Hi! Unfortunately Excel only allows you to copy the entire cell format. I think you can copy only the color by using the VBA macro.

  3. Came here to say Thank You!

  4. The shortcut for Paste Formats is now shorter: Shift+F10 R

  5. I would like to copy the data in row 1 and insert this to all the data below .
    how to do it? Thank you

  6. Hi, I have a filter formula, whereby I am only displaying one row of data at a time on a separate sheet based on the filter selected. Example(=FILTER(B5:D14,D5:D14=H2,"No results")
    The cells in b5:d14 have a conditional formatting on them to highlight in colour.
    How do I pull across the colours to the filtered cells?

  7. I would like to create a HotKey to allow copying of a the complete cell format to another cell or groups of cells. Having to "hit" Alt-H-F-P is too many keys. I want to set up a hotkey so that I can simply press Ctrl + (A single key) to copy the complete format of that cell.

    I am using Office Professional 2016.

    Is it possible to create a HotKey?

    • Use quick access toolbar and add format painter, then you can get an Alt + number combination

  8. I would like to copy all column and cell formatting from one workbook to another workbook using format painter-- I don't want the source content. Any help would be appreciated

  9. I would like to copy the conditional formatting from one row to another.......I have tried "Paste Special", but this still requires a LOT of editing. (my worksheet will have about 800 row, and I wish to apply the same formula to each row)

    RULE
    Formula: =$E$4="Open" Format (Red colour) Applied to: =$B4:$J$4

    If I use "Paste Special", every row is dependant upon $E$4
    Formula: =$E$4="Open" Format (Red colour) Applied to: =$B25:$J$25
    Formula: =$E$4="Open" Format (Red colour) Applied to: =$B26:$J$26
    Formula: =$E$4="Open" Format (Red colour) Applied to: =$B27:$J$27
    Formula: =$E$4="Open" Format (Red colour) Applied to: =$B28:$J$28

    What I want is the formatting to be dependant upon its own row.
    Formula: =$E$25="Open" Format (Red colour) Applied to: =$B25:$J$25
    Formula: =$E$26="Open" Format (Red colour) Applied to: =$B26:$J$26
    Formula: =$E$27="Open" Format (Red colour) Applied to: =$B27:$J$27
    Formula: =$E$28="Open" Format (Red colour) Applied to: =$B28:$J$28

  10. Amazing! Thank you for saving me so much time trying to guess the color of a cell so I can replicate it in another cell. It's such a simple function, yet why do I have to spend 30 minutes googling it before I find a solution. This should be excel 101. It will be handy for a lot of other situations when I want to copy a formula etc.

  11. These instructions do not work. What about 365?

  12. Thank you Svetlana Cheusheva! Alt+HFP has the done the job for me:-)

  13. Copy cell select cells which you want to change the format then press CTRL and ALT then press V select format and enter.

  14. Hello,
    I'd like to apply multiple rules set in the second row, to the whole spreadsheet. How can I do it without compromising on the rules in the initial row? Paste special and the painter function doesn't seem to work. Also, I have various deadlines (at different stages of the same action request) captured in different columns, but within the same row. When phase 1 is done, I'd like to color code the next phase with the upcoming deadlines,(due today, overdue,) while the completed phase shows in green. I creaed multiple color-coded alerts in phase one which I'd like to tranfer the other phases as well. When phase B is completed, I need to focus on phase C's deadlines, within the same row. Each action request has sub categories, hence, multiple rows. How could I best capture all the above: each request within a row and with multiple deadlines? Thank you! Evelyn

  15. I WANT AN aUTO FORMAT.
    A single cell(D5) is open to format.
    Neighbouring cell(E5) is locked ..
    I want E5 to copy format that of D5 (whenever D5 changed.. )
    please help ...

  16. Excellent! The tips are useful...

  17. Hi, I use Excel 2013 and cannot find the feature in a special paste, which pastes a replica of the copied block. I do get nearly a replica but not the row height. However, the row height is material for me. The mirror copy option will not be useful for me. Could you kindly assist?

  18. I would like to copy the value of one cell and its font and shaded color by simply using the =A2 formula.
    Cell A2 might be italicized and shaded yellow; if it is, then I need the other cell to reflect that formatting automatically.
    How is this accomplished?

    • This should work:
      Select the cell you like the look of and then click Format Painter, and then click the cell you want to format. Just using =A2 won't copy the formatting.

  19. Dear Svetlana Cheusheva,

    I need your regarding Gregorian months name convert to Hijri months name with the help of excel formula.

    Example: January = Muharram

    Thanks & Regards,
    abas

  20. Hi, I'm familiar with the Format Painter, but my corporate laptop was just upgraded to Office 2016 (from 2011 for Mac). In Excel, when using the Format Painter, it will sometimes not apply the same font or color as the original. Sometimes it will. Any idea what is up?

    • Just a suggestion, but check the formatting of your destination cell. Clear any and all existing formatting. And check for any conditional formatting.
      That is, if after six years you haven't found a solution.

      ?

      But this might be helpful to others.

  21. Hey guys!

    I enjoy a lot your post, maybe it is not the forum; I could not find in others posts or people questions.
    I have a pivot table which brings names and on the left column there are a formula bringing two options of codes and I am trying to do the conditional formatting, like cell not equal " " has border. However, I could not find a way to do it, the spreadsheet brings border table blank and it is "not clean" as report to send to agents. I tried formulas as =formatatext or blabla,A=fastrac...

    Nothing helped, do you have idea how I can do it? It is necessary to be kind of "automatic" format as the pivot functions to others select something and check the information in the clean way.

    Thank you so much!

    Cris

    IFERROR((VLOOKUP($C11,'Agent database'!$B:$AT,7,FALSE)),(IFERROR(VLOOKUP($C11,PAULA_AG_SU16!$S:$T,2,FALSE),"")))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)