How to Use the Format Painter in Excel 2016

By Greg Harvey

For those occasions when you feel the urge to format on the fly (so to speak) in Excel 2016, use the Format Painter button (the paintbrush icon) in the Clipboard group on the Home tab. This wonderful little tool enables you to take the formatting from a particular cell that you fancy up and apply its formatting to other cells in the worksheet simply by selecting those cells.

To use the Format Painter to copy a cell’s formatting to other worksheet cells, just follow these easy steps:

  1. Format an example cell or cell range in your workbook, selecting whatever fonts, alignment, borders, patterns, and color you want it to have.

  2. Select one of the cells you just fancied up, and click the Format Painter button in the Clipboard group on the Home tab.

    The mouse pointer changes from the standard thick, white cross to a thick, white cross with an animated paintbrush by its side, and you see a marquee around the selected cell with the formatting to be used by the Format Painter.

  3. Drag the white-cross-plus-animated-paintbrush pointer (the Format Painter pointer) through all the cells you want to format.

    As soon as you release the mouse button, Excel applies all the formatting used in the example cell to all the cells you just selected!

To keep the Format Painter selected so that you can format a bunch of different cell ranges with the Format Painter pointer, double-click the Format Painter button on the Home tab after you select the sample cell with the desired formatting. To stop formatting cells with the Format Painter pointer, you simply click the Format Painter button on the Home tab again (it remains selected when you double-click it) to restore the button to its unselected state and return the mouse pointer to its normal thick, white cross shape.

You can use the Format Painter to restore a cell range that you gussied all up back to its boring default (General) cell format. To do this, click an empty, previously unformatted cell in the worksheet before you click the Format Painter button and then use the Format Painter pointer to drag through the cells you want returned to the default General format.

Using the Format Painter on a touchscreen has a very limited application: You can only copy the formatting from the one cell to another by first tapping the formatted cell and then tapping the cell to be formatting. Unfortunately, you can’t format an entire cell range by dragging your finger or stylus as you can with the mouse.