Excel - Dragging a formula to the Right or Down Side

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
happy 1001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
happy 1001Author Commented:
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
Rgonzo1971Commented:
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
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

happy 1001Author Commented:
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
Rgonzo1971Commented:
You have to use the Ctrl Arrows where the cells are populated
happy 1001Author Commented:
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
Rgonzo1971Commented:
is your scroll lock on?
happy 1001Author Commented:
no the scroll lock is not on.
Rgonzo1971Commented:
Sorry cannot help further
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
Richard DanekeTrainerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.