1 00:00:06,700 --> 00:00:07,830 Hello everyone. 2 00:00:07,840 --> 00:00:12,690 You must have seen different kinds of data visualization designs for percentages. 3 00:00:12,820 --> 00:00:19,000 We have already discussed some of the basic designs such as pie charts and tagged charts in an Excel 4 00:00:19,020 --> 00:00:19,690 video goes. 5 00:00:20,470 --> 00:00:27,100 But today I will be showing you some advanced techniques that seem almost impossible with basic Excel 6 00:00:28,330 --> 00:00:34,630 so if you look at the left these are simple pie charts representing percentages but the ones on the 7 00:00:34,630 --> 00:00:39,790 right are some advanced dynamic charts to represent percentages. 8 00:00:39,790 --> 00:00:46,660 Such designs are most commonly used and infographics when the most important parameter is a percentage 9 00:00:48,400 --> 00:00:54,020 I'm sure including these charts will definitely improve the quality of your presentation and export. 10 00:00:54,130 --> 00:01:01,610 One of the easier way to do this is to manually at age and collect the icons but in this tutorial we 11 00:01:01,610 --> 00:01:08,310 will be discussing how to create dynamic charts that change according to your percentage value and creating 12 00:01:08,310 --> 00:01:14,970 dynamic chart is also fun to create these charts will be using scatter plots. 13 00:01:15,270 --> 00:01:21,660 If you look closely we have hundreds of colors of squares each representing one percentage. 14 00:01:21,720 --> 00:01:32,960 So for 45 percent we have 45 circles in blue color and for 88 percent we have 88 squares in orange color. 15 00:01:32,970 --> 00:01:38,840 One thing we can notice these icons are arranged in 10 rows and 10 columns. 16 00:01:39,060 --> 00:01:46,770 And to achieve the desired percentage we are sequentially filling first three rows and then the columns 17 00:01:48,020 --> 00:01:52,440 to understand more clearly support 45 percent. 18 00:01:52,490 --> 00:01:59,350 We are first filling all the columns of first four rows and then filling only five columns of leafing 19 00:01:59,370 --> 00:02:03,240 through if you imagine this as a scattered block. 20 00:02:03,320 --> 00:02:11,360 You can quickly figure out that equation of four square is X is equal to one and y is equal to one. 21 00:02:11,600 --> 00:02:13,780 The second point is X is equal to do. 22 00:02:13,790 --> 00:02:14,810 Y is equal to 1. 23 00:02:15,050 --> 00:02:16,070 And so on. 24 00:02:16,280 --> 00:02:20,030 The last point which is it is equal to 10 y is equal to 1. 25 00:02:20,870 --> 00:02:24,020 Similarly the 11th grade is X is equal to 1. 26 00:02:24,650 --> 00:02:25,760 But why is equal to 2. 27 00:02:27,440 --> 00:02:33,300 And if you go to the topmost row the last quarters X is equal to men and Y is also the equal to paint. 28 00:02:35,400 --> 00:02:40,750 So let us first create this series so we'll have X and Y 29 00:02:44,200 --> 00:02:45,220 for the first plane. 30 00:02:46,100 --> 00:02:52,670 X is varying from 1 to 10 so we'll have 1 2 up to 10 31 00:02:57,810 --> 00:03:01,600 so these are the X will lose for me first rule for the first row. 32 00:03:01,600 --> 00:03:04,000 Y y loser 1 Always 33 00:03:08,330 --> 00:03:13,640 when you go to the next row X again starts from 1 and goes to 10. 34 00:03:15,200 --> 00:03:18,380 You use the formula is equal to not 1 35 00:03:21,950 --> 00:03:24,710 and Y will be do the same. 36 00:03:25,640 --> 00:03:26,300 So when you use 37 00:03:29,310 --> 00:03:30,300 plus 1 38 00:03:36,360 --> 00:03:41,300 note you select both disease and you extend it 200 point 39 00:03:47,110 --> 00:03:49,230 to the last point is in committee. 40 00:03:50,550 --> 00:03:53,360 So we have all the points of a US get a blog. 41 00:03:53,970 --> 00:04:00,610 Let us plot these points using this approach are we going unserved. 42 00:04:01,420 --> 00:04:03,620 Let's get a record. 43 00:04:03,870 --> 00:04:10,830 You can see that we have now have a hundred point ten each row in each column. 44 00:04:12,940 --> 00:04:18,090 From this we want to achieve this. 45 00:04:18,190 --> 00:04:26,260 So now we have to segregate these plot point into two separate cities. 46 00:04:26,260 --> 00:04:35,730 Now for each only point on the in the cities will assign a percentage value so the first point actually 47 00:04:35,730 --> 00:04:38,140 represents 1 percent. 48 00:04:38,250 --> 00:04:40,080 So I'll write it 0.01 49 00:04:42,710 --> 00:04:48,540 the next point represents 2 percent. 50 00:04:48,540 --> 00:04:50,060 The next point is 3 percent. 51 00:04:50,400 --> 00:04:51,080 And so on. 52 00:04:54,000 --> 00:04:55,920 Like this number like 53 00:05:00,110 --> 00:05:05,540 not differentiate between the squares below and above the percentage mark that we have. 54 00:05:05,660 --> 00:05:14,330 So for example now I have 15 percent to if I want to mark 15 squares does add a different color and 55 00:05:14,330 --> 00:05:22,870 15 points or different color we can use the if statement to create two different cities I created separate 56 00:05:22,900 --> 00:05:27,840 cities that automatically takes all the values less than or equal to the desired percentage value. 57 00:05:28,440 --> 00:05:30,010 So this is the X fill. 58 00:05:30,330 --> 00:05:31,560 And why should I lose 59 00:05:35,120 --> 00:05:42,300 well this I'll use if that value is less than the percentage value 60 00:05:46,650 --> 00:05:51,290 then X will be same. 61 00:05:51,540 --> 00:05:52,290 Be any 62 00:05:58,780 --> 00:06:00,750 number to put dollar signs 63 00:06:05,540 --> 00:06:11,050 and drag this in and see for the first fifteen point. 64 00:06:11,120 --> 00:06:17,300 We have the value of x for the next 15 points which we do not want to feel any color we have value of 65 00:06:17,390 --> 00:06:18,350 any. 66 00:06:19,400 --> 00:06:27,860 Similarly for Y if that percentage value is less than the collective person 67 00:06:31,110 --> 00:06:34,800 we want lower value added we want in a 68 00:06:39,900 --> 00:06:41,050 for dollar saying 69 00:06:46,270 --> 00:06:51,970 now we will create a separate 2D for X blank 70 00:06:56,030 --> 00:06:56,900 and Y blank 71 00:06:59,890 --> 00:07:03,940 and this will have the reverse logic. 72 00:07:04,520 --> 00:07:11,500 It will show value only if the percentage value is greater than the desired percentage value 73 00:07:21,400 --> 00:07:22,310 will be in a 74 00:07:48,770 --> 00:07:58,660 now you can see that since we have 15 percent value at the given value the first 15 of the full point 75 00:07:58,930 --> 00:08:07,000 which is which is giving us devaluing the excellent vital column for the blank ones we have point only 76 00:08:07,000 --> 00:08:09,610 in the X blackened y blank all other values. 77 00:08:10,100 --> 00:08:16,040 And if you can see by changing this 15 percent value to 10 percent 78 00:08:20,990 --> 00:08:26,550 now the first 10 points are getting the values for. 79 00:08:27,950 --> 00:08:28,340 Year. 80 00:08:28,350 --> 00:08:36,490 The point of getting value for black no let us create scalable plot for these series 81 00:08:39,320 --> 00:08:48,660 we go to insert select scatter cloud it's a black blast get applaud you will have to select data add 82 00:08:49,700 --> 00:08:58,880 it his name is well x values will be X will value from beginning to end. 83 00:09:02,070 --> 00:09:05,220 Y values will really lay full value 84 00:09:08,060 --> 00:09:08,740 okay. 85 00:09:11,280 --> 00:09:15,270 Now add another one Julie's name is blank 86 00:09:18,080 --> 00:09:19,790 x value that X blank 87 00:09:23,330 --> 00:09:24,860 y value that y blank 88 00:09:31,800 --> 00:09:32,340 okay. 89 00:09:32,680 --> 00:09:33,070 Okay 90 00:09:36,930 --> 00:09:43,300 you can see in this case the plant since the percentage values given is 10 percent. 91 00:09:43,300 --> 00:09:51,060 You have an I couldn't in blue collar and the other 90 at an orange color. 92 00:09:51,060 --> 00:10:01,650 If I change the percentage value to 15 again you can see that there are additional five blue dot and 93 00:10:01,650 --> 00:10:05,470 the orders start are now 85 only. 94 00:10:05,540 --> 00:10:07,350 The structure of what I did already. 95 00:10:07,560 --> 00:10:12,230 We will modify the design aspect to create the desired result. 96 00:10:12,330 --> 00:10:20,070 So first of all we will remove degrade land selectively land and delete them delete. 97 00:10:20,090 --> 00:10:26,860 Now we will change the color to this blue color is fine for the icon but this orange. 98 00:10:26,940 --> 00:10:40,080 I like to change two great format does it color markers so remember to select my good option it you 99 00:10:40,100 --> 00:10:41,810 can change the color to green 100 00:10:49,930 --> 00:10:51,630 you have to remove the border also. 101 00:10:51,660 --> 00:10:54,450 You can see though these are gray. 102 00:10:54,690 --> 00:10:59,250 You can increase the size also to see them more clearly. 103 00:11:01,790 --> 00:11:03,020 More good options 104 00:11:11,010 --> 00:11:14,330 so you can see that the size of these icons have increased. 105 00:11:14,330 --> 00:11:16,410 You can change the shape of these icons also. 106 00:11:16,910 --> 00:11:25,730 Right now this round you can select square will also increase the size to 13 large to increase the size 107 00:11:25,730 --> 00:11:32,510 of these blue point make them square and lead them to 13 108 00:11:39,130 --> 00:11:43,630 we can also change the background color 109 00:11:46,680 --> 00:11:54,590 if we want to you can go again go to formatting the plot area and change the color from light to some 110 00:11:54,590 --> 00:11:55,610 other color if we want. 111 00:11:57,520 --> 00:12:04,220 Let us show you once and we go back to white and delete them. 112 00:12:06,250 --> 00:12:14,040 Delete the y axis also so that only your infographics is dead. 113 00:12:14,100 --> 00:12:15,480 Everything else is gone. 114 00:12:17,210 --> 00:12:23,270 You can do 80 percent daily value change it to 50 percent if you would like to see more blue squares. 115 00:12:23,820 --> 00:12:25,500 If you want to see 88 percent 116 00:12:28,720 --> 00:12:34,600 if you want to get it exactly like the one on the top you can change this blue color to Orange also 117 00:12:34,600 --> 00:12:35,120 if you want. 118 00:12:38,460 --> 00:12:38,990 My good 119 00:12:41,920 --> 00:12:42,370 orange 120 00:12:46,360 --> 00:12:48,340 you may have to change the Border Patrol to 121 00:12:56,930 --> 00:12:59,180 you can see that our chart is now ready. 122 00:13:00,110 --> 00:13:07,400 I can change the percentage and the changes are affected in the graph to use other icons instead of 123 00:13:07,400 --> 00:13:09,280 square or circle. 124 00:13:09,290 --> 00:13:12,710 You can also use custom images to try that. 125 00:13:13,010 --> 00:13:16,650 Let us go to formatted as it is option. 126 00:13:17,240 --> 00:13:18,890 Go to the first one. 127 00:13:19,050 --> 00:13:22,560 Corruption's. 128 00:13:22,760 --> 00:13:26,680 If you select picture texture fill instead of solid fill. 129 00:13:31,290 --> 00:13:35,300 This icon has come because I had already selected this one. 130 00:13:35,340 --> 00:13:40,740 If you want to change the icon you can go to file line too like any other icon. 131 00:13:42,190 --> 00:13:51,400 So if you want or dislike I can link this change to dislikes so you can change the multipoint although 132 00:13:52,210 --> 00:13:57,040 for both fail points also and for blank point all from that is all. 133 00:13:57,470 --> 00:14:02,830 This is all you create dynamic charts to highlight percentages for more such videos. 134 00:14:02,830 --> 00:14:04,390 Visit our YouTube channel. 135 00:14:04,400 --> 00:14:05,990 Star Trek Academy. 136 00:14:06,420 --> 00:14:11,920 Or if you want to learn Excel overall check out our course on udemy. 137 00:14:11,990 --> 00:14:12,410 Thanks.