Working on spreadsheets all day? Take a look at these 15 copy and paste tricks to save time when 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 in 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. Copying 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 the results of the formula, the formula is automatically updated in relation to the cell in which you are pasting it.
You can prevent this from happening and copy only real values with a simple trick.
Select cells with the values you want to copy. Right-click on any of the cells and select Copy from the pop-up menu.
Right-click on the first cell in the range where you want to paste the values. Select the Values icon in the pop-up menu.
This will paste only the values (not the formulas) into the target cells.
This eliminates all the relative complexity of the formula that occurs when formula cells are normally copied and pasted in Excel.
2. Copy formulas without changing references
If you want to copy and paste formula cells but keep formulas, you can also do so. The only problem with the strict paste of formulas is that Excel will automatically update all the cells referred to in relation to the place where you are pasting them.
Highlight all the cells that contain the formulas you want to copy. Select the Start menu click on the Search icon and select in the Edit group and select Replace .
In the window Find and replace type = in the field Find what and # in the field Replace with . Select Replace all . Select Close .
This will convert all formulas to text with the # sign in front. Copy all these cells and paste them into the cells where you want to paste them.
Next, highlight all the cells in both columns. Hold down Shift and highlight all cells in a column. Then hold down the Control key and select all the cells in the pasted column.
With all cells still highlighted, repeat the search and replace the previous procedure. This time, type # in the field Find what and = in the field Replace with . Select Replace all . Select Close .
Once the copy and replacement is done, both ranges will contain exactly the same formulas, without the displaced references.
This procedure may seem like some additional steps, but it is the easiest method 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 of copying and pasting. If you select and paste those cells, you will see that the hidden cell appears in the range where you paste them.
If you only want to copy and paste the visible cells, select the cells. Then, in the Start menu select Search and select and then select Go to special in the drop-down menu.
In the Go to special window, enable Only visible cells . 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 : Paste the cells into a column where a second full row is hidden will hide the second visible cell that you have pasted.
4. Fill in the bottom with formula
If you have entered a formula in an upper cell next to a range of cells already filled in, there is an easy way to paste the same formula into the rest of the cells.
The way people do this is to click and keep control in the lower left of the first cell and drag the lower part of the range. This will complete all the cells and update the cell references in the formulas accordingly.
But if it has thousands of rows, dragging to the end can be difficult.
Instead, select the first cell, then press and hold the Shift key and hover over the lower right handle in the first cell until you see two parallel lines appear.
Double-click on this double-line controller to fill to the bottom of the column where there is data to the left.
This technique filling cells is quick and easy and saves a lot of time when it comes to very large spreadsheets.
5. Copy using Drag and Drop
Another time saving is to copy a group of cells by dragging and dropping them on the sheet. Many users do not realize that they 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.
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 will save you some keystrokes.
6. Copy from the cell above
Another quick trick to save keystrokes is the command Control + D . If you place the cursor under the cell you wish to copy, simply press Control + D and the top cell will be copied and pasted into the cell you have selected.
Control + Shift + & # 39; also performs the same action.
7. Copy from the left cell
If you wish to do the same but copying from the cell to the left, simply select the cell to the right and press Control + R .
This will copy the cell to the left and paste it into the cell to the right, with the press of a key!
8. Copy cell format
Sometimes, you may want to use the same format in other cells that you used in an original cell. However, you do not want to copy the content.
You can copy only the format of a cell by selecting the cell, then type Control + C to copy.
Select the cell or cells you want to format the original, right click and select the format icon.
This will paste only the original format, but not the content.
9. Copy the entire sheet
If you have ever wanted to work with a spreadsheet but did not want to spoil the original sheet, copying the sheet is the best approach.
Doing this is easy. Do not bother to right click and select Move or Copy . Save some keys by pressing the control key right-click on the sheet tab and drag it to the right or left.
You will see a small sheet icon appear with a + symbol. Release the mouse button and the sheet will be copied where you placed the mouse pointer.
10. Repeat fill
If you have a series of cells that you want to drag down a column and make those cells repeat, doing so is simple.
Simply highlight the cells you want to repeat. Hold down the Control key, right-click on the lower right corner of the lower cell and drag down the number of cells you want to repeat.
This will fill all the cells below those copied in a repetitive pattern.
11. Paste the entire column or blank row
Another trick to save keystrokes is to add blank columns or rows.
The typical method that users use to do this is to right-click on the row or column where they want a blank space and select Insert in the menu.
A faster way to do this is to highlight the cells that make up the row or column of data where you need the blank.
While holding down the Shift key, click the left button in the lower right corner of the selection and drag down (or to the right if you selected a column range).
Release the mouse key before releasing Shift .
This will insert blank spaces.
12. Paste multiple from a single cell
If you have a single data cell that you want to replicate in many cells, you can copy the individual cell and then paste it into as many cells as you want. 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 in as many cells as you want.
13. Copy column width
When copying and pasting a column of cells and want the destination to have the exact same width as the original, there is also a trick.
Simply copy the original cell column as you would normally use the Control-C keys. Right click on the first cell in the destination and press Control-V to paste.
Now, select the original cell column again and press Control-C . Right-click on 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 also match the original widths.
It may be easier to simply adjust the width of the column with the mouse, but if you are adjusting the width of several columns to the Once on a very large sheet, this trick will save you a lot of time.
14. Paste with calculation
Have you ever wanted to copy a number into a new cell but perform a calculation at the same time? Most people will copy the number into a new cell and then write a formula to perform the calculation.
You can save that additional step by performing the calculation during the paste process.
Starting with a sheet containing the numbers you want to perform a calculation, first select all the original cells and press Control + C to copy. Paste those cells into the destination column where you want to get the results.
Then, select the second range of cells you want to calculate and press Control + C to copy. Select the target range again, right-click and choose Paste Special .
In the Special Paste window, under Operation, select the operation you want to perform in the two numbers. Select OK and the results will appear in the target cells.
This is a quick and easy way to perform quick calculations on a spreadsheet without the need to use additional cells just to make quick calculations .
15. Transpose column by row
The most useful paste trick of all is the transposition of a column into a row. This is especially useful when you have a sheet with elements vertically along a column that you want to use as headings 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.
Change to a new sheet and select the first cell. Right-click and select the transpose icon in Paste options .
This strikes the original column on the new sheet as a row.
It is quick and easy and saves the trouble of having to copy and paste all individual cells.
Use the 15 tips and tricks above to save a lot of time the next time you work with your Excel spreadsheets.