0402 Implementation of OFFSET for dynamic data

Advanced Excel Dashboard Crash Course Section 4: 3rd Dashboard using Functions $ Macros
3 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 to the third dashboard. In this video, we will see how to create a trendline. For the trendline, we need we do require two pivots, one that will show the overall result and the second one will show the result that the user will be selecting. So now let's get started. First, I keep the cursor on the data to make this data dynamic. I will be using the OFFSET function that we covered in a previous video.

Refer to that video for a refresher on offset. I will be doing the OFFSET function outside the sheet and then I'll be copy pasting the formula into the referencing will just speed up the video. So here I have created the formula that will automatically select all of the data for me and I simply cut the formula and then keep the cursor in data and go to the formula Name Manager. I'll delete the existing names as we will be creating a new one. And I'll name it data read or master data to be specific, and then paste the formula into the formula bar. That is refers to.

Now the best part is it automatically selects the entire data and then in the future, if the data expands, the reference will also expand automatically. Now delete this particular formula. Now to create a pivot, I just go to insert pivot table, and I will not be selecting the data rather I'll mention the name that is master data. There it is, I click OK. And the pivot table is ready, which is now dynamic. Now I create the pivot that we require and I'll be placing years of experience into the road table and the South. into value fields.

So here's the data that we have. Now this will be data that will show the overall average of the average overall salary. Let me convert it into currency, we wish to see this as an average. So we simply right click and I select summarize value by, in the older version, you'd be seeing summarized data by from here we'll be selecting average as we are looking for average based on US dollars. So I'll copy this entire data, because we need two of them, and I paste it over here. The second one is where the user selection will be taken into consideration once the two pivot tables are created.

Now the best part is both of the pivot tables are controlled with a single data. But both are different pivots. Now the user will be controlling the job types for both of them. So I'll be selecting the second which is controlled by the user and drop jobs. type into the filter box where the data will show me records for all. So for the first pivot also, I'll put in the filter box.

So the first filter box will show the data records for all. And the second will show the records selected by the or the user selection. So if the user selects admin there will be a gray data highlighter for the whole and the admin will be highlighted darker. And we'll name this data for trend chart. Hmm that should do it. Now once this data is ready, let me put it back to normal.

In the next video, we will learn how to make a trend chart out of this till then, keep practicing keep learning. See you in the next video.

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.