Using the Paste Options Button to Copy and Paste Formatting in Excel 2013

Page content

You are probably already familiar with Excel’s copy and paste commands to copy data or formulas to different cells. By default, however, Excel 2013 copies everything, including formulas, values and formatting. In most cases, that is exactly what you want, but other times it can cause more problems than it’s worth.

Say you had spent considerable time formatting a particular table. You decide to save time by copying that formatting to a new table. The problem is that the new table already contains important data. If you use the default copy method, the formatting would be copied, but so would the data. That means the data in the new table would be overwritten and need to be reentered.

Excel 2013, however, goes well beyond the default copy behavior by enabling you to choose what is actually copied. In this case, opting to only copy formats keeps the new table data and replaces only its formatting with that of the first table.

1. Select the cells whose formatting you want copied, right-click anywhere in the selection and choose Copy. Alternatively, select the cells and press Ctrl-C.

2. Right-click the upper left cell of the range to which you want the formatting copied. Select Paste Special.

3. Select Formats and click OK.

4. Notice that the formats all copied, including borders, number formats, row colors and font effects, without changing any of the values.

Note that this method does not create a formatting link between the source and target ranges. That is, if you change the formatting in the first table, the second table will not be updated with the new formats. However, you can always copy the formatting again to save time.