home » mobile connection » How to copy only the contents of cells. How to copy a cell in excel? Functionally copying contiguous cell ranges

How to copy only the contents of cells. How to copy a cell in excel? Functionally copying contiguous cell ranges

Hello dear readers! In this article, I will explain how to copy and cut cells in Excel. On the one hand, you will find out as much information as I consider necessary. Daily. On the other hand, it will provide a foundation for learning more advanced copy and paste techniques. Therefore, if you want to use Excel to its fullest, read this post and the next one to the end!

First, we will deal with the principles of copying and transferring information, and then we will go deeper into practice.

And so, to copy one or more cells - and perform the copy operation (for example, by pressing Ctrl + C). The copied range will be highlighted with a "creeping" frame, and the data from it will be moved to the Windows and Office clipboards. Place the cursor in the cell for insertion and perform the "Insert" operation (for example, press Ctrl + V). The information from the clipboard will be placed in a new location. When inserting an array - select the cell in which its upper left cell will be located... If there is already data in the cells for insertion, Excel will replace it with new ones without additional notifications.

If you copy, the original data is retained, and if you move, it is deleted. Now let's look at all the methods of copying and transferring that Excel offers us.

Copy using hotkeys

This method is the simplest and most familiar, probably for everyone. The copy and paste keys are the same as those used in Windows applications:

Ctrl + C - copy the selected range
Ctrl + X - cut selected range
Ctrl + V - paste without deleting from the clipboard
Enter - paste and delete from clipboard

For example, if you need to copy the A1: A20 array into cells C1: C20, select it and press Ctrl + C (while moving - Ctrl + X). Place the cursor in cell C1 and press Ctrl + V. The information will be pasted and remain on the clipboard, you can re-paste it elsewhere. If you press Enter instead of Ctrl + V, the data will also be inserted, but will disappear from the clipboard, the "running" selection will disappear.

Copy using the context menu

Copy, cut and paste commands are also available in the context menu of the Excel worksheet. To copy a range, select it and right-click inside the selection. Select Copy or Cut from the context menu. Similarly, to paste the copied information, in the cell to paste, call the context menu and select Paste (or move the cursor there and press Enter).

Copy commands in the Excel context menu

Copy using ribbon commands

You can do the same using the ribbon commands:

Copy: Home - Clipboard - Copy
Cutting out: Home - Clipboard - Cut
Insert: Home - Clipboard - Paste

The last command from the above is combined, it has additional insert options (see the figure above) insert only formulas:

Paste - insert a cell completely (values, formulas, cell and text formats, check conditions)
Formulas - insert only or values
Formulas and number formats - numbers, values \u200b\u200bwith as in the source
Keep original formatting - insert values, formulas, cell and text formats
No frames - all values \u200b\u200band formats, except frames
Maintain column widths of original - insert values, formulas, formats as in the original
Transpose - when inserting, rotate the table so that the rows become columns, and the columns become rows
The values - insert only values \u200b\u200bor results of formula calculations
Values \u200b\u200band number formats - formulas are replaced with the results of their calculations in the original number format
Values \u200b\u200band original formatting formulas are replaced with the results of their calculations in the original format of numbers and cells
Formatting - only original format, no data
Insert Link - inserts a formula referring to the copied cell
Picture - inserts the selected range as an "Image" object
Related drawing - Inserts an array like an image. When the source cell changes, the image also changes.

All listed commands are tools Paste special.

Copy by drag and drop to Excel

This is the fastest and least flexible method. Select the array to copy and hover over one of its boundaries. The cursor changes to a four-way arrow. Grab the mouse and drag the cells where you want to move them.

To copy an array, hold down Ctrl while dragging. The cursor will change from four-way to an arrow with a "+" sign.

Copy autocomplete

I have already described how autocomplete works in the post. Here I will just remind and add a little. If you need to copy data or formulas into adjacent cells - select the cell to copy, find a small square (auto-fill marker) in the lower right corner of the cell. Drag it to fill in adjacent cells with similar formulas or to copy information.

Autocomplete marker

There is another way - the Fill command. Select the fill array so that the copy cell is first in the fill direction. Execute one of the commands, depending on the direction of filling:

Home - Editing - Fill Down
Home - Editing - Fill Right
Home - Editing - Fill Up
Home - Editing - Fill Left

All selected cells will be filled with data or formulas from the original.

So I have listed the basic methods of copying and pasting. As I promised, next we will look at copy and paste that newbies are not aware of. Read on, they are easy to use and have a lot of benefits.
Did you like the article? Recommend to a friend and subscribe to updates with him! A lot of interesting and useful material has already been written, but the best posts are yet to come!

To automatically copy and paste a cell value in the current sheet or from one sheet to another without using keyboard shortcuts, this article can help you.

Automatically copy and paste a cell in the current sheet or from one sheet to another using a formula

You can use a formula to automatically copy and paste a cell. Please do the following.

1. To copy and paste a cell in the current sheet, such as a copy of A1 to D5, you can simply select the destination cell D5, then type \u003d A1 and press Enter to get the A1 value.

2. To copy and paste a cell from one sheet to another, such as copy cell A1 from Sheet1 to D5 Sheet2, select cell D5 in Sheet2, then enter \u003d Sheet1! A1 and press Enter to get the value.

Advice: The target cell is linked to the copied cell, and it will change as the source cell changes.

Auto copy and paste a cell into the current sheet using VBA

The following VBA code helps you copy a cell or a range of cells and paste into the specified cells on the current worksheet.

1. Click other + F11 to open windows.

2.in Microsoft Visual Basic for Applications windows, click Paste > moduleThen copy and paste the following VBA code into the module window.

VBA code: Auto copy and paste a cell in the current sheet

Sub RangeTest () On Error Resume Next Dim xRg As Range Set xRg \u003d Application.Selection Range ("A1") .Copy Range ("D5") xRg.Select End Sub

Attention: In the code, A1 is the cell you will copy and D5 is the destination cell to place the value of cell A1. To automatically copy and paste a range of cells, replace the cell with the range, for example, replace A1 with A1: A10 and replace D5 with D1: D10.

3.click F5 key to run the code, then the specific cell (range) will be copied and pasted into the specified cell (range) automatically, as well as the cell formatting.

Copy and paste multiple ranges in the current sheet or from one sheet to another with Kutools for Excel

If you are copying multiple ranges at the same time, you can try Copy ranges usefulness Kutools for Excel.

1. Select multiple ranges to be copied by holding Ctrl key. Then press Kutools > Copy rangesSee screenshot:

2.in the dialog box, you can see the selected ranges are listed in Ranges to copy box. Then put the option in Paste special section, check Including line height or Including column width as needed, and finally press OK button. See screenshot:

3. Per second Copy multiple ranges dialog box, specify a blank cell to insert the selected ranges, and then click OK Button.

Attention: If you want to paste the selected ranges into another sheet, select a cell on that sheet and click OK in the second Copy Multiple Ranges dialog box.

The selected ranges are now copied and pasted to the specified location.

Copy and paste multiple ranges in the current sheet or from one sheet to another

Kutools for Excel includes more convenient Excel 300 tools. Free, unlimited, 60 days. Download your free trial now!

How to auto fill in other cells when selecting values \u200b\u200bin Excel dropdown list?
How to autocomplete as you type in Excel dropdown list?
How to auto-fill date in a cell when adjacent cell is refreshed in Excel?

Recommended productivity tools in Excel

Kutools for Excel helps you always finish ahead of time and stand out from the crowd

More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of your family and enjoy your life.
No more memorizing VBA formulas and codes, give your brain a rest.
Become an Excel expert in 3 minutes. Complex and repetitive operations can be done in seconds,
Cut thousands of keyboard and mouse operations every day, say goodbye to occupational diseases now.
110,000 high performing people and a selection of 300+ world renowned companies.
60-Day fully functional free trial. 60-day money back guarantee. 2 years of free updates and support.

Provides tabbed browsing and editing in Microsoft Office, much more powerful than browser tabs

Office Tab is for Word, Excel, PowerPoint, and other Office applications: Publisher, Access, Visio, and Project.
Open and create multiple documents in new tabs of the same window, not in new windows.
Increases your productivity by 50% and reduces hundreds of mouse clicks for you every day!

In Excel, all the time you have to carry out various manipulations with tables, and then we will look at how to copy a table in Excel, and how to insert a table into Excel. These operations are actually very simple, but when using them, it is necessary to take into account a number of subtleties when working in this program.

To copy a table to Excel, it is naturally necessary to first select it. The fastest way to copy is to use the keyboard shortcut "Ctrl + C" or use the corresponding menu item "Copy" in the tab "Home".

Next to a menu item "Copy" there is an arrow, by clicking on which an additional opportunity appears "Copy as Picture ..."... When this option is selected, the table we have selected will be copied as a picture, but a dialog box with additional copy settings will be shown.

The table copied in this way in Excel will be stored in the clipboard as a picture.

Let's assume that we managed to copy the table, now it's time to figure out how to insert a table into Excel. Here, too, everything can be very simple, since you can use the key combination "Ctrl + V"... But when inserting a table into Excel, as well as when inserting any other data, you should take into account the fact that the inserted data will, starting from the selected cell on the sheet, take up the necessary space, replacing all the information placed in the cells necessary for this table. Data is inserted starting from the top left cell. And you should also remember that if, after copying a table or other data, any manipulation on the sheet is performed, except for moving the cursor, then the data from the clipboard will be deleted. This means that you need to insert the table into Excel immediately after copying it.

You can also use the menu item to insert "Paste" in the tab "Home".

Consider here, how to copy to Excelformulas, cells how to copy a sheet to Excel, spreadsheet, value, format , etc.One of the many table features that make your work easier iscopy to Excel.
You can copy the entire table or only the value of cells without formulas, or copy formulas with binding to new cells or without binding to a cell (with changing the cell address or leaving the address unchanged)It is possible to copy data with a specific sequence and thus fill the table.You can copy the sheet ... And many many others.
To call the "Copy" function, just right-click on the selected cell, range and select the "Copy" function from the context menu.
Or, you can press the keyboard shortcut "Ctrl" + "C".
Or, on the "Home" tab in the "Clipboard" section, press the "Copy" function button, having previously selected the cells for copying.
How to copy cell value in Excel(no formula).
Select the cells to be copied. Click "Copy". We move to a new place, make the active cell where we will copy, right-click and select the "Paste" function.
When the values \u200b\u200bof cells containing formulas (numbers) are not copied, do this: select the cells as usual-\u003e "Copy". A pulsating frame will appear around the cells that we are copying, once again right-click on the selected cell and in the dialog boxselect the command "Paste Special", choose - "Value".
Then, as usual - "Insert". Using the "Paste Special" function, you can not only copy data, but also add, subtract, multiply, etc. For more details, see the article "Summation in Excel".
If we copy numbers with dots - separators instead of commas (1.23), then inserting these numbers will turn into a date. There are several ways to avoid this. See "Excel AutoCorrect".
How to copy a formula in Excel.
First option.
When we copy cells with relative formulas ("Copy" -\u003e "Paste") and then transfer to another place, then the Excel table itself changes the addresses of the cells in the formulas relative to the new cells.See the article "Relative and Absolute References in Excel" for more details.
Second variant.
Can copy formulas by draggingto avoid writing the same formula in other cells of the table.Thus, we fill the table with formulas. For example, copy the formulas down a column.To do this, set (write) the formula in the first cell. Then we click on the cell with the formula with the left mouse and move the mouse over the lower right corner of this cell, a black cross will appear there. Click on it with the left mouse and drag it down, keeping the left mouse button pressed.
We release the mouse. The formulas were copied, the addresses of the cells were changed in them relative to the new location. The table is filled with formulas.
To copy a formula to Excel in such a way that the format is not copied, see the article "How to copy to Excel".
Third option.
How to copy formulas in Excel to entire column.
We need to copy the formula from cell F24. Hover over the square in the lower right corner of the cell. The mouse pointer changes to a black cross. Click twice in a row with the left mouse on this cross. The formula will be copied to the end of the filled column. In our example, in cells F25: F26.
Happened.
Attention!
The formula will be copied in the table if the cells in the column to the left of the table are filled. Then Excel will understand to which cell the table is located.
In this way, you can copy the value, formulas, format. If we select cells in several columns, then copying will be done in all selected columns at once. For example, if we select cells D24, E24, F24, double-click on the corner of cell F24, then the data for all these columns will be copied.
Copy to Excel via cell.
You can copy not only filled cells, but also empty, filled cells interspersed with empty ones. For example, we have such a table, we need to put the dates in column A through one blank cell.
Select the first date cell and the empty cell below.
Drag the green square in the lower right corner of the selected range of cells down the column. the dates are copied like this. If we select three blank cells with a date, then the dates are copied through three blank cells. It will turn out like this.
If you need to transfer cells with formulas, but addresses in Excel formulas should remain the same (do not bind to new cells), then this is again in the article " Relative and absolute references in Excel formulas". Link to the article in the article above.
If needed not copy, but move the cell with the formula, then do not copy it, but through the context menu "Cut" -\u003e "Paste". The formula will be removed from the first cell and transferred to a new location.
More options for copying formulas so that the links in them do not change are described in the article "How to copy a formula in Excel without changing links".
How to fill a table in Excel.
You can copy cell data in sequence.For example, you need fill in the table column with the names of the months of the year. We write in the first cell "January", in the next cell below we write "February". Now select both of these cells. Hover the mouse over the lower right corner of the selected range and drag a black cross down the column. The table itself will fill in the cells for the next months of the year.
On the right, a hint as to which month the table was filled in appears. We release the mouse. That's it, the table is full.
You can also fill in the days of the month, year, etc.In the same way you can fill in and numbers, both in order and with an interval... For example: 5,10, 15, etc. Let's see an example: interval 2.
If needed write the same text in several Excel cells in a row, then write the text in the first cell, then drag it down (with a black cross).
You can copy by dragging either column by column or by row.
For a few more ways on how to write the same text, formulas in multiple cells, etc., see the article "How to fill cells in Excel at the same time".
How to copy a sheet to Excel - Right click on the sheet label.In the dialog box, select "Move / Copy".
Put a tick next to the words "Create a copy".Choose where to move the copied sheet (to this or another book, in front of which sheet) or leave it that way and click "OK".Then, for the copied sheet, you can change the name, move it to another place, change the color of the tab.
How to copy a sheet to a new Excel workbook.
To do this, select "(new book)" in the "Move selected sheets to book" section of the dialog box.
How to copy format to Excel cells or conditional formatting, without the cell values \u200b\u200bthemselves, see the article "Copy conditional formatting, format to Excel".
Sometimes, when you need to print a document, it is not the values \u200b\u200bof the cells that are printed (copied), but the formulas. To have meaning (numbers, not formulas) in cells, see Print in Excel.
You can use the "Search" function to quickly find the desired cell or range in a large Excel table. Looksee the article "Search in Excel".

In Excel, you can quickly copy a formula to many cells in one row or one column, but the result will not always be as expected. If you do not achieve the desired result or #REF and / DIV0 messages appear in the cells, read what the absolute and relative cell addresses are to find out the cause of the error. Remember that you do not have to make changes in all cells of the table, which consists of 5000 rows - for this there are methods that allow you to automatically update the formula in a specific cell or copy it without changing the values.

Steps

Copying a formula using drag and drop

Enter the formula in one cell. First enter an equal sign (\u003d), and then enter the desired function or mathematical operation. For example, you need to add the values \u200b\u200bof columns A and B:

Press the Enter key to calculate the result using the entered formula. The cell where you entered the formula will display the result (in our example, the calculated sum is 19), but the formula will be stored in the spreadsheet.

Click on the marker in the lower right corner of the cell. Move the pointer to the lower-right corner of the formula cell; the pointer turns into a "+" character.

Hold the "+" sign and drag it over the desired cells in the column or row. Hold down the mouse button, and then drag the + symbol down a column or to the right in a row to select the cells you want. The entered formula will be automatically copied to the selected cells. Since a relative cell address is present here, the cell addresses (in the copied formulas) will change accordingly. In our example (the formulas that were automatically changed and the calculated values \u200b\u200bare shown):

Double click on the "+" to copy the formula to all cells in the column. Instead of dragging the "+" symbol, move the mouse pointer to the lower-right corner of the formula cell and double-click the "+" icon that appears. The formula is copied to all cells in the column.

If there are blank cells in the column, automatic copying of the formula will stop as soon as the first blank cell is reached. In this case, repeat the above steps to copy the formula to the cells below the empty cell.

Copying a Formula Using Paste

Exact copy of the formula

Use this method to copy a formula without changing the addresses of the cells that it contains. Sometimes the spreadsheet is full of formulas and they need to be copied exactly (that is, unchanged). If you change the relative addresses of the cells to absolute addresses in all formulas, you can waste a lot of time (this is all the more unacceptable if you have to change addresses again in the future). This method involves copying a formula with relative cell addresses to other cells so that the formula does not change. For example, let's copy the entire contents of column C to column D:
- If you just want to copy one formula, skip to the “Use an alternative method” step in this section.
Open the Find window. In most versions of Excel, click the Home tab at the top of the Excel window, and then click Find and Select in the Editing section. Alternatively, press CTRL + F.

Find and replace the equal sign (\u003d) with another character. Enter "\u003d", click on "Find All", and then in the "Replace" field, enter any other character. In this case, all cells with formulas (beginning with an equal sign) will automatically turn into text cells that start with different characters. Please note that you need to enter a character that is not in the table cells. For example, replace the equal sign with # or &; also "\u003d" can be replaced with several symbols, for example, ## &.
Table

Column A Column B Column C Column D
line 1 944 Frogs ## & A1 / 2
line 2 636 Toads ## & A2 / 2
line 3 712 Newts ## & A3 / 2
line 4 690 Snakes ## & A4 / 2
- Do not use the * or? Symbols to avoid running into problems later.

	Column A	Column B	Column C
line 1	944	Frogs	## & A1 / 2
line 2	636	Toads	## & A2 / 2
line 3	712	Newts	## & A3 / 2
line 4	690	Snakes	## & A4 / 2

Copy and paste the cells. Select the cells you want to copy and paste them into other cells. Since the selected cells are now text cells and not formula cells, the latter will be copied unchanged.

Table

	Column A	Column B	Column C	Column D
line 1	944	Frogs	## & A1 / 2	## & A1 / 2
line 2	636	Toads	## & A2 / 2	## & A2 / 2
line 3	712	Newts	## & A3 / 2	## & A3 / 2
line 4	690	Snakes	## & A4 / 2	## & A4 / 2

Use the Find and Replace functions again to revert to the previous formulas. Once you have copied the formulas as text, use the Find All and Replace With functions to revert to your previous formulas. In our example, we will find all the ## & characters and replace them with an equal sign (\u003d) so that formulas appear in the cells.