Infographics: KPI Indicators with excel cells

Welcome back, my name is Briandra Kumar and today we will learn how to create a KPI indicator with Excel cells. As you are seeing my screen, I have created these three matrix performance by using the Excel cells only. I haven't used any kind of graph or any kind of VBA macros to creating these KPI indicators. So here I have taken the three matrix and the performance of the matrix, service level, productivity and sales conversion. These all the three graphs are connected with this data table. If I will change the value of the service level, let's say 90, 89%, then the service level graph will be changed accordingly. Similarly, for the productivity, if I will change at 62% or sales conversion, if I will change at 34%, then the value will be changed accordingly. So I will tell you how can you create this in Microsoft Excel. So let's start. I'm creating a new worksheet here. I'm copying this data and pasting on the new worksheet, which is C6. First of all, we will create the graph for the service level. So to creating this graph, we need a 10 by 10 matrix on Excel sheet. So I'm creating a matrix here. Let's say I'm taking a 1, then 2. I will drag this range up to 10. In next row, I will create the 11, then 12. I will drag it to the 20. Now we can select both the range. Now we can drag to upside up to the 91. So data has been created from 1 to 100. Now what we'll do, just select the entire column, press control space and right click, go to the column width and change the column width as 2.4. Now column width has been changed. Now what I will do, I will select, press equal to and select service level. Now select these range from D to M and go to the merge and center. Similarly, here we will take the performance of the service level. So press equal to and select V2. Now select the range from D14 to M14 and press merge and center. Now what we will do, we will change the color of service level and service percentage or the service level performance. Just select this and go to the fill color and change the color as, let's say I'll taking this theme. So here in accent 2, we will take the color of headings as accent 2 darker 25 and we'll change the font color as white. And for this area, we will change the background color as this one, which is accent 2 lighter 80 percent. Now we will use the conditional formatting here. Press alt od or you can go to the com and conditional formatting and from here you can take the new rule and go to the use a formula to determine which cell to format. Click on this and here we will put a formula. The formula will be equal to and then the active cell, which is D4, you can check from here, D4 is less than or equal to then our service level value, which is D13. So click here. Now we will give the format, go to the format and format we will select from the same theme. So we will select the format from this and we'll take the third one, this one. Okay, that's okay. Okay, so we need to change something because our value of the service level is in percentage. That is why we need to take equal to D14. So we will use this formula in conditional formatting, which is D4 is equal to less than dollar sign D14. D14 is our the service level value. Okay, and then multiply 100 so that it can convert the number from percentage. Now press OK and OK. Now what we will do, first of all, we will select this range and go to the right click and go to the format cell. And from here we will go to the border and in border we will select the border color as white and we will select this one which is already selected. Let's say and select this one then go to the white. Now we will select click on the outline and then click inside and press OK. So here white water is available. Now graph is almost ready. What we will do, we will just hide the values from 1 to 100. For that we will again go to the format cell and here we will go to the number and now we will go to the custom. In custom we will change the format. Currently it is the general format. Now we will give here price semicolon. Okay, and press OK. A value has been hidden. Okay, now what we will do, we will change the font for the service level. We'll take the Arial 12 and make it bold. And for this change the font size as 16 and change the font as impact. Now we will go to the view and remove the grid lines. Now check service level graph is ready. Change the value as let's say 1. Change as 99 or 100. It's working 99. Yes, 65. So it is working. Okay friends. Now I will tell you how can you create the other graph. It is pretty simple. Just copy this and paste it here. And now what we will do, just select this and copy. Now select this and go to the paste special. You can use Alt E I S or go to the paste special from here and paste a special column with. Okay, so now what we will do, first of all, we will change the value is equal to productivity. So this graph we are getting for the productivity and now we will change the well performance of the productivity, which is V3 and here we will change the theme color. So select the productivity and the performance, press control and select. Now what we will do, we'll change the theme. Let's say I'm taking this theme. Okay, so select this one here. The fourth one is selected. You can take any other. This time I'm taking this one. Okay, now we'll change the background color, go to the fill. So here, as you are seeing, this is selected. Now we will select this one. Okay, we are taking the accent one. Similarly, we will go to the conditional commenting, press Alt O D and here we will change the go to the edit. First of all, we will change the formula, which is connected from the D14. Now it should be connected from the O14. O14, this value is changed automatically because we have not used any dollar sign. Okay, now we will go to the format and we'll change the format color. So we will take this one will one currently this has been selected. Now we'll take this one. That's okay. Okay, the graph is ready. So that is how you can create the second graph. And similarly, we can create the third graph also to creating the third graph. Just copy this and paste it here. And we will copy this and we will use the paste a special column with and we will change the sales. And similarly, for the performance of sales conversion, press equal to V4. And now you can change the theme for this. You can take any other, let's say I'm taking green one. So to green, I will take this. And similarly, for the backside fill, you can fill as currently this has been selected. Now press Alt O D to edit the conditional formatting and edit this tool. Now here, first of all, I will change the so it already has been selected at Z4. Now we will change the O and we'll take Z14. Now go to the format and we will change the fill color as the same. This the third color from the green. Okay. Okay. Okay. The graph has been changed. Now just check that I'm taking this 70 and this 80 and this 90. So it's working fine friends. That is how you can create these graph in Microsoft Excel without using of any kind of graph. These are not graph actually. Okay. I hope you have enjoyed this video. Thank you so much for the watching.