?
Solved

Excel - Dragging a formula to the Right or Down Side

Posted on 2016-09-12
13
Medium Priority
?
166 Views
Last Modified: 2016-11-01
Hi Experts

I have an excel sheet in which I have the data for a large number of items. Many times I have to apply the same formula on large cell ranges. I find it pretty hard to drag the mouse to large ranges, and I have to do such thing quite often.

Please suggest some easy method by which the formula given in Cell B1 can be easily dragged to the right hand side for the last cell located at -
Row B - column number 120
Row B - column number 180
Row B - column number 550


I do not want to manually drag the formula cell to those 3 locations manually.

Please suggest some short cut keys or some other method. And what if I had to drag the formulas down and not towards the right hand side ? I know, simply clicking on the bottom right corner of the cell, copies it ALL THE WAY DOWN to the last cell in the adjacent column, but what if I have to move it down to just specific cells like -

Column B - Row 50
Column B - Row 220
Column B - Row 785


Please suggest various methods by which this could be done.
Any ideas are most welcome.

I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

Thanks
0
Comment
Question by:happy 1001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 41795463
Hi,

With Ctrl-G (Go to) you can move directly to last cell you want and form there use (eg enter B120 and OK)

Ctrl+Shift+Arrow Up to select the range to fill down then Ctrl-D

Respectivly with Arrow Left( then Ctrl+R)

Regards
1
 

Author Comment

by:happy 1001
ID: 41797672
Thanks for the suggestions @Rgonzo1971

I thought that maybe I should upload the sample file itself, so that I can express my requirement properly. As I need to find the method, which could be used, when we do not know the exact end point beforehand.

In the attached sample file, I need to drag to the Right Hand, the formulas in the cells H11 and H12 respectively. And drag formulas to the downside, in case of cell H29. I have highlighted these cells differently, so you could understand it in an instant.


Please suggest which method would work in these cases.

Thanks a lot
Dragging-the-Formula-to-Right-Side.xlsx
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41797678
If you have a help like in cell H11 un can select H10 CTRL+Arrow-Right Arrow-Down
Ctrl+Shift+Arrow Left and then Ctrl+R to fill to the right
1
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:happy 1001
ID: 41799514
Thanks for your comments @Rgonzo1971, but unfortunately it is not working.

Can some other expert please give it a try. The above mentioned solution is not working for me. When I do it, it simply takes me to the Very Right Hand Side of the complete worksheet, which is column named XFD in my case.

Please someone suggest a working solution in the sample file.

Thanks a lot
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41799518
You have to use the Ctrl Arrows where the cells are populated
0
 

Author Comment

by:happy 1001
ID: 41800022
Yes I used Ctrl arrow. Can someone please check if I am the only one who is not being able to get it to work, or if this method does not works on other machines as well ?

Thanks a lot
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41800998
is your scroll lock on?
0
 

Author Comment

by:happy 1001
ID: 41805575
no the scroll lock is not on.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41805912
Sorry cannot help further
1
 
LVL 22

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41823668
select the cell with the formula you want to copy.  Ensure that absolute cell references (ie: $A$1) are converted to relative references (ie: A1) or mixed references (ie: $A1, A$1) so when you copy, references  will be adjusted as you desire. $ before a row or column in a reference means that row or column will not be adjusted.  You can highlight or click on a reference in the formula bar and press F4 to cycle through the different reference styles, or edit the formula.

copy the cell with the formula you desire

press Ctrl-G to go to the range where you want it to be (ie --> B2:b999 or a list such as B2:B10, C54, D85 or B50, B220, B785). Alternately, you can click in the first cell and shift-click in the last one and then ctrl-click or ctrl-click and drag additional ranges -- or click first cell, ctrl-click next cell, ctrl-click next cell --all that is needed in this step is to select where you want the formula to go.

then paste
0
 
LVL 19

Accepted Solution

by:
Richard Daneke earned 1000 total points
ID: 41824025
Selecting cells and copying formulas can be accomplished in many ways.
When you use the Shift key with any key combination that moves a cell, you extend/adjust the current selection (highlighted cells).
For example, to highlight your row of cells, I would:
select the cell with the formula,
press Shift-Alt-Page Down to extend a highlight a page at time to the right until I near the last column
press Shift and left or right arrow to complete the selection area,
press F2 to enter into Edit mode for the cell with the formula, and
press Control-Enter to paste the formula into the selection (highlighted cells).

This is demonstrated in the attached zip file which has screen shots during the process.

ONE QUICK technique for copying down is to use the Fill Handle (the small square in the right corner of the active cell).   When you DOUBLE CLICK the Fill Handle, Excel will copy the formula DOWN for the data in the spreadsheet.
CopyPaste-in-Excel.zip
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question