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).
- 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 + → (to paste the format across a row), or CTRL + SHIFT + ↓ (for a column). This pastes the format up to the first empty cell.
- See Excel Paste Formats and Format Painter Shortcuts for more keyboard shortcuts.
- Use Format Painter in VBA, too.
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; 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 a cell or range (below, C1:E1).
- After this, the paintbrush is still active, and you can select another cell (F1).
- In this way, select all the cells you want 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).
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 Paint format icon.
(Google Sheets uses a paint roller, rather than Excel’s paintbrush, for the icon.)
- Now, either select cells or use the CTRL + SHIFT + → shortcut to paste the format.
Tip: Google Sheets doesn’t have Excel’s double-clicking functionality for its format painter, but you can select another cell and use CTRL + Y to redo the formatting.