Excel Keyboard Shortcuts: Easily Get Or Create Any Keyboard Shortcut.Excel In Depth: Jelen, Bill: : Books
Looking for:
Excel In Depth | InformIT. |
Microsoft excel 2016 in depth by bill jelen free
Microsoft excel 2016 in depth by bill jelen free. Follow the Author
Keyboard shortcuts are, perhaps, one of the Excel topics that virtually any user can or should be interested in. I have already created, and update from time to time, one of the most comprehensive lists of keyboard shortcuts available online. You can find this list of keyboard shortcuts for Excel here. If you want to get the data from the PDF file into Excel, you can use any of these conversion methods which you can also implement with macros.
There are 3 basic reasons why you should consider learning more than lists when studying keyboard shortcuts :. This is where this Excel tutorial comes in. My purpose with this blog post is to provide you with the knowledge you need to master keyboard shortcuts in Excel and become even more productive.
Therefore, in addition to explaining the different types of keyboard shortcuts that are available in Excel, I show you some methods you can use to create your own custom keyboard shortcuts. The table of contents below shows the specific topics this blog post covers:.
Let's start with a preliminary introduction about how I suggest you approach keyboard shortcuts in Excel:. Lists and cheat sheets with keyboard shortcuts are great. Most of us want to have them and, as I show below, lots of Excel bloggers and writers including myself have created their own version of them. These amazing lists can, however, be slightly overwhelming if you don't know how to approach them. More precisely, if you approach keyboard shortcut cheat sheets with the idea that you'll quickly learn them all by heart, you're likely to be disappointed.
This isn't a bad thing. After all, you probably don't need to know so many keyboard shortcuts. Furthermore, some of those shortcuts may be for commands or features that you rarely use.
And if it makes you feel better, you're in good company. You can rest assure that most other Excel users including very advanced ones , don't know all the shortcuts either. Therefore, before you begin to collect lists of keyboard shortcuts or trying to learn every single shortcut you come along, I have 1 basic suggestion :. Don't overload yourself by trying to learn too many keyboard shortcuts at the same time. You're unlikely to learn a huge amount of keyboard shortcuts in a very short period of time.
I suggest you start slowly and, perhaps, learn 1 or 2 shortcuts per week. Once you've mastered a particular shortcut, learn a new one. As you start gaining more familiarity with Excel keyboard shortcuts, you'll be able to handle more.
You may wonder, then, how should you choose which keyboard shortcuts to learn first. My suggestion is that you take the following 2-step approach to determine what keyboard shortcuts to master :. Once you've determined the command or task you want to execute with your keyboard, you'll have 2 basic options for purposes of searching for the corresponding keyboard shortcut :. I have already created and update from time to time a very comprehensive shortcut list that helps you with option 1.
However, depending on factors such as the type of command or tasks you need to execute and your own personal preferences, the keyboard shortcuts included in such lists may not for different reasons work for you. Fortunately, Excel provides several options for you to carry out a task or execute a command by using only the keyboard.
In addition to the huge amount of built-in keyboard shortcuts that Excel has, you can create your own custom keyboard shortcuts taking into consideration some requirements and limitations that I explain below. The following section may help you avoid confusion with the, sometimes, irregular terminology used by different authors when talking about keyboard shortcuts.
If you aren't interested in this particular topic, or already have a good grasp of the topic, please feel free to skip to the next section, which talks about regular keyboard shortcuts.
You may find some discussions regarding the exact meaning of each term. Without going into more theoretical discussions regarding the technical meaning of each term, let's take a look at how I use them in this Excel tutorial :. The terms listed above are, however, not necessarily used in exactly the same way by other Excel writers or documents.
These are what I refer to here as Ribbon keyboard shortcuts, Quick Access Toolbar keyboard shortcuts and dialog box keyboard shortcuts. Once you've read this Excel tutorial, you'll have enough knowledge to understand other texts that talk about keyboard shortcuts. Therefore, you'll easily figure out how the relevant author is using each term. Now that the terminology is clear, let's start taking a look at the different types of keyboard shortcuts that you can use in Excel.
Let's begin with:. When talking about keyboard shortcuts for Excel, most people refer to Ctrl combination shortcut keys. As implied by their denomination, Ctrl combination shortcut keys involve pressing the Ctrl key in combination with some other s key s. In most cases, these keyboard shortcuts are of the following form :. Some of the most well-known and commonly used keyboard shortcuts fall within this group. Some examples are the following:.
You can, however, find Ctrl combination shortcut keys that combine Ctrl with other keys. The following are some examples:. There are, however, additional keyboard shortcuts that aren't Ctrl combination shortcut keys. I can't list all the regular keyboard shortcuts that Excel has here.
However, for illustrative purposes, the following are some examples of keyboard shortcuts that aren't Ctrl combination shortcut keys :. If you're using Excel on a computer that doesn't meet these 2 requirements, there will likely be some differences in some keyboard shortcuts.
For example, if you're using Excel for Mac, some of the Ctrl combination shortcut keys that Excel for Windows has don't work.
If you're interested in lists or cheat sheets with the actual keyboard shortcuts , you may be interested in Excel Shortcuts and Hotkeys: The Power Spreadsheets Cheat Sheet. This is my compilation of keyboard shortcuts, which I update from time to time.
In addition to the actual blog post, you can gain immediate free access to the PDF file containing that list. Excel's Ribbon is the group of tabs and buttons that you see in the upper section of Excel. If you've used Excel before, you probably know that you can use the Ribbon to carry out the most common tasks in Excel. In fact, my guess is that you use the Ribbon constantly. Also, if you're like most Excel users, you probably use the mouse to work with the Ribbon at least most of the time.
However, thanks to Key Tips, you can get to any tab or button within the Ribbon using only the keyboard. As explained by John Walkenbach in the Excel Bible :. At first glance, you may think that the Ribbon is completely mouse centric …. But in fact, the Ribbon is very keyboard friendly.
In this section, I explain what you need to know in order to work with the Ribbon using these Ribbon keyboard shortcuts. When using Ribbon keyboard shortcuts, you don't need to hold down all the keys at the same time, as you do with regular keyboard shortcuts.
In other words, with Ribbon keyboard shortcuts, you can:. Now that you know this, let's take a look at the 3 easy steps you can follow to use Ribbon keyboard shortcuts. After explaining these 3 steps, I show you an example of how you can use Ribbon keyboard shortcuts. Tell Me was introduced in Excel Therefore, if you're using Excel or earlier, you won't have access to this feature.
The Ribbon of your particular version of Excel likely looks different from that of the image above. For example:. However, the Key Tips for the Ribbon tabs remain the same. Regardless of how your Ribbon looks like, Excel always displays the Key Tips corresponding to all the sections Ribbon tabs, Backstage View and Tell Me you can access at that particular time.
Just remember the rule you must follow once you've turned on the Key Tips:. Press the key s that corresponds to the section usually a tab of the Ribbon you want to activate. In fact, as I shown in step 3 below, when you're working with the Ribbon, you just need to continue applying this rule:. Once you've turned the Key Tips on as explained in step 1 above , Excel continues to display the Key Tips that correspond to the particular situation you're in.
In other words:. At any given time, the Key Tips displayed by Excel show all of the options you have for your next step. You just need to determine what key you must press to go where you want to go, and press it.
Depending on the particular context, Excel does one of the following:. In some cases, a Key Tip may display 2 keys. In such situations, just press both keys. If Excel displays a new set of Key Tips, just repeat this step 3 press the appropriate keyboard key as many times as necessary until you've reached your desired destination.
Remember that you don't need to keep any key depressed while typing the other keys that compose the Ribbon keyboard shortcut. Let's assume that, on step 2 above, you press the letter H. This keyboard key takes you to the Home tab. In my version of Excel , this looks as follows:. Notice how the Key Tips displayed by Excel no longer correspond to the Ribbon tabs, as shown in step 2 above. The many Key Tips in the image above correspond to the different buttons, drop-downs and dialog launchers in the Home tab.
If, for example, you want to execute the Increase Indent command, to move the content further away from the border of a cell, you can now press the number 6. This key tip is shown in the image below:. To make the whole process of using Ribbon keyboard shortcuts Key Tips very clear, let's take a look at an additional example:.
In this case, I'm adding all borders to cell D2, which I select at the beginning of the recording. Notice that, in this case, I need to press 4 keys Alt, H, B and A in order to achieve the desired result.
In the example within the previous section, I only needed to press 3 keys Alt, H and 6.
Excel VBA Delete Column: 8 Easy-To-Use Macro Code Examples.Excel in Depth - MrExcel Products - MrExcel Publishing
Pivot Tables allow you to calculate and analyze data in several different ways. At the most basic level, a basic Pivot Table provides some basic but powerful calculation functionality to determine the displayed values. For example, in the sample Pivot Table below:. Furthermore, Pivot Tables also allow you to display values based on other items or cells. You can, for example, display values as percentages or running totals. These 2 options allow you to deal with a wide variety of situations and data analysis requirements.
However, in certain cases, you may want microsoft excel 2016 in depth by bill jelen free create your own formulas. However, you can't simply add a new row or column to a Pivot Table. If you try to do so, Excel usually displays a warning such pixelmator blur edges free the following:.
If you want to create your own formula within a Pivot Tableyou usually work with either of the following:. I provide all the information you need to quickly carry out the most common and relevant operations with Calculated Fields. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter. Let's start by looking at the sample source data and Pivot Table within the workbook that accompanies this Excel Tutorial:. Using this source data, I create the following Pivot Table.
This is the Pivot Table we'll be working with throughout the rest of this Tutorial. Let's dive into the topic of Calculated Fields. I start by defining what a Calculated Field is and when can they help you.
The newly added Field carries out certain calculations based, usually, on the values of other Fields. In more technical terms, Calculated Fields use the sum of the underlying data of the Field s the Calculated Field formula uses.
In other words, when you work with Calculated Fields, you're working with all the underlying data vs. Because of the above, Calculated Fields are useful when you want to use all the data from certain Field s in your formulas. Let's assume that you need to make a very basic calculation to estimate the Cost of Goods Sold for each store and item. To keep the examples as simple as possible, and focus on the topic of Calculated Fields, the calculation is very basic.
We're going to assume that you can calculate the Cost of Goods Sold as a percentage of the Sales Amount microsoft excel 2016 in depth by bill jelen free each item and store. In some situations, you may be able to подробнее на этой странице back to the source data and add such a column. However, in other cases, adding a new column to the source data may not be practicable or convenient.
An alternative to adding a new column to the source data is to use a Calculated Field. Excel and Mike Alexander list several advantages of using Calculated Fields vs.
In general, most of the advantages of working with Calculated Fields boil down mostly to the following 2 points:. This doesn't mean that Calculated Fields are suitable for any situation. Further below, I introduce several common problems and limitations of Calculated Fields.
That introduction should help you to determine the cases in which Calculated Fields may not be the best alternative. Before I start to explain how you work with Calculated Fields, let's go back to the sample workbook I introduce in the previous section and look at the…. This Calculated Field:.
Notice the new column Cost of Goods Sold. Even though they share some similarities, these terms refer to different microsoft excel 2016 in depth by bill jelen free. The main difference is the fact that Calculated Items перейти with individual records. In other words, Calculated Items allow you to work with Item s from within a Microsoft excel 2016 in depth by bill jelen free. Calculated Fields, as I mention above, use all the underlying data of the Field s the Calculated Field formula uses.
Now that you have a clear understanding of what a Calculated Field is, let's see how you can insert one:. You can easily add a Calculated Field to a Pivot Table in the following 6 steps :. Select the Pivot Table report to which you want to add the Calculated Field to. You can do this by clicking anywhere on the Pivot Table. After you complete step 2 above, Excel launches the Insert Calculate Fields dialog box.
In the example we're working with, this dialog looks as follows:. Excel automatically enters a basic name Field1 in the example above. However, generally, it's better if you enter a more descriptive and useful name.
As I explain above, one of the main purposes of Calculated Fields is to allow you to create your own formulas using data from other Fields within the same Pivot Table.
Therefore, I can calculate the total Cost of Goods Sold with the following formula:. Notice that the syntax you use to specify a formula for a Calculated Field isn't the same that you use when working with worksheet formulas.
In a section below, I provide a more detailed introduction to Calculated Field formula syntax and behavior. I also explain how you can easily insert a Field reference. Hint: you don't have to type the Field reference. Once you've entered a name and formula for your Calculated Field, you can do either of the following to confirm the addition :.
The formulas you use in Calculated Fields have a different syntax and behavior to regular worksheet formulas. The purpose of this section is to give you a basic overview of both topics.
Generally, you can use the following elements build formulas for Calculated Fields:. When creating a Calculated Field formula, you can generally use the following elements in the same way you'd use them when creating a worksheet formula :. These are items 1, 2, 3 and 4 from the list of elements you can use to build Calculated Field formulas above.
In this section, I explain the basic rules you can apply to create references to data within the Pivot Table report item 5 of the list. Let's start by recalling that Calculated Fields work with the sum of the underlying data for a Field. In other words, you use Calculated Fields when you want your formulas to work with the data from certain Field s.
To understand what the above means, let's go back to the formula I enter for the Cost of Goods Sold Calculated Field in step 5 of the process to add a Calculated Field above:. The process followed by Excel to calculate the product returned by this formula is roughly as follows:. Notice that, as I mention above, I enter Items 2 operator and 3 constant as I читать for a regular worksheet formula.
This helps us illustrate the basic guidelines you follow when creating such a Field reference within a Calculated Field formula :.
In most cases, however, you don't even need to worry too much about the syntax for Field references. The Insert Calculated Field dialog box has a Fields list box that is quite helpful for these purposes. The Fields list box includes microsoft excel 2016 in depth by bill jelen free the Fields that are available for you to use in your formula.
Therefore, you can insert a Field reference in the Formula input box in either of the following 2 ways:. The result of the above process is a reference to the chosen Field. In the example we're working with, this looks as follows:. Depending on the complexity of your Pivot Tables, you may have to deal with several Calculated Fields and Calculated Items at the same time. In such situations, it may be helpful if you can view all these elements at the same time.
You can see all Calculated Fields within a Pivot Table by following these 2 easy steps :. From time to time, you may have to go back to your Pivot Table reports and edit previously entered Calculated Field formulas.
Other than microsoft excel 2016 in depth by bill jelen free 4 and 5, the process is substantially the same as that which you follow to на этой странице a Calculated Field.
For clarity purposes, let's go through each of these steps using an example:. Click anywhere on the Pivot Table report you want to work with.
Excel displays the Analyze and Адрес страницы contextual tabs. Once Excel displays the contextual tabs, go to the Analyze tab. This is the same dialog box that you use when you originally create the Calculated Field formula. Click on the drop-down arrow on the right side of the Name box within the Insert Calculated Field dialog box. Excel displays a drop-down list that includes all the Calculated Fields перейти на страницу added to the Pivot Table report.
From within this list, select the Calculated Field whose formula you want to amend. I select it. Once you've selected the Calculated Field you want to work with, Excel displays the current formula in the Formula box. For example, the formula that I entered when originally creating the Cost of Goods Sold Calculated Field is as follows:. Let's assume that your assumption for calculating Cost of Goods Sold changes.
The new Calculated Field formula microsoft excel 2016 in depth by bill jelen free as follows:. Once you've modified the Calculated Field formula, you can confirm your changes in either of the following ways:. In some cases, you may not need to display a Calculated Field within your Pivot Table report. In such situations, you have the following 2 options:. The basic difference between hiding and deleting a Calculated Field is that, if you delete a Calculated Field, Excel removes it permanently.
That isn't the case when you just hide the Office word 2010 free 32 bit. More precisely, the Calculated Field usually appears within the following Areas:. Because of the above, you can hide a Calculated Field by simply removing it from the Pivot Table Areas. In other words, you can quickly hide a Calculated Field in either of the following 3 ways:. Begin by microsoft excel 2016 in depth by bill jelen free the Pivot Table you're working with.
Comments
Post a Comment