Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.
I'm trying to copy a block of cells, but instead of shifting down the rest of the rows, it overwrites them How do we copy and paste a set of cells and shift the rest so nothing is lost?
I guess that we can use "Go To Special function" –under Find and Select option - and select only the required cells, then just paste them, but I haven't been able to get this working yet.
I have tried to solve this issue simply by examining the following trials in copying and pasting without overwriting block of cells in several attempts of mine, and found that:
1- We could just run a new column and do it the old fashioned way.
Run a new column somewhere (anywhere really), and put this formula:
=if(An="","",An&" "&Bn) , in which n is the row number(s).
If An is blank then enter nothing, and if An isn't blank, combine the data from An and Bn. That will make a new cell combining the data from both cells, with a space in the middle.
Copy this formula down as far as needed. To make it permanent, just copy the column then past special- values right over the top of itself.
2- You may actually do this:
=CONCATENATE(An," ",Bn)
That way you don't lose any data either way. You would get a new column that has the data from A followed by a space, and then whatever is in Bn. If you wanted to clean it up so you don't risk having a space in front of the Bn data if the A column is empty you could do this:
3- =TRIM(CONCATENATE(An," ",Bn))
If you want it so that you will only paste in the new value if J is empty, then do this:
=if(An="",Bn,An)
4- By using both mouse and keyboard shortcuts in order to copy and paste a cell(s) containing data without overwriting
a- Select the rows or columns of data you wish to cut or copy.
b- Cut or copy the data (press Ctrl+x to cut, Ctrl+c to copy or right-click on the selected data and click CUT or COPY).
c- Click on the cell, row, or column heading where you want to paste the data. Your Selection should be for a cell inside the range of cells that should not be overwritten
d- Right-click on the cell, row, or column heading and click “Insert Cut/Copied Cells”. Or you hold the Ctrl+Shift + (+)keys to open the Insert dialog box, select Shift cells right or Shift cells down, and click OK.
e- If you selected an entire row or column, the existing data will shift and insert the cut/copied data. If you did not select the entire row/column, but just specific cells, you might see a dialog box asking you how to shift the existing data if there is surrounding data that wasn’t originally selected.
5- Or where you want to paste, select the number of rows you want to insert. Then on the ribbon, Home/Cells/Insert/Insert Sheet Rows paste the selected rows of cells. Also, when you see Insert Sheet Rows, you can right click on it and select Add to Quick Access Toolbar to make it faster to right click on the row header where you want to paste and select Insert Copied Cells without your having to select them. In the Insert Paste dialog box, click the direction in which you want to shift the surrounding cells. If you insert entire rows or columns, the surrounding rows and columns are shifted down and to the left.
by selecting the numbers of rows or column then right click insert and an options shows shift cells up, down left or right or " Insert copied cells" by right clicking the mouse as option is the easiest way for not overwriting the content of cells but to move down.
I'm trying to copy a block of cells, but instead of shifting down the rest of the rows, it overwrites them How do we copy and paste a set of cells and shift the rest so nothing is lost?
????????????????? Are you serious with this question ?????????????????????
You may click the cell whose contents needs to be copied. Press F2 and select the portion which you wants to copy and then move the cell in which you want to paste. Click F2 aagain in that cell and click Ctrl + V. It will pste the data whitout overwiting the data.
