Solved

Excel - Dragging a formula to the Right or Down Side

Posted on 2016-09-12
13
48 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
13 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 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 48

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
 

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 48

Expert Comment

by:Rgonzo1971
ID: 41799518
You have to use the Ctrl Arrows where the cells are populated
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 48

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 48

Expert Comment

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

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 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 18

Accepted Solution

by:
Richard Daneke earned 250 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now