Excel - Dragging a formula to the Right or Down Side

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.

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).

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.

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.

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.

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.

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

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

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 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.