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	CTRL+SHFT+@	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.