apply a formula to a very long column excel WITHOUT dragging

Question

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?

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

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

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

Answer 3

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.

Answer 4

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