Which of the following keys do you press to copy cells while dragging them?

This video shows how to copy new data, where some cells are blank, and paste over existing data, without pasting the blank cells. Use the Paste Special command, and select Skip Blanks, to avoid overwriting existing data with blanks.

1. Copy the cells with the new data
2. Select the cells where you want to paste
3. On the Ribbon, click the arrow below the Paste button, and click Paste Special
4. In the Paste Special window, add a check mark in Skip Blanks, then click OK

You can get the sample file, to follow along with the video.

Copy and Paste Numbers Only

If a range of cells contains numbers, blank cells and text, you can use the Go To Special command to select just the cells with numbers. Then, copy those cells, and paste in another location on the worksheet.

Watch this short video, to see the steps. To follow along with the video, get the Copy and Paste Numbers sample file. The file is in xlsx format, and does not contain macros.

How to Copy and Paste Numbers Only

To select just the numbers in a column with formulas:

• Select the entire column
• On the Excel Ribbon's Home tab, click Find and Select
• Click Go To Special
• In the Go To Special window, select Formulas
• Remove the check marks from Text, Logicals and Errors, then click OK

NOTE: This technique can be used for numbers that were typed into the cells, or for numbers that are the result of a formula.

Copy and Paste Multiple Selections

You can run into problems if you copy multiple ranges, and try to paste them to a new location. You might see an error, "This action won't work on multiple selections", or values might be pasted, instead of formulas.

Watch this short video, to see how to avoid problems when copying and pasting multiple selections. To follow along with the video, get the sample file for copy and paste problems.

Avoid Copy Error for Multiple Selections

If you select more than one group of cells on a worksheet, and try to copy them, Excel might show an error message, "This action won't work on multiple selections".

To avoid that error, be sure to select multiple regions in either:

• the exact same columns
• OR, the exact same rows

For example, Excel will show an error message if you select cells in columns A:C, and make another selection in different columns, A:B

Be sure that each selection includes exactly the same rows or columns, like the next example, with both selections in columns A:C.

Avoid Paste Problem for Multiple Selections

If you copy more than one group of cells on a worksheet, and paste them in a different location, Excel might change your formulas to values.

Working in spreadsheets all day? Check out these 15 copy & paste tricks to save time when you’re copying and pasting cells in Microsoft Excel.

Excel is one of the most intuitive spreadsheet applications to use. But when it comes to copying and pasting data throughout different parts of the spreadsheet, most users don’t realize how many possibilities there are.

Whether you want to copy and paste individual cells, rows or columns, or entire sheets, the following 15 tricks will help you do it faster and more efficiently.

1. Copy Formula Results

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

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

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

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

This will paste only the values (not the formulas) into the destination cells.

This removes all of the relative formula complexity when you normally copy and paste formula cells in Excel.

2. Copy Formulas Without Changing References

If you want to copy and paste formula cells but keep the formulas, you can do this as well. The only problem with strictly pasting formulas is that Excel will automatically update all the referenced cells relative to where you’re pasting them.

You can paste the formula cells but keep the original referenced cells in those formulas by following the trick below.

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

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

This will convert all formulas to text with the # sign at the front. Copy all of these cells and paste them into the cells where you want to paste them.

Next, highlight all of the cells in both columns. Hold down the Shift key and highlight all cells in one column. Then hold down the Control key and select all of the cells in the pasted column.

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

Once the copy and replace are done, both ranges will contain the same formulas without shifting references.

This procedure may seem like a few extra steps, but it’s the easiest to override the updated references in copied formulas.

3. Avoid Copying Hidden Cells

Another common annoyance when copying and pasting in Excel is when hidden cells get in the way when you copy and paste. If you select and paste those cells, you’ll see the hidden cell appear in the range where you paste them.

If you only want to copy and paste the visible cells, select the cells. Then in the Home menu, select Find & Select, and then select Go To Special from the dropdown menu.

In the Go To Special window, enable Visible cells only. Select OK.

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

This will paste only the visible cells.

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

4. Fill to Bottom with Formula

If you’ve entered a formula into a top cell next to a range of cells already filled out, there’s an easy way to paste the same formula into the rest of the cells.

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

But if you have thousands of rows, dragging all the way to the bottom can be difficult.

Instead, select the first cell, then hold down the Shift key and hover the house over the lower-right handle on the first cell until you see two parallel lines appear.

Double click on this double-line handle to fill to the bottom of the column with data to the left.

This technique to fill cells down is fast and easy and saves a lot of time when you’re dealing with extensive spreadsheets.

5. Copy Using Drag and Drop

Another neat time-saver is copying a group of cells by dragging and dropping them across the sheet. Many users don’t realize that you can move cells or ranges just by clicking and dragging.

Give this a try by highlighting a group of cells. Then hover the mouse pointer over the edge of the selected cells until it changes to a crosshair.

Left-click and hold the mouse to drag the cells to their new location.

This technique performs the same action as using Control-C and Control-V to cut and paste cells. It’ll save you a few keystrokes.

6. Copy from Cell Above

Another quick trick to save keystrokes is the Control+D command. If you place the cursor under the cell, you want to copy, press Control+D, and the cell above will get copied and pasted into the cell you’ve selected.

Control+Shift+’ performs the same action as well.

7. Copy from Left Cell

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

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

8. Copy Cell Formatting

Sometimes, you may want to use the same formatting in other cells that you’ve used in an original cell. However, you don’t want to copy the contents.

You can copy just the formatting of a cell by selecting the cell, then type Control+C to copy.

Select the cells you want to format like the original, right-click, and select the formatting icon.

This will paste only the original formatting but not the content.

9. Copy Entire Sheet

If you’ve ever wanted to work with a spreadsheet but didn’t want to mess up the original sheet, copying the sheet is the best approach.

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

You’ll see a small sheet icon appear with a + symbol. Release the mouse button, and the sheet will copy where you’ve placed the mouse pointer.

10. Repeat Fill

If you have a series of cells that you want to drag down a column and have those cells repeat, doing so is simple.

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

This will fill all of the cells below the copied ones in a repeating pattern.

11. Paste Entire Blank Column or Row

Another trick to save keystrokes is adding blank columns or rows.

The typical method users use to do this is right-clicking the row or column where they want a blank and selecting Insert from the menu.

A faster way is to highlight the cells that make up the row or column of data where you need the blank.

Holding down the Shift key, left-click the bottom right corner of the selection, and drag down (or to the right if you selected a column range).

Release the mouse key before you release Shift.

This will insert blanks.

12. Paste Multiples of a Single Cell

If you have a single cell of data that you want to replicate over many cells, you can copy the individual cell and paste it over as many cells as you like. Select the cell you want to copy and press Control+C to copy.

Then choose any range of cells where you want to copy the data.

This will replicate that cell across as many cells as you like.

13. Copy Column Width

When copying and pasting a column of cells and you want the destination to be the same width as the original, there’s a trick to that as well.

Just copy the original column of cells as you normally would using the Control-C keys. Right-click the first cell in the destination and press Control-V to paste.

Now, select the original column of cells again and press Control-C. Right-click the first cell in the column you previously pasted and choose Paste Special.

In the Paste Special window, enable Column widths and select OK.

This will automatically adjust the column width to match the original widths as well.

It might be easier to adjust the column width with your mouse, but if you’re adjusting the width of multiple columns at once on a huge sheet, this trick will save you a lot of time.

14. Paste with Calculation

Have you ever wanted to copy a number to a new cell but perform a calculation on it simultaneously? Most people will copy the number to a new cell and then type a formula to perform the calculation.

You can save that extra step by performing the calculation during the paste process.

Starting with a sheet containing the numbers you want to calculate, first select all of the original cells and press Control+C to copy. Paste those cells into the destination column where you want the results.

Next, select the second range of cells you want to calculate and press Control+C to copy. Select the destination range again, right-click, and choose Paste Special.

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

This is a fast and easy way to perform quick calculations in a spreadsheet without using extra cells to do quick calculations.

15. Transpose Column to Row

The most useful paste trick of all is transposing a column into a row. This is especially useful when you have one sheet with items vertically along a column that you want to use as headers in a new sheet.

First, highlight and copy (using Control+C) the column of cells you want to transpose as a row in your new sheet.

Switch to a new sheet and select the first cell. Right-click and select the transpose icon under Paste Options.

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

It’s fast and easy and saves the trouble of copying and pasting all of the individual cells.

Use all of the 15 tips and tricks above to save yourself a lot of time the next time you’re working with your Excel worksheets.

What is the shortcut for drag and copy in Excel?

What is the shortcut key to copy a cell?

When you copy a selection of cells using drag and drop?