In this chapter, we’re going to take a look at some of the Paste Special features in Excel. Let’s say we’ve got a table of data and we want to copy the table to another location. We’ll copy the table using the Copy button in the top-left of the menu, or by pressing CTRL + C on the keyboard as shown in Figure 11.1. Next, we select the new location and select Paste on the menu or press CTRL + V. It will paste everything that was in the table, including the formatting, all of the numbers, as well as the formulas.

However, let’s suppose that we don’t want to copy all of the formatting and the formulas. Instead, we can press the arrow under the Paste button and select Paste Special or press CTRL + ALT + V. It will open a Paste Special dialogue box with several options. (See Figure 11.2)

Additionally, we can paste the table and select the clipboard button on the bottom-right corner of the pasted data for quick paste options. (See Figure 11.3)

Paste All

The All selection will paste the copied data with all formulas, values, and other formatting intact.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select All and press OK to paste the data. (See Figure 11.4)

Paste Formulas

The Formulas selection will paste the copied data with all formulas intact, but values and other formatting will not be kept.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select Formulas and press OK to paste the data. (See Figure 11.5)

Paste Values

The Values selection will paste the copied data with all values intact, but formulas and other formatting will not be kept.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select Values and press OK to paste the data. (See Figure 11.6)

Paste Formats

The Formats selection will paste the copied data with all formatting intact, but formulas and values will not be kept.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select Formats and press OK to paste the data. (See Figure 11.7)

Paste Values and Number Formats

The Values and number formats selection will paste the copied data with all values and number formatting intact, but formulas will not be kept.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select Values and number formats and press OK to paste the data. (See Figure 11.8)

Paste Column Widths

The Column widths selection will paste the copied data with all column widths intact.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select Column widths and press OK to paste the data. (See Figure 11.9)

Paste Transpose

The Transpose selection will swap table data on the y and x-axis planes. Data displayed across the first row will be pasted vertically in the first column and so on.

1. Copy the desired data and select a location to paste.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select the Transpose box and press OK to paste the data.
1. Alternatively, paste the data and select the clipboard button in the bottom-right corner of the pasted content. Then select the Transpose button to adjust the data accordingly. (See Figure 11.10)

Paste Picture or Linked Picture

The Paste Picture button will adjust the pasted data as an image. The contents of the image won’t reflect changes to the table. However, the Paste Linked Picture button will paste an image that will reflect changes to the table.

1. Copy the desired data and select a location to paste.
2. Paste the data and press the clipboard button in the bottom-right corner of the pasted content.
3. Select the Paste Picture or Paste Linked Picture button and press OK. (See Figure 11.11)

   

    

Paste Operations

The Operations section of the Paste Special window can apply (paste) copied numerical data to selected cells. The method provides a quick way to add, subtract, multiply, or divide multiple cells with the copied numerical data.

1. Copy the desired data and select the relevant cells with existing numerical data.
2. Press the arrow under the Paste button in the top-right of the ribbon menu and select Paste Special.
3. Select the operation to apply to the selected cells and press OK.
1. For example, in Figure 11.12, the copied cell containing 2 in the Lead Time column can be added to the selected cells in the Order Qty column, thereby increasing each of their values by 2.

F4 Repeat

The F4 key on the keyboard has special functions in Excel. It can lock reference cells in a formula, and it can repeat the last formatting function applied in the spreadsheet. For example, if we apply a background color to one cell, we can repeat the action by selecting another cell and pressing F4. It also works for adding rows or columns and other repeatable functions.