I would like to apply the formula n/log(n) to a very large number of inputs in excel. specifically across a column with 10,000 numbers in it. What is the most efficient way to do this without dragging?
-
1How to fill a large series (over 10,000 rows) in Microsoft Excel without dragging or selecting cells?, Possible to apply a function to an entire column?, Apply Formula to a Range of Cells without Drag and Drop, How to make Excel 2010 auto-fill new row with formulas?, Excel Auto-Fill a Series Without Mouse, AutoFill Large Number of Cells in Excel?– phuclvCommented Mar 24, 2019 at 0:16
-
Fill a large range with a formula in Excel, without mouse-dragging to extend– phuclvCommented Mar 24, 2019 at 0:18
4 Answers
Assuming your column 1 has 10,000 numbers (columns A-NTP) and you want to paste a formula in row 2 to the range A2:NTP2:
.
With your A2
formula selected, copy it. Press UP ARROW, then CTRL+SHIFT+RIGHT ARROW, then DOWN ARROW, then CTRL+SHIFT+LEFT ARROW, then paste.
Note: this assumes the columns from A
to NTP
have continuous data with no gaps
Dragging is just one of the ways to make a formula populate many cells at once.
The fastest way is to first copy the cell that has the formula, then select the first cell of the target, Scroll quickly to the target cell of the last one in your range by using the scrollbar, hold shift and click on the cell to make a selection.
With the selection active, hit paste and your formula will be in all cells as if you used the little block on the bottom right of the source cell and dragged it.
My recommendation is to turn your data range (for example A1:A9999
) into a table (by selecting it - or any data element in it - and choosing Insert > Table
).
If you insert a formula (for example =[@X]/LOG([@X])
, assuming your column was called X
) in a new column of such a table, newer versions of excel will (by default) apply this formula to every row of the table.
- Enter the formula into the first cell.
- Select the range of cells.
- It might be fastest to go to the last cell by entering its coordinate in the Name Box on the top left and pressing Enter, then scrolling to the first cell using the scroll bar, holding Shift and clicking on the first cell.
- Ribbon menu ⟶ Home tab ⟶ Editing section ⟶ Fill ⟶ Series ⟶ AutoFill ⟶ OK.