In the second series of ninja level Excel shortcut keys from our Excel Training program, let me focus on Excel shortcut keys which are more usable when you are working with Excel formulas. To start with I will be talking about F2, function key F2. In case if you are writing any formula lets say sum formula and you selected certain cells. Now when you choose a particular cell often you would want to double click and see what's inside it. Well, you can press function key F2 to be able to see what's inside it through the entire formula parameters. So this was a function key F2.
Apart from this if you want to repeat your last action in terms of formatting in Excel. For example, my last action was coloring the cell yellow. And if I choose another cell and press function key F4, notice it repeats my last action. Now F4 function key is also used to select a particular cell and freeze it using the $ component. If I keep on pressing F4 at this moment notice the $ is appearing with multiple combinations. Now this has been discussed in detail when we will be talking about cell referencing. So that was a function key F4 to repeat your last action. And also F4 to put $ in the formula to be discussed later. Apart from this let me take you to the sample data one case study. Here you have a series of formulas thrown across.
Talking about sales, talking about cost, the average cost, profit, etc. If ever you get some file like this which has so many calculations thrown across. You initiate your work by doing what. You double click and see what's inside it, correct. Well, if you press this shortcut key you notice, Ctrl, and backtick key, the backtick key which is the one which is the lower character of the above two. So the first one is called till. The second one is called back tick key. If I press Ctrl and backtick key, in one single glance I get to see all the formulas on my screen. And the major advantage is, if you select any one cell, the colored borders as you can see from the red and the blue color, the colored border also navigates along with it. So that helps you find out what are the origins of this formula. If I press the same shortcut key, Ctrl back ticks key, it takes me back to normal view.
Apart from this, if I wish to find out what is the origin of a particular cell and let my cursor reach that level physically. So for example, this green cell which you are noticing on my screen right now is referring to a cell of the sheet 002. So if I go to the green cell and then I'm going to press a excel shortcut key, Ctrl third bracket open. Let me write this for your convenience here. Ctrl [ third bracket open. So once I choose the green cell I press Ctrl [ it physically takes my cursor to this cell of sheet 002. Now the question is how do I go back, especially when you are working with different worksheets with a good amount of numbers in it. High complexity level and then after coming back to this origin press F5 blindly and blindly presses Enter thereafter. It takes you back.
In fact, if you want to try this with a different cell which are interconnected with the different sheet. Let's try this. =, and I'm putting a formula which points to this cell Enter. Now this particular cell is actually coming from a different sheet. So what I'm going to do is, I'm going to press Ctrl [, it takes me to the origin. Then I press F5 and I press Enter blindly, it takes me back where I started from. So that was a shortcut key to help you navigate between different worksheets. Apart from this what are the other excel shortcut keys.
Well, alt =. If you have a series of numbers. One of the most commonly used formula is the Sum formula. So if they are continuously placed one after another, if you go to the last cell which appears to be blank right now. And if I press Alt = it quickly catches up all the number and puts the sum formula after which you just need to press Enter. The only catch is in case by any chance there are blanks within the series of numbers. Then Alt = will not be able to capture the entire portion. In which case, you would have to go to the first cell. Shift Ctrl Down. And Shift Down to be able to choose that selection and then press Enter.
Next, any formula that we be working with in the video series that are about to be shown to you, we'll be using a couple of techniques to help you navigate with the formula parameters quickly. For example, if I write =VL, notice the parameter drop down says Vlookup already. I just need to press the tab key to autocomplete this. Apart from this if you intend to apply SUMIFS so I write =SUM, But there are a couple of Excel formulas starting with the word SUM so I use a down arrow key to go to the SUMIFS. And then I press the tab key.
Now the question is I want to see different parameters in the function argument box to which lot of people click on this fix button, right. So fix button, well, I want a Excel shortcut key instead of clicking on this fix button directly. So let me write =SUMIFS, as soon as soon I press the tab key the bracket of parenthesis is opened up. Now let me press Ctrl A. It then allows me to see what are the different function argument box. Now notice as I press the tab key and tab and tab, it quickly opens other boxes. If I want to move upwards, then Shift tab, Shift tab, Shift tab. And as you can see the movement of the cursor defining how Shift tab and tab directs the position of the cursor.
Let's assume that you have written a basic excel formula which involves submission of a couple of numbers. And you have closed the bracket and press Enter. Now, once a formula is written, you want to go back to the same view. A function argument approach where you can see the different parameters put in. So either you select the cell, click on Fx. Or else you keep the cell selected and press Shift F3. I'm going to press Shift F3. There you go. This will take you directly to the function argument box and that's where you see the different parameters and input. So do practice this because these are excel shortcut keys that I'll be using in my sessions of Excel Training Program. And I want you to use this while you're working on different Excel files at your office.
The course itself was designed in very good way having step by step & logical approach to explain the concepts & logic that will make things easy to remember in future. This definitely will help to improve my work as I work extensively with excel.
- Ashraf Halai
CPA, CA at Aviation & Aerospace
if you are a regular user who performs complicated tasks in Excel on daily basis, you are likely to take that back within a week. The author's delivery is very efficient. Highly recommended.
- Ivan Arnaudov,
Owner and CEO at Business Ventures International LLC
If you're not completely satisfied, let us know within 30 days for a full refund. We want you to be 100% satisfied.