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.NOShortcutCommand
1ALT+' (apostrophe)This shortcut will open the formatting Style dialog window.
2CTRL+1This shortcut will open the Format Cells dialog window.
3CTRL+SHFT+~This shortcut will apply the General number format to the specified cell(s).
4CTRL+SHFT+$This shortcut will apply the Currency format to your specified cell(s) with two decimal places (negative value are put in parentheses).
5CTRL+SHFT+%This shortcut will apply the Percentage format to the specified cell(s) with no decimal places.
6CTRL+SHFT+^This shortcut will apply the Exponential number format to the selected cell(s) with two decimal places.
7CTRL+SHFT+#This shortcut will apply the Date format with the dd/mm/yyyy (day, month, and year).
8CTRL+SHFT+@This shortcut will apply the Time format to the selected cell(s) with the hour and minute, and AM or PM.
9CTRL+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.
10CTRL+BThis shortcut will apply or remove the bold formatting from the selected cell(s).
11CTRL+IThis shortcut will apply or remove italic formatting from the selected cell(s).
12CTRL+UThis shortcut will apply or remove underlining from the selected cell(s).
13CTRL+5This shortcut will apply or remove strikethrough from the selected cell(s).
14CTRL+9This shortcut will hide the selected rows from your Excel worksheet.
15CTRL+SHFT+( (opening parenthesis)This shortcut will unhide any hidden rows within the selected cell(s).
16CTRL+0 (zero)This shortcut will hide the selected columns from your Excel worksheet.
17CTRL+SHFT+) (closing parenthesis)This shortcut will unhide any hidden columns within the selection.
18CTRL+SHFT+&This shortcut will implement the outline border to the selected cells.
19CTRL+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.NOShortcutCommand
1CTRL+RIGHT SHFTTo shift your Excel text to the right-to-left paragraph direction.
Note: To use this shortcut, your text must only contain neutral characters.
2CTRL+LEFT SHFTTo shift your Excel text to left-to-right paragraph direction.
Note: To use this shortcut, your text must only contain neutral characters.
3ALT+SHFT+UP ARROWTo move the pointer into the phonetic models in Japanese text (for which you've displayed the phonetic guides).
4ALT+SHFT+DOWN ARROWTo shift the Excel pointer from the phonetic guides back to the original string of characters.
5NUM LOCK, ALT+ NUMERIC PAD NUMBERSTo enter a Unicode character in your Excel worksheet.
6ALT+XThis 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.NOShortcutCommand
1SHFT+TABWhen 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
2ALT+STo send the e-mail message to the specified email address(s).
3CTRL+SHFT+BTo launch the Address Book.
4ALT+OTo launch the Options menu for access to the Options, Bcc Field, and From Field commands.
5ALT+POpens the Outlook Message Options dialog box (Options menu, Options command).
6ALT+KChecks the names in the To, Cc, and Bcc boxes against the Address Book.
7ALT+PERIODTo launch the Address Book for the 'To' panel.
8ALT+CTo launch the Address Book for the Cc panel.
9ALT+BTo launch the Address Book for the Bcc panel If the Bcc panel is shown.
10ALT+JTo move to the Subject box.
11CTRL+SHFT+GCreates a message flag.
12ALT+AAdds 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.NOShortcutCommand
1ALT+TTo apply or eliminate the top border from the selected cell(s).
2ALT+BTo apply or eliminate the bottom border from the selected cell(s).
3ALT+LTo Apply or eliminate the left border from the selected cell(s).
4ALT+RTo Apply or eliminate the right border.
5ALT+HTo apply or eliminate the horizontal divider if you select cells from multiple rows.
6ALT+VTo apply or eliminate the vertical divider if you select cells in multiple columns.
7ALT+DTo apply or eliminate the downward diagonal border from the selected cell(s).
8ALT+UTo 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.NOShortcutCommand
1DOWN ARROWTo move your cursor to the same field in the next record.
2UP ARROWTo move your cursor to the same field in the previous record.
3TAB and SHFT+TABTo move to each field in the record, then to each command button.
4ENTERTo move to the initial field in the next record.
5SHFT+ENTERTo move to the initial field in the previous record.
6PAGE DOWNTo move to the same field 10 records forward.
7CTRL+PAGE DOWNTo start a new, blank record.
8PAGE UPTo move to the same field 10 records back.
9CTRL+PAGE UPTo move to the initial record.
10HOME or ENDTo move to the beginning or end of a field.
11SHFT+ENDTo extend selection to the end of a field.
12SHFT+HOMETo extend selection to the beginning of a field.
13LEFT ARROW or RIGHT ARROWTo move one character left or right within a field.
14SHFT+LEFT ARROWTo select the character to the left within a field.
15SHFT+RIGHT ARROWTo 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.NOShortcutCommand
1ALT+DOWN ARROWThis shortcut shows the AutoFilter list for the selected column in the cell that includes the drop-down arrow.
2DOWN ARROWThis shortcut selects the next element in the AutoFilter Excel list.
3UP ARROWThis shortcut selects the previous item in the AutoFilter list.
4ALT+UP ARROWThis shortcut terminates the AutoFilter list for the selected column.
5HOMEThis shortcut selects the first element in the AutoFilter list.
6ENDThis shortcut selects the last element in the AutoFilter list.
7ENTERThis 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.NOShortcutCommand
1F11 or ALT+F1To create a chart of the data in the current range.
2CTRL+PAGE DOWNTo select a chart sheet: selects the next sheet in the workbook, until the chart sheet you want is selected.
3CTRL+PAGE UPTo select a chart sheet.
4DOWN ARROWTo select the previous group of elements in an Excel chart.
5UP ARROWTo select the next group of elements in an Excel chart.
6RIGHT ARROWTo select the next element within a group.
7LEFT ARROWTo 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.NOShortcutCommand
1ALT+SHFT+RIGHT ARROWTo group rows or columns in Excel worksheet.
2ALT+SHFT+LEFT ARROWTo ungroups rows or columns in Excel worksheet.
3CTRL+8To show or hide the outline symbols in Excel worksheet.
4CTRL+9To hide the selected rows in Excel worksheet.
5CTRL+SHFT+( (opening parenthesis)To unhide any hidden rows within your selected range in Excel worksheet.
6CTRL+0 (zero)To hides the selected columns in Excel worksheet.
7CTRL+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.NOShortcutCommand
1ALT+DOWN ARROWTo show the drop-down list for a field in a PivotTable or PivotChart report.
2UP ARROWTo select the previous element in the specified range.
3DOWN ARROWTo select the next element in the specified range.
4RIGHT ARROWTo display the lower-level elements.
5LEFT ARROWTo hide the lower-level elements.
6HOMETo select the first visible element in the given Excel list.
7ENDTo select the last visible element in the given Excel list.
8ENTERTo terminate the list and show the selected elements.
9SPACEBARTo check, double-check, or delete a check box in the give Excel list.
10TABTo 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.NOShortcutCommand
1UP ARROW or DOWN ARROWTo select the previous or next field button in the list on the right.
2LEFT ARROW or RIGHT ARROWWith two or more columns of field buttons, selects the button to the left or right.
3ALT+RTo shift the selected field into the Row area.
4ALT+CTo shift the selected field into the Column area.
5ALT+DTo shift the selected field into the Data area.
6ALT+PTo shift the selected field into the Page area.
7ALT+LTo 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.NOShortcutCommand
1CTRL+SHFT+* (asterisk)This shortcut selects an entire PivotTable report at once.
2ALT+SHFT+RIGHT ARROWThis shortcut groups the selected elements in a PivotTable field.
3ALT+SHFT+LEFT ARROWThis 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.NOShortcutCommand
1CTRLTo switch between dictation and command mode in Excel.
2ESCTo 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.NOShortcutCommand
1ALT+F8To show the Macro dialog box.
2ALT+F11To show the VBA or Visual Basic Editor.
3CTRL+F11To 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.NOShortcutCommand
1F2This shortcut displays Object Browser dialog window in Visual Basic Editor (VBA).
2F4This shortcut launches the properties dialog window in Visual Basic Editor (VBA).
3F5This shortcut runs Sub/ Form or macro in VBA.
4F6This shortcut switches to split windows in Visual Basic Editor (VBA).
5F7This shortcut displays the code window in VBA.
6F9This shortcut toggles the breakpoint in VBA.
7F10This shortcut activates the VBA menu bar
8SHFT + F2This shortcut displays the definition window in VBA.
9SHFT + F7This shortcut is used to view the used objects in your VBA code.
10Shift + F10This shortcut shows the right-click menu options.
11Alt + F4This shortcut terminates the VBE.
12Alt + F6This shortcut switches between the previous two Windows
13Alt + F11This shortcut returns to the Application
14Ctrl + Shift + F2This shortcut moves to the last position
15Ctrl + Shift + F9This shortcut step out of the current window.
16InsertThis shortcut toggles insert mode
17HomeThis shortcut moves the cursor to the start of the line.
18EndThis shortcut moves the cursor to the last of the line.
19EnterThis shortcut inserts a new line.
20Shift + InsertThis shortcut pastes the content from the clipboard to your VBA editor window.
21Alt + SpacebarThis shortcut shows the System menu.
22Alt + TabThis shortcut cycles between different applications.
23Alt + BackspaceThis shortcut undoes the changes.
24Ctrl + EThis shortcut exports the VBA module.
25Ctrl + IThis shortcut turns On the VBA 'quick info'
26Ctrl + JThis shortcut lists all the properties or methods of your VBA code.
27Ctrl + LThis shortcut shows the call stack.
28Ctrl + MThis shortcut imports a file from another location to VBA.
29Ctrl + NThis shortcut adds a new line in your VBA code.
30Ctrl + RThis shortcut opens project explorer window in VBA
31Ctrl + TThis shortcut shows all the available components of VBA.
32Ctrl + YThis shortcut cuts the complete line of your VBA code.
33Ctrl + InsertThis shortcut copies contents of your VBA code to clipboard.
34Ctrl + DeleteThis shortcut deletes the word present on the right of your cursor.
35Ctrl + HomeThis shortcut moves to the top of your VBA module
36Ctrl + EndThis shortcut goes to the end of your VBA module
37Ctrl + Left ArrowThis shortcut moves the cursor one word to the left in your VBA code.
38Ctrl + Right ArrowThis shortcut moves the cursor one word to the right in your VBA code.
39Ctrl + Up ArrowThis shortcut shifts to the previous VBA procedure
40Ctrl + Down ArrowThis shortcut shifts to the next VBA procedure
41Ctrl + SpacebarThis shortcut completes the entire word (especially for formula) after typing the first few characters.
42Ctrl + Shift + IThis shortcut displays the parameter information used in your VBA code.
43Ctrl + Shift + JThis shortcut is used to list all the constants used in your VBA code.