0316 Introduction to Offset Function

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
€88.46
List Price:  €123.85
You save:  €35.39
£75.37
List Price:  £105.52
You save:  £30.15
CA$138.19
List Price:  CA$193.47
You save:  CA$55.28
A$155.11
List Price:  A$217.17
You save:  A$62.05
S$129.96
List Price:  S$181.95
You save:  S$51.99
HK$774.92
List Price:  HK$1,084.92
You save:  HK$310
CHF 82.69
List Price:  CHF 115.77
You save:  CHF 33.08
NOK kr1,041.08
List Price:  NOK kr1,457.56
You save:  NOK kr416.47
DKK kr660.36
List Price:  DKK kr924.54
You save:  DKK kr264.17
NZ$167.99
List Price:  NZ$235.19
You save:  NZ$67.20
د.إ367.26
List Price:  د.إ514.19
You save:  د.إ146.92
৳12,188.92
List Price:  ৳17,064.98
You save:  ৳4,876.05
₹8,462.20
List Price:  ₹11,847.42
You save:  ₹3,385.22
RM426.95
List Price:  RM597.75
You save:  RM170.80
₦160,354.96
List Price:  ₦224,503.36
You save:  ₦64,148.40
₨28,187.10
List Price:  ₨39,463.07
You save:  ₨11,275.97
฿3,309.66
List Price:  ฿4,633.66
You save:  ฿1,324
₺3,857.27
List Price:  ₺5,400.33
You save:  ₺1,543.06
B$565.91
List Price:  B$792.30
You save:  B$226.38
R1,839.21
List Price:  R2,574.97
You save:  R735.76
Лв172.99
List Price:  Лв242.19
You save:  Лв69.20
₩139,971.25
List Price:  ₩195,965.36
You save:  ₩55,994.10
₪358.69
List Price:  ₪502.19
You save:  ₪143.49
₱5,565.74
List Price:  ₱7,792.26
You save:  ₱2,226.52
¥14,488.55
List Price:  ¥20,284.55
You save:  ¥5,796
MX$1,957.85
List Price:  MX$2,741.07
You save:  MX$783.22
QR364.06
List Price:  QR509.70
You save:  QR145.64
P1,361.73
List Price:  P1,906.48
You save:  P544.74
KSh12,933.70
List Price:  KSh18,107.70
You save:  KSh5,174
E£5,075.98
List Price:  E£7,106.58
You save:  E£2,030.59
ብር13,459.34
List Price:  ብር18,843.61
You save:  ብር5,384.27
Kz91,190.88
List Price:  Kz127,670.88
You save:  Kz36,480
CLP$94,605.53
List Price:  CLP$132,451.53
You save:  CLP$37,846
CN¥729.42
List Price:  CN¥1,021.22
You save:  CN¥291.80
RD$5,902.61
List Price:  RD$8,263.89
You save:  RD$2,361.28
DA13,288
List Price:  DA18,603.74
You save:  DA5,315.73
FJ$225.56
List Price:  FJ$315.80
You save:  FJ$90.23
Q770.30
List Price:  Q1,078.46
You save:  Q308.15
GY$20,924.33
List Price:  GY$29,294.90
You save:  GY$8,370.57
ISK kr12,929.70
List Price:  ISK kr18,102.10
You save:  ISK kr5,172.40
DH926.13
List Price:  DH1,296.62
You save:  DH370.49
L1,709.82
List Price:  L2,393.82
You save:  L683.99
ден5,441.97
List Price:  ден7,618.98
You save:  ден2,177
MOP$801.19
List Price:  MOP$1,121.71
You save:  MOP$320.51
N$1,867.19
List Price:  N$2,614.14
You save:  N$746.95
C$3,690.56
List Price:  C$5,166.94
You save:  C$1,476.37
रु13,561.38
List Price:  रु18,986.48
You save:  रु5,425.09
S/367.72
List Price:  S/514.83
You save:  S/147.10
K405.78
List Price:  K568.11
You save:  K162.33
SAR374.98
List Price:  SAR524.98
You save:  SAR150
ZK2,781.79
List Price:  ZK3,894.63
You save:  ZK1,112.83
L440.50
List Price:  L616.72
You save:  L176.22
Kč2,204.44
List Price:  Kč3,086.31
You save:  Kč881.86
Ft35,802.41
List Price:  Ft50,124.81
You save:  Ft14,322.40
SEK kr965.37
List Price:  SEK kr1,351.56
You save:  SEK kr386.18
ARS$117,263.27
List Price:  ARS$164,173.27
You save:  ARS$46,910
Bs693.19
List Price:  Bs970.49
You save:  Bs277.30
COP$426,824.69
List Price:  COP$597,571.64
You save:  COP$170,746.95
₡50,658.12
List Price:  ₡70,923.39
You save:  ₡20,265.27
L2,602.65
List Price:  L3,643.81
You save:  L1,041.16
₲803,267.16
List Price:  ₲1,124,606.16
You save:  ₲321,338.99
$U4,195.69
List Price:  $U5,874.13
You save:  $U1,678.44
zł378.49
List Price:  zł529.90
You save:  zł151.41
Already have an account? Log In

Transcript

Hello, everybody. Welcome to the second dashboard. In this video, we will learn a formula called offset. It is used for dynamic referencing. Here I created a small illustration just to demonstrate the example. So on this particular dashboard is a particular set of blocks.

These have a lot of money being put up over here, that person is standing here and I'm trying to navigate or guide the person to reach to a particular point where he can stand. And from there he throws a net to capture the data, the information will be provided in mind. Now, if I presume that this particular dashboard is like an Excel sheet where all the blocks are cells, and rows and columns, so let's bring the up to fate. So here it is. So ABC D. So I have column H and I have eight rows. Now first, we will start with the offset.

First we need to decide where to start the selection from so I might be standing at a particular location. So I have to give out the particular location. Where do I start? So currently, it's a one. So I'll say a one. So that's the starting point, how many sales?

Do I have to move down? The first parameter is how many sales do I move down? So here, I have to move three steps down, including the one where I'm standing. So three is a number. So I moved down three steps first. So there it is.

I'm down three steps. Now second question is, how many blocks Should I move to the right so that I reached the point where the data starts or where the money starts. So here, I have to move three more blocks to the right. So that's number three. So I'll move three more blocks to the right. Now once I'm on the third block, that means I've gone to the point from where the data starts in the current example from where the money starts.

So now I'll be throwing in that the net can can't be infinite, there should be width and height. So the first parameter is the height which is the height or what is the height of the particular area where the money are being placed. So currently from the active cell itself, I have to insert the data. So it can be a minimum one, but it cannot be zero that means 12345 so the height of the area that is being plotted with money is five blocks all say five, my net is ready to be spread across. So, I'll just have five blocks selected by the net. Now, the thing is I also have to input the width as well.

The width is also five blocks to the right so there it is, five blocks and that in turn will select the whole data for me. So you can see the money is now mine. So that was an illustration of how offset works. In the next video we will see how to use this in Excel. Till 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.