Advanced Excel Shortcut keys
In the previous section, we covered the generic Excel shortcut keys. But Excel is very vast, and so are their shortcuts. Advanced Excel tools and features are widely used in analysis statistical and data manipulation tasks. In this tutorial, we will take a deeper look at all the shortcut keys that will help us quickly work with different advanced topics.
1. Shortcut keys to format your Excel data
Formatting in Excel is used to change your data’s appearance and make it look more visually pleasing. To make it easy Excel has provided some shortcuts that are listed below:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | ALT+’ (apostrophe) | This shortcut will open the formatting Style dialog window. |
| 2 | CTRL+1 | This shortcut will open the Format Cells dialog window. |
| 3 | CTRL+SHFT+~ | This shortcut will apply the General number format to the specified cell(s). |
| 4 | CTRL+SHFT+$ | This shortcut will apply the Currency format to your specified cell(s) with two decimal places (negative value are put in parentheses). |
| 5 | CTRL+SHFT+% | This shortcut will apply the Percentage format to the specified cell(s) with no decimal places. |
| 6 | CTRL+SHFT+^ | This shortcut will apply the Exponential number format to the selected cell(s) with two decimal places. |
| 7 | CTRL+SHFT+# | This shortcut will apply the Date format with the dd/mm/yyyy (day, month, and year). |
| 8 | [email protected] | This shortcut will apply the Time format to the selected cell(s) with the hour and minute, and AM or PM. |
| 9 | CTRL+SHFT+! | This shortcut will implement the Number format to the selected cell(s) with two decimal places, thousands separator, and minus sign (-) for negative values. |
| 10 | CTRL+B | This shortcut will apply or remove the bold formatting from the selected cell(s). |
| 11 | CTRL+I | This shortcut will apply or remove italic formatting from the selected cell(s). |
| 12 | CTRL+U | This shortcut will apply or remove underlining from the selected cell(s). |
| 13 | CTRL+5 | This shortcut will apply or remove strikethrough from the selected cell(s). |
| 14 | CTRL+9 | This shortcut will hide the selected rows from your Excel worksheet. |
| 15 | CTRL+SHFT+( (opening parenthesis) | This shortcut will unhide any hidden rows within the selected cell(s). |
| 16 | CTRL+0 (zero) | This shortcut will hide the selected columns from your Excel worksheet. |
| 17 | CTRL+SHFT+) (closing parenthesis) | This shortcut will unhide any hidden columns within the selection. |
| 18 | CTRL+SHFT+& | This shortcut will implement the outline border to the selected cells. |
| 19 | CTRL+SHFT+_ | This shortcut will exclude the outline border from the selected cells. |
2. Shortcut keys to access and work with multiple national languages
Excel enables the users to work with multiple national languages. Let’s explore the shortcut that will quickly help you to work different languages:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | CTRL+RIGHT SHFT | To shift your Excel text to the right-to-left paragraph direction. Note: To use this shortcut, your text must only contain neutral characters. |
| 2 | CTRL+LEFT SHFT | To shift your Excel text to left-to-right paragraph direction. Note: To use this shortcut, your text must only contain neutral characters. |
| 3 | ALT+SHFT+UP ARROW | To move the pointer into the phonetic models in Japanese text (for which you’ve displayed the phonetic guides). |
| 4 | ALT+SHFT+DOWN ARROW | To shift the Excel pointer from the phonetic guides back to the original string of characters. |
| 5 | NUM LOCK, ALT+ NUMERIC PAD NUMBERS | To enter a Unicode character in your Excel worksheet. |
| 6 | ALT+X | This shortcut converts the numbers to the characters if pressed immediately after entering the hexadecimal code for a Unicode character. If you press the shortcut immediately after a Unicode character, it converts the character to its hexadecimal code. |
3. Shortcut keys to send e-mail messages
Excel provides a feature where you can send your file as the body of an email message. As an added advantage, Excel has listed some shortcuts keys as well that will fasten the mailing process:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | SHFT+TAB | When cell A1 is selected, moves to the Introduction box in the e-mail message header. In the message header, moves to the Subject, Bcc (if displayed), Cc, To, and From (if displayed) boxes, then to the address book for the Bcc, Cc, To, and From boxes, and |
| 2 | ALT+S | To send the e-mail message to the specified email address(s). |
| 3 | CTRL+SHFT+B | To launch the Address Book. |
| 4 | ALT+O | To launch the Options menu for access to the Options, Bcc Field, and From Field commands. |
| 5 | ALT+P | Opens the Outlook Message Options dialog box (Options menu, Options command). |
| 6 | ALT+K | Checks the names in the To, Cc, and Bcc boxes against the Address Book. |
| 7 | ALT+PERIOD | To launch the Address Book for the ‘To’ panel. |
| 8 | ALT+C | To launch the Address Book for the Cc panel. |
| 9 | ALT+B | To launch the Address Book for the Bcc panel If the Bcc panel is shown. |
| 10 | ALT+J | To move to the Subject box. |
| 11 | CTRL+SHFT+G | Creates a message flag. |
| 12 | ALT+A | Adds interactivity to the range or sheet being sent. |
4. Use the Border tab in the Format Cells dialog box
When you open the Format cells dialog box, you frequently use the border tab. Let’s explore Excel shortcut keys that will assist you in applying or deleting different border options:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | ALT+T | To apply or eliminate the top border from the selected cell(s). |
| 2 | ALT+B | To apply or eliminate the bottom border from the selected cell(s). |
| 3 | ALT+L | To Apply or eliminate the left border from the selected cell(s). |
| 4 | ALT+R | To Apply or eliminate the right border. |
| 5 | ALT+H | To apply or eliminate the horizontal divider if you select cells from multiple rows. |
| 6 | ALT+V | To apply or eliminate the vertical divider if you select cells in multiple columns. |
| 7 | ALT+D | To apply or eliminate the downward diagonal border from the selected cell(s). |
| 8 | ALT+U | To apply or eliminate the upward diagonal border. |
5. Use data forms (Data menu, Form command)
While working advanced Excel, data forms are one of the commonly used features Excel users use to add, edit and delete records (rows) and later display those records in your worksheet. Below given are shortcut keys that will make this experience easier:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | DOWN ARROW | To move your cursor to the same field in the next record. |
| 2 | UP ARROW | To move your cursor to the same field in the previous record. |
| 3 | TAB and SHFT+TAB | To move to each field in the record, then to each command button. |
| 4 | ENTER | To move to the initial field in the next record. |
| 5 | SHFT+ENTER | To move to the initial field in the previous record. |
| 6 | PAGE DOWN | To move to the same field 10 records forward. |
| 7 | CTRL+PAGE DOWN | To start a new, blank record. |
| 8 | PAGE UP | To move to the same field 10 records back. |
| 9 | CTRL+PAGE UP | To move to the initial record. |
| 10 | HOME or END | To move to the beginning or end of a field. |
| 11 | SHFT+END | To extend selection to the end of a field. |
| 12 | SHFT+HOME | To extend selection to the beginning of a field. |
| 13 | LEFT ARROW or RIGHT ARROW | To move one character left or right within a field. |
| 14 | SHFT+LEFT ARROW | To select the character to the left within a field. |
| 15 | SHFT+RIGHT ARROW | To select the character to the right within a field. |
6. Shortcut keys to Filter ranges (Data menu, AutoFilter command)
Filtering becomes a powerful Excel feature when you work with huge Excel data. Below given are shortcut keys that will help you to filter ranges in Excel:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | ALT+DOWN ARROW | This shortcut shows the AutoFilter list for the selected column in the cell that includes the drop-down arrow. |
| 2 | DOWN ARROW | This shortcut selects the next element in the AutoFilter Excel list. |
| 3 | UP ARROW | This shortcut selects the previous item in the AutoFilter list. |
| 4 | ALT+UP ARROW | This shortcut terminates the AutoFilter list for the selected column. |
| 5 | HOME | This shortcut selects the first element in the AutoFilter list. |
| 6 | END | This shortcut selects the last element in the AutoFilter list. |
| 7 | ENTER | This shortcut selects filters the Excel range based on the element selected from the AutoFilter list. |
7. Create charts and select chart elements
A chart is a powerful Excel tool that helps the user to communicate data graphically. Charts represent numbers visually, and it displays the comparisons and trends in an easy format. To make it easier, Excel has listed some shortcuts keys that are as follows:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | F11 or ALT+F1 | To create a chart of the data in the current range. |
| 2 | CTRL+PAGE DOWN | To select a chart sheet: selects the next sheet in the workbook, until the chart sheet you want is selected. |
| 3 | CTRL+PAGE UP | To select a chart sheet. |
| 4 | DOWN ARROW | To select the previous group of elements in an Excel chart. |
| 5 | UP ARROW | To select the next group of elements in an Excel chart. |
| 6 | RIGHT ARROW | To select the next element within a group. |
| 7 | LEFT ARROW | To select the previous element within a group. |
8. Shortcut keys to show, hide, and outline Excel data
Below given are shortcut keys that will quickly help to show, hide and outline data in your Excel worksheet:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | ALT+SHFT+RIGHT ARROW | To group rows or columns in Excel worksheet. |
| 2 | ALT+SHFT+LEFT ARROW | To ungroups rows or columns in Excel worksheet. |
| 3 | CTRL+8 | To show or hide the outline symbols in Excel worksheet. |
| 4 | CTRL+9 | To hide the selected rows in Excel worksheet. |
| 5 | CTRL+SHFT+( (opening parenthesis) | To unhide any hidden rows within your selected range in Excel worksheet. |
| 6 | CTRL+0 (zero) | To hides the selected columns in Excel worksheet. |
| 7 | CTRL+SHFT+) (closing parenthesis) | To unhide any hidden columns within your selected range in Excel worksheet. |
9. Shortcut keys to display and hide items in an Excel field
This category presents all the shortcut keys useful to display and hide items in Excel:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | ALT+DOWN ARROW | To show the drop-down list for a field in a PivotTable or PivotChart report. |
| 2 | UP ARROW | To select the previous element in the specified range. |
| 3 | DOWN ARROW | To select the next element in the specified range. |
| 4 | RIGHT ARROW | To display the lower-level elements. |
| 5 | LEFT ARROW | To hide the lower-level elements. |
| 6 | HOME | To select the first visible element in the given Excel list. |
| 7 | END | To select the last visible element in the given Excel list. |
| 8 | ENTER | To terminate the list and show the selected elements. |
| 9 | SPACEBAR | To check, double-check, or delete a check box in the give Excel list. |
| 10 | TAB | To switches between the list, the OK button, and the Cancel button. |
10. Shortcut keys to use the PivotTable and PivotChart Wizard – Layout dialog box
Pivot Table and PivotChart Wizard are powerful Excel tools used to summarize a large set of data quickly. Below given are shortcut keys that will quickly help to use the PivotTable and PivotChart Wizard – Layout dialog box in Excel worksheet:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | UP ARROW or DOWN ARROW | To select the previous or next field button in the list on the right. |
| 2 | LEFT ARROW or RIGHT ARROW | With two or more columns of field buttons, selects the button to the left or right. |
| 3 | ALT+R | To shift the selected field into the Row area. |
| 4 | ALT+C | To shift the selected field into the Column area. |
| 5 | ALT+D | To shift the selected field into the Data area. |
| 6 | ALT+P | To shift the selected field into the Page area. |
| 7 | ALT+L | To show the PivotTable Field dialog box for the selected field. |
11. Shortcut keys to change the layout of an Excel report
This category includes the shortcuts that will help you to change the layout of an Excel report:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | CTRL+SHFT+* (asterisk) | This shortcut selects an entire PivotTable report at once. |
| 2 | ALT+SHFT+RIGHT ARROW | This shortcut groups the selected elements in a PivotTable field. |
| 3 | ALT+SHFT+LEFT ARROW | This shortcut ungroups the grouped elements in a PivotTable field. |
12. Shortcut keys to work with speech recognition and text-to-speech
This category includes the shortcuts that will help you to work with speech recognition and text-to-speech conversion in Excel:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | CTRL | To switch between dictation and command mode in Excel. |
| 2 | ESC | To escape reading when text is already being read aloud. |
13. Excel shortcut keywords to access and use macros
Excel macro is a set of actions recorded and saved with a name to run as many times as the user wants. Macros help Excel users to save time on repetitive tasks such as data manipulation and generating data reports. So getting your hand on macros shortcut keys will help you to access and use macros faster:
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | ALT+F8 | To show the Macro dialog box. |
| 2 | ALT+F11 | To show the VBA or Visual Basic Editor. |
| 3 | CTRL+F11 | To insert a Microsoft Excel 4.0 macro sheet. |
14. Shortcut keys to access and work with VBA (Visual Basic for Application)
VBA or Visual Basic for Application is an advanced Excel feature used to automate any task. Knowing VBA shortcuts will give you an edge, make the job faster, and ultimately increase overall productivity.
Note: Open the VBA Editor window to apply and use the below shortcuts.
| S.NO | Shortcut | Command |
|---|---|---|
| 1 | F2 | This shortcut displays Object Browser dialog window in Visual Basic Editor (VBA). |
| 2 | F4 | This shortcut launches the properties dialog window in Visual Basic Editor (VBA). |
| 3 | F5 | This shortcut runs Sub/ Form or macro in VBA. |
| 4 | F6 | This shortcut switches to split windows in Visual Basic Editor (VBA). |
| 5 | F7 | This shortcut displays the code window in VBA. |
| 6 | F9 | This shortcut toggles the breakpoint in VBA. |
| 7 | F10 | This shortcut activates the VBA menu bar |
| 8 | SHFT + F2 | This shortcut displays the definition window in VBA. |
| 9 | SHFT + F7 | This shortcut is used to view the used objects in your VBA code. |
| 10 | Shift + F10 | This shortcut shows the right-click menu options. |
| 11 | Alt + F4 | This shortcut terminates the VBE. |
| 12 | Alt + F6 | This shortcut switches between the previous two Windows |
| 13 | Alt + F11 | This shortcut returns to the Application |
| 14 | Ctrl + Shift + F2 | This shortcut moves to the last position |
| 15 | Ctrl + Shift + F9 | This shortcut step out of the current window. |
| 16 | Insert | This shortcut toggles insert mode |
| 17 | Home | This shortcut moves the cursor to the start of the line. |
| 18 | End | This shortcut moves the cursor to the last of the line. |
| 19 | Enter | This shortcut inserts a new line. |
| 20 | Shift + Insert | This shortcut pastes the content from the clipboard to your VBA editor window. |
| 21 | Alt + Spacebar | This shortcut shows the System menu. |
| 22 | Alt + Tab | This shortcut cycles between different applications. |
| 23 | Alt + Backspace | This shortcut undoes the changes. |
| 24 | Ctrl + E | This shortcut exports the VBA module. |
| 25 | Ctrl + I | This shortcut turns On the VBA ‘quick info’ |
| 26 | Ctrl + J | This shortcut lists all the properties or methods of your VBA code. |
| 27 | Ctrl + L | This shortcut shows the call stack. |
| 28 | Ctrl + M | This shortcut imports a file from another location to VBA. |
| 29 | Ctrl + N | This shortcut adds a new line in your VBA code. |
| 30 | Ctrl + R | This shortcut opens project explorer window in VBA |
| 31 | Ctrl + T | This shortcut shows all the available components of VBA. |
| 32 | Ctrl + Y | This shortcut cuts the complete line of your VBA code. |
| 33 | Ctrl + Insert | This shortcut copies contents of your VBA code to clipboard. |
| 34 | Ctrl + Delete | This shortcut deletes the word present on the right of your cursor. |
| 35 | Ctrl + Home | This shortcut moves to the top of your VBA module |
| 36 | Ctrl + End | This shortcut goes to the end of your VBA module |
| 37 | Ctrl + Left Arrow | This shortcut moves the cursor one word to the left in your VBA code. |
| 38 | Ctrl + Right Arrow | This shortcut moves the cursor one word to the right in your VBA code. |
| 39 | Ctrl + Up Arrow | This shortcut shifts to the previous VBA procedure |
| 40 | Ctrl + Down Arrow | This shortcut shifts to the next VBA procedure |
| 41 | Ctrl + Spacebar | This shortcut completes the entire word (especially for formula) after typing the first few characters. |
| 42 | Ctrl + Shift + I | This shortcut displays the parameter information used in your VBA code. |
| 43 | Ctrl + Shift + J | This shortcut is used to list all the constants used in your VBA code. |
Next TopicAdjust Margins in Excel
