0106 For "Formula" One Speedstars

Advanced Excel Crash Course Section 1: Ninja Shortcuts
6 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

The second series of ninja level shortcut keys let me focus on shortcut keys which are more usable when you working with formulas. To start with, I will be talking about f2 function, the f2. In case you are writing any formula, which says some 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 F to be able to see what's inside it through the entire formula parameters. So this was function key f two. Apart from this, if you want to repeat your last action in terms of formatting for example, my last action was coloring a 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 dollars component. If I keep on pressing f4 at this moment, notice the dollar is appearing with multiple different combination. Now this has been discussed in detail When we will be talking about cell referencing, so that was function key f4 to repeat your last action, and also f4 to put dollars inside 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 the shortcut key notice Ctrl and backtick key, the backtick key is the one which is the lower character of the above two. So the first one is called till the second one is called backtick 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 advantages. If you select any one cell, the colored borders as you can see from the red and the blue color, The colored bars also navigate along with it. So that helps you find out what are the origins of this formula. If I press the same shortcut key Ctrl backtick key, it takes me back to normal view.

But 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 from the sheet 002. So if I go to the green cell, and then I'm going to press a shot to keep control third bracket open, let me write this for your convenience here. Control third bracket open. So once I choose a green cell, I press Ctrl third bracket open, it physically takes my cursor to this cell of sheet number 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.

Then, after coming back to the origin press, f5 and blind Key, press Enter thereafter, it takes you back. In fact, if you want to try this with a different cell, which I interconnected with a different sheet, let's try this equal to, and I'm putting a formula which points to the cell, enter. Now this particular cell is actually coming from a different sheet. So what I'm gonna do is, I'm going to press Ctrl third bracket open, it takes me to the region, when I press F five, 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 work sheets. Apart from this, what are the other shortcut keys?

Well, all equal to if you have a series of numbers. One of the most commonly used formulas the sum formula, so they're continuously placed one after another, if you go to the last cell, which appears to be blank right now, and if I press or equal to it quickly catches up all the number and puts the sum formula after which you just need to press enter the only catches in case by any chance there are blanks within the series of numbers, then all equal to not be able to capture the entire portion, in which case, you'd 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 will be working within 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 equal vl, notice the parameter drop down says v lookup already, I just need to press the Tab key to autocomplete this. Apart from this if you intend to apply some ifs, so I write equal to su M. But there are a couple of formulas starting with the word sum. So I use the down arrow key to go to some ifs and then I press the Tab key. Now the question is, I want to see the different parameters in function argument box two, which are people click on this FX button, right, this FX button Well, I want to show shortcut key instead of clicking on this FX button directly, so let me write equal to some A's. As soon as I press the Tab key, the bracket or parenthesis is opened up. Now let me press Ctrl A, it then allows me to see one of the different functions argument box.

And notice, as I press 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 formula which involves submission of a couple of numbers and you have close the bracket and press Enter. Now, once the formula is written, you want to go back to the same view function argument approach where you can see the different parameters put in. So either you select the cell click on effects, or else you keep this selected and press Shift f3.

I'm going to press Shift F Free. 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 shortcut keys that I'll be using in my sessions. And I want you to use this while you're working on different files at your office.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.