Creating Pie Chart from Slab Report from Pivot

Advanced Excel Dashboard Crash Course Section 2: 1st Dashboard using Pivot
4 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.78
List Price:  €129.90
You save:  €37.11
£79.40
List Price:  £111.16
You save:  £31.76
CA$136.11
List Price:  CA$190.56
You save:  CA$54.44
A$154.13
List Price:  A$215.78
You save:  A$61.65
S$135.08
List Price:  S$189.12
You save:  S$54.03
HK$782.28
List Price:  HK$1,095.23
You save:  HK$312.94
CHF 90.61
List Price:  CHF 126.85
You save:  CHF 36.24
NOK kr1,085.23
List Price:  NOK kr1,519.37
You save:  NOK kr434.13
DKK kr692.01
List Price:  DKK kr968.84
You save:  DKK kr276.83
NZ$167.80
List Price:  NZ$234.94
You save:  NZ$67.13
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,976.08
List Price:  ৳15,366.96
You save:  ৳4,390.87
₹8,339.52
List Price:  ₹11,675.66
You save:  ₹3,336.14
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦141,842.81
List Price:  ₦198,585.61
You save:  ₦56,742.80
₨27,810.04
List Price:  ₨38,935.18
You save:  ₨11,125.13
฿3,647.70
List Price:  ฿5,106.92
You save:  ฿1,459.22
₺3,232.12
List Price:  ₺4,525.11
You save:  ₺1,292.98
B$499.21
List Price:  B$698.91
You save:  B$199.70
R1,908.54
List Price:  R2,672.04
You save:  R763.49
Лв180.65
List Price:  Лв252.92
You save:  Лв72.26
₩135,197.71
List Price:  ₩189,282.20
You save:  ₩54,084.49
₪368.63
List Price:  ₪516.10
You save:  ₪147.47
₱5,633.91
List Price:  ₱7,887.71
You save:  ₱2,253.79
¥15,144.47
List Price:  ¥21,202.86
You save:  ¥6,058.39
MX$1,659.40
List Price:  MX$2,323.22
You save:  MX$663.82
QR364.31
List Price:  QR510.04
You save:  QR145.73
P1,370.91
List Price:  P1,919.33
You save:  P548.42
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,729.52
List Price:  E£6,621.52
You save:  E£1,892
ብር5,680.63
List Price:  ብር7,953.11
You save:  ብር2,272.48
Kz83,612.74
List Price:  Kz117,061.18
You save:  Kz33,448.44
CLP$97,978.20
List Price:  CLP$137,173.40
You save:  CLP$39,195.20
CN¥722.95
List Price:  CN¥1,012.16
You save:  CN¥289.21
RD$5,921.50
List Price:  RD$8,290.34
You save:  RD$2,368.83
DA13,490.83
List Price:  DA18,887.70
You save:  DA5,396.87
FJ$226.12
List Price:  FJ$316.58
You save:  FJ$90.46
Q779.86
List Price:  Q1,091.83
You save:  Q311.97
GY$20,923.51
List Price:  GY$29,293.76
You save:  GY$8,370.24
ISK kr13,946.60
List Price:  ISK kr19,525.80
You save:  ISK kr5,579.20
DH1,013.19
List Price:  DH1,418.51
You save:  DH405.32
L1,763.34
List Price:  L2,468.75
You save:  L705.40
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$805.89
List Price:  MOP$1,128.28
You save:  MOP$322.39
N$1,893.44
List Price:  N$2,650.90
You save:  N$757.45
C$3,681.15
List Price:  C$5,153.75
You save:  C$1,472.60
रु13,335.63
List Price:  रु18,670.42
You save:  रु5,334.78
S/370.84
List Price:  S/519.19
You save:  S/148.35
K382.72
List Price:  K535.82
You save:  K153.10
SAR375
List Price:  SAR525.01
You save:  SAR150.01
ZK2,522.76
List Price:  ZK3,531.96
You save:  ZK1,009.20
L461.43
List Price:  L646.02
You save:  L184.59
Kč2,350.75
List Price:  Kč3,291.15
You save:  Kč940.39
Ft36,729.02
List Price:  Ft51,422.10
You save:  Ft14,693.08
SEK kr1,071.30
List Price:  SEK kr1,499.86
You save:  SEK kr428.56
ARS$85,766.82
List Price:  ARS$120,076.98
You save:  ARS$34,310.16
Bs691.04
List Price:  Bs967.48
You save:  Bs276.44
COP$387,583.68
List Price:  COP$542,632.66
You save:  COP$155,048.97
₡50,832.34
List Price:  ₡71,167.31
You save:  ₡20,334.97
L2,468.78
List Price:  L3,456.40
You save:  L987.61
₲737,805.73
List Price:  ₲1,032,957.54
You save:  ₲295,151.80
$U3,781.90
List Price:  $U5,294.82
You save:  $U1,512.91
zł400.73
List Price:  zł561.05
You save:  zł160.31
Already have an account? Log In

Transcript

Hello everybody, welcome back to dashboard. In this video we will see how to create a slab report. And once again we'll be creating a copy of the same pivot table so that the pivots that we are creating are connected to the same master data. So we will hold on the Ctrl key drag and drop as a third pivot. So I'll just change the name to pivot three. And I'll be deleting the chart for now.

So here I'm planning to create a slab report. So a salary wise slab or salary is given to what experienced person but the question over here is our data has unexperienced option. There are some na values. Now let me explain what the problem is. So I'll just remove all the fields. So I will drag and drop in the salary in US dollars.

That is the total and they experience that is the yearly experience in the role label. And as we can see the Extreme bottom of the table data there is value called not applicable. Not Applicable is where the conditions are not to be met. But if I try to right click and try to group it now. Now that's a feature already mentioned in a previous video is applicable to two data sets. One is date and the other is number and once I tried to group it, it says the group cannot be possible because there is a text value which cannot be grouped.

So to do So first, I have to update my data. So I'll just filter the data and I'll just convert these values to zero. And so once converted into the data, I'll just clear the filter. Now my data has been updated, and I just have to redo the process. So right click, and I refresh the data. And as soon as I do that, now there are no values as such.

Now let's see the output and when I right click and group it this time does not give me an error and it brings up the data directly. Now I can have a slab report because the numbers are grouped into slabs. The first condition, what is the start with the smallest possible number and I will start with zero. And at the end by default the system takes the biggest number here as of now it's 40. And we can go for an even bigger number. So the 40 years is the maximum experience.

I will go with 50 as my deadline, then need the interval was a gap between the slabs so I'll go with five years of gap or gapping between the experience Okay, now keep it as zero. And once done, just click on OK. And the slab reports are ready. Isn't that cool? Now if you wish to have a 10 year gap, it is also simple enough just have to change the five to 10. And there it is. There might be a question.

It's a Zero 1010 to 2020 to 30. So the numbers are getting repeated. But don't worry, this is because there are decimals and if there are no decimals, the next slab will always start from 11. So because of the decimal problem, there's a decimal, the first variable is actually from 10.5. And the next one is from 10.6. So that's how the slab is divided.

Once done, that is the total salary just keep it as it is, once again, alt f1 to create the chart. And this time as we are just providing slab reports as in sections and groups, so we are planning to prepare the data so we'll make a pie chart. So we will make a pie chart. So this will be right click on the chart and change the chart type pie diagram now go with 3d pie. So that gives us an overview of how the data looks. Now to make the data even better, we can simply go and click on the brush icon to format it.

And we can also use the plus sign That his chart elements, which is also called Layout option, and which is provided as a plus just next to the chart from 2013, then layout that is data label data call out. Once done simply we can delete any parameter that is not here. Now I'll just shift this to a little bit to the right. And that should do it. So here's our third chart. So just to recap, now we have three pivots with three different reports being generated, but all coming from the same data.

Now comes the climax for the first dashboard. In the next video, we'll be seeing how to club all these charts into one screen and make a final dashboard and use some buttons to control it. 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.