Alif Bin Hussain
The Format Painter tool is very useful for copying the format of cells to multiple rows. Using the Format Painter, one can apply formatting to adjacent as well as non-adjacent rows. In this article, we will show you how to use Format Painter for multiple rows in Excel using five easy methods.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
5 Easy Ways to Use Format Painter for Multiple Rows in Excel
In this article, we will demonstrate five easy methods to use Format Painter for multiple rows in Excel. We will use the following dataset for this purpose.
1. Utilize Go To Dialog Box
In the first step, we will utilize the Go To dialogue box to format multiple rows. The procedure is discussed below.
Steps:
- First of all, select the rows (4 to 6) with formatted cells.
- Then click on the Home tab and go to,
Home → Clipboard → Format Painter
- After that press F5 to open the Go To dialogue box.
- Now type the range of rows (8 to 10) you want to format and press Enter .
- As a result, rows 8 through 10 will be formatted like rows 4 to 6.
Read More: How to Use Format Painter for Multiple Cells in Excel
2. Use Keyboard Shortcuts
In this method, we will use keyboard shortcuts to use Format Painter for multiple rows in Excel. We will copy the format of rows 4 to 6 and apply it to the 8th, 9th, and 10th rows. Read the following steps to learn how to do it.
Steps:
- First, select cells B4 to F6 and press Ctrl + C .
- Next, select cell B8 and press Shift + F10 simultaneously, and then press R .
- Consequently, you will have the proper formatting in your desired rows.
Read More: How to Use Format Painter to Copy Formatting in Excel (5 Ways)
3. Utilize Single-Click Process to Use Format Painter for Multiple Rows
This is a very quick and efficient method to format multiple adjacent rows. The steps to do so are described in the following section.
Steps:
- First of all, select the rows whose formatting you want to apply. In this example, we selected rows 4, 5, and 6.
- Then from the Home tab, go to,
Home → Clipboard → Format Painter
- Finally, click on cell B8 and the formatting will be copied to rows 8 to 10.
Read More: How to Use Format Painter Shortcut in Excel (5 Ways)
4. Use Double-Click to Format Non-Adjacent Rows
This method is useful if you need to copy formatting to non-adjacent cells. In the following example, we want to copy the formatting of the 4th row and apply it to the 8th and 10th rows. Keep reading to learn the steps to do it.
Steps:
- First, select the 4th row.
- Then navigate to the Home tab and double-click on Format Painter in the Clipboard section.
- Then click on rows 8 and 10.
- Hence, you will have the formatting of the 4th row in rows 8 and 10.
5. Apply Conditional Formatting to Use Format Painting for Multiple Rows
In this method, we will use Format Painter to apply conditional formatting to multiple rows in Excel. The cells with Quantity > 900 in this example have a light blue cell color and a bold font. We want to copy this conditional formatting to cells C9 to F10. The steps are discussed below.
Steps:
- First, select the conditionally formatted cells C5 to F6.
- Then from the Home tab, go to,
Home → Clipboard → Format Painter
- Finally, click on cell C9.
- Consequently, in rows 9 and 10, cells with a Quantity greater than 800 will have the desired conditional format.
- If you need to format non-adjacent cells, you can use the 4th method.
- Keep in mind that Format Painter only copies the format of a cell, not the content.
Read More: How to Use Format Painter in Excel for Multiple Sheets
Conclusion
Thanks for making it this far. I hope you find this article useful. Now you know five easy methods to use Format Painter for multiple rows in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.
