Use Format Painter for Multiple Cells in Excel & Google Sheets
This tutorial demonstrates how to use the format painter on multiple cells in Excel and Google Sheets.
Use Format Painter
The format painter in Excel allows you to copy all formatting (font size and colors, alignments, cell borders and backgrounds, etc.) from one cell to another, or a range of cells. Say you want to paste the format from cell B1 to cells C1:E1.
- Select the cell with the format you want to copy (B1) and in the Ribbon, go to Home > Format Painter.
- The cursor changes to a cross with a brush. To paste a format, just select cells you want to format (C1:E1).
As a result, selected cells are formatted the same as the source cell (B1).
Note: You can also use the format painter to format all cells in the same row or column. Instead of selecting the target cells, press CTRL + SHIFT + Right Arrow (to paste the format across a row), or CTRL + SHIFT + Down Arrow (for a column). This pastes the format up to to the first empty cell.
Double-Click: Multiple Cells
The method above only lets you format one cell (or range) at a time. Once you click your target cell(s), the format painter brush disappears, and you have to activate it again to paint another cell. To keep the paintbrush active and click on cells or ranges as many times as you need, follow these steps:
- Select the cell with the format you want to copy (B1) and in the Ribbon, go to the Home tab and double-click the Format Painter icon.
- First, select one cell or range of cells (below, C1:E1).
- After this, the paintbrush is still active, and you can select another cell (F1).
- In this way, you can select all the cells you need to format. When you’re finished, press ESC on your keyboard to turn off the format painter. As a result, all selected cells now have the same format as the initial cell (B1).
See also…
- VBA – Format Painter
- Excel Paste Formats and Format Painter Shortcuts
Use Format Painter in Google Sheets
Google Sheets works similarly.
- Select the cell with the format you want to copy (B1), and in the Menu, click the Format Painter icon.
(Google Sheets uses a paint roller, rather than Excel’s paintbrush, for the Format Painter icon.)
- Now, either select cells – or use the CTRL + SHIFT + Right Arrow shortcut – to paste the format.
Note: Google Sheets doesn’t have the same double-clicking functionality as Excel, but you can select another cell and use CTRL + Y to redo the formatting.