Those people who regularly work with Excel spreadsheets need to perform the same actions often. To automate your actions, you can use two methods. The first is the assignment of keyboard shortcuts on the Quick Access Toolbar. The second is the creation of macros. The second method is more complicated, since you need to understand the program code to write macros. The first method is much simpler, but we need to talk more about how to put the required tools on the quick access panel.
Most Useful Keyboard Shortcuts in Excel
You can create hot keys yourself, but this does not mean that they will be as useful as possible. The program has already built-in many key combinations, certain commands, with which you can perform various actions.. The whole variety of available shortcuts can be divided into several groups depending on their purpose. Quick commands for data formatting:
- CTRL+T – using this key combination, you can create a separate worksheet from one cell and a selected range of cells around it.
- CTRL+1 – Activates the Format Cells From Table dialog box.
A separate group of quick commands for formatting data can be distinguished by CTRL + SHIFT combinations with additional characters. If you add% – change the format to percentages, $ – activate the monetary format, ; – setting the date from the computer, ! – set the number format, ~ – enable the general format. Standard set of keyboard shortcuts:
- CTRL + W – through this command, you can instantly close the active workbook.
- CTRL+S – save the working document.
- CTRL+N – create a new working document.
- CTRL+X – Add content from the selected cells to the clipboard.
- CTRL+O – open a working document.
- CTRL + V – using this combination, data from the clipboard is added to the cell marked in advance.
- CTRL+P – opens a window with print settings.
- CTRL+Z is a command to undo an action.
- F12 – this key saves the working document under a different name.
Commands for working with various formulas:
- CTRL+ ‘ – copy the formula that is in the cell above, paste it into the marked cell or line for formulas.
- CTRL+ ` – using this command, you can switch the display modes of values in formulas and cells.
- F4 – this key allows you to switch between different options for references in formulas.
- Tab is a command for automatic completion of a function name.
Data entry commands:
- CTRL+D – using this command, you can copy the content from the first cell of the marked range, adding it to all cells below.
- CTRL+Y – if possible, the command will repeat the last action performed.
- CTRL+; — adding the current date.
- ALT+enter Enters a new line inside a cell if edit mode is open.
- F2 – change the marked cell.
- CTRL+SHIFT+V – Opens the Paste Special docker.
Data View and Navigation:
- Home – with this button you can return to the first cell on the active sheet.
- CTRL+G – brings up the window “Transition” – Go To.
- CTRL+PgDown – using this command, you can go to the next worksheet.
- CTRL+END – Instant move to the last cell of the active sheet.
- CTRL+F – This command brings up the Find dialog box.
- CTRL+Tab – switch between workbooks.
- CTRL+F1 – Hide or show the ribbon with tools.
Commands for selecting data:
- SHIFT+Space – keyboard shortcut to select an entire line.
- CTRL+Space is a keyboard shortcut to select an entire column.
- CTRL+A – a combination to select the entire worksheet.
Important! One of the useful commands is to select a range of cells that contain any data, the user is actively working with them. However, compared to other combinations, it consists of two parts. First you need to press Ctrl + Home, then press the combination Ctrl + Shift + End.
How to assign hotkeys to create your own set
You cannot create your own shortcut keys in Excel. This does not apply to macros, for writing which you need to understand the code, correctly put them on the quick access panel. Because of this, only the basic commands that were described above are available to the user. From the key combinations, you need to select those commands that are used or will be used very often. After that, it is desirable to add them to the quick access panel. You can take any tool from various blocks into it, so as not to look for it in the future. The process of assigning hotkeys consists of several steps:
- Open the quick access toolbar by clicking the down arrow icon, which is located above the main toolbar.
- A settings window should appear on the screen for assigning, changing keyboard shortcuts. Among the proposed commands, you need to select “VBA-Excel”.
- After that, a list should open with all the commands available to the user that can be added to the quick access panel. From it you need to choose what interests you the most.
After that, the shortcut key for the selected command will appear on the shortcut bar. In order to activate the added command, the easiest way is to click on it with LMB. However, there is another way. You can use a key combination, where the first button ALT, the next button is the command number, as it counts in the shortcut bar.
Advice! It is not recommended to assign a keyboard shortcut in the default Quick Access Toolbar. This is due to the fact that each person needs their own commands, which will not be assigned by the program itself in the standard version.
When keyboard shortcuts are assigned, it is recommended to practice activating them not with the mouse, but with a combination of buttons starting with ALT. This will help you save time on repetitive tasks and get the job done faster.