Info-graphics: 3D Glass Chart in Excel

[Music] hello friend welcome back my name is pre Andrew Kumar in this video you will learn how we can create a beautiful 3d glass chart in Microsoft Excel so as you are seeing my screen I have created this beautiful 3d glass chart I have all data said John by service level and here I have displayed this unreasonable in the glass if I will change the value of any service level let's say for East it is 40% if I will change it as 85% then the glass for East will be filled accordingly similarly for the others also if I will change it as 25 then it will be filled only 25% this chart can be used for for example if you want to do it as a by service level then you can use it or you want to see date wise then even you can create discharge all right friends so in this video we will learn how we can create it in Microsoft Excel so let's start I am going to copy this table and I will add a new workbook here press ctrl n and then I will paste this data here so first of all I will require to support column to create this chart first of all I'll add 5% in the service level value so I am adding here 5% because we have to cap in this class this upper cap and the lower gap so we will add 5% for the lower cap and the maximum value we will take here as 110 per service level can be maximized at 100% and the 10% we are taking 5% for the lower care and 5% for the upper gap now here I will create a chart let this Joan and then I'll press the control key and using the mouse I will select this range and this range also we will create the chart for the selected range now I'll go to the insert charts and then I'll insert a 2d clustered column chart so you can see this chart has been inserted here now first of all I'll for me the vertical axis so right click here go to the format axis now here the minimum value I'll take a zero which is already there but still we will put here so that the auto will be changing to reset similarly for maximum value we will take 110 percent because for maximum we are taking 110 percent so here we will type 1 point 1 which is 110 percent now I'll close this window and select this and press Delete similarly for deletion and similarly for the gridlines now we will fill the color to just double click on this east blue column I will go to the fill and line and I will fill some dark colors so solid color and I will take some dark color let's say I'm just taking this one and for this second for bears I'll fill them other color and I will choose this time let's say this blue dark blue and then let's say dark red and here I'll take purple so we have filled this color now what we will do first of all I would like to remove the grid lines from the worksheet so go to the view and remove the grid lines and now I will insert a save so go to the insert shapes and then I will insert a say which is rectangle so you can take it from that angle and just take this simple that that means change size of this rectangle take almost this size now right-click go to the format see so this form it's a window will be open now here we will go to the fill in line which is already there and we'll choose the gradient field so this is some default gradient is available here so what I will do first of all I'll choose here linear which is already here otherwise you can select from here and then the angle I will take 0 degree and then I'll take here 6 stop the fastest stalks will be on 0 then the second stop should be on 20% you can change the position from here 20% then the third stop just click and it will be added will take on 40% and the fourth one we will take on 80% so just change it manually as 80 and then we will take next one is on 90% just make it 90 and then the last one 100% we have added six stops here we will change the color of each stopped for the first stop we will keep the black color this one then we will give the white color for the next one again black then divide and for the fourth one we will give the black then white again black so we will start from black and end with black all right so our say will look like this sequin see here this shape has some outline so we will go to the line and say no it doesn't have just make a copy of this and paste it here and just keep this here so this will be the lower cap and this will be the upper cap I'll make another copy of this and this time I will keep this here now I will change the size which is the height of this and we'll create height almost decide so our same will look like this now select the middle shape and again go to the gradient field this time we will keep the same gradient but we will give some transparency so for the first one we will give almost 50% transparent the second one we will keep 75% transparent and the third one we will keep hundred-percent similarly for the fourth one we will keep hundred percent transparent and the fifth one 75 and the last one we will keep 50% then our side will look like this now select this shape right click and make this quote now copy this save and select the orange column and just paste this here right click on the chart go to the form a data series now here in format data series in series option the Cirie's overlap is minus 27% just put here 100 and the cab bead we will take around 50 or the 60% then our chart will look like this now we will form a axis so we will take the fourth name ad agency FB and make it bold and change the color as black make it little bit of bigger around 12 and then we will change the line color also this line so select the horizontal axis again go to the form a shape outline chooses black and the same outline babe we will take around this one so it will look like this now the time to add data label so right click and edit a label data level has been added here so what I will do and select the plot area and change the height like this and then we can change the height of entire chart now right-click on the data level here go to the fornicator navel and here we will choose value from cells value from cells feature is available in Excel 2013 or above version it is not available in Excel 2010 or below version if you are using Excel 2010 then you can simply select this go to the formula bar press equal to and just click on this service level value and just press ENTER it will be changed but here since I'm using Excel 2013 I will use value from cells option is click here and I'll give the range of the data level which I want to display this service level value click on OK and now uncheck value and so let aligned from here now I'll change the font of this data level I'll take as as you'll see every and the size I will take around 15 and made these bold now we will change the data level color we will give the theme colors so just select the first data level clip and go to the formate and here we will go to the text fill and color we will give the similar color this one and for this we will give the dark blue this one and similarly for this we will keep the dark red and then the purple color our chart is ready here you can put your chart title whatever you want to give you can move this shape here so that is how you can create your chart similarly you can use the other chart also the method will remain same you can create when your data has around 20 records and still you can create this chart I hope you have enjoyed this video thank you so much for watching [Music]

Channel: PK: An Excel Expert

Views: 170,605

Rating: 4.9408798 out of 5

Keywords: ADVANCE EXCEL, VBA MACRO, BI Dashboard, EXCEL dashboard, Info-graphics in Excel, 3D Chart in Excel, 3D Glass Chart in Excel, Auto Shapes in Excel, Gradient fill in Excel, Business Chart in Excel, Innovative chart in Excel, Beautiful Chart, Informative Chart, KPI Chart, Service Level Chart, How to use Shapes in Chart, Gap Width in Excel Chart, Series overlap in Excel Chart, Battery Chart in Excel, PK’s Chart

Id: KsyySvsdz0w

Length: 10min 14sec (614 seconds)

Published: Tue Feb 19 2019