Hello everyone.
You must have seen different kinds of data visualization designs for percentages.
We have already discussed some of the basic designs such as pie charts and tagged charts in an Excel
video goes.
But today I will be showing you some advanced techniques that seem almost impossible with basic Excel
so if you look at the left these are simple pie charts representing percentages but the ones on the
right are some advanced dynamic charts to represent percentages.
Such designs are most commonly used and infographics when the most important parameter is a percentage
I'm sure including these charts will definitely improve the quality of your presentation and export.
One of the easier way to do this is to manually at age and collect the icons but in this tutorial we
will be discussing how to create dynamic charts that change according to your percentage value and creating
dynamic chart is also fun to create these charts will be using scatter plots.
If you look closely we have hundreds of colors of squares each representing one percentage.
So for 45 percent we have 45 circles in blue color and for 88 percent we have 88 squares in orange color.
One thing we can notice these icons are arranged in 10 rows and 10 columns.
And to achieve the desired percentage we are sequentially filling first three rows and then the columns
to understand more clearly support 45 percent.
We are first filling all the columns of first four rows and then filling only five columns of leafing
through if you imagine this as a scattered block.
You can quickly figure out that equation of four square is X is equal to one and y is equal to one.
The second point is X is equal to do.
Y is equal to 1.
And so on.
The last point which is it is equal to 10 y is equal to 1.
Similarly the 11th grade is X is equal to 1.
But why is equal to 2.
And if you go to the topmost row the last quarters X is equal to men and Y is also the equal to paint.
So let us first create this series so we'll have X and Y
for the first plane.
X is varying from 1 to 10 so we'll have 1 2 up to 10
so these are the X will lose for me first rule for the first row.
Y y loser 1 Always
when you go to the next row X again starts from 1 and goes to 10.
You use the formula is equal to not 1
and Y will be do the same.
So when you use
plus 1
note you select both disease and you extend it 200 point
to the last point is in committee.
So we have all the points of a US get a blog.
Let us plot these points using this approach are we going unserved.
Let's get a record.
You can see that we have now have a hundred point ten each row in each column.
From this we want to achieve this.
So now we have to segregate these plot point into two separate cities.
Now for each only point on the in the cities will assign a percentage value so the first point actually
represents 1 percent.
So I'll write it 0.01
the next point represents 2 percent.
The next point is 3 percent.
And so on.
Like this number like
not differentiate between the squares below and above the percentage mark that we have.
So for example now I have 15 percent to if I want to mark 15 squares does add a different color and
15 points or different color we can use the if statement to create two different cities I created separate
cities that automatically takes all the values less than or equal to the desired percentage value.
So this is the X fill.
And why should I lose
well this I'll use if that value is less than the percentage value
then X will be same.
Be any
number to put dollar signs
and drag this in and see for the first fifteen point.
We have the value of x for the next 15 points which we do not want to feel any color we have value of
any.
Similarly for Y if that percentage value is less than the collective person
we want lower value added we want in a
for dollar saying
now we will create a separate 2D for X blank
and Y blank
and this will have the reverse logic.
It will show value only if the percentage value is greater than the desired percentage value
will be in a
now you can see that since we have 15 percent value at the given value the first 15 of the full point
which is which is giving us devaluing the excellent vital column for the blank ones we have point only
in the X blackened y blank all other values.
And if you can see by changing this 15 percent value to 10 percent
now the first 10 points are getting the values for.
Year.
The point of getting value for black no let us create scalable plot for these series
we go to insert select scatter cloud it's a black blast get applaud you will have to select data add
it his name is well x values will be X will value from beginning to end.
Y values will really lay full value
okay.
Now add another one Julie's name is blank
x value that X blank
y value that y blank
okay.
Okay
you can see in this case the plant since the percentage values given is 10 percent.
You have an I couldn't in blue collar and the other 90 at an orange color.
If I change the percentage value to 15 again you can see that there are additional five blue dot and
the orders start are now 85 only.
The structure of what I did already.
We will modify the design aspect to create the desired result.
So first of all we will remove degrade land selectively land and delete them delete.
Now we will change the color to this blue color is fine for the icon but this orange.
I like to change two great format does it color markers so remember to select my good option it you
can change the color to green
you have to remove the border also.
You can see though these are gray.
You can increase the size also to see them more clearly.
More good options
so you can see that the size of these icons have increased.
You can change the shape of these icons also.
Right now this round you can select square will also increase the size to 13 large to increase the size
of these blue point make them square and lead them to 13
we can also change the background color
if we want to you can go again go to formatting the plot area and change the color from light to some
other color if we want.
Let us show you once and we go back to white and delete them.
Delete the y axis also so that only your infographics is dead.
Everything else is gone.
You can do 80 percent daily value change it to 50 percent if you would like to see more blue squares.
If you want to see 88 percent
if you want to get it exactly like the one on the top you can change this blue color to Orange also
if you want.
My good
orange
you may have to change the Border Patrol to
you can see that our chart is now ready.
I can change the percentage and the changes are affected in the graph to use other icons instead of
square or circle.
You can also use custom images to try that.
Let us go to formatted as it is option.
Go to the first one.
Corruption's.
If you select picture texture fill instead of solid fill.
This icon has come because I had already selected this one.
If you want to change the icon you can go to file line too like any other icon.
So if you want or dislike I can link this change to dislikes so you can change the multipoint although
for both fail points also and for blank point all from that is all.
This is all you create dynamic charts to highlight percentages for more such videos.
Visit our YouTube channel.
Star Trek Academy.
Or if you want to learn Excel overall check out our course on udemy.
Thanks.