Copy a Formula to All Data Rows

September 09, 2021 - by Bill Jelen

Copy a Formula to All Data Rows

Problem: I have a worksheet with thousands of rows of data. I often enter a formula in a new column and need to copy it down to all of the rows. I try to do this by dragging the fill handle. But as I try to drag, Excel starts accelerating faster and faster. Before I know it, I’ve overshot the last row by thousands of rows. I start dragging back up. Again, Excel starts accelerating. Soon, the cell pointer is moving somewhere close to the speed of sound, and I find that I’ve overshoot the last row in the other direction. I end up going down and up, down and up. I call this frustrating process the “fill handle dance.” Is there a way to stop the madness?

You have headings in row 1 and thousands of rows of data. You just entered a formula in E2 and need to copy it down to all rows. With E2 selected, you will see a square dot in the lower right corner of the selection rectangle. This is called the Fill Handle. Double-click the fill handle to copy the formula to the end of the data.
Figure 72. Copy this formula all the way down.

Strategy: You can very quickly copy a formula down to all the rows by double-clicking the fill handle. Excel will copy the formula down until it encounters a blank row in the adjacent data. The fill handle is the square dot in the lower-right corner of the cell pointer box. When you hover your mouse over the fill handle, the cell pointer changes to a plus.

Gotcha: In Excel 2007 and earlier, a single blank cell in the adjacent column would cause the copy action to stop prematurely. If the adjacent column is particularly sparse, you could hide that column and then Excel would look at the next visible column to the left. This algorithm was improved in Excel 2010 and will usually get you to the bottom of the data.

Alternate Strategy: From E2, press Left Arrow to go to D2. Ctrl+Down Arrow to get to the bottom, Press Right Arrow to go back to column E. Ctrl+Shift+Up Arrow selects from the last row to E2. Ctrl+D will fill down and the formula in E2 will get copied. Practice for a week, and you can do all the keystrokes in less than a second. You will be on the team if Excel ever becomes an Olympic sport.

This article is an excerpt from Power Excel With MrExcel

Title photo by Pawel Czerwinski on Unsplash

Bill Jelen is the author / co-author of:
MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.