Interesting Excel's twists

[Sektor]

Today I have discovered two interesting twists in Excel according to moving and repositioning cells. Here's workbook with description.

 

[RoryA]

The second of those is pretty well known I think - the first less so. (You can also use it to delete cells) I've never found a way to insert and copy at the same time though.

 

[Michael M]

Any chance of sharing more info....some of us can't download from those sites....gotta luv Gov't restrictions.

 

[RoryA]

Basically:
1. If you hold shift while dragging the fill handle of a selected range up or down, you insert or delete cells.
2. If you hold Shift while dragging the border of a cell or cells, you can move them from place to place with other cells shifting to fill the gaps. Eg select column A, hold shift and drag the edge of the selection between cols B and C, and col B becomes col A and A becomes B.

 

[Michael M]

Thank you sir, I can see that would be handy !

 

[RoryA]

Yep, I use 2 all the time for quick swapping columns of data.

 

[Sektor]

Thanks, Rory! Also you can drag and fill cells with right-button.
Here's description and structure for those who can't download file.

Excel Workbook
	A	B	C	D	E	F	G
1	1	11	1	2	3	4	5
2	2	12	1	2	3	4	5
3	3	13	1	2	3	4	5
4	4	14	1	2	3	4	5
5	5	15	1	2	3	4	5
6	6	16	1	2	3	4	5
7	7	17	1	2	3	4	5
8	8	18	1	2	3	4	5
9	9	19	1	2	3	4	5
10	10	20	1	2	3	4	5
Sheet1

TWIST 1
Range A1:A10
1. Activate A1 cell.
2. Press and hold SHIFT.
3. Move pointer to fill handler at the bottom right corner of cell - it will change to double-arrow pointer.
4. Now press mouse left button and drag that pointer till A3 cell.
5. All cells below A1 will move down for the amount of selected cells sduring dragging.
6. Release mouse left button, but don't release SHIFT. Take a look at Name Box - it shows selection address in notation which doesn't appear in normal selection (which would be: 3R x 1C)
7. Now try do the same but in other directions. For instance, drag C1:C10 to right: all columns to the right will be repositioned.

TWIST 2
Range A1:A10
1. Activate A1 cell.
2. Move pointer to bottom border of cell. The pointer will have four arrows.
3. Press and hold SHIFT.
4. Press mouse left button and drag cell. You will see greyed out line with two little lines at edges. This line shows the new position of cell(s).
5. Release mouse button. Cell is now repositioned. Cells, which were below A1, are now above it.
6. You can also move cells from bottom to top.
7. All steps are also applied to several cells. For example, you can reposition cells A1:B1 or A3:B3.

 

[Michael M]

Thank you Sektor, kind of you to post examples.
I like the 2nd one for my work !!

 

[Sektor]

Also, if you have long formula and want to know the result of some part of formula, then:
1. Enter formula either by pressing F2 or in formula in Formula Bar
2. Select that part of formula
3. Press F9

For not to accidentally save result, press ESCAPE.

 

[GlennUK]

The second of those is pretty well known I think - the first less so. (You can also use it to delete cells) I've never found a way to insert and copy at the same time though.

... isn't that done by using Ctrl at the same time?