- Drag A Formula In Excel Without Mouse Traps
- Drag A Formula In Excel Without Mouse Pad
- How To Drag A Formula Down In Excel Without Mouse
Drag down ongoing numbers without using mouse. I want to drag down onogoing numbers, so that every row has the number of the upper row +1. Using the mouse it's easy: Just put in the first three or so rows 1, 2, 3, mark them with the cursor and drag down the black point in the bottom right corner. But how do I do it using only the keyboard?
- Using the mouse does not mean that you have to drag a formula down hundreds of pages. You can use a simple double click instead. Enter the formula into C1, then double-click the Fill Handle (the black square at the lower right corner of the cell) Before the double click.
- I know this could be solved by using a formula and then CTRL+D, but unfortunately for this particular case I cannot use a formula. At the moment I just have to use the mouse to either highlight and drag the sequence down, or highlight and double click to automatically fill in the formula.
- Excel: How to Drag Without Changing Numbers Basic Excel. Excel Details: 1. Click on the cell which contains the formula you want to copy. To the formula bar and highlight the formula. Ctrl+C or Ctrl+X to copy or cut respectively. Press the Esc button to exit from the formula bar, which prevents a change in reference.
Podcast: Play in new window | Download
How to do reverse order for scatter plot in mac numbers. Subscribe: Google Podcasts | Spotify | Stitcher | TuneIn | RSS
Drag A Formula In Excel Without Mouse Traps
This spreadsheet tip is based on a question I get asked all the time when I teach (well taught) Excel at in-person classes: How do I fill a formula down to the last row of my data set without over-shooting the last row with keyboard shortcuts? This problem occurs with larger data sets where you have several hundred or thousands of rows and need to quickly apply a formula in a column for all these rows. This screenshot show the problem:
As the text on the screenshot shows, the Revenue per passenger formula needs to be applied to all rows in the data set, but we don’t know where the data set ends. It could be row 100, 500, or 800,000. If you want to try this exercise for yourself, see this Google Sheet and make a copy for yourself.
Method 1: Double-click the bottom-right of the cell
This is how most people approach this problem, but the downside is that it requires you to use your mouse or trackpad. You basically hover your cursor over the bottom-right corner of the cell that contains the formula (in this case E2) and wait until your cursor turn into a black plus sign. Then you double-click and the formula for Revenue per passenger fills down to the last row in the data set (in this case row 281):
Method 2: Drag-and-drop the formula until it reaches the last row of data set
Even less ideal, you can drag the bottom right corner of the cell down and basically wait until the window scrolls to the last row of the data set. The downsides of this method:
- You’re still using your mouse or trackpad
- You might under-shoot or over-shoot the last row of your data set because the scroll depends on how far down you are holding down your mouse
Method 3: Press page down while having the first cell selected
This method uses keyboard shortcuts so definitely more ideal compared to methods 1 and 2. You keep the first cell with the formula selected by holding down SHIFT and then press PAGE DOWN a few times until you get close to the bottom of your data set. The downside is that you might overshoot your data set which means you have to keep SHIFT pressed while pressing UP ARROW a few times to get the select to “stop” right on the last row (row 281) in the Google Sheet. You can then press CMD+D on the Mac or CTRL+D on the PC to fill the formula down:
Some people ask me about using CMD+DOWN ARROW at this point to get to the bottom of the column (column E in this case) but the problem is that since all of column E is pretty much empty (rows 2 and below), you will simply go to the last row of the spreadsheet. You are over-shooting the last for of your data set by a lot in this scenario.
Method 4 (most ideal): Go to the bottom of the data set in the column to the left and then use the fill formula down shortcut
This method involves using only keyboard shortcuts and hence the most ideal. These are the steps:
Drag A Formula In Excel Without Mouse Pad
- Move your cursor to the column to the left of your column that contains the formula you want to fill (in this case column D)
- Press
CMD+DOWN ARROWon the Mac orCTRL+DOWN ARROWon the PC and you’ll most likely go to the last row of the data set (column D in this case) since the data should be contiguous. - Move your cursor to the right which puts you in the last row of your data set but also in the column that contains the formula you want to fill down (cell E281 in this data set)
- Press
CTRL+SHIFT+UP ARROWto select all the empty cells including the first cell that contains your formula above your current empty cell (in this data set, you’ll have the E2:E281 range selected). - Press
CMD+Don the Mac orCTRL+Don the PC to fill the formula down
Index.dat file reader for mac. The reason this method works is because of step 4 where you’re able to select all the empty cells above your empty cell while the first cell of the selection contains the formula you want to fill down. While this is technically a workaround, I’ve found this is the easiest way to get the range selection properly set up before applying the fill formula down shortcut:
Other Podcasts & Blog Posts
How To Drag A Formula Down In Excel Without Mouse
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
- Animalz Podcast episode #36: Why Community Is the Most Important Trend in Marketing
- SyntaxFM episode # 219: Hasty Treat – Non-Glamorous Skills You Should Have