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. Comment
Δ
