I am not a huge fan of this, since you can start typing the name and then choose from AutoComplete. Many folks in the United States think this is Ctrl+~, but it is actually the grave accent to toggle into and out of Show Formulas mode. Thanks to Michael Byrne, Jeneta Hot, and Bob Umlas. Another way to remember which is which: The Shift key is much longer (like a row!) than Ctrl. Also, the “S” in Shift is adjacent in the alphabet to the “R” in row. How can you remember which is which? The “C” in Ctrl stands for the “C” in column.
Watch for the indicator in the status bar:Ĭtrl+Spacebar and Shift+Spacebar to Select an Entire Column or RowĬtrl+Spacebar selects a whole column. Press Esc to exit the Extend Selection mode. To return to normal, press Esc.Ī bonus tip from Bill Hazlett: if you select A1, press F8, then click in S20, you will select from A1:S20. Press Shift+F8, and you are in Add to Selection mode. The correct way to choose VLOOKUP is to press Tab! But if you press Enter, you get a #NAME? error. The AutoComplete shows that there is only one function that starts with VL. If you have to select two regions, select the first one, then hold down Ctrl while clicking on other cells or regions. If you are at Sheet9 and need to move to Sheet3, press Ctrl+Page Up six times. If you need to move from Sheet1 to Sheet5, press Ctrl+Page Down four times. Thanks to Dawn Bjork Buzbee and Olga Kryuchkova.Ĭtrl+Page Down and Ctrl+Page Up to Jump to Next Worksheet Press Alt+= to invoke the AutoSum function. Thanks to Olga Kryuchkova and Schmuel Oluwa. To bring the window back to encompass the active cell, press Ctrl+Backspace.
You’ve used the scrollbars, and now you are looking at ZZ999. Thanks to Olga Kryuchkova, Roger Govier and Tim O’Mara.Ĭtrl+Backspace to Bring the Active Cell into View Excel will interpret it as the proper date and time. To put both the date and time in one cell, type either keystroke, a space, then the other keystroke. Note the shortcut enters the current time, not a formula. Press Ctrl+Shift+: to enter the current time. Thanks to Crystal Long, Schmuel Oluwa, Ken Puls, Peter Raiff, Sven Simon, and Gavin Whyte. When you press Ctrl+Enter, the formula is copied, but no formatting is copied. Press the F2 key to put the cell in Edit mode. You need to copy the formula down but not the formatting.
Press Ctrl+Enter, and Excel enters a similar formula in all cells of the selection. Ken Puls, who is the king of Power Query, says, “You would think my favorite Excel tip would be Unpivot with Power Query, but my favorite all-time is Ctrl+Enter.” Say that you want to enter a formula into 400 cells. Thanks to to Copy Formula into Entire Selection But my muscle memory still prefers tip #2. It is really superior in every way to keyboard tip #2. If I could slow down enough to stop pressing Ctrl+Shift+Down Arrow followed by Ctrl+Shift+Right Arrow, I would realize that Ctrl+* is much shorter and does not get tripped up by blank cells. This one is easier if you have a number keypad so you don’t have to press Shift to get to an asterisk. Why 5? If you are making hash marks, the fifth hash mark crosses out the first four. This is great for crossing things off your to-do list. Thanks to Crystal Long, and Steve McCready. From the bottom-right corner, press Ctrl+. If the selection is rectangular, you move in a clockwise fashion. to move to the next corner of the selection. While you have a large range selected, press Ctrl+. Thanks to Captain Excel, José de Diego, Mike Girvin, Elchin Khalilov, Crystal Long, Paul Sasur, and to Jump to Next Corner If it seems awkward at first, try it for a few days until you get the hang of it. While still holding down Ctrl+Shift, press the Right Arrow Key to select A1:K545. Starting from A1 in the above figure, press Ctrl+Shift+Down Arrow to select A1:A545.
Ctrl+Right Arrow will jump the gap of empty cells and land on N1.Īdd the Shift key in order to select from the active cell to the landing cell. In the following figure, Ctrl+Down Arrow will jump to K545. If you have a column with no blanks, press Ctrl+Down Arrow to jump to the end of the data set. Your cell pointer is sitting at the top of 50K rows of data, and you need to get to the bottom. Thanks to Mitja Bezenšek, Alexa Gardner, Andrej Lapajne, Schmuel Oluwa, Jon Peltier, and to Navigate or Select Whether it is a cell, SmartArt, a picture, a shape, or the March data point in a column chart, press Ctrl+1. After the first eight or so, they are then sorted by my subjective sequence.Ĭtrl+1 (the number one) works to format whatever is selected. If a lot of readers suggested a tip, it is at the top. As I started polling readers about their favorite Excel tips, a large number of them were keyboard shortcuts.įollowing keyboard shortcuts are presented in order of popularity.