This article shows the magic of Shift and Control keys and how we can use them with arrow keys to work faster.

If you have a lengthy data and you wish to drag a formula in a particular cell down to the end of the data then instead of using shift and down arrow to copy the formula, you can use excel’s auto-fill function. When you move the cursor to the bottom right hand corner of the cell, you would see a small square. When you double click on it, your formula would automatically get copied down to the last cell of the adjacent left column.

However, this method would not work when the adjacent left cell is blank. In that case the easiest and fastest method is to copy the cell that has the formula and then just go to any column which is non-empty and which has entries to the end of the data and hit control+ down arrow, that way you would reach to the end and now using left or right key go to the column which you want to fill up and hit shift+control+up arrow key, followed by the enter key. And it will be done in no time.
For e.g. you have entries in column A but column B is empty. And you have some formula in cell C1 which you want to drag down to the end. So simply copy cell C1, using the left arrow key go to cell A1, reach the end of the data using control+down arrow key. Then go right using the right arrow key to reach column C. Then holding down the shift and control key hit the up arrow key which will take you to cell C1 and just hit ENTER.

Share This

Popularity: 37%



Reader's Comments

  1. Needed Help | April 2nd, 2008 at 5:33 am

    Thanks a lot buddy!

  2. keith | July 9th, 2008 at 9:03 pm

    Awesome! Just what I needed.

  3. Beyond Help | October 22nd, 2008 at 7:16 pm

    Good stuff. Thanks very much. I sure miss Lotus.

  4. Laura | March 12th, 2009 at 5:48 pm

    Hi there! I am trying to copy a formula that is long from one cell to another. My problem is that half of the formula changes based on row and the other half I want to depend on the header in row 2.

    ((IF(AND(AP2=”number”,AP3=1),1,0)))

    When I copy it, it changes AP2 to AP3, but I want it to stay as AP2 while I do want AP3 to change to AP4. I could really use some help if you have any suggestions.

  5. Shikha | March 12th, 2009 at 9:15 pm

    Hi Laura

    You can freeze AP2 by using the F4 key and leave AP3 as it is. Now if you copy it down, AP2 will not change. However, AP3 will change to AP4.
    Hope this helps.

  6. Mordheimer | April 9th, 2010 at 3:24 pm

    Shikha:

    THANKS!!! I have been trying to figure how to do this ‘freeze’ cell thing. :)

Leave a Comment

Close
E-mail It