Excel Format Painter Shortcut
MS Excel is one of the most powerful spreadsheet software with a variety of built-in functions and features. The Format Painter is an essential feature; however, users often neglect it while working on Excel. Although formatting data in Excel helps in giving a clean and professional look to an Excel sheet, it can be tedious and time-consuming at times. That is why Excel enables users to use the Format Painter and organize formatting quickly from one cell to another. Also, when the Format Painter feature is used with shortcuts, it makes a huge difference and saves a lot of time when working with large amounts of data in Excel.
In this article, we discuss some essential Excel Format Painter shortcuts and respective tutorials on using them. Before discussing the shortcuts, let us first introduce the Format Painter feature in Excel.
What is Format Painter in Excel?
By definition, “Format Painter is an excellent Excel feature that allows users to copy formatting from one cell or range of cells and apply/ paste it to a different cell or range of cells within the same worksheet, other worksheets, and even different workbooks.”
In simple words, the format painter feature helps to copy the formatting of one or more cells onto other cells. Specifically, we can use the format painter to copy the formatting like listed below:
- Size, style and colour of fonts
- Font modifiers, such as Bold, italic, and underline
- The background colour of the cell
- Cell borders
- Number formats, such as the dates, general, percentages, etc.
- Alignments and orientations of cells’ data, such as the left, right, and center
Format Painter Shortcut in Excel
The most common shortcut of Format Painter is present under the Home tab inside the category Clipboard on the ribbon.
However, using this shortcut button, again and again, is a quite tedious task and time-taking, especially when dealing with large datasets. Therefore, it is essential to find keyboard shortcuts for any specific task in Excel and perform a relevant task by pressing some keys or a sequence of keys from the keyboard.
There are multiple methods to complete any specific task in Excel. Likewise, it also supports multiple shortcuts for performing an individual task. When it comes to using the format painter shortcut in Excel, there are three significant ways, i.e.:
- Format Painter using Keyboard Shortcut
- Format Painter using Paste Special Dialogue Box Shortcut
- Format Painter using Paste Special Shortcut from Context Menu
Let us discuss each method in detail:
Shortcut 1: Format Painter using Keyboard Shortcut
There is no definite keyboard shortcut for the Format Painter option in Excel. However, the Alt key method works. Specifically, we first need to press the Alt key on the keyboard to activate the keyboard shortcuts for the commands on the Excel ribbon. Once the shortcuts are activated, or we see some specific characters associated with the ribbon options, we must press the keys H, F and P.
Let us apply this shortcut in an example sheet in Excel:
Consider the following Excel sheet with some students’ data, such as marks in two semesters, respective percentage and grades of each student. Here, we need to change the formatting of all the students’ data according to the second row. Although we can arrange the formatting or each row/ column one by one, it is better to use the format painter and arrange it at once.
In the above sheet, we can see that the table has different formatting and styles. For example, some students’ names are left-aligned, some are right-aligned, the first cell of column D has a percentage sign while others lack it, etc.
Let us understand the steps of using this particular shortcut, i.e. “Alt + H + F + P” without quotes:
- First, we need to select the desired row from which we want to copy the formatting and apply it to others. Since we need to arrange all the rows as the second row, we select the second row in an example sheet.
- Next, we must press the shortcut keys Alt, H, F, and P, one after another, using the keyboard. Upon pressing the keys, the corresponding data (second row in our case) will be selected or highlighted, as shown in the following image:
- Lastly, we must select all the cells using the mouse where we want to apply the desired formatting. In our case, we select and drag the area from cell A3 to cell E8 using the mouse. After this, the formatting from selected data is applied instantly to the targeted cells or data.
After using the format painter in an example sheet, our sheet looks like this:
Thus we can use Excel Format Painter shortcut keys from the keyboard and apply the desired formatting to specific cells, rows or columns.
Shortcut 2: Format Painter using Paste Special Dialogue Box Shortcut
This is an alternate method to use the Format Painter feature in Excel using the shortcut. According to this method, we first copy the data with formatting and then paste the copied formatting (without data) on the desired cells.
Let us apply this shortcut in an example sheet in Excel:
Consider the following Excel sheet with some employees’ records, such as name, employee ID, mobile, e-mail, and designation of each employee. Here, we need to change all the employees’ records’ formatting similar to the second row.
In the above sheet, we can see that the table has different formatting and styles as in the example sheet of the previous shortcut.
This time, we will arrange the formatting using the Paste Special shortcut instead of the “Alt + H + F + P” shortcut. For this, we must perform the following steps:
- First, we must select the desired row from which we want to copy the formatting and apply it to others. In our case, we select the second row, as shown below:
- After selecting the row, we press the shortcut keys Ctrl + C to copy the selected row with its formatting.
- Next, we need to select all the cells where we want to apply the formatting of the selected row. In our case, we select the cells from A3 to E8, as shown below:
- After that, we need to use the shortcut “Ctrl + Alt + V” without quotes. Here, we must press all the keys simultaneously. As soon as we use this particular shortcut, Excel launches Paste Special dialogue box, as shown below:
Alternately, we can use the shortcut “Alt + E + S” in place of “Ctrl + Alt + V” to open the Paste Special dialogue box. Here, we need to press each key separately, one after another.
- Lastly, we must press the key T to select the Format option from the Paste Special dialogue box and press the Enter key on the keyboard.
This will only paste the formatting from the copied data to the targeted cell(s), row(s) or column(s).
Our example sheet looks like this:
Thus we can use the Excel Format Painter feature using the Paste Special shortcut keys from the keyboard and apply the desired formatting to specific cells, rows or columns.
Shortcut 3: Format Painter using Paste Special Shortcut from Context Menu
Another quick shortcut to use Format Painter in Excel involves the Context Menu (right-click) feature. According to this method, we copy the desired formatting and paste only the formatting on the targeted cell using the Paste special feature from the Context Menu list.
This method includes the following steps:
- First, we copy the data in Excel from which we want to use the formatting on other targeted cells.
We can use the keyboard shortcut Ctrl + C to copy the desired data to the clipboard.
- Next, we need to select all the cells in which we want to apply the copied formatting. We can also select the cells with values because we will be pasting the formatting only, and the actual values in corresponding cells will not be affected.
- After selecting the cells to receive the formatting, we must simultaneously press the keys “Shift + F10” without quotes. This will launch Context Menu (Right-Click Menu). Lastly, we must press the keys S and R one after another, which will select the option to paste the copied formatting from the clipboard onto selected cells/ targeted cells.
The above steps are almost identical to a previous shortcut while doing it practically. Thus, we can follow the same example as the previous shortcut, just with different shortcut keys.
Important Things to Remember
- There is no definite keyboard shortcut key for the Format Painter option in Excel. However, a customized macro can be created to get a single shortcut key/ button for Format Painter.
- When we try to copy formatting using the Format Painter shortcut, we can copy and paste the desired formatting only once. This means that we have to copy the formatting again if we want to paste on certain cells that are non-adjacent.
- If we want to copy-paste formatting in several non-adjacent cells, we can copy the formatting and then double-click the Format Painter shortcut on the ribbon. Doing this will lock the Format Painter, and we will be able to paste the formatting several times until we disable the Format Painter by pressing the Escape key on the keyboard.