1

In Excel, suppose I have a row with, say, 26 formulas horizontally from A1 to Z1, and then I move down to cell A2 and type a formula. Now I want to fill that formula to the right, through cell Z2. The issue is that if I just use Ctrl+R, it will fill this formula to the edge of the worksheet. Normally I get around this by going to cell A1, then Ctrl+→ (right arrow) to the end of the row (cell Z1), then drop down one row to cell Z2, put something (e.g., a 1) into that field, then Ctrl+← (left arrow) to select cell A2, and then finally I do Ctrl+R, which will fill my formula only through cell Z2.

Is there a much easier way to do this?

1

1) If your used range is between columns A and Z, then you can press Shift+Ctrl+End to select cells Ax:Zx (x being your current row). If you accidentally paste something outside of this range and delete it, then you can restore that range by saving the workbook.

2) You could put all of the formulas in Column A and then select all of the formulas and fill right in one action.

3) You can use this sequence (starting on a cell containing a newly entered formula):

  • Ctrl+C
  • Up
  • Ctrl+Right
  • Down
  • Shift+Ctrl+Left
  • Ctrl+V
  • Down (you are now ready to enter a new formula)

(While this seems like a lot, it can catch on relatively quickly)

4) Record a macro to accomplish the same steps as 3 with relative references. Assign that to a simple keystroke such as Shift+Ctrl+A.

5) Write a macro that does the same as 4, but only takes 2 lines of code.

6) Continue using Ctrl+R to fill all the way to the end of the sheet. Once you have finished building out the worksheet, delete columns after Z.

0

Its easier if you are working in rows you can just double click the little black box in the corner of a2 when it is selected.

For copying across columns I just

  • navigate to Z2 from A1 (up, ctrl+right,down)
  • select the range and copy (ctrl+shift+right, ctrl+right)

Fills the range, takes about a second, seems fast enough for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.