Javatpoint Logo
Javatpoint Logo

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.






Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA