0314 Using NameRange to simplify calculation

Advanced Excel Dashboard Crash Course Section 3: 2nd Dashboard using Functions
2 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
€92.30
List Price:  €129.22
You save:  €36.92
£79.14
List Price:  £110.81
You save:  £31.66
CA$135.92
List Price:  CA$190.30
You save:  CA$54.37
A$153.17
List Price:  A$214.44
You save:  A$61.27
S$134.71
List Price:  S$188.59
You save:  S$53.88
HK$782.26
List Price:  HK$1,095.19
You save:  HK$312.93
CHF 90.56
List Price:  CHF 126.79
You save:  CHF 36.22
NOK kr1,076.34
List Price:  NOK kr1,506.92
You save:  NOK kr430.58
DKK kr688.47
List Price:  DKK kr963.88
You save:  DKK kr275.41
NZ$166.49
List Price:  NZ$233.09
You save:  NZ$66.60
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳11,000.23
List Price:  ৳15,400.77
You save:  ৳4,400.53
₹8,335.05
List Price:  ₹11,669.40
You save:  ₹3,334.35
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦139,656.03
List Price:  ₦195,524.03
You save:  ₦55,868
₨27,872.43
List Price:  ₨39,022.53
You save:  ₨11,150.09
฿3,637.73
List Price:  ฿5,092.97
You save:  ฿1,455.24
₺3,222.80
List Price:  ₺4,512.05
You save:  ₺1,289.25
B$497.67
List Price:  B$696.75
You save:  B$199.08
R1,888.37
List Price:  R2,643.79
You save:  R755.42
Лв180.67
List Price:  Лв252.95
You save:  Лв72.27
₩134,942
List Price:  ₩188,924.20
You save:  ₩53,982.20
₪366.06
List Price:  ₪512.51
You save:  ₪146.44
₱5,625.18
List Price:  ₱7,875.48
You save:  ₱2,250.29
¥15,118.78
List Price:  ¥21,166.90
You save:  ¥6,048.12
MX$1,663.06
List Price:  MX$2,328.35
You save:  MX$665.29
QR365.44
List Price:  QR511.63
You save:  QR146.19
P1,373.02
List Price:  P1,922.28
You save:  P549.26
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,723.53
List Price:  E£6,613.14
You save:  E£1,889.60
ብር5,694.53
List Price:  ብር7,972.57
You save:  ብር2,278.04
Kz83,612.73
List Price:  Kz117,061.17
You save:  Kz33,448.44
CLP$98,024.19
List Price:  CLP$137,237.79
You save:  CLP$39,213.60
CN¥722.49
List Price:  CN¥1,011.52
You save:  CN¥289.02
RD$5,928.03
List Price:  RD$8,299.48
You save:  RD$2,371.45
DA13,469.95
List Price:  DA18,858.47
You save:  DA5,388.52
FJ$225.25
List Price:  FJ$315.36
You save:  FJ$90.11
Q781.84
List Price:  Q1,094.62
You save:  Q312.77
GY$20,987.69
List Price:  GY$29,383.60
You save:  GY$8,395.91
ISK kr13,817.61
List Price:  ISK kr19,345.21
You save:  ISK kr5,527.60
DH1,010.57
List Price:  DH1,414.84
You save:  DH404.26
L1,763.81
List Price:  L2,469.40
You save:  L705.59
ден5,677.01
List Price:  ден7,948.05
You save:  ден2,271.03
MOP$807.66
List Price:  MOP$1,130.76
You save:  MOP$323.09
N$1,895.98
List Price:  N$2,654.45
You save:  N$758.46
C$3,689.21
List Price:  C$5,165.04
You save:  C$1,475.83
रु13,357.88
List Price:  रु18,701.57
You save:  रु5,343.68
S/371.02
List Price:  S/519.45
You save:  S/148.42
K383.60
List Price:  K537.05
You save:  K153.45
SAR375.02
List Price:  SAR525.04
You save:  SAR150.02
ZK2,548.47
List Price:  ZK3,567.96
You save:  ZK1,019.49
L458.83
List Price:  L642.38
You save:  L183.55
Kč2,334.38
List Price:  Kč3,268.22
You save:  Kč933.84
Ft36,425.52
List Price:  Ft50,997.18
You save:  Ft14,571.66
SEK kr1,059.50
List Price:  SEK kr1,483.35
You save:  SEK kr423.84
ARS$85,666.43
List Price:  ARS$119,936.42
You save:  ARS$34,269.99
Bs695.09
List Price:  Bs973.16
You save:  Bs278.06
COP$389,673.99
List Price:  COP$545,559.18
You save:  COP$155,885.18
₡50,509.65
List Price:  ₡70,715.54
You save:  ₡20,205.88
L2,474.33
List Price:  L3,464.17
You save:  L989.83
₲736,638.54
List Price:  ₲1,031,323.43
You save:  ₲294,684.88
$U3,781
List Price:  $U5,293.55
You save:  $U1,512.55
zł398.17
List Price:  zł557.46
You save:  zł159.28
Already have an account? Log In

Transcript

Hello, everybody. Welcome back to the second dashboard. In this video, we will see how to create a very understandable and short formulas, which we can use in the future as well. For this particular trick, I'll be using Name Manager. And what we normally do is in any kind of mathematical calculation, I'll go with a simple one. That is plus minus multiply and divide.

So I'm going with a plus symbol. So normally we put equals to select the value plus the second value, press enter, we do get the answer and we drag it down. Now let's assume that this is complicated calculation rather than just a few pluses. What we can do here is we can add this task in the Name Manager. Now let's see how it is done. So to do so, we go to the Formulas tab.

Under Formulas tab, define name and there we can click on Name Manager. So once we select the Name Manager panel appears where you can put in the name and the task So we will add a new name and we'll just say addition. So I'll just go with ADD. Now here at the bottom we can specify the calculations we want to perform. Currently our cell is activated on C two, so it will capture the base and it will take the basis see to no worries to select itself plus the other cell. So what I'm trying to do is add is an indicator of whichever cell I select from the selection just with the left, that is the first left and the second left.

But as we can see, it's $1 sign so I'll remove the dollars on the sheet name, it does not matter if you do not remove the sheet name. The reason behind it is that it will always work only for the current sheet for which we are making this format. So as we remove this So currently, what I say active cell is at the left in the next left, I click on OK and I do get the answer is add and we can see once I'm done, the format goes back With the current sheet name, close this let's try it out. Now in the future if I wish to add this up I did not have to do the task again simply equals add. And there it is. So the selection is done I simply drag it down and it goes or does the rest of the calculation because when it moves down the reference cell will also change with it.

Just imagine if you have just put up a big chunk of formula and drop it into the smallest name possible like taxation. Sorry, we just have to put equals to tax and there it is. Now the answer will be ready for us for now I have not created it so it won't be there available for me, but I can use it in the future if I need to get the work done faster. Now in the next video, we will see how to use this in a practical implementation with a dashboard Until then, keep practicing. See you in the next video. Thank you

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.