Excel equivalent for a 1-2-3 range selection keyboard shortcut?

[OxfordCurmudgeon]

Suppose a table with 6 columns and 1000 rows, so A1:F1000. I want to insert a column between C and D. That's easy and I now have A1:G1000 and all the cells in column D are empty. I fill cell D1 with a formula, format it -- and now I want to copy it to D2:D1000. This was trivially easy in 1-2-3. Control-C to copy cell D1 to the clip board. Click on cell C2 then Ctrl-Shift-Down Arrow which selects C2:C1000. Shift-Right Arrow selects C2:D1000. So far we're set; the same keystrokes work in Excel. In 1-2-3, I would now press the period key. That would move range anchor from C2 to D2. (If I pressing it, the anchor would move to D1000, then C1000 and then back to C2.) With the anchor in D2, I can press Shift-Right Arrow and now D2:D1000 are selected, and Ctrl-V Paste completes the copy operation.

In Excel, the only way to select cells D2:D1000 that I have found is to either scroll all the way down, press Shift and click on cell D1000 -- or put D1000 in the Name Box in the upper left corner and press Shift-Enter. While that technique is certainly fine, it only works if I know the cell address. The Lotus approach was simpler, faster, and more versatile. It's also so ideologically simple, I can't believe it doesn't have an Excel equivalent. Yet each time I've gone looking, I've come up empty. I'm hoping someone here can enlighten me.

 

[alansidman]

If after placing your formula in D1, if you hover your mouse over the lower right corner of the cell, a small cross will appear. Double click on it and the formula will fill down.

 

[Fluff]

To do it like you are trying, select C2 then Ctrl down arrow, right arrow, Ctrl Shift up arrow & finally Ctrl D

 

[OxfordCurmudgeon]

If after placing your formula in D1, if you hover your mouse over the lower right corner of the cell, a small cross will appear. Double click on it and the formula will fill down.

Thank you! I didn't know that trick. It appears to only work for filling cells below, but it can handle multiple columns at once. Is there a comparable shortcut for filling cells to the right or left?

 

[Fluff]

Ctrl R will fill right and Alt H FI L will fill left

 

[OxfordCurmudgeon]

To do it like you are trying, select C2 then Ctrl down arrow, right arrow, Ctrl Shift up arrow & finally Ctrl D

Thanks for the reply. The specific steps you list didn't work for me, but I think I understand what you meant: Select C1, press control-C. Click on C2, then Control-down to move to the last cell in the range, right arrow to get to column D, then Control-Shift-Up arrow to select all the cells above that one, then Control-V to paste the cell. That works, but only if the table is at the top of the sheet. Otherwise the Control-Shift-Up arrow may select too many cells.

 

[Fluff]

There is no need to copy anything. If you have a value in D1 & use the steps I mentioned that value will be filled down.

 

[OxfordCurmudgeon]

If after placing your formula in D1, if you hover your mouse over the lower right corner of the cell, a small cross will appear. Double click on it and the formula will fill down.

Discovered another limitation; that technique does a copy, which overwrites other formatting.

While there are times when this technique will work for me (and I am GLAD to have added it to my arsenal), I'm still on my quest...

 

[Alex Blakenburg]

Discovered another limitation; that technique does a copy, which overwrites other formatting.

While there are times when this technique will work for me (and I am GLAD to have added it to my arsenal), I'm still on my quest...

Everything you mentioned seems to work fine in Excel you might need to be a little clearer on what you are trying to achieve.
eg
• Select ctrl+shift+down on C
• Shift right to select C & D or more
• Ctrl + Period to select the right corner
• Shift + right arrow to move the selection to Column D (and beyond if required)
• Ctrl D to copy down OR if you did a copy at the start ctrl+V to include formatting or another paste option if you didn't want the formatting
(for multiple columns Ctrl+R then Ctrl+D vice versa is also an option - but does include formatting)

 

[Alex Blakenburg]

PS: If you use @alansidman's suggestion you will get a drop down box appear that gives you choices as to whether you want formatting or not.
It appears in the currently visible screen even if the data extends well below this.