A few lesser-known, but pretty useful Keyboard Shortcuts in Excel are explained in this post.
1. Legacy shortcut for Creating Table
Ctrl + T is a very popular Excel shortcut that is used to create Tables. But there is one more shortcut for creating a Table in Excel and that is Ctrl + L.
Select the data and press Ctrl + L to activate the Create Table dialog.
Excel Table was previously known as List and this shortcut comes from that old name (L for List).
2. Activate Function Arguments dialog
To activate the Function Arguments dialog related to an Excel function, type in the function name, and press Ctrl + A.
This dialog box contains the definition of each function argument and is helpful for those who are new to Excel functions.
3. Insert Arguments of a Function
Type in the name of the function and press Ctrl + Shift + A to insert the arguments of a function.
4. Move through Sheet tabs
To select the current Sheet Tab press the function F6. You will see a Green Rectangle around the current sheet tab.
Use the Right/Left Arrow keys to move to the Next/Previous sheets and press the Enter key to select a sheet.
5. Activate Excel Name Box
Alt + F3 is the shortcut to activate Excel Name Box.
Once the Excel Name box is activated you can type in the address of a Cell or a Data range and press the Enter key to select it.
6. Insert Multiple Sheets
Shift + F11 is the shortcut to insert a New Worksheet into an Excel workbook. Interestingly, when you select ‘n‘ number of sheets and press Shift + F11, ‘n’ new sheets will be added.
In the following example, I have selected 4 sheets and used Shift + F11 to add 4 new sheets.
7. Toggle through matches
Once you use the Find dialog to find a match, you can close the dialog and use the shortcut Shift + F4 to select the next matching values. Similarly, Ctrl + Shift + F4 will take you through previous matches.
8. Evaluate formula in parts
The function key F9 can be used to evaluate an Excel formula in parts. You can make use of this feature to debug formulas that aren’t working.
9. View the Active Cell
When you have multiple cells selected and the active cell is off-screen, use the shortcut Ctrl+ BackSpace to see the active cell.
This is one of my fav. shortcuts while working with bigger data sets and here is a practical use of this shortcut.
Suppose you are creating a VLOOKUP formula. The second argument for this formula is a table_array with 150 rows. After selecting the table_array you don’t need to scroll up manually. You can use the shortcut Ctrl + BackSpace to go back to the position containing the formula.
10. Open the Options Menu
Alt + ↓ (Down arrow) is an Excel shortcut that comes handy in many situations. This shortcut will bring up the Drop-down menu of cells containing Data Validation Lists, the Filter drop-down menu of column headers, and the AutoFill list of unique values for cells without data validation lists.
Data validation lists
Filter Drop down of Column headers
Auto fill list for Cells without Data Validation Lists
11. Activate Data Entry Form
Excel has an in-built Form which makes Data Entry easier. Maybe due to space constraints or some other reasons, this feature isn’t included in the Excel ribbon.
But we can use the keyboard shortcut Alt + D + O to activate this in-built Data Form of Excel.