I provide you with two methods to accomplish what you want. Unfortunately, both methods do have their own caveats, since Excel does not provide an all-in-one keyboard shortcut for auto-fill. I highly suggest you try both methods and then determine which works best for you. Outside of these two methods, I do not believe you can accomplish what you seek without the use of macros or add-in modules. Should you go the macro route, the AutoFill
method on any Range
object should be all that you need.
Method 1 (alternate, but it works with no mouse interaction)
Another way to accomplish this is to use a combination of the fill-down shortcut and the select all in range. After you enter the data in the cell, press Ctrl+Shift+End to select from the current cell to the end of the range that would be auto-filled. Then, press Ctrl+D to "fill down" into the entire range from the top cell.
Method 2 (my preferred way, one-time mouse interaction)
Once you fill in the cell you want to auto-fill downwards, double-click the bottom right corner as usual. To repeat the "auto-fill down" step, so long as you only enter data and press enter, you can repeat it by pressing Ctrl+Y.
This means that you do need to use the mouse, but only the first time. All subsequent work can be done purely with the keyboard, so long as you only use it to enter data (i.e. you don't use the context menu button to modify anything).
Note that the first method does come with a minor caveat - your view/cursor will move to the bottom of the spreadsheet (which does not happen with the Ctrl+Y method). You can press the Home key to move the cursor back up quickly.
No, but set it up this way, and Tab and Shift+Tab will take you to the next and previous highlighted row. First do this:
- Change your formula to
=IF(MID($C2,5,1)=" ", 1, "")
(copying down, of course)
- Highlight the column (Ctrl+Space will do that if you're in any cell in that column).
Press F5, then Alt+Special, Formula, de-select Text, then Enter
- To exclude errors, de-select Errors in step 3 also. (e.g., when
LEN($C2)<5
, but you could also change the formula to account for that)
Now the Tab key will step you through each cell in that column that has a numeric value in it (1
). It also has the added bonus that in step 2 above, you can see the total of the highlighted rows in the status bar at the bottom of the screen (Sum:).
Best Answer
Is there an opposite of ctrld fill down?
Not directly, however:
Or:
Source Is there an opposite of the ctrl + d fill down
Or use the following macro:
...
...
Source Create shortcut for "Fill Up"