Solved

Excel - Dragging a formula to the Right or Down Side

Posted on 2016-09-12
13
137 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 51

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 51

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 51

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 51

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 51

Expert Comment

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

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 19

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

717 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