Shortcut for Format Painter in Excel

Last Updated :

-

Blog Author :

Twinkle Sethi

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Format Painter Excel Shortcut

Format Painter in Excel is used to copy the format of one or more cells and apply it to other cells. We can use it to copy format settings like:

  • Colour, face, and size of the font
  • Cell border
  • Colour of background cell
  • Number Format (like Date, General, Percentage, etc)
  • Alignment and orientation of the text in a cell (like Left, Right, Center)
  • Font modifiers (like Underline, Bold, Italic)

Format Painter is a button that is present in the "Clipboard" group on the "Home" tab as below:

format painter

Copying formatting with this button is tedious and inefficient in case we are working with large datasets. So, formatting in Excel can also be copied using a sequence of keyboard shortcuts. These shortcuts require pressing a series of keyboard keys instead of clicking on the "Format Painter" button in the "Clipboard" group.

Here, we discuss the top two methods to use "Format Painter" in Excel.

Table of contents

Shortcut Method #1 | Format Painter in Excel

Suppose we have a table consisting of some students' records of seven rows/seven students, and we wish to change the formatting (orientation, display, and alignment) of all the records as per the first record. We can do it through a "Format Painter. Let us see below how we can use the format painter shortcut to accomplish this task:

format painter example 1.1

We have the above table consisting of students' records. In column “D,” the number format is "Percentage" for the first cell and 'General' for the other. Also, the first cells in columns "B" and "C" are right-aligned. The column "E" is left aligned. At the same time, the other cells are not aligned the same way. So, we do this using "Format Painter." Let us see below how the shortcut works for format painter in Excel:

  1. Select the cells or rows containing the desired format or those from which we wish to copy the formatting and paste it to others.


    format painter example 1.2

  2. Then, press the keyboard Alt, H, F, and P keys.


    format painter example 1.3

  3. On pressing these keys, those cells from where we wish to copy the format will get selected/highlighted as below:


    format painter example 1.4

  4. Now click the cells where we want to paste the desired formatting.


    format painter example 1.6

  5. So, we can see in the above screenshot that this also copies the formatting to the target cells (rest of the records) as well.


    Note: While pressing the keys: Alt, H, F, P; each key should be pressed one after the other and not simultaneously.

    Alt Key: This will activate the keyboard shortcuts for the commands on the ribbon.

    H Key: This will select the Home tab on the ribbon

    F, P keys: It will select the Format Painter

Shortcut Method #2 | Format Painter in Excel

Let us say in the example below that we wish to do the same task using another shortcut for "Format Painter," so below is the illustration with another keyboard excel shortcut/approach for format painter.

example 2.1

So, we see that the above table consists of students' records. In this, the number format of column "D" is "Percentage" for the first cell and "General" for the other. Also, the first cells in columns "B" and "C" are right-aligned, and column "E" is left-aligned. In contrast, the other cells are not aligned the same way. So, we do this using another shortcut key for "Format Painter" in Excel. Let us see below how this shortcut works:

  • Select the cells or rows containing the desired format or those from which we wish to copy the formatting and paste it to others.
example 2.2
  • Then press Ctrl+C to copy the format of the selected cells.
example 2.3
  • Now click on the cells where we wish to paste the desired formatting.
example 2.4
  • Then, press the keyboard's Alt, E, S, and T shortcut keys and press the "Enter" key.
example 2.5

So, we see in the screenshot below that this also copies the formatting to the target cells (rest of the records).

example 2.6

Note: While pressing the keys: Alt, E, S, and T, each key must be pressed one after the other and not simultaneously.

  • Alt +E +S Keys: Pressing this will activate the Paste Special dialog box. The "H" key will select the "Home" tab on the ribbon.
  • T Key: Pressing the "T" keyboard key will select the "Format" from the "Paste Special" dialog box, i.e., it will paste only the format.

Things to Remember

  • The keyboard shortcuts for Format Painter only activate the Format Painter. To copy formatting in excel and paste it to other cells, the range of cells from which the format is to be applied is first selected immediately. Then the cells to which the format is to be pasted have to be adjacent to the cells from which the format is copied.
  • We can build a macro with real shortcut keys for "Format Painter" or a single key/button press.
  • When we use the "Format Painter" button, it allows us to copy and paste the desired formatting only once. It can be a problem when we wish to copy the formatting from some cells and then paste it to those cell ranges that are not contagious.
  • To overcome this problem, we can double-click the "Format Painter" button, which would lock the "Format Painter." Thus, allow pasting the formatting until the "Format Painter" is disabled using the "Esc" key on the keyboard.

You can download this Shortcut for Format Painter Excel Template - Shortcut for Format Painter Excel Template