The problem with posts about keyboard shortcuts is that I list ten or twenty of them and exactly none of them stick. So I’m just doing one.
If you have to select a long column of data, using the mouse can take a while. And if I’m sitting behind you in your office trying to help you with your spreadsheet, I really don’t want to wait for you to select 1,000 cells with your mouse. Hey, I’m a busy guy. That’s why I think that if you only know one keyboard shortcut, it should be Control+Shift+ArrowKey.
In this video, I select ~200 cells with the mouse. Then I select B2 and select those same cells by holding down the Ctrl and Shift keys and pressing the down arrow.
I’m sure you’ll agree that the second method is much quicker. And you won’t have to hear me sigh in a melodramatic fashion when you select long columns with your mouse.
I need a screen capture program that also captures the keypresses and scrolls them across the bottom of the screen. There’s a million dollar idea for you.
If you could force your coworkers to use one keyboard shortcut, what would it be?
I tend to use excel with one hand on the mouse and the other doing the typing bits,
The short dut Dick puts forward is a bit slow for me needing 2 hands to press all of the buttons
The method I use for this, is to double click on the botttom of the first cell which takes you to the last cell.
This cell is then selected first then drag up with the mouse with maximum acceleration to the first row.
If need be then drop down one row.
This doesn’t work so well if the first cell is half way down the range.
A bit of trial and error shows shows that the 2 methods can be combined.
Hold down CTRL + Shift and double click the bottom of the first cell – this works exactly the same as CTRL+SHIFT+ENTER.
I will now experiment with other mouse short cuts to see if they can be extended in the same way
Peter
D’oh, does not work for me as I’m still on 1-2-3 navigation keys. I wonder about making the change but that might eat my macros…..
“If you could force your coworkers to use one keyboard shortcut, what would it be?”
F1.
With Transition Navigation switched on it is whilst holding shift [end][arrowkey]
I shudder when I see people use mouse clicks instead of CTRL-C / CTRL-V
Deep breath ….
Ctrl – c, x and v get my vote. There are some situations where these are the only option for cutting, copying and pasting because the right-click options aren’t there. Plus, they’re just so basic.
AH! Teylyn, you beat me to it! Inability of coworkers to adopt -c/-v is a true blood boiler.
Second in the lineup is probably -z, or perhaps -a
First I want to say that that ‘sigh of exasperation’ – I’ve heard it – and it is oh-so-true!
I’m going to agree with the Ctrl C/X/V function – but where I work now – the most frustrating… is Ctrl H. Just use it! Stop using your mouse… Drives me crazy! I can tell them a thousand times… and they won’t use it. Flat out refuse.
Hey! This must be just a small inkling of how you feel every. day. of. your. life.
Since CTRL X,C & V are already taken…
I’ll go with CTRL-S to save the workbook. I always have my left hand ready close so when I has become an automatism for me to use it probably hundreds of time a day. I rarely lose much now when Excel or Windows crashes.
I use a mixture of keystrokes and mouse selections.
To select a range of cells, I generally start from the bottom of the list, then select upward (with shift-home or ctrl-shift-up).
That ctrl-H is a great tip!
Apart from the ones mentioned.. Ctrl-D copies down the formulae.. Use Ctrl-Shift-Down and Ctrl-D.. All your formulae get fill up..
If the column where formulas are filled up is empty, Fill the first top cell with formula; Go to the bottom of the page using Ctrl-Down on a filled in column, Select the empty column (to be filled formula column) and use Ctrl-Shift-Up and Ctrl-D to copy formula down. Pretty long!! but can save huge time if dealing with thousands of records..
Ctrl-D and Ctrl-R
Once you master Ctrl-Shift-Down and Ctrl-Shift-Right … the next most useful are Ctrl-D (to copy cells Down) and Ctrl-R (to copy cells Right).
And another useful thing about Ctrl-D and Ctrl-R: if you select a only single cell and press Ctrl-D, it copies the value from the cell above. If you select only a single cell and press Ctrl-R is copies the value from the left.
Useful Extension to this idea:
Useful macros which extend this idea (and can be assigned to Ctrl-Shift-D and Ctrl-Shift-R) only overwrite BLANK cells in the range, so for example if you have cells with values 100,,,,200,,,, and you use Special_FillRight you end up with 100,100,100,100,200,200,200,200
——————————
Sub Special_FillDown()
‘ fill blank cells in column with value above
Dim rng As Range, rng2 As Range
Dim LastRow As Long
Dim MyCol As Range
Dim col As Long
Dim i As Long
Dim StartRow As Long
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next
Selection = Selection.Value
Set rng = Selection.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.FormulaR1C1 = “=t(R[-1]C)”
For Each c In rng.Cells
c.Value = c.Value
Next c
End If
End With
Application.ScreenUpdating = True
End Sub
‘———————————
Sub Special_FillRight()
‘ fill blank cells in column with value above
Dim rng As Range, rng2 As Range
Dim LastRow As Long
Dim MyCol As Range
Dim col As Long
Dim i As Long
Dim StartRow As Long
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next
Selection = Selection.Value
Set rng = Selection.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.FormulaR1C1 = “=t(RC[-1])”
For Each c In rng.Cells
c.Value = c.Value
Next c
End If
End With
Application.ScreenUpdating = True
End Sub
‘——————————-
I use a multi button mouse with 2 of the buttons set to Copy & Paste.
2 others set to delete and clear contents
Final buton set to call up a special macro to do a perticular operation depending on the Workbook/worksheet name
The one that sends 25000 volts through their keyboard.
It’s Friday morning.
Ctrl+S. I don’t care how long it takes them to do their work, as long as they remember to SAVE IT! I can’t tell you how many stories I’ve heard from co-workers saying “I worked an hour and half on that sheet and now it’s gone.” Save the damn thing already!
ALT F4…because there’s no way I want them tinkering incompetently with Excel when I’m around.
Alt + Tab
I literally had someone ask me “How does it know which window to take you to” once.
F4 on a cell reference to cycle through absolute/relative references
Tab, for the love of Pete, tab!!!
Phani Lanka, alternatively you can select the range that you want filled, type in your formula and press Ctrl-Enter.
Crtl-Tab to switch between Excel files and [F12] to Save as…
There are keyboard macro programs which come with record modes that send recorded keystrokes and mouse actions to text files. You could use a separate process to read such text files, transform the keystrokes from the recorded format to the format you want to display, then write them to a modeless dialog. A scripting language with Tk could handle that.
As for keystrokes, Tony said it best: [F1].
In terms of time saving, ^; and ^: for the current date and time are also pretty useful.
My favourite is – CTRL + ENTER
No 2 – selecting entire column – CTRL + SPACE and CTRL + ‘+’ for inserting or ‘-‘ for deleting (shift + Space for selecting entire row)
If they hadn’t gone already I would say ctrl c and v.
For non beginners I wish they would use alt F11 to get the VBAIDE.
CTRL + *.
Is the same as ActiveCell.CurrentRegion.Select
Ctrl-Enter – puts same value in multiple selection
“If you could force your coworkers to use one keyboard shortcut, what would it be?”
All the suggestions above, plus ‘Right Arrow then Enter’ to enable macros when the workbook opens. Our nanny-state IT admin won’t allow changes to macro security, and we can’t publish digital signatures, but the amount of people I see clicking ‘Disable Macros’ when opening internal workbooks drives me mad. If you’re not going to use the macros, then why did you have me tinkering with VBA for hours/days/weeks?
Ctrl + (which is in fact Ctrl + Shift + <) for filling in the column after doing Dick’s shortcut recommendation. (or filling in the row after ctrl + shift + right arrow).
They are like Ctrl D and Ctrl R, but easier! (at least in my Spanish Keyboard they are both between the Z and left shift and I use them all the time, using just one hand).
Sorry:
Ctrl + (Ctrl + shift + <)
F4, most definitely is the best Excel shortcut known to man. Saves massive amounts of time repeating your last action (formatting, generally).