Excel Shortcut Keys and Implementations Contents
Introduction What exactly is Microsoft Excel? Excel Shortcuts are Required Chapter 1: Working in Microsoft Excel Creating Wоrkbооkѕ How to create a new workbook Chapter 2: Cell Formatting Shortcut Keys Chapter 3: Row and Column Formatting Shortcut Keys Chapter 4: Pivot Table Shortcut Keys Chapter 5: How to Quickly Get or Create ANY HotKey in MS Excel Why you need to create hotkeys Best Approach to Keyboard Shortcuts In Excel Terminology used in this book Ribbon Keyboard Shortcuts for Excel Excel Key Tips Quick Access Toolbar Keyboard Shortcuts And Creating Custom Keyboard Shortcuts How To Create Custom Keyboard Shortcuts For Excel Using The Quick Access Toolbar Dialog Box Keyboard Shortcuts For Excel Other Ways Of Navigating The Excel Interface Using The Keyboard Chapter 6: Develop and Implement Short Cuts in Excel with Macros Show The Developer Tab What You Need For Creating Excel Macros Lets Create Your First Macro Click on the Developer tab. Record a macro Save the Macro Working with recorded macros in Excel Macro Keyboard Shortcuts Chapter 7: List of 300+ Excel ShortCuts Charts and Graphs Data Display Formatting Formulas And Functions General
Workbooks And Worksheets Introduction You may wonder if it is possible to automate some of the more repetitive tasks or make navigating Microsoft Excel easier as you become more familiar with the software. Fortunately, there are a plethora of Excel shortcuts available to assist you in accomplishing this and more. Knowing and implementing these shortcuts in your daily life will help you save time and optimize your workflow. Users frequently use these shortcuts and find them far more intuitive than constantly twiddling a mouse. In addition, those who have mobility or vision issues will appreciate shortcuts because they can be easier to use than touchscreen devices. Whether you're a student, a data analyst, or someone who uses Excel regularly, you've probably developed a list of shortcuts that you frequently employ. This guide will pave the way to new Excel shortcuts that you can add to your toolbox and a reference point so you don't have to memorize shortcuts that you might not use as frequently. Microsoft Exсеl is a valuable and powerful program for data analysis and documentation. It is a рrеаdhееt program with many columns and rows, where each intеrsесtоn of a column and a row is a сеll. Each cell contains a single point of data or piece of information. You can easily find and automatically draw information from changing data by organizing information like this. MS Excel is an industrial spreadsheet application developed by Microsoft and distributed for Microsoft Windows and Mac. It can, among other things, do the basic calculations, use visual tools, create pivot tables, and create macros. To оrgаnzе аnd mаnрulаtе dаtа, sрrеаdhееt аррlсаtоn uсh аnd MS Exсеl use a collection of cells аrrаngеd ntо rows аnd columns. They can also show data in the form of graphs, charts, and line graphs.
MS Exсеl allows you to аrrаngе dаtа tо vеrу fасtоrs frоm difеrеnt реrsресtivеs. Exсеl applications use the Miсrоsоft VBA programming language, which allows users to create a wide range of complex numerical methods. Furthermore, programmers can directly code using the Visuаl Bаsiс Editor, which includes Wndоw for writing code, debugging, and organizing code modules. There's no denying that keyboard shortcuts make our lives easier, and if you use Microsoft Excel, you can learn some of the most useful ones with this guide. The number of Excel shortcuts available may surprise you. While we do not expect you to remember them all, it is always helpful to have a list at your fingertips. So continue reading as we list some of the most useful Microsoft Excel shortcuts for Windows to make your life easier. Many users discover that working more efficiently with an external keyboard equipped with Excel keyboard shortcuts is possible. In addition, keyboard shortcuts can be easier to use than a touchscreen for users with mobility or vision disabilities. As a result, they are an indispensable substitute for using a mouse. Nota bene: This topic contains keyboard shortcuts for the US keyboard layout. The keys on other layouts may not correspond precisely to the keys on a standard US keyboard. A plus sign (+) in a shortcut indicates that multiple keys must be pressed concurrently. A comma (,) in a shortcut indicates that multiple keys must be pressed sequentially. Microsoft Excel is widely used to store and analyze data throughout the world. Despite the availability of numerous new data analytics tools on the market, Excel remains the industry standard for working with data. It has many built-in features that make it easier to organize your data. Excel's shortcut keys allow you to work quickly on your data. This article will go over the various Excel shortcuts. These keyboard shortcuts are used to complete tasks more effectively and rapidly. What exactly is Microsoft Excel?
Although you are already familiar with Excel, let me give you a brief overview. Microsoft Excel is a data recording, analysis, and visualization application created by Microsoft. Microsoft made Excel, a spreadsheet application, in 1985. Excel data is organized into rows and columns. Excel is widely used for mathematical operations, data recording and analysis, and visualizing structured data in charts and graphs. Finally, another practical application of Excel is that it aids in automating tasks via Excel macros. Excel has a set of shortcuts that can quickly help you complete the tasks listed above. With a few simple keyboard strokes, you can perform various operations. This book takes an in-depth look at Excel shortcuts that can aid someone work more efficiently with spreadsheets. Excel Shortcuts are Required Excel includes a plethora of keyboard shortcuts designed to increase your efficiency and productivity. Instead of using a mouse to access the toolbar, two or three keystrokes perform important functions. Isn't that more convenient and time-saving? Excel shortcuts massively boost productivity and, as a result, reduces work time. No, you are not required to memorize these shortcuts. Nonetheless, it would be prudent to recall a few of them. With regular practice, you'll be able to identify the majority of the most common Excel shortcuts. After looking at the many built-in Excel shortcuts, we will learn how to create your own shortcuts in MS Excel. Finally, to make reading and studying this book more effortless, we categorized these Excel shortcuts based on their operations. You will also learn how to use Macros to create your own. However, macros are a more advanced topic. If you want to become a more advanced programmer, you will have to work with more complex materials. As a result, some training resources on this topic can be challenging. However, this does not imply that learning how to set up a macro in Excel is impossible. You will also learn this advanced Excel skill in this book. This guide includes a step-by-step example in addition to walking you through the process of creating a macro.
Chapter 1: Working in Microsoft Excel Excel is a spreadsheet application that is used to manage, analyze, and report large amounts of data. Accounting teams, for example, frequently use it for financial analysis. Nonetheless, any professional can use it to manage lengthy and unwieldy datasets. Excel applications include balance sheets, budgets, and editorial calendars. Excel is primarily used to create financial documents due to its powerful computational capabilities. Accounting offices and teams frequently utilize the software because it automatically displays sums, averages, and totals to accountants. They can easily decipher their company's data with the aid of Excel. While Excel is most commonly associated with accounting, professionals in any field, especially marketers, can gain from its functionalities and formulas because they can be used to track virtually any type of data. It saves time by eliminating the need to count cells or copy and paste performance figures. Additionally, excel frequently includes a shortcut or quick fix that speeds up the process. This section will cover the fundamentals of working with a workbook. First, we'll go over how to make a new workbook, open an existing workbook, and save a spreadsheet so you don't lose any data or calculations. Creating Wоrkbооkѕ In Microsoft Excel, a workbook is a collection of one or more spreadsheets, also known as worksheets. Below is an example of a spreadsheet named \"Sheet1\" contained within an Excel workbook file named \"Book1\". In our example, the sheet tabs \"Sheet2\" and \"Sheet3\" are also from the same workbook.
How to create a new workbook There are three ways to create a new Microsoft Excel workbook. You can open a new, blank workbook or access Backstage View using the File tab when you launch Excel. You can open an existing Excel workbook, enter new or additional data, and save the file with a new name, creating a new workbook. You can also use a template to create a new workbook. A tеmрlаtе is a mоdеl that has already been set up to display certain types of data, such as аlе rероrt, invoices, and so on. Making a Wоrkbооk оf Sсrаtсh To create a new workbook, open Exсеl and choose a blank workbook or another template type. If you're working in Exсеl and want to start a new workbook, click the Flе Tab, then Nеw, and finally Blаnk wоrkbооk. If you already have Ms Excel open, here are helpful shortcuts: Description Excel Shortcuts 1. To create a new workbook Ctrl + N 2. To open an existing Ctrl + O workbook
3. To save a Ctrl + S workbook/spreadsheet 4. To change the Saved Name F12 of the workbook 5. To close the current Ctrl + W workbook 6. To close Excel Ctrl + F4 7. To move to the next sheet Ctrl + PageDown 8. To move to the previous Ctrl + sheet PageUp 9. To go to the Data tab Alt + A 10. To go to the View tab Alt + W 11. To go the Formula tab Alt + M 12. To print spreadsheet CTRL + P 13. To close current workbook CTRL + W If you need to add a new line inside a cell, the shortcuts listed here can simplify it. The great thing about this shortcut is that it makes it simple to enter comments and longer paragraphs. Professionals who frequently enter long text entries into Excel spreadsheets will almost certainly use this shortcut daily. ALT + ENTER These are the Excel shortcuts that can assist you in navigating your spreadsheet. Once the workbook has been created, the next critical step is cell formatting.
Chapter 2: Cell Formatting Shortcut Keys Excel allows you to format individual cells in your worksheet, allowing you to change things like borders, cell color, cell margins, and so on. In addition, excel includes a few tools to help you format quickly and easily. In Excel, a cell contains all of the data that you are working with. A cell can be edited, its contents aligned, a border added to it, an outline added to all the selected cells, and many other things. Here's a sneak peek at some of these Excel shortcuts. Description Excel Shortcuts 1. To edit a cell F2 2. To copy and paste cells Ctrl + C, Ctrl + V 3. To italicize and make the Ctrl + I, Ctrl + B font bold 4. To center align cell contents Alt + H + A + C 5. To fill color Alt + H + H 6. To add a border Alt + H + B 7. To remove outline border Ctrl + Shift + _ 8. To add an outline to the Ctrl + Shift + & select cells 9. To move to the next cell Tab 10. To move to the previous Shift + Tab cell In addition to the cell formatting shortcuts mentioned above, let's look at a few more advanced cell formatting Excel shortcuts that might come in handy. We'll go over how to add a comment to a cell. Comments help provide additional information about cell content. We will also learn how to find a value in the spreadsheet and replace it with another value. Following that, we'll look at inserting the current time and date, activating a filter, and adding a hyperlink to a cell. Finally, we'll look at how to apply a format to data in a cell.
Description Excel Shortcuts To add a comment to a cell Shift + F2 To delete a cell comment Shift + F10 + D To display find and replace Ctrl + H To activate the filter Ctrl + Shift + L / Alt + Down Arrow To insert the current date Ctrl + ; To insert current time Ctrl + Shift + : To insert a hyperlink Ctrl + k To apply the currency format Ctrl + Shift + $ After you've mastered cell formatting Excel shortcuts, the next step is to learn how to work with an entire row/column in Excel.
Chapter 3: Row and Column Formatting Shortcut Keys Row and column headings, as well as worksheet cells, can be formatted in a variety of ways. Worksheet headings are divided into row headings, column headings, and the box in the top left corner of the worksheet where row and column headings intersect. This section will go over some vital row and column formatting shortcuts. We'll learn how to delete rows and columns, hide and reveal selected rows and columns, and group and ungroup rows and columns. Description Excel Shortcuts To select the entire row Shift + Space To select the entire column Ctrl + Space To delete a column Alt+H+D+C To delete a row Shift + Space, Ctrl + - To hide selected row Ctrl + 9 To unhide selected row Ctrl + Shift + 9 To hide a selected column Ctrl + 0 To unhide a selected column Ctrl + Shift + 0 To group rows or columns Alt + Shift + Right arrow To ungroup rows or columns Alt + Shift + Left arrow Now that we've gone over the various shortcut keys for formatting cells, rows, and columns, it's time to move on to a more advanced topic in Excel, namely dealing with pivot tables. First, let's take a look at the various shortcuts for summarizing your data with a pivot table.
Chapter 4: Pivot Table Shortcut Keys A pivot table is an extract or summary of your original table in Excel that contains source data. A pivot table can quickly answer questions about your table that would otherwise necessitate the use of complicated formulas. A PivotTable is a highly effective tool for calculating, summarizing, and analyzing data, allowing for the visualization of comparisons, patterns, and trends. PivotTables behave differently depending on the operating system on which Excel is installed. Description Excel Shortcuts To ungroup pivot table items Alt + Shift + Left arrow To hide pivot table items Ctrl + - To create a pivot chart on the Alt + F1 same sheet To create a pivot chart on a new F11 worksheet Excel shortcut keys will help you create reports and analyze data more quickly and effectively. After reading this article, you should better understand Excel shortcuts for the workbook, cell formatting, row and column formatting, and pivot tables. I hope you find these hacks helpful and that by working on Excel regularly, you will be able to quickly master these shortcut keys.
Chapter 5: How to Quickly Get or Create ANY HotKey in MS Excel Keyboard shortcuts are possibly one of the Excel topics that almost any user can (or should) be interested in. This is understandable. The following are some of the reasons for this: When working with Excel, keyboard shortcuts are extremely useful for increasing your speed and productivity. Suppose you don't know the correct keyboard shortcut. In that case, you'll have to lift your hand from the keyboard, reach for the mouse, and return to the keyboard instead of quickly typing the relevant key combination. Excel's most recent versions include keyboard shortcuts that enable you to access virtually every command or feature via the keyboard. You can always learn a new Excel keyboard shortcut, regardless of your level of Excel knowledge. However, given the many Excel keyboard shortcuts available, it's unlikely that you'll memorize them all. There are at least 300+ shortcuts built-in in Excel. We will compute a more extensive list after this chapter. Why you need to create hotkeys There are three main reasons why you should learn more than lists when learning keyboard shortcuts: Reason #1: No matter how comprehensive a keyboard shortcut list is, mapping absolutely all of the keyboard shortcuts available in Excel is complicated. Even massive keyboard shortcut resources may not always include the keyboard shortcut for your required item. Knowing how to find or create a keyboard shortcut on your own can be extremely useful in these circumstances. Reason #2: Keyboard shortcut lists and cheat sheets only include Excel's default keyboard shortcuts. This excludes the ability to create custom keyboard shortcuts in Excel using the Quick Access Toolbar or macros.
Reason #3: Most of us don't want or need to learn every single keyboard shortcut in Excel. Even if we tried to remember them all, only a few of us would probably succeed. Here's where this section in this Excel guide comes in handy. My goal with this section of the book is to arm you with the knowledge you need to master Excel keyboard shortcuts and become even more productive. As a result, besides explaining the various types of keyboard shortcuts available in Excel, I demonstrate some methods for creating your own custom keyboard shortcuts. Best Approach to Keyboard Shortcuts In Excel Lists and cheat sheets with keyboard shortcuts are extremely useful. However, these incredible lists can be a little overwhelming if you don't know how to approach them. More precisely, you're likely to be disappointed if you consider keyboard shortcut cheat sheets with the assumption of quickly cramming them all. This isn't necessarily a bad thing. After all, you probably don't need to know as many keyboard shortcuts as you think you do. Furthermore, some of those shortcuts may be for commands or features that you only use infrequently. And if it helps you feel better, you're not alone. You can be confident that most other Excel users (including experts) don't know all the shortcuts. As a result, before you start collecting lists of keyboard shortcuts or attempting to learn every single shortcut you come across, I have one simple suggestion: Don't overburden yourself by attempting to learn too many keyboard shortcuts at once. It's unlikely that you'll discover many keyboard shortcuts in a short period of time.
I recommend that you start slowly and learn one or two shortcuts per week. Then, learn a new shortcut once you've mastered an old one. As you gain familiarity with Excel keyboard shortcuts, you'll be able to handle more. You'll realize that your knowledge and use of keyboard shortcuts increases over time. You may be wondering how to decide which keyboard shortcuts to learn first. To determine which keyboard shortcuts to master, I recommend that you follow the two-step process outlined below: Step 1: List the commands or tasks you frequently use while working with Excel. This varies from person to person due to the variety of activities people perform with Excel. So your list of frequently used commands may differ significantly from mine. That's all right: Step 2: Look for a keyboard shortcut that will allow you to execute that specific command or task. Once you've decided which command or task you want to perform with your keyboard, you'll have two basic options for finding the corresponding keyboard shortcut: Option #1: Look through a list of keyboard shortcuts compiled by someone else. I've included a few of these lists below. Option #2: Look for a built-in keyboard shortcut or, if none are available, create your own keyboard shortcuts. This section will focus on Option #2. After this section, you will find an extensive and comprehensive shortcut list to assist you with option #1. However, depending on the type of command or task you need to execute and your own preferences, the keyboard shortcuts in such lists may not work for you (for various reasons).
Excel, on the other hand, provides several options for performing a task or executing a command using only the keyboard. Excel comes with several built-in shortcut keys, but you can also develop your own. The following section may assist you in avoiding confusion with the, at times, inconsistent terminology used by different authors when discussing keyboard shortcuts. If you aren't interested in this topic or already have a good understanding of it, please proceed to the next section, which discusses regular keyboard shortcuts. Terminology used in this book When reading about Excel keyboard shortcuts, you may come across the terms \"keyboard accelerators,\" \"Hotkeys,\" or \"Key Tips.\" There may be some disagreements about the precise meaning of each term. Without delving into more theoretical discussions about the technical purposes of each term, here's how I use them in this Excel guide: Shortcuts for the Keyboard: I generally refer to \"keyboard shortcuts\" as a combination of keyboard keys that allows you to perform a specific task that would typically require a mouse. In some contexts, the term \"keyboard accelerators\" may be used to refer to all of these combinations. In contrast, the term \"keyboard shortcuts\" is reserved for the type of keyboard shortcuts that I call regular keyboard shortcuts. Key Tips: The term \"Key Tips\" refers to specific labels or badges that Excel displays as guidance to help you find the appropriate sequence for a Ribbon keyboard shortcut or a Quick Access Toolbar keyboard shortcut. The image below depicts how Key Tips appear when they are enabled: Hotkeys: Hotkeys are similar to Key Tips in that they provide some guidance as to what key you must press to select a specific option. Ribbon Keyboard Shortcuts for Excel The Ribbon in Excel is the group of tabs and buttons visible in the upper section of the program. If you've used Excel before, you're probably aware
that the Ribbon can be used to perform the most common tasks in Excel. In fact, I'm guessing you're constantly using the Ribbon. In addition, if you're like the majority of Excel users, you probably use the mouse to interact with the Ribbon (at least most of the time). However, thanks to Key Tips, you can enter any tab or icon on the Ribbon using only the keyboard. These critical tips are depicted in the screenshot above. You don't have to hold down all the keys at the same time when using Ribbon keyboard shortcuts, as you do with regular keyboard shortcuts. In other words, you can use Ribbon keyboard shortcuts to: Press and release the first key. Press and release the second key. And keep pressing and releasing the keys that make up the keyboard shortcut. Now that you know this let's look at the three simple steps you can take to use Ribbon keyboard shortcuts. After explaining these three steps, I demonstrate how to use Ribbon keyboard shortcuts. Step 1: Enable Key Tips To enable Key Tips, press one of the following keys: Alt. F10. Remember that you do not need to hold these keys down while performing the following steps. Once you press Alt, or F10, you will see the key tips appear like in the screenshot above. Step #2: Press The Key(s) Corresponding To The Ribbon Tab You Want To Open.
After enabling Key Tips, Excel displays the keys for each Ribbon tab, from the Backstage View, that is the \"File\" tab, to the Tell Me what you want to do\" Tab. This Tell Me Tab came in Excel 2016. As a result, if you're using Excel 2013 or earlier, you won't be able to use this feature. The image below shows an Excel Ribbon with the Key Tips turned on: Excel Key Tips Key Full Keyboard Ribbon Tab Tip Shortcut To File Display Tab (Backstage View) F Alt + F Home or Insert F10 + F Page Layout H Alt + H Formulas or Data F10 + H N Alt + N or F10 + N P Alt + P or F10 + P M Alt + M or F10 + M A Alt + A
or F10 + A Review R Alt + R or F10 + R View W Alt + W or F10 + W Developer L Alt + L or F10 + L Tell me what you Q Alt + Q want to do or F10 + Q Step #3: Keep Pressing The Key(s) Corresponding To What You Want To Activate. Once you've enabled the Key Tips (as described in step #1 above), Excel will continue to display the Key Tips that are relevant to the situation you're in. That is to say: The Key Tips displayed by Excel at any given time show all of the options you have for your next step. First, you have to figure out which key you need to press to get where you want to go, and then press it. Next, excel performs one of the following functions depending on the context: Executes the command you've specified, opens the appropriate dialog box or something similar, or Displays a new set of Key Tips displaying all of your new options. A Key Tip may display two keys in some cases. In such cases, simply press both keys. For example, this means pressing \"J + C\" or \"J + A\" in the case of the Design and Format tabs mentioned above.
If Excel displays a new set of Key Tips, simply repeat step #3 (press the appropriate keyboard key) as many times as necessary until you reach your destination. Remember that you do not need to hold any key down while typing the other keys that comprise the Ribbon keyboard shortcut. Let's take a look at how this works in practice: Assume you pressed the letter H in step #2 above. This keyboard shortcut navigates to the Home tab. This appears to be the case in my version of Excel 2016. As shown in step #2 above, the Key Tips displayed by Excel no longer correspond to the Ribbon tabs. The (many) Key Tips in the image above correspond to the various Home tab buttons, dropdowns, and dialog launchers. Suppose you want to execute the Increase Indent command, for example, to move the content further away from a cell's border. In that case, you can now press the number 6. This key point is depicted in the image below: In other words, the full shortcut for the Increase Indent command using Ribbon keyboard shortcuts (or Key Tips) is \"Alt + H + 6.\" The number of keys you must press may vary. Still, the general procedure for using Ribbon keyboard shortcuts remains the same: Rule 1: Enable Key Tips by pressing Alt or F10.
Rule 2: Use Key Tips to find the key(s) that correspond to the section of the Ribbon (such as a tab or button) that you want to visit, and then press that key (or key combination). These rules allow you to access almost any command on the Ribbon by using only the keyboard. They also work to any tabs or groups of commands that you add to the Ribbon when customizing it. Quick Access Toolbar Keyboard Shortcuts And Creating Custom Keyboard Shortcuts The Quick Access Toolbar is the toolbar that by default appears in the upper left corner of Excel. By default, the Quick Access Toolbar contains only a few commands. The commands shown in the screenshot above are typical of those found in the Quick Access Toolbar: Save. Undo. Redo. The Optimize Spacing Between Commands command button may also appear if you're using a touch-enabled device. If you want to step up your game by using keyboard shortcuts, the Quick Access Toolbar may not appear to be very exciting. The Quick Access Toolbar, on the other hand, has two features that make it very appealing to Excel users who want to use more keyboard shortcuts: The Quick Access Toolbar is customizable, which is the first feature. As a result of this feature, you can add or remove commands from it. Using keyboard shortcuts, you can quickly access the controls in the Quick Access Toolbar.
This is because Key Tips (which I mentioned earlier when discussing Ribbon keyboard shortcuts) can also be used to access any command in the Quick Access Toolbar. Among the most significant implications of these features is that you can use the Quick Access Toolbar to build custom keyboard shortcuts for virtually any Excel command. Before we get into the process of creating such custom keyboard shortcuts, let's take a closer look at how Key Tips interact with the Quick Access Toolbar: As I mentioned previously, you can use Key Tips to access any command in the Ribbon. The Quick Access Toolbar is the same way. The general steps for using Quick Access Toolbar shortcuts are essentially the same as those for using Ribbon keyboard shortcuts. To be more specific, in order to use your keyboard to access command in the Quick Access Toolbar, simply follow these two simple steps: Step 1: To enable Key Tips, press Alt or F10. Step 2: Using the Key Tips displayed by Excel, find the key that corresponds to the Quick Access Toolbar item (usually a command) that you want to select, and press it. Quick Access Toolbar keyboard shortcuts have functioned similarly to Ribbon keyboard shortcuts. There is, however, a significant difference: The Quick Access Toolbar is not dependent on the currently selected Ribbon tab. As a result, irrespective of which Ribbon tab is currently active, the Quick Access Toolbar is (nearly) always visible. This has a significant impact on keyboard shortcuts: Keyboard shortcuts in the Quick Access Toolbar are shorter than those in the Ribbon. As previously stated, most Ribbon keyboard shortcuts are at least three keys long and take the following basic form: Alt or F10 is the first key.
And then the key that corresponds to the appropriate Ribbon tab. Lastly, you press the command-specific key. The shortcuts are longer and include more than three keys in a few instances. Applying cell borders, for example (as shown in the example above), necessitates the use of four keys (Alt, H, B, and A). Quick Access Toolbar shortcuts can be as few as two keys long, but they can also be much longer. The precise length of a Quick Access Toolbar shortcut is determined by two factors, which are discussed below: • The number of Quick Access Toolbar buttons on your computer. • The precise location of the pertinent command (within the Quick Access Toolbar). To understand how Quick Access Toolbar shortcuts work in practice, consider what happens when I enable key tips by pressing Alt or F10: Notice that the Quick Access Toolbar is simpler than Ribbon tabs. Only pressing a corresponding number will activate it. How To Create Custom Keyboard Shortcuts For Excel Using The Quick Access Toolbar To develop a useful keyboard shortcut through Quick Access Toolbar, you only need to do one thing: Add the appropriate command to the Quick Access Toolbar in the location that corresponds to the keyboard shortcut you want to use for that command. Excel assigns the keyboard shortcut in accordance with the rules outlined above.
You can add commands to the Quick Access Toolbar using various methods. Because this course isn't about the Quick Access Toolbar, I'll only go over one of these methods below. Step 1: Navigate to the Quick Access Toolbar tab in the Excel Options dialog box. There are several ways to access the Excel Options dialog's Quick Access Toolbar tab. I'll go over the most frequently encountered ones below: 1: When right-clicking on the Quick Access Toolbar or the Ribbon, select \"Customize Quick Access Toolbar...\" from the context menu. 2: From the Customize Quick Access Toolbar menu, select \"More Commands..\" (located at the right end of the Quick Access Toolbar).
3: Navigate to the Backstage View (File tab) and then to the left-hand side of the screen, select \"Options.\" When the Excel Options dialog box appears, click the Quick Access Toolbar tab on the left side of the window.
Step 2: Choose the command group that you wish to work with. You can customize the Quick Access Toolbar with over 1,000 commands. That's a lot of information to take in. Excel, fortunately, allows you to browse through several different subsets of commands. Follow these two steps to accomplish this: 1: Expand the Choose commands from the dropdown list in the Excel Options dialog's upper left corner. 2. Once the dropdown list has been expanded, select any of the listed subsets. Typically, the list includes the following categories or subsets: Popular Orders (shown by default), Commands Not in the Ribbon, All Commands,
Macros, commands from the Backstage View (File tab) or a specific (main) Ribbon tab (Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Developer, and Add-ins), commands from contextual tabs related to a specific tool or feature (SmartArt, Charts, Drawing, Picture, PivotTables, Header & Footer, Tables, PivotCharts, Ink, Spark). 3: Insert A Command Into The Quick Access Toolbar Once you've decided on a subset of commands to work with, the actual commands you can Choose commands from the list box to add to the Quick Access Toolbar are displayed in the Choose commands from the list box. This is located on the left side of the pane, immediately beneath the Choose commands from the dropdown list. You can add a command to the Quick Access Toolbar in the following two steps: Step 1: Select the desired command from the Choose commands from the dropdown list. For example, in the screenshot below, I selected the Fill Color command:
Step 2: In the Excel Options dialog, click the Add button in the center. Or simply double-click the Fill Color command. 4: Arrange the commands in the desired order. Once you add a function to the Quick Access Toolbar, it appears at the bottom of the list box labeled Customize Quick Access Toolbar on the right side of the Excel Options dialog. The Quick Access Toolbar now includes the location of the command. As I previously stated, the exact keyboard shortcut assigned to a specific command is determined by its location in the Quick Access Toolbar. As a result, by placing controls in the appropriate position, you can determine (within certain bounds) what the keyboard shortcut is for the commands you add to the Quick Access Toolbar. Use the Move Up and Move Down arrows on the right side of the Excel Options dialog to reposition commands in the Quick Access Toolbar. Click on the appropriate arrow as many times as necessary to position the command in the desired position.
Final: Press OK. Dialog Box Keyboard Shortcuts For Excel The information in the preceding sections of this Excel tutorial enables you to execute many commands. However, you'll quickly notice that a specific keyboard shortcut will take you to a dialog box in several cases. This section describes how to interact with most dialog boxes in Excel using keyboard shortcuts. In general, Hotkeys can be used to interact with dialog boxes in Excel. At the start of this Excel tutorial, I discuss Hotkeys. Hotkeys are generally identifiable within a dialog box because Excel highlights the corresponding letter. Working with Hotkeys is simple and, in some ways, similar to working with the Key Tips that assist you with Ribbon and Quick Access Toolbar keyboard shortcuts. Let's begin by looking at a dialog box and its Hotkeys. The Format Cells dialog box is shown in the screenshot below, with the Alignment tab selected. The arrows in this dialog box point to some of the underlined letters.
When working with a dialog box and encountering such a situation, keep the following in mind: To use keyboard shortcuts to select a command, press the relevant Hotkey. In some cases, you must press the appropriate Hotkey along with the Alt key. In other words, the keyboard shortcut could be \"Alt + Hotkey.\" The following are some examples of when you won't need to press the Alt key alongside the Hotkey:
If the dialog box doesn't have tabs and (ii) the currently selected option isn't a dropdown list or a spinner control. For example, the Paste Special dialog box shown at the start of this blog post lacks tabs, dropdown lists, and spinner controls. If the dialog box you're working with has tabs. Still, the currently selected option is within the Tab where the relevant Hotkey appears. So the currently selected option isn't a box or a spinner control. In other words, just because the displayed Tab is the one you want to work with isn't enough. The currently selected option must be one of the Tab's commands. Even though it is not necessary in these cases, you can press the Alt key at the same time as the relevant Hotkey. Typically, the result is identical, and Excel navigates to the same option as if you had not pressed the Alt key. If the conditions listed above are not met, you must generally press the Alt key at the same time as the Hotkey. In other words, you usually press the Alt key when you want to: A list box or spinner control is the currently selected option within a dialog box. The dialog box has tabs, and the currently selected option isn't in the Tab that contains the relevant Hotkey. In some cases, determining which option is currently selected can be difficult. Most of the time, especially if the current selection option is within the displayed Tab, Excel displays a dotted square around the current selection option. In the screenshot below, for example, \"Merge cells\" is the currently selected option:
In this case, if you want to wrap text, all you have to do is press W because the group is already selected. However, if that Merge cells has no dotted box around it, you will have to press Alt + W to activate the Wrap cells. Most Excel dialog boxes have clearly labeled Hotkeys for almost all of their commands. However, you might notice that not all options in a Dialog Box have an underlined letter. In fact, several of the dialog boxes I show in this Excel tutorial have at least one such option, where no letter is highlighted. The absence of an underlined letter does not preclude you from using your keyboard to interact with such an option.
To see how this works, return to the Excel Options dialog I mentioned earlier when discussing how to build custom keyboard shortcuts by configuring the Quick Access Toolbar: To customize the Quick Access Toolbar using the method described in this Excel tutorial, open the Excel Options dialog and navigate to the Quick Access Toolbar tab. I describe several approaches, including the two keyboard shortcuts listed below: Alt + T + O + Q. Alt + F + T + Q. Each of these keyboard shortcuts' first three components opens the Excel Options dialog box. In other words, the keyboard shortcuts \"Alt + T + O\" and \"Alt + F + T\" cause Excel to display the Excel Options dialog. When in the Excel Options dialog box, pressing Q takes you to the Quick Access Toolbar tab. Despite the absence of an underlining letter guide, the majority of the tabs in the Excel Options dialog can be accessed by typing the first letter of the label. In other words, the letter Q opens the Quick Access Toolbar table: The letter G navigates to the General Tab, resulting in the full keyboard shortcut \"Alt + T + O + G\" or \"Alt + F + T + G.\" The Formulas tab is displayed by the letter F. As a result, the complete keyboard shortcut is \"Alt + T + O + F\" or \"Alt + F + T + F.\"... and so on. The same guidelines apply to other dialog boxes. Return to the Alignment tab of the Format Cells dialog box, which I used in the preceding examples. It's worth noting that the tab labels lack underlined letters:
In such cases, if the currently selected option is not within the displayed Tab, you can navigate to a different tab by pressing the first letter of the label. That is to say: The Number tab is displayed when you press N. As a result, the full keyboard shortcut for accessing the Number tab is \"(Ctrl + 1) + N.\" As an aside, \"Ctrl + 1\" is a primary keyboard shortcut for bringing up the Format Cells dialog box. For example, the letter F navigates to the Font tab. In this case, a full keyboard shortcut is \"(Ctrl + 1) + F.\" This is already quite specific, so you may be surprised that it does not cover all of the possible options. For example, you may eventually come across the following scenario:
When the first letter of two commands is the same. When there are no underlined letters (as explained in the previous section), the rule for using keyboard shortcuts within a dialog box is relatively simple: Press the first letter of an option's label to select it. If two labels begin with the same letter, however, this rule does not apply to all possible combinations. After all, a single key should not produce two distinct results. Let's return (once more) to the left-side pane of the Excel Options dialog to see how this can happen. It's worth noting that two labels (Advanced and Add-ins) start with the same letter (A): The letter A serves as a keyboard shortcut for the first Tab in this case (Advanced). In other words, the keyboard shortcuts \"Alt + T + O + A\" or \"Alt + F + T + A\" open the Excel Options dialog’s Advanced tab. To access the Add-ins tab, you must first press the label's first key twice. In other words, the following two are full keyboard shortcuts to the Excel Options dialog's Add-ins tab: Alt + T + O + A + A or Alt + F + T + A + A Other Ways Of Navigating The Excel Interface Using The Keyboard The preceding sections of this Excel tutorial covered keyboard shortcuts that enable you to work with a wide variety of commands, options, and features. However, there may be times when you prefer (or are required) to navigate through Excel's interface using other (more common) navigation keys, such as the arrow keys.
The following are some general rules and examples of how to use the keyboard to navigate through Excel's interface. Alt and \"Alt + Down Arrow\": When a dropdown list is selected, you can expand it by pressing \"Alt + Down Arrow.\" The use of Alt alone closes (or contracts) the expanded dropdown list. On the other hand, some dropdown lists do not require you to press \"Alt + Down Arrow\" to expand them. Instead, you can expand them by pressing the Down Arrow alone. The Font Face dropdown list in the Home tab of the Ribbon is one example of such a list. An example of a keyboard shortcut for expanding an Excel list Arrow Keys: The arrow keys can be used to navigate various situations. For instance, arrow keys enable you to move to the next (right) or previous (left) option or Tab inside the Ribbon. Within a dropdown list, arrow keys assist you in moving between the various items. \"Ctrl + Tab\" and \"Ctrl + Shift + Tab\": When working with a dialog box with multiple tabs, \"Ctrl + Tab\" navigates to the next tab. \"Ctrl + Shift + Tab\" navigates you to the previous tab in the opposite direction. Enter: Inside a dialog box, Enter typically performs the action that corresponds to the dialog box's default command button. For example, in the Excel Options and Format Cells dialog boxes, pressing Enter is equivalent to pressing the OK button. Esc: When working with a menu, sub-menu, dialog box, or message window, pressing Esc cancels the commands and closes the corresponding menu, sub-menu, dialog, or window. Esc can also be used to return to the previous \"level\" of the shortcut. Excel, for example, displays the Key Tips for the Home tab of the Ribbon when you use the Ribbon keyboard shortcut \"Alt + H.\" Home/End keys: When a menu or submenu is visible, use the Home or End key to pick the first or last command inside the menu or sub-menu. Spacebar: When working with a dialog box, you can use the Spacebar to perform the action associated with the selected button or to select/clear a
specific checkbox. When working with the Ribbon, the Spacebar activates the currently selected command or expands the presently set menu or gallery if the current selection is a menu or gallery. Tab and \"Shift + Tab\": Tab has the same effect whether you are working with the Ribbon or a dialog box. It allows you to move on to the next option or option group in both cases. \"Shift + Tab\" allows you to go backwards to the previous option or option group.
Chapter 6: Develop and Implement Short Cuts in Excel with Macros If you've ever worked with Excel (and perhaps even if you haven't), you've probably noticed how time-consuming routine tasks like formatting or inserting standard text can be. Even if you are familiar with these tasks and are capable of completing them quickly, you will spend at least 5 minutes almost every day inserting your company's name and contact information into all the Excel worksheets you send to clients/colleagues. In most cases, devoting a significant amount of time to these common but repetitive operations does not yield proportional results. In fact, most of them are excellent examples of the 80/20 rule in action. However, they are a part of the vast majority of efforts that have little impact on the end result. However, for those reading this section of this book, you're probably already aware that macros are one of Excel's most powerful features and can help you automate repetitive tasks. In this chapter, I will show you how to create a macro that does the following: Type \"This is the best Excel tutorial.\" Auto-fit the active cell's column width to fit the typed text in a single column. Highlight the active cell in red. Change the active cell's font color to blue. Now, let's get started. Show The Developer Tab Before you begin creating your first Excel macro, make sure you have the necessary tools. The Developer tab in Excel contains the majority of the useful commands for working with Excel macros and Visual Basic for Applications.
Excel hides the Developer tab by default. As a result, unless you (or someone else) have added the Developer tab to the Ribbon, you must force Excel to display it in order to have access to the appropriate tools when configuring a macro. It's worth noting that you only need to ask Excel to show the Developer tab once. Assuming the configuration is not changed later, Excel will continue to display the Tab in future opportunities. Select Options > Customize Ribbon from the File tab. Under Customize the Ribbon and Main Tabs, check the Developer check box. Unless you clear the check box or reinstall a Microsoft Office program, the Developer tab remains visible after you show it. When you want to do or use the following, go to the Developer tab: Make macros. Execute macros that you have previously recorded. Make use of XML commands. Make use of ActiveX controls. Make applications that can be used with Microsoft Office programs. In Microsoft Excel, use form controls. In Microsoft Visio, work with the ShapeSheet. In Microsoft Visio, you can make new shapes and stencils. What You Need For Creating Excel Macros You can create macros in Excel by using one of the following tools: The Macro Recorder is a tool that allows you to record the actions you take in an Excel Workbook.
The Visual Basic Editor, in which you must write the instructions for Excel in the programming language Visual Basic for Applications. The second (programming-required) option is more complex than the first, especially if you are new to macros and have no programming experience. Because this is a beginner's guide, I'll show you how to use the recorder to record an Excel macro. Let us Create Your First Macro You've added the Developer tab to the Ribbon by now, and you're aware that there are two tools you can use to create a macro, including the recorder. When you record a macro, the macro recorder creates a Visual Basic for Applications (VBA) file that contains all of the steps. These steps include typing text or numbers, selecting cells or commands from the ribbon or menus, formatting cells, rows, or columns, and even importing data from an external source, such as Microsoft Access. VBA is a subset of the powerful Visual Basic programming language that is included with the majority of Microsoft Office applications. Although VBA enables you to automate processes within and between Office applications, the Macro Recorder does not require knowledge of VBA code or computer programming. It is critical to understand that when you record a macro, the Macro Recorder records almost every movement you make. As a result, if you make an error in your sequence, such as accidentally clicking a button, the Macro Recorder will record it. Either re-record the entire sequence or modify the VBA code directly. This is why, whenever you record something, it is best to record a process with which you are intimately familiar. The more smoothly recorded a sequence, the more efficiently the macro will run when it is played back. You're all set to create your first Excel macro. Simply follow the simple steps outlined below to get started. Click on the Developer tab. Everything you need to create anything in Excel is on the Developer tab, and that is why you had to show it in the first place.
Record a macro There are a few things you should know about macros: When you create a macro in Excel to accomplish a series of tasks within a range, the macro will execute only on the cells contained within the range. As a result, if you add a new row to the range, the macro will perform the operation only on the cells contained within the range, not on the new row. If you have a lengthy list of tasks to record, consider breaking them up into smaller relevant macros rather than one long macro. It is not necessary to create a macro that records only tasks in Excel. Your macro process can be extended to include additional Office applications and any other applications that support Visual Basic for Applications (VBA). For instance, you could create a macro that updates a table in Excel and then opens Outlook to send the updated table to a specified email address. On the Developer tab's Code group, click Record Macro. Optionally, give the macro a name in the Macro name box, a shortcut key in the Shortcut key box, and a description in the Description box before
clicking OK to begin recording. Now, type \"This is the best Excel Tutorial\" in any cell. Then click Stop Recording. Once you press CTRL + SHIFT + T, this will appear on the cell you recorded it in. Save the Macro Select a location for the macro to be saved from the \"Store macro in\" dropdown list. In most cases, you'll save your macro to the This Workbook folder, but you can choose Personal Macro Workbook if you want it to be accessible whenever Excel is opened. When you select Personal Macro Workbook, Excel creates and saves the macro in a hidden personal macro workbook (Personal.xlsb) if one does not already exist. Optionally, in the Description box, type a brief description of what the macro does. Although the description field is not required, it is strongly recommended that you complete it. Additionally, attempt to include a meaningful
description that includes any information that you or other users who will run the macro might find useful. For instance, if you have a large number of macros, the description can assist you in quickly determining which macro performs which function; otherwise, you may have to guess. Working with recorded macros in Excel To view macros associated with a workbook, go to the Developer tab and select Macros. Alternately, press Alt+ F8. This activates the Macro dialog box. If you follow the simple steps outlined above, you can already begin creating basic macros. Macro Keyboard Shortcuts
I show you how to run a macro by assigning a keyboard shortcut to an existing macro. When you create a macro with the macro recorder, you can also set it as a keyboard shortcut. Let's go over the three simple steps you can take to assign or change the keyboard shortcut of any macro: 1st, launch the Macro Dialog Box. You can access the macro dialog box via either of the two methods listed below: Method #1: Navigate to the Developer tab and select \"Macros.\" Open the Macro dialog box from the Developer tab. Method #2: Use the keyboard shortcut \"Alt + F8.\" 2: Choose the macro that you want to edit. The Macro dialog box displays the majority (but not all) of the available Sub procedures. To be more specific, the Macro dialog only shows public procedures. When Excel displays the Macro dialog box, select the VBA Sub procedure to which you want to assign a keyboard shortcut and then click the Options... button on the right side of the screen. 3: Create a Keyboard Shortcut After you've completed step #2, Excel will display the Macro Options dialog. This dialog allows you to assign a shortcut key and (optionally) a description to the relevant macro. Any keyboard shortcuts assigned to a macro override Excel's default keyboard shortcuts. As a result, if you set a keyboard shortcut that is identical to a built-in one, the built-in shortcut will be disabled. For example, the built-in keyboard shortcut for the Cut task is \"Ctrl + X.\" When you assign the keyboard shortcut \"Ctrl + X\" to a macro, you can no longer use it to cut.
Once you've assigned the keyboard shortcut to the macro, close the Macro Options dialog box by clicking the OK button in the lower right corner.
Chapter 7: List of 300+ Excel ShortCuts Here is a tedious list of over 300 built-in Excel shortcuts. It will serve as a reference for you whenever you need it: Charts and Graphs ShortCut Description Alt + F1 Alt + JA Create an embedded chart using currently selected data Alt + JC Alt + JC + A (1) In Excel 2013 and later, go to Format Alt + N + B tab of the Ribbon (when active); or (2) In Excel 2007 and 2010, go to Layout tab of the Ribbon (when active) Go to the Design tab of the Ribbon (when active) In Excel 2013 and later, expand Add Chart Element dropdown menu Insert Bar Chart Alt + N + C Insert Column Chart Alt + N + N Insert Line Chart Alt + N + Q Insert Pie or Doughnut Chart Alt + N + R In Excel 2013 and later, Recommended Charts Alt + N + SD In Excel 2013 and later, Insert Combo Chart Alt + N + X Insert Text Box F11 Create a chart in a separate Chart sheet using currently selected data Data
(Ctrl + Alt Paste special characters, + V) + B omitting blanks (Ctrl + Alt Paste cell comments + V) + C (Ctrl + Alt Make a special paste by + V) + D performing an addition. (Ctrl + Alt Alt + use + V) + E H + V When pasting, + T transpose. (Ctrl + Alt Alt + Paste everything, keeping + V) + H H + V the formatting from the + K source. (Ctrl + Alt Paste special, division + V) + I performance (Ctrl + Alt Using a special paste, + V) + M perform a multiplication. (Ctrl + Alt Validation of pasted data + V) + N (Ctrl + Alt Subtraction with a paste + V) + S special (Ctrl + Alt Alt + Values and number + V) + U H + V formatting can be copied + A and pasted. (Ctrl + Alt Alt + + V) + V H + V + V Paste the values (Ctrl + Alt Alt + + V) + X H + V Paste everything except + B the borders. Alt + A + The Remove Duplicates M dialog box appears. Alt + A + Show the Goal Seek W+G dialog box. Alt + A + The Scenario Manager
Search
