21 ways to use the Paste feature in Excel

These tricks are very easy to do and you will definitely use them more than before once you know them.

In Excel 2007 and 2010, Paste is in the Clipboard group on the Home tab . In Excel 2003, Paste Special in the Edit menu . For those who use keyboard shortcuts, we have a table at the end of the article that includes keyboard shortcuts for common actions.

1. Copy the column width

When copying data to a new column, the length of the column will not automatically adjust to match the new value. With just one extra click, you can copy the original column width to the target column:

1. Select the data to copy and press [Ctrl] + C to copy the selected value to the Clipboard .
2. Select a cell in the target column.
3. On the Home tab , click the Paste option in the Clipboard group and select Keep Source Column Widths . This option requires an additional step when you use Excel 2007: Select Paste Special from the Paste drop-down menu. Next, click on the Column Widths option in the Paste section and click OK .

Excel will copy the data and the column's original width into the target cell.

21 ways to use the Paste feature in Excel Picture 1

2. Perform calculations

Users can use Paste Special to add, subtract, multiply and divide. Just enter the number and press [Ctrl] + C . Then, select the value you want to change with the number you just entered and click Paste Special in the Clipboard group. In the results window, click on the appropriate calculation and then click OK . Excel will perform the corresponding action using the value you just copied to the Clipboard.

Users can also perform calculations with multiple values. Use the same process as above, but copy a range of values ​​instead of just one as above. Excel will not pay attention to the size if it is not enough, it will only perform calculations one after another in the order of the copied values.

3. Paste with calculation

Have you ever wanted to copy a number into a new cell, and perform calculations on it at the same time? Most people will copy the number into a new cell and then enter the formula to perform the calculation.

You can eliminate that extra step by doing calculations during the gluing process.

Start with a table containing the numbers you want to perform the above calculation on. First select all the original cells and press Control+ Cto copy. Paste those cells into the destination column where you want to export the results.

21 ways to use the Paste feature in Excel Picture 2

Next, select the second cell range you want to perform the above calculation on and press Control+ Cto copy. Reselect the target range, right-click and select Paste Special .

21 ways to use the Paste feature in Excel Picture 3

In the Paste Special window , under Operation, select the operation you want to perform on the two numbers. Click OK and the results will appear in the target cells.

21 ways to use the Paste feature in Excel Picture 4

This is a quick and easy way to do quick calculations in a spreadsheet without using additional cells just to do quick calculations.

4. Swap columns to rows

The most useful pasting trick out of all the tips mentioned in this article is to swap a column into a row. This tip is especially useful when you have a worksheet with vertical (column) items and want to use it as a title in a new worksheet.

First, highlight and copy (using Control+ C) the column containing the cells you want to convert into a row in the new worksheet.

21 ways to use the Paste feature in Excel Picture 5

Switch to a new worksheet and select the first cell. Right click and select the swap icon in Paste Options .

21 ways to use the Paste feature in Excel Picture 6

This pastes the original column into the new table as a row.

21 ways to use the Paste feature in Excel Picture 7

This method is quick and easy and eliminates the hassle of having to copy and paste individual cells.

5. Copy format

Most of us use Format Painter to copy formatting from one cell to another drive or to a certain row. However, when using Format Painter, it is a bit inconvenient for entire columns or rows. Here's how to use Paste Special instead:

1. Select the cell containing the format you want to copy and press [Ctrl] + C .
2. Click inside the column or row you want to format. (Press [Ctrl] + Spacebar to select the entire column or [Shift] + Spacebar to select the entire row).
3. With the column or row selected, choose Formatting from the Paste drop-down menu in the Clipboard group. Excel 2007 requires an additional step: Select Paste Special from the Paste drop-down menu, click Formats in the Paste section , and click OK .

6. Copy the chart format

Users may have to spend a lot of effort formatting a chart to look good and then have to do all the same work again with another type of data. In this case, do not reformat the new chart but use Paste Special. This hypothesis is similar to method number 5, but the steps are a bit different:

1. Select the chart with the format you want to copy and press [Ctrl] + C .
2. Select the chart you want to format, then choose Paste Special from the Paste drop-down menu. Excel will display the Paste Special dialog box, with 3 options.
3. Select Formats and click OK .

Excel reacts differently when copying chart formats, but it can easily complete the task.

7. Ignore a series of cells with blank values

Using the Skip Blanks option , you can replace existing values ​​while still skipping cells that have no value in the original data. In other words, if there is a blank cell in the original data and a value in the range to paste, this option does not replace the existing value with a blank cell. You can use it with all the other options:

1. Select the original data range and press [Ctrl] + C .
2. Select the top left cell in the target range.
3. From the Paste drop-down menu, select Paste Special.
4. Check the Skip Blanks option and click OK .

Excel will not overwrite existing values ​​with blank cells.

21 ways to use the Paste feature in Excel Picture 8

8. Copy Data Validation

After taking the time to set up a Data Validation rule or list to improve data entry, you may want to apply the same rule to other cells or ranges. The good news is that you don't have to go through all the steps above again. Using Paste Special, you just need to copy it:

1. Select the cell containing the Data Validation rule and press [Ctrl] + C .
2. Select the target cell to paste.
3. From the Paste drop-down menu, select Paste Special .
4. Click on the Validation option and then click OK .

Setting up Data Validation can be tedious and time-consuming; Paste Special is way easier!

9. Delete text on the Web

When copying text from the Web, you may run into trouble because Excel retains the original format. Overall, this is not what you want. If you have enabled Excel's Show Paste Options feature (found in the Advanced panel of the Excel Options dialog box ), you will see the Clipboard icon immediately after pasting the text into the worksheet. Users can select Match Destination Formatting from the Clipboard drop-down menu to remove unnecessary formatting.

If you are using Excel 2010, you will definitely enjoy using this Paste Special solution:

1. Copy text from the Web.
2. In Excel, click where you want to insert the text.
3. Select Paste from the drop-down menu and select Match Destination Formatting .

Use this option to copy external data, not just Web text, into Excel.

10. Paste the reference

Users can reference a cell by quoting a cell address with an equals sign. For example, enter =A1 to return to the contents of cell A1. Usually, we use these references in larger expressions and formulas. Sometimes, we still refer to a cell to itself. Paste Link can help when referencing multiple cells:

1. Select the range to refer to and press [Ctrl] + C .
2. Select the target cell and then click Paste Link from the Paste drop-down menu.

Using Paste Link is faster than referencing manually.

Shortcuts for Paste actions:

21 ways to use the Paste feature in Excel Picture 9

11. Copy formula results

One of the most annoying things about copy and paste in Excel is when you try to copy and paste the results of formulas. This is because, when you paste the formula result, the formula automatically updates to the cell you pasted it into.

You can prevent this from happening and copy only the actual values ​​with a simple trick.

Select the cells whose values ​​you want to copy. Right-click any cell and select Copy from the pop-up menu.

21 ways to use the Paste feature in Excel Picture 10

Right-click the first cell in the range where you want to paste the values. Select the Values ​​icon from the pop-up menu.

21 ways to use the Paste feature in Excel Picture 11

This will paste only the values ​​(no formulas) into the target cells.

21 ways to use the Paste feature in Excel Picture 12

This eliminates all the complexity that occurs when you copy and paste formula cells in Excel.

12. Copy the formula without changing the reference

If you want to copy and paste cells, while keeping the formulas in them, you can do this too. The only problem with pasting formulas this way is that Excel automatically updates all referenced cells where you paste them.

You can paste formula cells but keep the cells originally referenced in those formulas intact by following the tips below.

Highlight all the cells that contain the formulas you want to copy. Select the Home menu, click the Find & Select icon in the Editing group , and select Replace.

21 ways to use the Paste feature in Excel Picture 13

In the Find and Replace window , enter = in the Find what field and # in the Replace with field. Select Replace All, then click Close.

This will convert all formulas to text with a # sign in front. Copy all these cells and paste them where you want.

21 ways to use the Paste feature in Excel Picture 14

Next, highlight all the cells in both columns. Hold down the key Shiftand highlight all the cells in a column. Then hold down the key Ctrland select all the cells in the pasted column.

21 ways to use the Paste feature in Excel Picture 15

With all cells still highlighted, repeat the search and replace process above. This time, enter # in the Find what field and = in the Replace with field . Select Replace All and then click Close.

21 ways to use the Paste feature in Excel Picture 16

When copy and replace is performed, both ranges will contain the same formulas without shifting references.

21 ways to use the Paste feature in Excel Picture 17

This procedure may seem like redundant operations, but it is the simplest method for overwriting updated references in copied formulas.

13. Avoid copying hidden cells

Another common annoyance when copying and pasting in Excel is hidden cells that get in the way as you perform the operation. If you select and paste those cells, these hidden cells will appear in the area where you pasted them.

If you only want to copy and paste the displayed cells, select these cells. Then in the Home menu, select Find & Select , then click Go To Special from the drop-down menu.

21 ways to use the Paste feature in Excel Picture 18

In the Go To Special window , enable the Visible cells only boxes , then click OK.

Now press Control+ Cto copy the cells. Click the first cell where you want to paste and press Control+ V.

21 ways to use the Paste feature in Excel Picture 19

This will paste only visible cells.

Note : Pasting cells into a column where the entire second row is hidden will actually hide the second visible cell you pasted.

14. Paste the same formula into the remaining cells in the range

If you've entered a formula in the top cell next to a series of already filled cells, there's an easy way to paste the same formula into the remaining cells.

The typical way people do it is to click and hold the handle to the bottom left of the first cell and drag it down to the bottom of the range. This will fill all the cells and update the cell references in the corresponding formulas.

But if you have thousands of lines, scrolling down can be a bit difficult.

Instead, select the first cell, then hold down the key Shiftand move the lower right handle over the first cell until you see two parallel lines appear.

21 ways to use the Paste feature in Excel Picture 20

Double click on the handle consisting of two parallel lines to fill to the bottom of the column where the data is on the left.

21 ways to use the Paste feature in Excel Picture 21

This technique makes filling down cells quick and easy and saves a lot of time when dealing with large spreadsheets.

15. Copy using drag and drop

Another time-saving method is to copy a group of cells by dragging and dropping them on the worksheet. Many users don't realize that you can move cells or ranges simply by clicking and dragging.

Try this by highlighting a group of cells. Then move the mouse pointer over the edge of the selected cells until it changes to a cross.

21 ways to use the Paste feature in Excel Picture 22

Left click and hold the mouse button to drag the cells to a new location.

21 ways to use the Paste feature in Excel Picture 23

This technique performs the same action as using Control+ Cand Control+ Vto cut and paste cells, but will help you eliminate a few keystrokes.

16. Copy from the box above

Another quick tip to reduce the need to use key combinations is the Control+ command D. If you place the cursor below the cell you want to copy, just press Control+ Dand the cell above will be copied and pasted into the selected cell.

21 ways to use the Paste feature in Excel Picture 24

Control+ Shift+ 'also performs the same action.

17. Copy from the cell on the left

If you want to do the same thing but from the left cell instead, just select the right cell and press Control+ R.

21 ways to use the Paste feature in Excel Picture 25

This will copy the cell on the left and paste it into the cell on the right, with just one keystroke!

18. Copy the entire worksheet

If you want to work with a spreadsheet but don't want to damage the original sheet, copying the sheet is the best approach.

Doing this is quite easy. Don't constantly right-click and select Move or Copy. Save a few keystrokes by holding down the key Control, left-clicking the sheet tab and dragging it to the right or left.

21 ways to use the Paste feature in Excel Picture 26

You'll see a small sheet icon appear with a + sign . Release the mouse and the sheet will be copied to where you placed the mouse pointer.

19. Repeat the values ​​filled in from the cells above

If you have a series of cells that you want to drag down in a column and repeat those values, it's very simple to do.

Just highlight the cells you want to repeat. Holding down the key Control, left-click the lower right corner of the bottom cell and drag down the number of cells you want to repeat.

21 ways to use the Paste feature in Excel Picture 27

This will fill all the cells below the copied cells in a repeating fashion with the values ​​above.

20. Paste an entire blank column or row

Another trick to reduce the use of keystrokes is to add blank columns or rows.

The typical method users use to do this is to right-click on the row or column where they want it to be blank and select Insert from the menu.

A quicker way to do this is to highlight the cells that make up rows or columns of data that you need to leave blank.

Holding down the key Shift, left-click the lower right corner of the selection and drag down (or right if you select a range of columns).

Release the mouse before you release the key Shift.

21 ways to use the Paste feature in Excel Picture 28

This will help you insert additional spaces.

21. Paste a single cell multiple times

If there is a cell of data that you want to copy to multiple cells, you can copy this cell, and then paste it into as many cells as you like. Select the cell you want to copy and press Control+ Cto copy.

Then select any cell ranges you want to copy data from.

21 ways to use the Paste feature in Excel Picture 29

This will copy that cell to as many cells as you want.

Using all 21 tips and tricks above will save you a lot of time when working with Excel spreadsheets.

Good luck!

David Pac

Update 06 June 2024