Excel VBA Class Favorite Keyboard Shortcuts To Help You Save Time With Microsoft Excel
Excel VBA Class NYC is pleased to share some of our favorite Excel keyboard shortcut techniques. This list is not meant to be exhaustive, we believe it offers some of the most common and frequently used shortcuts to save you substantial time and effort when using Microsoft Excel. We teach these techniques and many others in our Introduction to Excel class, Advanced Excel training, Advanced Excel Functions class, and Excel VBA classes.
Exit Editing Mode: Ctrl+Enter
Rationale: After you complete a data entry task in Excel you need to commit your edit to preserve your change. Pressing the Enter or Tab key works, but it then navigates you away from the active cell. If you wanted to use that cell to autofill a series formula or do formatting work, now you no longer lose an extra click selecting the cell again to continue to use it.
There are times when you want to use the tab and enter keys. Excel intellisense tries to help you do data entry. If you type, press the tab key to move right, type something, press the tab key, type something and then press the enter key, you would not end up on column c of row 2, but back in column a. Intellisense wraps your active cell back to the starting column and moves down one row.
Select a Row: Shift+Space (hold the shift key, tap and release the space bar key)
Select a Column: Ctrl+Space (hold the ctrl key, tap and release the space bar key)
Select ranges: Ctrl+Shift+Arrow Key (hold both the ctrl+shift keys and tap an arrow key)
Rationale: There are several reasons you need this keyboard technique; A) It is helpful when working in the Insert Function Dialog and you need to select a large range of cells; B) You download a workbook, or someone emails it to you, and you have no idea where the last row of data appears. Pressing the arrow key could take hours if there are thousands of rows of data. Ctrl+Arrow is a jump technique. Ctrl+Arrow jumps in the direction of the arrow key you press until Excel finds data, or a space after the data. Shift+Arrow highlights data. When you combine Ctrl+Shift+Arrow you both jump and highlight. These same techniques are also effective in Microsoft Word and PowerPoint.
Copy anything: Hold the Ctrl key while dragging an ‘item’ with the mouse
Rationale: There is nothing wrong with using copy and paste to move information large distances, or if you like clicking your mouse excessively. To copy a cell, a row, a column, a worksheet, a chart, a pivot table, a smart art object – You name it – Hold the ctrl key before you click on an item and drag it. You have to release the mouse before you release the ctrl key or it doesn’t work. This simple and effective time saving technique also works in Microsoft Word and PowerPoint.
Insert Today’s Date (static): Ctrl+; (hold the Ctrl key, tap the semicolon key, release the ctrl key, exit editing mode with Ctrl+Enter)
Rationale: I don’t mind typing May 5, 2011 but I dislike typing long months like November 29, 2011. The date is static, meaning tomorrow it will not update automatically (dynamically), which occurs if you use the now() or today() functions. The Ctrl+; technique originated in Microsoft Access, but doesn’t work in Word or PowerPoint.
Autosum Shortcut: Alt + = (hold the Alt key, tap the equal symbol key, release the alt key, exit editing mode with Ctrl+Enter)
Rationale: The autosum button is used frequently and is not convenient to click. Some users will just type the formula as =Sum( and then select a range of cells. To use the Alt+= keyboard shortcut, select the cell where you want the answer to appear, then press Alt+=. Autosum does a lazy search looking up, then down, then left, then right for numbers to add. Note that formatting like borders on cell may prevent or help autosum from selecting the correct range of cells to add on its own.
Another great autosum trick: Select the cells you want to add then press Alt+=. Autosum places the answer in the next open cell below the data. If you also select the empty column to the left or right, this will sum both rows and columns simultaneously.
Swap/Insert Between: Hold the Shift Key when dragging
Rationale: When you need to swap rows or columns, most users would insert a new row/column as needed, cut and paste their data, then delete the original duplicate. That’s great if you like clicking five times more than necessary! Instead, hold the Shift key, drag the border of a selection, release the mouse before you release the shift key.
Glenn Harris is a Microsoft Office Master Instructor and Microsoft Certified Trainer with 25 years of Microsoft Excel experience, fourteen years of training experience, enjoys sharing his time-saving Advanced Excel keyboard techniques. He teaches group and private classes at Excel Class, conveniently located across the street from Penn Station in New York City.