1

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?

2

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.

2

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.

  • This is correct answer – GeneCode Mar 24 at 3:17
1

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.

enter image description here

Note: this assumes the columns from A to NTP have continuous data with no gaps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.