Transcripts
1. Introduction - What are Infographics?: Hey, welcome to the course. In today's fast-paced world. Who wants to spend a lot of time reading, trying to understand pages of complicated facts and figures. It's certainly not a coincidence that so much of what we share today, maybe online or offline, is in the form of pictures and graphs. In this information driven world, there's just so much information around us that we cannot possibly remember it all in a limited amount of time. Therefore, using infographics to convey figures and data will help people absorb information in much more efficient way. So what are infographics? Infographics are graphic visual representations of information, data, or knowledge intended to present complex information quickly and clearly. And infographics display large amount of data and information in the form of graphs and pictures. And did you know you could create amazing professional looking infographics using Microsoft Excel? Are you excited to explore the power of Microsoft Excel to create visual and dynamic infographics, to make a dashboard looks model. And you'll be appreciated for your data visualization skills. If yes, then you are in the right place and I'm really happy and excited to have you here. Welcome to the XL infographics masterclass series. So why learn it for graphics and first-place? One can create infographics for a variety of reasons. Using them to grab the audience's attention, educate viewers, and drive engagement via multiple platforms. They are entertaining, eye-catching, concise, and all information they contain is easily digested by the reader, so they are definitely used. The eye-catching visuals are perfect for social media, email, newsletters and even corporate presentations and infographic shared in the right place at the right time can translate into a ton of information and insight that probably even a lengthy PowerPoint presentation cannot deliver. So what will you learn in this particular course? You must have definitely seen exit dashboards which are filled with same old boring line bar and pie charts. In this tutorial, you will learn how to create infographics with Excel. And each infographic will come with a fully functioning and updateable model and child added new life if you use them in constructing your professional words. In each series, we will learn ten or more infographics, which we build a right from scratch. And then it can be stored in your Excel file as templates that can be used based upon your dashboard requirements. Each lecture comes with it spawned raw data and resource file that you can utilize later for your own practice. That not only gives you a hands-on experience, but a lot of confidence. And do not forget, that is a project file attached with the course along with the raw data that you can use to complete your project and submit it as a proof of your learning. So what are you waiting for? Let's get started. I'm super excited to see you inside the course.
2. Lesson 1 RAG Chart: Hey, welcome back. Now what you see on your screen right now is referred to as the red, amber green chalk. Now why do I call it a red amber green chat is because any metrics that I see over here is reflected by a Conditional Formatting. Which means that if at all any of these metrics fall within 0 to 60%, then that particular metrics will be reflected by a red colour. If the matrix value is between 61 to 80%, then it will be shown as Amber and effect all the value of the metrics is about 80%, which could be the benchmark. Then the value will reflect in green shade. And this particular chart is completely dynamic. So if at all, i go ahead and change the value of productivity from 85% to 55%. You will observe that the shade just changed from green to red. If at all, i go ahead and change this value to around 75, you will see that the red shade has changed to a Amber shade. And similarly, even for my service level, if at all i go ahead and change the value of 6595% percent, you will see that the color changes dynamically. So our objective in this particular lecture will be on understanding how do we go ahead and create these beautiful charts. So what I'm gonna do first of all is I will go ahead and create this 3D layout for my charts. And what I'm gonna do is I will copy this raw data onto a blank Excel sheet. So I have gone ahead and pasted the data on a blank Excel sheet. I'm just going to select this entire sheet. Click on the View tab and I'm going to hide the grid lines. Okay? And now what I will do is I will go ahead and create these 3D images post. So for that, what I'm gonna do is I'm going to make use of PowerPoint 2016. You can use the PowerPoint that you have in your system. Any PowerPoint version will be able to do the tasks that we will be doing right now. So I'll open up a Powerpoint blank presentation. And then what I will do is I will click on, Insert, click on shapes. And then I will go ahead and select this particular shape, rectangle, rounded corners, OK. And I will just draw a rectangular weir. Okay? And now what I will do is once again, I will click on Insert voters shapes, and this time I will draw a over. And now what I'll do is I'll just go ahead and paste this oval on my rectangle. Okay? And then what I will do is I will go ahead and select both the shapes together. Click on format, and then I will click on mode shapes. And I'm gonna go ahead and click on combined. Okay? And now what I will do is I will go ahead and select the shape and paste it on my Excel sheet over here. Okay, and now let's go ahead and give it some 3D effect. So what I will do is I will select this diagram or we'll go to the format tab. I will go to shape effects, good presets. And I will go ahead and select a particular preset over here. So I'll go ahead and select preset three. And I will also go ahead and change the color of the shape. So what I will do is I will go to shape Phil, and I will go ahead and select this particular shade over here. Okay, downgrade. Cool. So with that, what I have done is I have gone ahead and created this rectangular outline that we will be using for going Ed and creating our chart. Now what I'm gonna do is I will go ahead and click on insert. Once again, flick on shapes, and once again I will click on over. And now I will go ahead and draw a well over a year. And then what I'm gonna do is I'm gonna go ahead and paste this over l over u. Ok. And now I will right click on it, go to Format shapes. And now I will click on finish. I'm gonna go ahead and select a gradient fill over you. And over here what I'm gonna do is I'm going to select the radial fill. Okay? I will come down to the gradient stops over here. I will click on the first gradient stop over here. And what I'm gonna do is I'm gonna select the color as white. Okay? And then what I'm gonna do is I will go ahead and select the position as 0, transparency as 0, and even brightness, I'll keep it as 0. I will go to my second stop over here. And I will go ahead and select this light gray shade over here. Okay? I will change the transparency to a 100%. And I will go ahead and change my brightness to fifty-five percent. Okay? So with that, I have gone ahead and formatted the circle inside over here. What I will do now is I will also go ahead and select the rectangle. Right-click on it, and then I will click on Group. So I can move this entire outline and keep it somewhere over here. Okay? And now what I will do is I will go ahead and select these cells over you. Give it to outline bottom. And what I will do is I will go ahead and select these particular cells. And now I will go ahead and insert a formula for you. And for that what I'm gonna do is I'm gonna go ahead and use my conditional formatting. So I will go ahead and click on Conditional Formatting and I will click on New Rule and then I will select this option, Use a formula to determine which cells to format. And now I will start giving my formula where you, So what I'm going to say is, is equal to this particular cell. If it is less than or equal to 60%. Then format this particular cell range in red color. So for that, I'm going to go ahead and click on Phil. Click on this particular shade over here, and then I will click on OK. And click on okay one more time. If I go ahead and change this value to 50, you will see that the color changes. Now, I will go ahead and add a second formula to this. So I will once again select these cells over here, and then I will go to my conditional formatting. I will click on a new rule. Once again, I will select this option, Use a formula to determine which cells to format. And now I will give the rule for the amber shade. And for that what I'm gonna do is I'm going to be used the and formulas. So I will say and, okay, if at all this particular cell is greater than or equal to 60.01%. And if at all this particular cell is less than or equal to 80%, then I will say format this in amber shade, and then I will click on OK. And click on okay one more time. So if I go ahead and change this value to 80%, now, you will see that the color from red changes to Amber. And now I will go ahead and give a formula for my green shade. So what I will do is once again, I will select this entire range over here. Click on conditional formatting, click on new rule. Go to use the formula to determine which cells to format. And then what I'm gonna say is equal to this particular cell is greater than 80%. Then format this range in a green Shapes. I'm going to select this particular green over here. And then I will click on, okay. So with that, I have gone ahead and set the rules for the cells over here. Okay, if at all i go ahead and change this value from 80 to 81%, you will observe that the amber shade changes to green. Now, all I need to do is copy the cells. Press control C, And then I will go ahead and paste them and a little distance from each other. Okay? And I will now go ahead and change the cell references for these particular cells over u. Ok, so for that what I will do is I will go to my conditional formatting and then I will click on Manage Rules. And now what I will do is instead of D2, i will just change it to D3 because service level is available in cell D3. So I will just double-click on it, and I will just go ahead and change this D2 to D3. And similarly over here also, I will go ahead and change the D22, d three. And then I will click on apply, and then click on, okay. If I go ahead and change any values over here now, you will observe that the colors are changing. Similarly, I will change the cell reference for my quality scores as well. So I will select the cells, go to conditional formatting, click on Manage Rules, and all the D2 will now be converted into d4 because quality score value is available in the cell D14. So I will just double-click on it and change my D22 default. I will do the same for the remaining rules as well. Now let's check our quality scores as well. So if at all i go ahead and change this value from 45 to maybe 90%, you will observe that the values change accordingly. Now all I need to do is just go ahead and create two more copies of this layout. So what I'm gonna do is I will click on Control C and press control v twice. So with that, I have gone ahead and created three copies which I'm going to place on my individual KPIs, no. Okay. So let's go ahead and do that. Okay. And now what I will do is I will just give them a label. So for that, I will click on Insert, go, do I work it out? And I will select a particular word or Dojo. And then let me just call this as productivity. I will change the font size and I will place it over here. Okay? I will create a copy of this one. I will place it over here. And I will call this as service level. And then I will go ahead and create a copy of it. They sit over here. And I will call this as quality score. Okay? And now let's quickly go ahead and test our RAG chart. Okay, so let's go ahead and change the productivity from 80% to maybe 60%. Let's try changing the service level from 80% to 95%. Or try changing a quality score from 90% to 35%. And you will observe that as and when the values of the KPIs or changing, the signals change accordingly. So this is how you can go ahead and create a nice red, amber green shot to go ahead and set a benchmark for your KPIs and then show them in your dashboard to reflect in which particular category they fall. Are they meeting your benchmark? Are there very close to your benchmark or are you missing out the target completely? So that's it about the RAD char, I hope you enjoyed this one and I shall see you in the next one.
3. Lesson 2 Forecast Vs Actual: Hey, welcome back. So what you see on your screen right now is what I referred to as the forecast versus actual chart. Now these type of charts are basically used in call centers where you have Podcast number of calls. And then against the focus, there are actual number of calls that are received. Now based upon the forecast, you decide a safe zone. Now saved zone is basically a variation of a certain percentage that is acceptable. In our case, the range is 10%. So whatever it is, our forecast, as long as the calls are plus or minus 10% of the forecast, we are in the same zone. But if I told the total number of actual calls that are received are beyond the 10% benchmark, then it will fall outside this green zone, which is the safe zone. And that is something that we really need to look into because we need to ensure that whatever are the actual number of calls that are received are always within the safe zone and an acceptable level of variance. So usually a focused versus actual chart is created to give us an insight in terms of how close or how far we are from the forecast. And based upon which, if at all, there are certain actions that needs to be taken. Now if you observe this particular chart, the blue dotted lines over here reflect my focus data. Whereas the orange bold line reflex my actual number of calls that have been received. The green section reflex my safe zone. Whereas if you look at this particular chart at the top, you'll see something called as the best-fit. Now, best fit is the percentage or the variance, if at all we go ahead and replace it within our range, all the values of the actual cause received will fall within our Safe Zone. For example, the best fit over a year is 26.9%. So if at all i go ahead and put my ranges twenty-seven percent, then you will observe that nothing falls outside my safe zone. My saves on actually expands. And even the extreme values of 1000 calls or 500 calls falls within my safe zone. So let's understand how do we go ahead and construct this particular chart to the example that we have over u. So I will just go ahead and replace this to 10%. Okay? And now what I will do is I will just go ahead and copy this raw data onto a blank Excel sheet. Also, I will go ahead and copy the range and paste it on my blank Excel sheet over u. Ok. I will select this entire worksheet, go to the View tab, and I will hide their Gridlines. And now I will show you how did I come to this particular upper limit, lower limit and the same source. So I will delete it and I will show you the formulas that I have used to go ahead and calculate my upper limit, lower limit and my saved soon. So basically what my upper limit is, it's equal to My focused plus this particular range away or so I'm going to hit F4 because I want to keep it constant. And then I will multiply it with the forecast once again. And I will just go ahead and drag this formula. Okay? Similarly, my lower limit is nothing but my focused minus my range. Once again, I will hit F4 to freeze it and then multiply it with the forecast once again. Okay? And then I will drag this formula as well. And basically what my Safe Zone is, nothing but my upper limit minus lower limit. Okay? And I will go ahead and drag this formula as well. And now what I will do is with this particular data, I will start constructing my chart. So what I'm gonna do is I'm going to press the Control key and I will select these particular cells over u. Then I will select the lower limit. And then I will select this same x2. And then I will go to Insert, click on this particular option that says Insert Line or ADR job. And then I will go ahead and select this particular option which is line with markers. Okay? I will just go ahead and increase the size of this one. Okay? And now let's go ahead and right-click on our chart, and then click on this particular option that says Change CDS chart type. Okay? And now what I'm gonna do over you is I will come down to this particular job which is lower limit. And I will go ahead and convert it into a stacked area chart. Okay? And similarly, I will go to my safe zone over here. And I will once again converted into a stacked area chart. Okay? And then I will go ahead and click on OK. And now what I will do is I can select this lower limit over you. Right click on it, click on format data series. And then I will go to this particular fill-in line option. Go to fill. And then I will click on No phil. Ok. Then I will just go ahead and increase the size of my channel a little bit. Okay? And then I will go ahead and remove this lower limit from my job. Ok? So now what I have over you is this blue line, which is my forecast line. Then I have the orange line, which is my actuals. And then I have this yellow drone, which is my safe zone. So now let's go ahead and format our chart a little bit. So what I'm gonna do is, first of all, I will select the job, go to Georgia lemons, and I will remove the grid lines. Okay. Then I will just select the safe zone. Right-click on it, click on format data series, go to fill an online option, and then I will click on Gradient Fill. Okay? And then I will select a radial fill OEO. And then I will go to my first gradient stop. I will go ahead and select the color as light green. Then I will come to my second gradients TOPO video, which I will keep it that around 50% And then I will go ahead and select the color as dark green oil. And now I will go ahead and increase the transparency to around 75%. Okay? And now what I will do is I will go ahead and format my forecast line or wheel. So what I will do is right-click on this particular line over here and click on format data series. Then once again, I will go to fill endline optional video. And under the lines option over here, I will go ahead and select a solid line. Okay, I will give the color as Doug blue. And now what I will do is I will go ahead and reduce the width to just 1. And then I will go to this particular dashed DIYbio and I will sit a dotted lines away. Ok. And now what I will do is I will go ahead and format my actions. So what I will do is right click on it. Click on format data series. Once again, go to the fill-in line option. Click on solid line over here. I will go ahead and select the color as orange. And I will go ahead and increase the tilt a little bit. And then I will click on the marker options or V0. And under the mockers, I will click on market option. And then I will click on this particular option that says Berlin. I will select any one particular type of markers. So let's go ahead with the diamond one. And I will just go ahead and increase the size of the diamond. Do maybe eight units. Okay, and then I will just go ahead and close this. And now what I want is the data labels for my actual. So what I will go ahead and do is click on Chart Elements and then click on data labels. Okay, let me just format the data labels a little bit. So I will go ahead and right click on the Data Labels, click on change data label ships, have it select this particular shape over here, and then I will go ahead and give it a small Fill color. So let's click on the gray shade or wheel. And let's also go ahead and change the color to black and make it a little bored. Okay. Now let's go ahead and give it a nice chart title. So I'm going to call it a forecast, was his actual job. So with that, we have gone ahead and created our forecast was his actual chart. Now let us quickly understand how do we go ahead and incorporate a best fit line or how do we find the best fit percentage? So for that, what I'm gonna do is I'm going to do a small calculation of V0. And the calculation goes like this. It's equal to actuals divided by the forecast. Okay? I will go ahead and convert this into percentages. And then I will just go ahead and drag this particular formula. Okay? Then I'm gonna say is equal to one minus this particular value where you and then hit Enter. And then I will go ahead and drag this particular formula we are. And what I need is the absolute value of these percentages. So what I'm gonna do over here is I will just go ahead and say absolute. That is ABS, open brackets and close brackets over here and hit enter. And once again, I would just go ahead and drag this particular formula. We'll, and now I will go ahead and calculate my best fit. So I will type best fit over here. I will just go ahead and select this ready-made format. And I will format this. And now my best-fit is nothing but the maximum value out of this entire range that I have over you. So basically my best bit is 27%. So if at all i go ahead and change my range from 10% to 27%, you will observe that all the data within my actual data column fits well within the safe zone. Okay? So I will just go ahead and change it back to 10%. And you will observe that all the values which are not within my safe zone now, showing outside this safe zone. Okay? So that's how you go ahead and create a wonderful forecast versus actual chart. That gives you an insight in terms of what does the actual variance as compared to your focus. And that is something that you can go ahead and look into and based upon which you can take certain actions. So I hope you enjoyed this lecture and I shall see you in the next one.
4. Lesson 3 Employee Performance Infographic: Hey, welcome back. Now what you see on your screen is a kind of an employee performance chart. But it has been shown in a very unique style over you. And if you can go ahead and create a chart like this, it can be incorporated in a team performance dashboard. And it, and really unique because every line over your represents the data for one particular team member. And along with that, the labels will actually tell us who is the best performers in the team, while Who is the bottom performer in the team? And while we look at the top and the bottom performer, we can also look at the performance of other team members in the team. So it's a very unique style of going ahead and showing that data. So let's quickly understand how do we go ahead and create this fancy visualization of our team statistics? So what I'm gonna do is I'm gonna go ahead and copy this data onto a blank Excel sheet. And now with this particular data, before we go ahead and start constructing the chart, will make a small modification over you. In other words, we are going to use a supporting data or you. So what I'm gonna do is I will just go ahead and insert another blank column over here. And I will call this as supple. And what I'm gonna do is I'm just gonna go ahead and add 0 to this entire column, Maria. Okay, and now what I'm gonna do is I will go ahead and hide the grid lines first. So I will go to the View option and I will hide the grid lines. And now I will go ahead and reduce the size of this column so that it is not visible. Okay, so I'll go ahead and reduce it. Ok, and now what we would do is we would go ahead and select this data. And then we will go to the insert tab. And we will go to this particular chart, which is line with markers. Okay, so let's go ahead and click on that. And haven't go ahead and drag this over here. And I will enlarge this size a little bit. Now, I don't want these grid lines, so let's go ahead and remove them. So I'm gonna go ahead and remove the grid lines. Okay? And I also don't want the axis, so I will remove the axis as with. And now what I would do is I will go ahead and select this line chart over here, right-click on it. And then I will click on Select Data. And then I will go to this particular option that's a switch column and row. And I will click on that. And I will click on OK. OK. You will observe that most of the work is already done. My chart is almost ready. All I need to do is just a little bit of formatting urine there. So what I'm gonna do is I will just select this legend over here, right click on it, click on Format Legend. And instead of keeping the legend to the bottom, I will just shift it to the left. Okay? And now what I will do is I will just increase the size of the chart a little bit. And let's go ahead and format these lines a little bit. So what I'm gonna do is I'm going to select this first line over here, right-click on it, and then click on format data point. Then I will go to this fill in line option over you. And we haven't click on markers. I will go to the market options and we'll click on built-in. And oh yeah, I will select this building type as a dot that you see over here. And I will just go ahead and increase the size of it to ten units. And then I will go to the fill option over here. And then I will go ahead and select the color as white. Okay? I will do the same for the other lines as well, so I will just right click on them. Go to Format Data Series, beautiful landline, Buddha mockers. Click on Marcus, will build in. Selected, increase the size, go to Automatic, and then select the color is white. I will do that for all the rest of the three. So I will just go ahead and do that quickly. Okay. So I have gone ahead and done the formatting for all the lines that I have a wheel. Now let me just go ahead and add the data labels to it. So I would just right-click on it. And then I will click on Add Data Labels and we'll do the same for the rest of them. Okay, and now let's go ahead and quickly formatted data labels a little bit. So I will select them, I will make them bold, and then I will just increase the size a little bit. And now what I will do is I will select it, would've home. Double-click on Format Painter. So I select the format and I will just go ahead and click on rest of the data labels law. Okay? And now what I would do is I will go to this particular Insert tab over here. And then I will go to shapes. I will select an overload where you and I will draw or I will reduce the size a little bit. And then I will go ahead and select this particular colour or black. Go to shape effects, go to glow. And I will select this orange glow, you. And I will reduce the size of it a little bit. And then I will place it over u. Ok, and now I will select my chart. Right-click on it. I would click on Outline. And I will say no outline. I will go to Phil and I will say no film. Okay? And now what I would do is I will get my chart or type it. So I will say this is a deme, performance jump. Okay? And now if I go ahead and change any values over here, so let's say I make this modified a 100. Ok? You will observe that my chart is changing dynamically. Okay, so you can go ahead and make use of these kinds of visualizations in your professional dashboards and give them a nice look and feel. So I hope you enjoyed this lecture and I shall see you in the next one.
5. Lesson 4 Line Microcharting: Hey, welcome back. Now what you see on your screen right now is something that I referred to as the line microwave routing techniques in infographics. Now micro chatting and using line charts is something that you must be really familiar with. And probably you must have used it in your dashboard multiple times. But if you observe that in these particular charts have used certain picture elements and then go ahead and add these kind of picture elements to your micro to charge. It gives a complete new look and feel to your professional dashboards. And that is what we will learn in this particular lecture. And if you observe this particular dashboard that you see in front of you right now, now this is the revenue dashboard. And always the markers are represented by a dollar. So it's very evident that it has something to do with money. If I'm looking at the VHDL wheel, it is represented by a watch. So anybody can understand that it represents time. If it is C sat, it's basically customer satisfaction and therefore it is represented by a smiley on a face. While this particular chart is about production, and therefore it chose production boxes, which will give some idea in terms of what it is trying to show us that this is a very helpful technique through which we can go ahead and give a kind of an idea in terms of what is the kind of data that we're looking at. And at the same time give a nice look and feel to our official dashboards. So let's quickly go ahead and understand how do we go ahead and construct these kind ofs small micro charts in a dashboards. So what I'm gonna do is I'm going to go ahead and copy all these raw data onto blank Excel sheets. But along with the raw data over your, I also have some PNG images that I have picked up from move it. Okay, you can go ahead and pick up any PNG images from Google. Just make sure that there are no copyright issues around that. So you can go ahead and pick up any images which are reusable. So that is what I have done. I've gone ahead and picked up for reusable images from Google. And I'm gonna go ahead and eat God put it then in my line charts that I'm going to go ahead and create. So let's go ahead and copy the raw data to our blank Excel sheet. So now I have gone ahead and copied my data and my images to a blank exit sheet. Now before I go ahead and construct my dashboard, what I'm gonna do is I'm gonna go ahead and select the Excel sheet. Click on the View tab and I'm going to hide the gridlines. Okay, and now let's quickly go ahead and start constructing our line charts. So what I'm gonna do is first I would select this data, voodoo Insert. And then I will go to the line chart and I will select Line with markers. Okay, let's go ahead and please this data, we're okay. I don't want the grid lines, so I'm gonna go ahead and select it. And I'm going to hide the gridlines. So with that, we have gone ahead and created our sales line chart. Let's go ahead and create the line charts for other elements as well. So let's go ahead and quickly construct the line chart for a PhD. So let's select the data. Click on Insert voodoo line charts, and select Line with markers. Okay, let's go ahead and place it a wheel. Okay, and I will remove the grid lines. Okay? I will construct a line chart for my seaside. So I will select the data, go to Insert Line Chart and select the Line with markers. Let's place a wheel and then let's resize it. Ok. And let's go ahead and hide the grid lights. And now let's go ahead and construct a line chart for up production. So I will click on Insert, will do line chart, select Line with markers. And then I will go ahead and place it over here. And then I will hide the grid lines. Okay, and now I need to go ahead and add these elements to my chart. Okay, so over here, I need to go ahead and add these dollar signs. Okay, so I'm going to select this particular image, and I'm going to press control C on my keyboard. So I'm gonna copy this. And then I will select any of the markers. The moment I select one marker, all the markers on the chart will get selected. And now all I need to do is just press Control and the letter v. Okay, so let's press control v. And you will observe that immediately my markers have been replaced by a dollar sign. Similarly for my PhD, I need the watch image, so I'm going to select this image. Let's control C to copy it. Select my markers, and then press control v. Ok. For my seaside, I need this, my lease. So I'm going to select the smileys, copy it. Select the markers and Risk Control V. Ok. And for my production, I need this box image. So I'm going to select this image, select my markers, and then I'm going to press Control V. Okay, and now let's go ahead and add the data labels. So I'm going to select the job. And then I will go ahead and add data labels over here. Okay, Let's select the job and add data labels. Okay, let's select this particular job and add the data labels. And I will go here and I will add the data labels as well. And just to let you know that these charts are dynamic, If I go ahead and change any values in this particular section over here, my chart will change accordingly. Okay, all I need to go ahead and now give it a nice title. So I will go ahead and click on Insert Woodward up. And let's select this particular word dot. And then let's call this as executive dashboard. Before we wrap up, let's go ahead and test our charts over here. If I go ahead and make any changes to the values area, you will observe that my charge is changing accordingly. Okay? So this is how you can go ahead and create beautiful micro charts and add some picture elements to it to give it a nice look and feel and add them to your executive dashboards.
6. Lesson 5 Column Microcharting: Hey, welcome back. So in our last lecture we got introduced to the concept of line micro charting in infographics. And in this particular lecture, our focus will be on understanding column micro charting in infographics. So the only difference that we see over here is instead of line charts, we have used column charts. So let's quickly understand how do we go ahead and build column micro charts and then add these picture elements to it to give it a nice look and feel. So to get started, what I would do now is I will go ahead and copy this raw data into a blank Excel sheet. And along with that, I would also go ahead and copy these images that I use in my charts later. So let's go ahead and copy this raw data onto a blank Excel sheet first. So now that my auditor is copied onto a blank Excel sheet, the first thing that I would do now is I will select the sheep, go to the View tab, and I will hide the grid lines. Okay, and now let's go ahead and first construct our column charts. So for that, what I'm gonna do is I will go ahead and select this data, go to the Insert tab, go to the insert column or bar chart option. And then what I'm gonna do is I'm going to select this particular chart over here, the clustered column chart. And then I will just go ahead and resize it and pasted over u. Ok, I don't want the grid lines, so I'm gonna go ahead and select the chart. And then I'm gonna go ahead and uncheck the grid lines over here. Okay, now let's go ahead and construct our column charts for the rest of the elements like HTC, sat, and production. So what I'm gonna do is I'm gonna select the data, go to Insert, go to column and bar charts. And let's select the clustered column chart. Okay, let's resize it and paste it over here. Okay, and let's hide the grid lines. Let's go ahead and construct a column chart for a C set. So I'm going to select the data, click on Insert. Good bar charts. And then let's select the clustered column chart. Let's place it over here. And then let's resize it. And I will go ahead and uncheck the grid lines. I will go ahead and construct a column chart for my production. So I'll select the data, click on Insert, select the clustered column chart. And then I'm going to place it over a year. And I'll go ahead and hide the grid lines now. Okay, so with that, I'm micro chart is ready. Now we need to go ahead and give it a nice look and field by adding these picture elements and then going ahead and formatting it a little bit. So over here I have the sales data. Okay, so what I'm gonna do is I'm going to use this money-back image that I've picked up from the Internet. And I'm going to go ahead and replace these buzz using this particular image over you. So what I'm gonna do is I'm going to select this image, press control C. I will select my bars over here, and I will press control v. Ok. And similarly for my PhD, I will select the watch press Control-C. Select my bar and press control v. Okay, then I will go ahead and use this arrow for my C set. So I'm going to select it, press control C, select the bar and press control v. Ok. Then I will go ahead and use this box over here. Select the bars and press control v. Okay? But this really does not look very catchy and doesn't really look very professional to me. I need to go ahead and do some formatting here and there. So let's go ahead and format are bar charts a little bit now. So what I'm gonna do is I will select this particular bar chart. I will right-click on it and click on format data series. And what I'm gonna do is I'm gonna go ahead and change this gap word from 219200 units. Okay, and now let's go ahead and click on the fill Inline option. And oh yeah, what I'm gonna do is I will come down to this particular option over you. Okay? That's a stack and scale with, and I will click on it. When I click on it, I get this particular option which says units per picture. And what I'm gonna do is I'm going to change this one to a 1000, which means one image will now represent 1000 units. So I will go ahead and hit enter. And you will observe that immediately my bar graph has changed, okay? Or where you're, each money bag represents 1000 units of sale. Now all I need to do is go ahead and select the chart and give the data labels over u. Ok, so with that, the sales data is ready. Let's go ahead and format our data. What I will do is I will go ahead and add the data labels to it. And let's format this a little bit. So I'm gonna right click on it, click on format data series, and I'm just gonna change the gambit from 219200 units. Okay, so it looks much better now. Now, over a year for my arrows, once again, I will select the Basel. We'll right click on it, click on format data series. Changed my gap width from 219200. Hit Enter. Maybe I can just go ahead and reduce it a little bit. So let's go ahead and change it to maybe 80 units. And now let's go ahead and give the data labels over here. So I will go ahead and put a checkmark on the data labels. Okay, let's go ahead and format the production now. So once again, I will select the data, right click on it, click on format data series. I'll change the gap width from 219 to maybe 90. And now I will go ahead and click on the fill Inline option, come to this particular option that says that and scale with. And once again, I will change one to 1000, which means one image will then represent 1000 units in my production. So I will go ahead and select 1000 and hit enter. And now I need to just go ahead and add the data labels over here. Okay? So with that, my micro chart is ready and have also gone ahead and incorporated the images. And now you can observe how amazing it looks. Not only does it look professional on any kind of an official dashboard, but it will also give you an idea in terms of what is the kind of data that it is trying to display. Again, the best part is it is dynamic. If at all i go ahead and change any values over here, maybe I changed this 91 to maybe a 110. You will see that the images when adjust themselves and they will change accordingly. Let's try making some changes over here. So instead of 1253, let me just change it to 2 thousand. And you will observe that the images will adjust themselves accordingly. And it displays the exact data as it is shown in the data table. So that's how you go ahead and incorporate infographics in your column micro charts. I hope you enjoyed this lecture and I shall see you in the next one.
7. Lesson 6 Hit or miss: Hey, welcome back. So what you see on your screen right now is what I referred to as the hit verses missed chart. And why do I call it that? So let's understand that. So over here what I have is a name of certain employees in my company. And along with that, I have that customer satisfaction score. Now for my organization, the target for CSR is 80%. And what this particular chart shows over you is the CSAC achieved by each of this particular employee. And at the same time, it also gives me a signal in terms of whether the target was hit or miss. For example, Sam, who is at 81%, CSAC, has crossed the benchmark of 80%. And therefore he's reflecting in a green shade over u. That is John who is at 45%, has missed the target, and therefore he's reflecting in a red shade over u. Now this particular chart is dynamic. So let's say if at all i go ahead and change the seaside percentage for John from 45% to 90%. You will observe that from the red zone, he's now moved to the green zone. And the labels are now reflected in a green shade, which was showing up in a spreadsheet earlier. Similarly, if at all, i go ahead and change the CSAC percentage for Kyle from 95% to 75%. You will observe that the green immediately changes to red. So this is a creative way of going ahead and showing whether a particular target was achieved or not achieved. And the colors that we use over you give us a visual signal in terms of whether the target was achieved or missed. So let us quickly understand how do we go ahead and create this chart. So for that, what I am going to do is first, I will go ahead and copy this data onto a blank Excel sheet. Okay, so now that I have my data on a blank Excel sheet, let's go ahead and construct our infographic chart. So before I go ahead and start constructing the chart, I would need some supporting data with you. So I wouldn't need the supporting data for my green color. And I would also need some supporting data for my red color. Ok, so let's start constructing AS supporting data first. Let me just format this a little bit. Okay? And now what I'm gonna do is I'm gonna say is equal to if I'm going to open brackets. And I'm gonna say if this c sat value is greater than or equal to 80%, which is our benchmark. Okay? And since I'm going to use the same benchmark for my entire data, I'm gonna go ahead and press F4. That will freeze the rows and columns. And I'm going to say that if this value is true, then return the same value, that is S3. Else, go ahead and return the value as any. Okay? I will open brackets and close brackets. And I will close the bracket one more time and hit enter. Okay? And now I will go ahead and drag this particular formula. Okay? So what is happening over here? So whenever this particular value is greater than 80%, it will show the value. Else it will return the value as any. Now similarly for my red, I will go ahead and give the formula as if this particular value over here is less than or equal to 80%. Then return the value as S3, as returned the value as any. And I will hit enter. Okay, i will have to go ahead and please this i2 away or because I'm going to use the same benchmark of 80%. And I hit enter. And now I will go ahead and drag this particular formula where you, okay, so what this particular column shows is if the value is less than 80%, then it will show the value else it will return the value as any. And now with my data ready, I can go ahead and construct my line graph. But before I do that, what I'm gonna do is I would select this particular chart. I will go to the View tab and I will hide the grid lines. And then I will select this entire data, go to my home tab and format it and give it a nice borders. Okay, and now let's go ahead and start constructing a line drop. So I'm gonna select this data. Click on insert. I will go to the line and area chart option and I will select this particular shot away you okay. Line with markers. Okay, and now with my chart ready, we need to go ahead and do some formatting over you. So, oh yeah, this orange line represents my green zone. Okay, so I will select this and I will right-click on it and I will click on format data series. Okay? And I will go to the line and mockers option. I will go to the line option first and I will say no line. Okay? And then I will go to the mockers section, and I will go to the market options over here. I will select this option has built in. And over here, I will select this dot eoyo. I will increase the size to ten units. Now I will go to the file option or you go to solid Phil. And I will go ahead and select a green shade over u. Ok. And then I will go to the borders. Click on solid line. I will go ahead and select a light green shaded area. And I will increase this 2.25 units and increase the width to 2.2. five units. Okay? And I will just come out in click over u. Ok. So with that, my green zone is ready. Now, the grey section over here represents my red zone. So what I'm gonna do is I will select this grey line or we go to Format Data Series. I will go to the line and mockers option. Come to the line of Chenault. Yeah, I will click on no line. Then I will go to the mockers option. I will click on market option, and I will click on built-in. I will once again select the daughter V0, and I will increase the size to ten units. Okay? I will come down to the field section over here, click on solid fill. And this time I'm gonna go ahead and select the red color YOU. Similarly, I will come down to the borders over here. I will select the solid line. And once again, I will select the color as red. And I will go ahead and increase this to 2.25 units, okay? And then I will hit anywhere outside. Okay? And now all I need to do is go ahead and give this chart a label. So what I will do is I will select the green zone away. I go to my chart elements and I will select data labels. Okay? Now I will go to my red section over here. And then I will go ahead and click on Chart Elements, and I will click on data labels. Ok, so my chart is almost ready. Now, I just need to go ahead and format my data labels a little bit. So what I will do now is I will just go ahead and select the data labels for the green zone over here. And I will right-click on it and I will click on change data navel shapes. I will select this particular rectangle with rounded corners away. Or you can select the shape of your choice. And I will go ahead and colored them as green over here. Okay? And I will change the font color to white. And I will make them a little bowled over you. Ok? Similarly, i will select the data labels for my red zone. I will right click on it, click on change data labels, shapes, and I will select rectangle, width, circular borders. Okay? And now what I will do is I will give them read shade over here. I will change the font to white and I will make it bold. And what I would do now is I will right-click on my Data Labels, click on format data labels, and I will move them below o you are right now they are above my markers. So I will go ahead and select the below option over here. And I will just go ahead and close this. And now my hit versus missed chart is ready. Let's go ahead and test it. So let's say if at all I changed this 81% to maybe 50%, you will observe that the color changes and the green ones move to the red zone. And let's now go ahead and change this 25% to 95%. You will observe that immediately jumped into the green zone and the color changes have occurred. So this is how you can go ahead and create an interesting hit versus mischief, which can give you visual signals or give you a visual information in terms of whether your benchmark was achieved or you missed the benchmark. So I hope you enjoyed this particular lecture and I shall see you in the next one.
8. Lesson 7 Target Vs Achieved: Hey, welcome back. So in this particular lecture we are going to learn a chart called as a target versus achieved chart. So let's understand what is this chart showing us? So this is my sales data for the entire year. And every month that has been target, that has been assigned to me. And against the target, I have managed to achieve a certain number. There have been certain months when the target has been achieved, and then there are certain months when the target has been missed. So let's take an example of January where my sales target was 241, but I managed to achieve only 175, which means that there was a deficit of around 66 sales for the month of January. Whereas if I take the example of the month of July, my sales target was 19 before. While I achieved around 225 sales, which means I created a surplus of around 80 sales. And another thing about this particular chart is that it is a dynamic chart. Let's say for example, in the month of January, if I go ahead and change my total number of seats from 175 to maybe 260, you will observe that immediately my red zone is converted into a green zone, which means that instead of a deficit, now that is a surplus of around 19 sales. And similarly for the month of July, if I go ahead and change my actual sales from 230 to only a 100, you will observe that the green now changes to red and it shows that there is a deficit of around 50 sales. So these type of charts give us a visual insight in terms of the kind of data that we're looking at without really looking into each and every data minute Lee. So a red signal will immediately tell me that there is a deficit of sales for a particular month. Whereas if I look at the green, I know that there is a surplus that has been created for that particular month. So let us understand how do we go ahead and create these beautiful dynamic charts and add them as a data visualization elements in our professional dashboards. So let's get started. So to get started, what I'm gonna do is I'm going to copy this entire data into a blank Excel sheet. And now it my data copied onto a blank Excel sheet. We can go ahead and start constructing our chart. Now before I go ahead and construct my chart, I would need some supporting data over you. So let's get started with the supporting data. So the first thing that I need over here is the sales. Okay? Don't worry about it. What I'm doing right now, you'll understand as I go ahead and start constructing the chart. Then, oh yeah, I need the below the target. Then I need about the target. And then finally I need the data labels. Okay? Okay. So now what I need is the minimum between the two values, the sales target and the actuals. Okay? And I will hit enter, okay. And I will just drag this over here. I'm going to use a formula. And the formula is, if the seas dog it is greater than the actuals, then I wanted to return the value as sales target minus the actuates. Okay? And if at all this is false, then I want it to return a value as any, okay? And I would hit enter, and I will just drag this formula u. And similarly, I will use a formula we'll, which goes like this. If the actuals are greater than the sales target, then I wanted to return the value as actuals minus the sales target and ends that it turned the value as any. And I will close the brackets and hit Enter. And once again I will drag this particular formula we'll, and last but not least, I'm going to use a formula that goes max of these two values. And then I'm gonna go ahead and add ten to it. Why I'm doing that? You'll understand that when we are going ahead and constructing that chart. And I would just go ahead and drag this. Okay, and now with our data ready, let's go ahead and construct our chart. So before I go ahead and start constructing the chart, I will first select the entire worksheet, go to my view tab, and I will hide the grid lines. I will select this entire data and give it a proper Bordeaux. Okay, and now let's start constructing that shot. So what I'm gonna do is I will just go ahead and press the Control key and I will select this data. Just be careful that I'm not selecting the actual data. Then I'm going to press the Control key again. And then I'm selecting the rest of the data on my data table. And now what I will do is I will click on Insert, go to this particular option that says insert column or bar charts. I will select this particular chart and I will add the 2D column charts overview. I will just bring it somewhere away and I will just resize it a little bit. Okay. I don't want the grid lines, so I will remove the grid lines. And what I will do is I will just go ahead and reduce the gap hurts. I will right click on the chart, go to Format Data Series. The initial gap width is at 150, so I will bring it down to around 40. Okay? And now let's start making certain modifications to our chart. So what I need to do is first, I need to go ahead and make some changes over you. So what I would do now is, first of all, I will go ahead and select the target data, not the target data is over here. So I'm gonna right-click on it. And then I will go to the Change series chart type. And what I'm gonna do is I'm gonna change my sales target data from a stacked column chart to a line with markers chalk. And similarly, what I would do is I will go to my data labels and I will change them into line with markers. And I will click on ok. So you will observe that the moment I did that. Both the elements in the chart has been converted into line charts. Now let's start formatting them. So what I'm gonna do first of all is I will select this first line over here, which is my Data Labels. And I will right-click on it and I will click on format data series. I will go to the fill-in line option over you. And under the line section, I'm going to select no line. Okay? And then I will go to the mockers section and we'll click on mocker option. I will click on building. I will come down to this diamond option over here, and I will change the size to 25 and hit enter. Okay, now I need to go ahead and format my sales target over you. So before I do that, what I need to do is I will go to the Insert option over here. I will click on shapes, and then I will select a rectangle you, okay? And let me just draw a small rectangular we'll okay. And in order to go ahead and ensure that my rectangle is of a proper size, I will just go ahead and place it where the Basel V0. And I will just resize this a little bit. Okay? And now I will just go ahead and give it a black sheet. They selected wrest control C to copy it. Then I will go to my line chart the way you, and I will just press control v. Ok. And then I will select my target line or we'll go to format data series, go to fill-in line option and then I will change it to no line. Ok. You will observe that the diamonds away or I just touching my borrowing. So what I can do over you is I can just go here. And instead of ten, maybe I can just try making it 12 units. Let's see if it works. We just go ahead and drag this. It moves a little bit up. Let's try making it 15. And I can see that now it probably looks a little proper. Okay, now I need to go ahead and start formatting might chop. So what I'm gonna do is first, I will go ahead and select this audience section over here, which is my DO good job seals. So I will just right click on it, click on format data series. I will go to the Finland line option and then I will go to the fill over you and I'm going to select a gradient field. Okay? And in the gradient field, what I will do is I will select a linear filter wheel and then have it come down to this particular option which says gradient stops. Click on the first option over here, and I'm going to select a black color over here. I will go to the second option over here, and I will select a light blue shade over here. Okay, so that gives a kind of a gradient effect to my sales figures who you. Now, I need to go ahead and format the above the target section. So for that, what I am going to do is I will select the above the target section over here. And my above the target section has to be in green shade. So I will once again right click on it, go to format data series, click on fill-in line option. I will once again silly Gradient Fill. And this time, instead of the blue color on my second gradient stop. I will go ahead and click on it. And change the color to light green, okay? And ensure that the angle is set at 0. In case you want to have a different kind of a gradient effect, you can go ahead and change this angle. But as of now for this particular lecture on keeping it to 0. Okay? Now let's go ahead and format are below the target section. So what I'm gonna do is right click on it, click on format data series, finland line option. Select a gradient fill. And this time on my second stop over you, I will just go ahead and select the color as red. Okay. And then I will just click anywhere outside. Okay, now my charges almost ready. Now I need to go ahead and format the data labels. So what I will do now is I will just right-click this particular section over here. And then I will click on Add Data Labels. I will select the data labels, and I will go ahead and change the color to white, and I will make them bold. Okay, then I need to go ahead and add data labels for my red section over here. So I will select them, right click and then click on Add Data Labels. Then I will select the data labels, and then I will change the color to white. Now I need to go ahead and format this particular section over u. Ok, so let me just go ahead and add data labels to them first. So I will say add data labels. And then I want to go ahead and format these data labels. So right click on it, click on format Data Labels, and then I will click on center. Okay, so instead of these being on the right-hand side of the screen, and now they are in the center of this particular diamond over u. Ok. And now what I would do is I will go ahead and select my diamonds or we'll right click on it, click on format data labels. I will go to the online option, go to markers. And now I will go ahead and select a gradient, fill a wheel. And then I will just keep the gradient field as it is the default gradient fit. And I would just click anywhere outside. And then I will just select these labels. And then I will make them a little bold. I don't want these legends away or saddled remove them. And I will go ahead and give my chart a nice title so I can call it a dog. It was this achieved job. Okay. And I can just select these axis so we'll make them a little bowl. And then I can select these axis and make them a little bold. Well, we'll OK, and just in case if I want to give the chart title outside this particular chart, I can always do that by clicking on insert, selecting the WordArt. I can just select any word out off my choice listed over here. And I can call it a target, verses a cheap shot. So with that, our target versus a cheap shot is ready. However, you will observe that, oh, you're, the labels need to be changed. What I want over here is, instead of these particular data labeled values to be displayed, I wanted the actual C as values to be displayed in the data labels over you. So what I can do is I can right-click on this Data Labels. And then I can click on format Data Labels. And then I can click on this particular option that says value from cells. And then I would click on the up arrow over you. And I will select this particular range and hit enter. And now I can go ahead and put a uncheck on value and show leader lines. Okay? And now you will observe that the data labels over here are showing the actual figures of my seals. And now last but not the least, we'll go ahead and test our data, will you? So let's say for the month of Jan, if I go ahead and change my actuals from 175 to maybe 275, you will observe that moving from the red zone, it is now showing a surplus of 34 sales. Whereas for the month of July, if I go ahead and change the actuals from two hundred and thirty two hundred, you will observe that my chart changes immediately and the greens change to red showing a deficit and seals. So that's how you go ahead and construct a wonderful target was achieved chart using excellent for graphics techniques. I hope you enjoyed this lecture and I shall see you in the next one.
9. Lesson 8 Butterfly Chart: Hey, welcome back. Now what you see on your screen is something that I referred to as the tornado chart. And sometimes it is also called as the butterfly chart. Now there are no prizes for guessing why it is called a bucket flight shot. Of course, because it looks like a butterfly. And if I look at the data, we have six different KPIs. And then I have the data for Team a and Team B. And what the tornado chart is doing for me is it is giving me a neck to neck comparison of the different KPIs for both the teams. For example, the CSAC for team a is at 85% and for team B it is at 95%. And if you look at the KPI, Yo-Yo, for the team a, it is showing as 85%, and for team B it is showing ads is 95%. So let's understand how do we go ahead and create this wonderful donated shot. And then probably we can use them one day in our professional dashboards. So let's go ahead and copy this raw data onto a blank Excel sheet first. So now that we have our audit on a blank Excel sheet, we can go ahead and start constructing that tornado or a butterfly chart. But as usual, I would need some supporting data to do that. And the supporting data that I'm going to use a way of his team a blank. Then I'm gonna use team be blank. And finally, I would need a data for my KPI, and that is going to be the KPI space. Okay, let's go ahead and set this data properly first. Okay? And now in the team, a blank, what I'm gonna do is I'm going to use the formula S is equal to a 100% minus the data in team a column. Okay? And I will just go ahead and simply drag this data. Similarly in my DMB blank, I'm going to go ahead and use the formula as a 100% minus the value in that team B column. Okay? And I would just go ahead and drag this data again. And in my KPI space, I'm going to go ahead and use a standard 50%. And I would hit enter. And we'll just go ahead and drag this data. Okay. We'll just go ahead and select this data and give it a nice border. And now with my data ready, what I can do is I can start constructing my tornado chart. So what I will do is I will go ahead and select this data or you are not selecting the KPIs and just selecting this particular DW. Okay? And now what I will do is I will go to the insert tab. I will come down to the column chart. So V0, and I'm going to select 2D bar charts, but I'm going to select a stacked borrower, you, okay? And I will just place it somewhere where you and I have a list increase this side. Okay, and now what I would do is I will just go ahead and right-click on this particular chart, a wheel. And I would go and click on this particular option that says Select Data. And now what I need to do is I need to go ahead and reset this data with you. So what I need is I need the team a blank at the first bot. So I will just go ahead and move it to the first bottle you then I need that team a data. Okay. Then I need the EPA's, then I need the DMV, and then I need IT team be blank data, okay, so my data is set correctly now. And I would just go ahead and click on, okay, I will just go ahead and set the gap width for my data now. So I will just right click on it, click on format data series. And this gap width of 150, I will change it to 50. Okay? And now I will start formatting my data to give it up, but to fly look. So what I'm gonna do first of all is I will select this data, go to the chart elements, and then I will remove the grid lines. Okay? And now what I'm gonna do is I'm gonna select this particular section. Okay, right-click on it. Click on format data series, go to fill-in line option, click on film, and I'm going to select no fit. Ok. Similarly, i will come to the left section over here, which is my team, a blank. And I will right click on it. Click on format data series, go to Finland line option. And I will select No phil. Okay, and now I need to go ahead and reset my axis over you. So what I wanna do is I will right-click on this particular axis over here. And I will click on format axis. Now, the minimum, I will keep it to 0, but maximum I'm going to set the value at 2.5. Okay? Now why did I go ahead and set the value at 2.5? Because if at all I Go ahead, select any row over you and look at the sum at the bottom over u, the sum is 250%. So basically 2.5. represents my 250%. Okay? Now what I need to do is I need to go ahead and create some shapes. So for that, what I'm gonna do is I'm gonna go ahead and click on Insert, click on shapes. And I'm gonna go ahead and select this particular shape, a wheel which is upflow jot dealing. Okay, let's go ahead and draw the shape over here. Okay? And also, I'm gonna go ahead and create a copy of it. So I will just copy it. I will go ahead and paste it over u. Ok. And this particular ship, I would just go ahead and rotate it completely. Okay, now let's go ahead and format are shapes. So this particular area, or I want to go ahead and give it a green sheet. So I will select this particular shape, a wheel. Then I will go to the format tab, and then I will select this particular sheep or we'll, okay, then I will do shape effects, go to preset. And I've acidic, but he said three. Ok. And similarly, I will select this particular shape, a wheel. I will go ahead and click on the drop-down where we are. And I'm going to select the orange sheet. Once again, click on the Shape effects, go to preset, and I haven't selected this particular preset YOU. Okay? Now, all I need to do is just go ahead and select this shape. But as control C to copy it, come down to this particular bar OEO and just press control. Ok. And now I will go ahead and select this particular shape. Having copy it, come down to this particular section area and press control v. Okay, now I need to go ahead and format this particular section or wheel. So for that, what I'm gonna do is I will once again go ahead and click on Insert. Then I will go to shapes. And this time I'm going to select this particular shape over u, which is low job preparation. Okay, I will draw the shape or we'll increase the size a little bit. And now I've been once again go to the format tab. And then I will go ahead and select the black effect over you. Okay? I will go to the shape effects Voodoo preset, and I would select this particular preset over u. Ok? And now what I can do is I can select this, but he gonna shape, well, we'll copy it. Come to this, but he could have borrowed wheel and just press control v. Ok, so now my shape looks like this. And now what I need to do is I need to go ahead and add data labels. So I would select this bottle, we right-click and then click on Add Data Labels. Okay, I will select this bottle wheel and I would click on Add Data Labels. And then let me just go ahead and format my data labels a little bit. So I'm going to select this particular data labels. Right click on it. I will click on format data labels and I will see insight. And similarly I will select this particular data labels. We'll, we'll right click on it, click on format data labels, and I will see insight B's. Okay. And oh yeah, once again, I will right click on it, click on Add Data Labels. And then I will just select the data labels. And I will go ahead and give it a white form so they are visible. Okay. But I want the names of the KPIs to be listed over you. So for that, what I need to do is I need to just select these labels, Zoe. And then I will click on format data labels. Then I will go to this particular option that sees value from says. I will click on this up arrow OEO, and I will select this particular range away You and hit enter. And you will observe that immediately the names of the KPIs have appear Dojo. Now I can just go ahead and uncheck the values. And I can also go ahead and check the Show leader lines option. Okay? And I would just go ahead and click anywhere outside. Okay, but now if you look at this particular data, my CSAC is at the top. But in my job, my seaside is at the bottom. Let me just go ahead and fix that real quick. So I can go ahead and click on this vertical axis over here, right-click on it, and then click on format axis. And I click on that, I get this option towards the right-hand side, that's his categories in reverse order. I will go ahead and put a checkmark on it and then have it glows it. Okay, and now what I can do is I can just go ahead and remove these axis over you. I also don't want the vertical axis, so I can remove that as well. I don't want this team a blank in my legend, so I can just go ahead and select it and hit delete. I also don't want the DB blank in my legend, so I can just go ahead and delete for it. And what I can do is I can just go ahead and fix the gap width a little bit. So I'm just gonna go ahead and click on format data series. And I will try to reduce the gap bit from 50% to maybe 30%. Looks much better now. Okay? I will just go ahead and remove this chart di due from you. I will select this particular sheet, OEO. Click on view, and I will remove the grid lines. Okay, and now what I can do is I can go ahead and give my chalk and ice di2. So for that what I will do is I will click on insert, would take on what up. And then I will just go ahead and drop a Dojo and I will call it a dotted chart. You can even call it a butterfly, John, if you want. And just to make our labels visible, I can just select my labels. I can go ahead and give them a nice white sheet and make them a little bold. I can select this one and make it white and make it good. Okay, and now let's go ahead and quickly desktop data. Okay, so let's try changing the CSAC of DNA from 85% to maybe 50%. And you will observe that the data is changing. Let's try making a few more changes. Let's change the occupancy. What db from 90% to maybe 55%. And let's try changing that tendons for DMB from 95% to maybe 80%. And you will see that the changes take place dynamically. So that's how you go ahead and create a beautiful donut or a butterfly chart in Microsoft exit. I hope you enjoyed this lecture and I shall see in the next one.
10. Lesson 9 Variance Chart: Hey, welcome back. So what you see on your screen is something that I referred to as the gradient is Chuck. And that is something that we will learn in this particular lecture. So now let's understand what is this chart all about? So first we will have a look at that raw data that we have at our hand. So I have this entire raw dataset reading. I have the month, I had the year, I have the name of the manager and then I have this seals or manager. So for example, in the month of Jan 2021, the manager named Superman has made one hundred, eight hundred and ninety seven seats. Whereas in the month of fit, the number of Syriza, 1745, so on and so forth. So now I have the data for all the managers for two years, that is year 2020 and the year 2021. And what I have done is I have gone ahead and created obedience chart over you. And now for each and every manager, it is going ahead and doing a month wise comparison for 2020 and 2021. So for example, if I'm looking at Aquaman and I'm going ahead and looking at the sales figures over here. The blue ones represent the data for the year 2020, whereas the yellow ones represent the data for the year 2021. And you will observe that whenever there is a spike, that is the number of seats for the year 2021 are more than 20. 20 you, it will be represented by a green arrow. And it chose the Poisson teachers over there. Which means that for the month of Jan, apple man has made around 7% more sales in 2021 as compared to 2020. Whereas if I'm looking at the month of August, that is a decline of around 1% seeds into 2021 as compared to 2020. And this data goes on for all the managers as and when I go ahead and click on the fetus. So let's go ahead and try to create this wonderful infographic that we can go ahead and invoke, put it in any of our professional dashboards. So for this, what I'm gonna do is I'm going to copy this straw data onto a blank Excel sheet. So now I have gone ahead and copy the entire audit data onto a blank Excel sheet. The first thing that I'm going to do is I will go ahead and create a pivot table out of it. So I will select any of the cells within my data, and then I will click on Insert, and then click on PivotTable. I will select a new worksheet over here and then I will click on, Okay. Now in my rose section or were you, I would go ahead and add the month. And in my column section, I will add the year. And then I will go ahead and select the scenes at the sum of sales in my value section, or were you, I would just right-click on this pivot table over here and then click on Pivot Table Options. I do not want the grand total, so I would click on totals that fetus. And I will uncheck these two options. Show grand total for rows and Show Grand Totals for columns. And then I would click on, Okay. And now what I'm going to do is I will start creating that data for my chart. So what I will do is I will just copy this data. Right-click on it, and then I would paste it over you. I will change this row numbers two months. And now what I need to do is I need to go ahead and add some supporting data to my data sheet over u. So over here what I'm gonna do is I'm going to say max left. Then I'm going to go ahead and add max right. Now, you will also that this particular chart over here shows a green arrow whenever there is a spike in the number of c's and it shows a red atom whenever the seeds have declined. So what I'm gonna do is I'm going to go ahead and say Reyn-Bardt. And then I will state red bar. Okay. And you would also observe that whatever is the difference is reflected in the form of percentages. Okay? So this means that as a free person drives, this means that it's 18 percent dip. So let's go ahead and add data labels over here. So I'm going to say green labels. And then I would say read labels. Okay? And now let's go ahead and start filling up our data. So all we are, I'm going to go ahead and use the formula as max of these two cs figures for both the US. And I would hit Enter. And I will just drag this detail. Okay, So this is the maximum out of the two. And what I'm gonna do is I'm going to copy the same data in my max, right, as well. Okay? And I will just drag this. So this added, this data is basically this C. Now I want the data for my green bars and how we like get that data. So let's go ahead and put a formula you so what I'm going to see is if the CSP goes for 2021 are greater than 2020, then return the value at the data for 2021 minus that data for 2020. Okay? As returned the value as a and I would hit enter, okay? And I would just go ahead and drag this formula you. So you would also have that whenever the C is greater in 2021, it returns the value. And whenever the seeds in 2002 and t are greater than 2021, it will return the value as EDI, format it. By what I'm gonna do is I'm going to say, if the value of T is for 2020 is greater than 2021, then return the value as seeds for 2020 minus the sales for 2021. As they've done the value at any. And I'm going to close the brackets. Once again, I will drag this formula YOU. Okay? And now what I need is the placenta stages for my rise or fall in the data. So for the i, what I'm gonna do is I'm going to use the formula is equal to IF is EDR open brackets. And then I will use this. But he could are said, oh yeah, oh, okay, So what I'm saying is if that is an error in this particular cell, then return the value as blank as returned the value as open brackets, the seats for 2021 minus the seams for 2020, close brackets and then divide it by this hits of 2020 once again. And I will close this and hit enter. Okay, so it's giving me the percentage. I will just go ahead and click on the percentage style wheel and a good return the value in percentages. I would just go ahead and drag this. Okay? And now for my red arrows, let me just go ahead and create the labels. So for that, what I'm going to do is I will just copy this same formula where you go to this, but he could have said it and I have it based on a formula you okay. But what I'm gonna do for you is I'm just going to go ahead and change the graphite to L5. Okay, so instead of picking up the adder for green, but I would check if there is an adder for my red bar. And my red bar data is available in the L column. So what I'm going to do is I will just change this gate l and hit enter. Okay? And I would just go ahead and drag this data and then I will convert it into was engages. Okay, let me just go ahead and make this board. So I know that these are the headers for my data. And now that we have all our data ready, we can go ahead and start constructing our audience Char. Okay, So let's start constructing one. So to do that, what I'm going to do is I would go ahead and select this particular data where you, okay, so I'm just deleting the data up to max, right? Click on Insert. And then I would click on this particular option that says jot. Okay, I will select this, but he going to chop and put it on a blank Excel sheet or what you have is select the entire worksheet. Go to View tab. And I will hide the grid lines. And now I will go ahead and make this a little bit bigger in size, so it's easy to understand. Okay, so now what I'm going to do is I would first go ahead and right-click on this chart. And then I would click on Select Data. Okay? And what I'm gonna do is I'm going to go ahead and select this max left. And I'm going to go ahead and drag it up. So I will go ahead and just aligned my data in such a way that I have my max left at the top. Then I have the data for 2020, then the data for 2021. And then I have my max right? And I haven't go ahead and click on. Okay. I will go ahead and hide the grid lines away. Okay? And now I will come to this particular data. Are you This particular data is my max lift. So what I'm gonna do is I'm going to select this data, go to my chalk regiments and I'm going to keep on error bars. Okay? And this is my data for max, right? You can see over here in the latest that max right, is the yellow bar. So what I'm gonna do is I'm going to select this data, click on the plus sign and then click on Edit and bars. Okay? Now we need to go ahead and modify these bars once again. So what I'm gonna do is I'm going to select this particular borrow. We'll right-click on it and then click on Format Data Series. I would click on fit OU. And I would say no fluid. Okay? And similarly what I'm gonna do is I'm going to select this particular borrowing 0, which is my max right? Right-click on it. Go to format data series, go to fill. And I'm going to say no fill. Okay? So now what I have is the data for 2020, which is in blue. And then I have the data for 2021, which is in orange. Let's go ahead and modify up arrow bars smell. So I'm going to say let these error bars. And you would also that the moment I selected the ETO buzz on the max left, that is a menu that popped up that says Format error bars. So what I'm gonna do is I'm going to go ahead and select minus OU. And then I would click on Guston, and then I would click on specified value, have been delete the positive error value wheel. And then I would also d1 negative error value. And now what I would do is I will go ahead and give the reference data, and my reference data is over you. So I'm going to say equal do come to sheet 3 over u. And for being bought, my reference data is over here. So I'm going to select this data and then click on, Okay. You will also that the error bars have increased in size over u. Ok, Let's go ahead and format them a little bit. So for now, what I'm gonna do is I would click on fill in line option away. I would click on solid line, and I would increase the width of these lines. Okay, so let's make it around two points. And then I will come down to the begin arrow optional you and oh yeah, I would select this particular arrow. And then you will observe that an arrow is thump. But you will observe that at the bottom of this. That is another ADL way I don't want this. So once again, I would select it and go to the shins or real and we are intestine. I will select as no cap. Okay, come back to the fill option. And I will select the color of these arrows as dark green. Okay? And now let's go ahead and format and red bars over here. So these are our red bars. Once I select them, you will observe that format error bars options have popped up. Once again, I will click on Minus. I will click on no cap. And now what I will do is I would go ahead and click on Custom. Click on specifying a new. I will remove the positive error value. We'll also go ahead and remove the negative error value. And now I haven't specified where exactly is my data. So I would say Equal to go back to my sheet 3 a wheel. And then I will select this particular detail you and I will hit, Okay? And you will observe that now even my red bars have up here. Let's go ahead and format them. So with them already selected, I will go to my fill option. I will change the color to red. Or were you I would go ahead and increase the width to around two points. And this time instead of begin arrow type, I will come down to end arrow type, and I will select this arrow over you. So with that, my charge is almost ready. And now all I need to do is just format it a little bit. So what I'm gonna do it we use, I will go ahead and select any bar graph over here, right-click on it, and then have it click on Format Data Series, or the primary axis is at minus 27. So I wouldn't go ahead and change it to 20. I will change the gap lit from 219 to around 75. Okay. And then what I would do is I will go to the CDS options over you. And I will select the CDS max left. Once again, the CDs overlap and gap width would remain the same, but I will go ahead and put it on a secondary axis for you. Similarly, I will go to the Series Options over u, and this time I will select the CDS and max, right? And once again, keeping the same dimensions, I will go ahead and put it on a secondary axis. Okay, So now my chart looks somewhat like this. Now, all I need to go ahead and add some data labels. Do it. Before that, I will go ahead and remove this vertical axis. So I would just select it and delete it. And then I will go to the chart elements over here. And I would click on Data Labels. And the moment I did that you will observe that my data labels have up here. But this is not the format in which I'm expecting that. So what I'm gonna do is I will select the data labels over here, right-click on it, and then I would click on Format Data Labels. When I do that, a format data labels window appears on the right-hand side. When I go ahead and uncheck value from you. And then I will click on this particular option that says value from cells. And now I will give my range from my sheet 3 over u. And since I'm giving the Levene's for my clean at all, we I will select this particular data breach and I would click on OK. You would also that the data labels for my green arrows have appeared over you. Now I do not need these data labels, so I'm going to select them and I'm going to delete them. At the same time. I do not need these data labels as well, so I'm going to select them and I will delete them as well. But I need the data labels for my red arrows over here. So I will right-click on it. Click on Format Data Label. Once again, the Format Data Labels menu will appear. I will uncheck Show leader lines and I will also uncheck value. I will click on value from cells. Once again, I will go back to my sheet 3. And since I'm giving the data for my red arrows, I will select this particular range of data. And with that, my data labels have appeared, both for my green arrows as well as via red arrows. Now let me just go ahead and format this Jada little bit. I don't need these grids away. Oh, so I will remove the max left, and I will also go ahead and remove max right. Now I will go ahead and give a title to my chart. So let's call it obedience chart. Okay, Now last but not the least, we need to go ahead and add a slicer to this particular chart. So for that, we will once again go back to our pivot cheat or were you select the pivot cheat over you. And I would click on Analyze, and I would then click on Insert Slicer. I wanted a slicer on the basis of the manager name. So I would select it and then click on, Okay. And then what I would do is I will select the slicer, come to my sheet for over you. And I've interfaced this license away you. Okay. I would select my chart, right-click on it, click on Format, Chart Area, give it a nice solid border. And I will select the color black. I will select this particular Slicer where you go to the Options. And let me just select a suitable color for it. So let me just select this one. Okay? And now what we will do is we will give our chart a nice title. So for that, what I'm going to do is I will click on Insert, go to war Dot, select any of the word art over year. And then I will call it a year wise variance shot. And now if I go ahead and make any changes to my slices over your, you will observe that this particular infographic is dynamic and it will change accordingly. So let's click on any of the manager names over here. So let's click on Green Lantern. You will observe that the data changes dynamically. Okay? Similarly, if I go ahead and click on any other manager name, you will observe that the changes happen automatically. So this is how you go ahead and create a wonderful year wise variance chart using Excel infographic and incorporated in any of your professional dashboards. That will definitely give a new look and feel to your dynamic dashboards. So I hope you enjoyed this lecture and I shall see you in the next one.
11. Lesson 10 Sales Funnel : Hey, welcome back. Now what you see on your screen is something that I referred to as a sales funnel chart. And why do I call it as a funnel chart? So let's understand that to the raw data over here. So over here, I have this particular auditor, wherein in the first column I have the month, then I have the region. Todd, I have the number of leads that I have received. Now, out of these leads, I have gone ahead and filtered the lead, which means that some leads might be good, some might be bad. Or maybe there is insufficient data in some of the leads. So what I've done is I've gone ahead and filtered them. Now, after filtering them, let's say my team started calling up these leads. Now, out of these leads, these many are the right contacts that I received. After receiving the right contact, my team tried to go ahead and fix an appointment with the client. And out of the total number of appointments that were fixed, these were the conversions which means your I was able to make a seal and I have this data for all the regions for the entire year. So I have it for the east, west, north, and the south zone. And based upon this particular data, I have gone ahead and created by sales funnel chart. So I have this region wise data available at my hand. At the same time, if I wish to go ahead and look at the data for any particular month, I can go ahead and do that. Okay, So the topmost borrower, your represents my leads. Then these are the filtered leads. Then I have the right contacts, appointments, and then finally the conversion. So in a way it looks like a funnel and that's the reason it is referred as a sales funnel chart. So let's go ahead and understand how do we go ahead and create this sales funnel chart. So to begin with, what I will do this, I will once again go ahead and copy my raw data onto a blank Excel sheet. So now what I have done is I have gone ahead and copied my data on a blank Excel sheet. And I'm going to convert this data into a pivot table. So I'm going to select this data, click on Insert, and then I will click on Pivot Table. I will go ahead and create this pivot table on a new worksheet. So in my pivot table, what I'm gonna do is I'm going to go ahead and add the leaves first. Then I will go ahead and add the filtered leads. Then I will select the right contacts. I will go ahead and add appointments. And then I will go ahead and add the conversions. Okay? And I will go ahead and put this in a row-wise fashion. Okay? You can choose any option, but I prefer row wise, so I will go ahead and change it to row wise. And now what I will do is I will just go ahead and remove some off from all these values over here. So what I will do is I will select it and I will press Control H, H, as in hotel. Okay. And I will say fine, some off and replace it with a blank. And I will just click on Replace All. Okay? And I will close this. So now the sum of has vanished, and now what I will do is I will go ahead and type values over here. And I need the same values over here. So what I'm gonna do this and we're just going to say trim, open brackets, select it, and then close bracket. The reason I have gone ahead and use trim because there might be some empty spaces in that text section over there. So I will go ahead and hit enter. And I will just go ahead and copy this. Okay? Okay. And now over here what I will do is I will just say total. And now under total, what I'm gonna do is I'm going ahead and adding these values. So I'm going to say is equal to B. Okay? And then I would just go ahead and drag these values away you, okay? Now in order to go ahead and create my sales funnel, I would also need sudden supporting data where you, okay, So I will say support data. And my support data goes something like this. So is equal to, I will open up brackets. I will say max of this particular value. We'll, okay, so max of this particular data range, I will go ahead and press F4 so that I go ahead and freeze this particular range of wheel. Then I will close brackets. And then I will say minus this particular value, we're close brackets and then I will divide it by two. Okay? Now why am I going ahead and dividing this by two so as to maintain this symmetry of my sales funded. Okay? If you don't understand it right now, you will understand when I go ahead and create the chart, and I will hit Enter. And then I will go ahead and once again drag and drop this. But he collect data we are. So now the data that I need for going ahead and creating my chart is ready. So what I'm gonna do is I'm going to select this and die data. We'll go to Insert, come down to bar charts. And then I will select this particular chart, OU, that is Doody stacked bar chart. Okay? And then I will go ahead and select this chart. Go to another blank Excel sheet. And I haven't pasted the way you. Okay, Then I will go ahead and select this excel sheet To View tab. And then I will hide the grid lines. And then I will go ahead and increase the size of this particular job. Okay? Now what I'm gonna do is I will right-click on this particular chart over here. And then I will click on Select Data. And what I'm gonna do is I'm going to push this supporting data upwards. Okay? And you will observe that because I have divided by two. Now, it is showing us symmetry because half of this particular data is on the other side of this particular bar over here. And that's the reason I went ahead and divide this by two. Now I don't want these axis away. So what I can do is I can just select the axis and deleted. I don't even need these legends, so I will delete them as well. And now what I would do is I will select this vertical axis, click on Format Axis. And oh yeah, I had this been a good option that says categories in reverse order. So I will click on it. And you will observe that my leads have now gone at the top and conversion has come down at the bottom. Okay, I will close this. And now I will click on Chart Elements and then I will say hide grid lines. Okay? Now what I will do is I will select this particular portion of the chart. Okay, Now this particular portion is nothing but my supporting data. So what I'm gonna do is right click on it, click on Format Data Series, go to fill option. And then I will click on No. Okay. I will select my vertical axis over here. Click on home. And then I will increase the size of the axis over here, I will make this particular axis board and I will align it properly or were you okay? And now what I'm gonna do is I'm going to select this bar graph over here, right-click on it. Click on Format Data Series. And you, instead of gap width as 150, what I'm gonna do is I will go ahead and change the gap would do 0. Okay? So this is how my chart quint, look right now. Now I need to go ahead and format this and give it a look and feel of a sales funded. So what I'm gonna do is I will click on Insert. I will come down to this particular option that sees shapes. And I'm going to go ahead and select this particular shape. You can go ahead and make the selection of your choice. But in order to go ahead and give it a look and feel of a sales funnel, make sure you use a figure which is somewhat similar to a rectangle or maybe a do. I'm going to select this one and I'm going to draw the wheel. Okay? And now let me just go ahead and format it. So for that what I'm going to do is right-click on it and then I will click on Format Shape. I'm going to select this particular option that's its gradient fill. And then I'm going to select a preset gradient over here. So I'm going to select this particular gradient over here. Okay? And now I will go ahead and add another gradient stop over here. So for that I will click on Plus. I will go ahead and take it somewhere where you, okay? And then I will go ahead and select the color wheel as black. And I will go ahead and change this from radio to linear. Ok. Now I can also click on Shape Effects. Go to preset. And I will select this particular preset over u. Ok? And now what I can do is I can select this particular image, copy it, come down to my bar graph, and I will just base to the wheel. For that, I would just press control C and control V. And the moment I did that, you will observe that it has gone ahead and taken a shape of a funded. Okay. I can just go ahead and resize this chart a little bit. Now I need to go ahead and add data labels to this chart. So what I'm gonna do is I'm going to select this entire chart over here. Click on Chart Elements and I will click on Data Labels. Okay, I don't need these data labels, so I will select them and I will delete it. I need these data labels. They are not a visible right now because they are black in color. So what I'm gonna do is I will go ahead and change the color to white. I will make them bold, and I will go ahead and increase the size a little bit. Now we do not need this particular diagram. So what we can do is we can go ahead and select it, and we can go ahead and delete it. I can go ahead and add a chart title ojos, so I can call this as a sales funnel chart. I can select the title, then I can make it a little bold. Okay. Now what I need to go ahead and do is I need to go ahead and add slicers to this particular data. So for that, once again, I will have to go back to my pivot Chido you. And now what I'm gonna do is I will select my pivot data where you go to the analyze tab. And then I will click on Insert Slicers. Now I need slices on the basis of month, and I need this license on the basis of region. So I will go ahead and click on OK. And now what I will do is I will select these licensed and pasted just next to my chart or were you okay? Okay. And now I will go to the Options and then I will select a particular layout for my slices. So let's say I'm going to select orange layout over here, and I will go ahead and select the blue layout. Okay, what I can do is I want, I can even go ahead and format them. So for this particular month wise data, either I can go ahead and extend this, build assemble on. I can go ahead and add two columns to this, but it looks better than the data is stretched, so I'm going to keep it as it is. Okay? And now if I go ahead and make any changes to my filter section over here, let's see what happens within my chart. So if I go ahead and select the data only for the E zone, you can see that I can see the data only for east zone. If I go ahead and select E zone and select the month of February, it is showing me the data for each zone for the month of April. So similarly, I can go ahead and use various permutations and combinations of these filters. And my sales funnel chart will change accordingly. I just feel that I should go ahead and make a small modification to my data labels over here. So I'm going to select it, right click on it, and then I will click on Change Data Labels, shapes. And I'm going to select this particular option here that says rounded corners. Okay? And then I will go ahead and give it a nice fellow year. And then I will click on white color font. Okay? And then what I will do is I would select my chart or your right-click on it. And then the outline. And then I would select black outline. Okay, So that gives a nice border to my chart, the wheel. And last but not the least, what I would do is I will go to Insert, click on word dot, select this particular word w. And I will call this as a seals funded shot. Okay, so that's how we go ahead and create this interesting sales funnel chart. And along with that, we have also come down to the end of the lecture series. Since you have completed the entire course, do not forget to go ahead and complete the project because that will be the proof of your learning. And at the same time it will go ahead and give you the confidence of going ahead and creating wonderful infographics for your professional dashboards. So happy learning and deep list.
The Guruskool
