How to Create Bar Charts, Histograms, Scatter Plots & Box Plots in Excel

[Music] foreign [Music]

how we can draw simple charts using excel's function so in this particular data set we have both nominal scale as

well as ratio scale remember whenever it's a nominal scale we can only get bar chart whereas whenever it is a ratio

scale we can come up with the histogram so let me show how you can get bar chart from this data for the nominal scales

here we have two nominal scale one is the release date the other one is John R before you actually go and create a bar

chart for the nominal scale ensure that this data is stored as a text if it is not you can actually select the colon

click right click and go and select that data as text so once you formatted that particular column as text all you have

to do is go to insert and then click on the recommended chart and here it gives you the bar chart so I am going to click

OK and here we have bar chart so if you look at large number of movies are released

during normal season and the second highest is probably holiday season then we have

festive season and finally long weekend so that's the kind of distribution that we have when we look at the movie

release date so let us come up with bar chart for the movie genre so I'm going to select the

colon which has a movie genre select insert button go on click on the recommended chart it recommends a bar

chart and when you click you get the bar chart we can see that the highest number of movies belong to the genre comedy

then you have drama then Thriller then romance then action so we have the bar chart for genre of the movie and the

remaining variables are continuous variables so whenever the variable is continuous we come up with the histogram

so let me draw the histogram for some of these variables so I am going to select budget

and insert recommended chart it gives you different charts so we are interested in

histogram so this is your histogram and I can click here and you can see the histogram it

has some exponential kind of shape now the rest of the variables such as budget

box office collection and so on are the ratio scale for the ratio scale we come up with histogram so here I have

selected the budget colon and we can click on the insert button look at the recommended chart so this is

your histogram when you click on histogram you get the histogram of that particular data now I can also come up

with the cumulative plot based on this data so I can go and select

the chart which is this one see here in addition to the

histogram you also get how the cumulative probability is changing when you change the budget on the horizontal

axis now let us look at the histogram for box office collection so I have selected the colon where we have the box

office collection again go and insert recommended chart and the histogram

so I'm just going to slightly magnify this because this has an outlier so what you can see is here there is an

outlier and here it is the box office collection is between approximately 680 crores and 744 crores this is basically

the movie PK so the histogram also helps you to identify if there are outliers in the data I can go and do histogram for

the remaining variables as well we can also do the scatter plot which is very very useful suppose let us say I want to

look at the scatter plot between budget and box office collection so I can select both these columns and click on

the tab insert recommended short so here this is the scatter plot so when I click on the scatter plot so we see that here

on the x axis we have the budget or the horizontal axis we have the budget and the vertical axis we have the box office

collection and this outlier again is the movie PK the budget is about 80 crores and the box office collection is more

than 700 crores another important plot in case of numerical variable is the box plot it

can be used to again identify outliers so I am going to select box office collection and

click on the insert Tab and we can click here so this is the box and whisker plot so you can see that there are a lot of

points above your ah Q3 plus 1.5 we can now ah check how we can come up

with the box plot using Excel so I have selected the colon box office collection and we can go on click insert and you

have the recommended chart okay let me explain how we can come with the box plot select the colon for which you want

to draw the box plot here I have selected box office collection the reason why I chose box office collection

is because it has lot of outliers and it is already on insert so if you click on this tab you can see the box and whisker

plot so this is the box and whisker plot and here the bottom line

the bottom most point is the lowest budget and the lower box is the first quartile the middle line in the box is

the median or the second quartile the Top Line in the box is the third quartile

and here you have the whisker the whisker is at 1.5 interquartile distance from the Q3 any value Beyond this

whisker is a outlier so you can see there are several outliers and you can see again the movie PK which is more

than 700 crore box office collection on the Lower Side you do not have any outlier but on the higher side you have

lot of outlier there are a lot of movies which have box office collection more than Q3 plus 1.5 interquartile distance

I can also collect more variables and put a box plot for all of them in one goes so I have selected now three

variables insert here I am selecting this now the problem here is the scale is quite

different that's why you are not able to see ah the other box plot but when the scales are more or less comparable then

you will see three box plots so let me try with the box office collection and budget

so when I try with box office collection and budget and I am going to do again the box office plot for these two you

can see that this is the box plot for the budget and you have the box plot for box office collection and you can create

more variables the only problem is that if the scales are completely different then it looks odd in the chart for

example here the box office collection is in few hundreds whereas YouTube likes are in few hundred thousand so that is

why when you try to draw a box office when you try to drop box plot for both these variables it gets skewed

in this session I demonstrated how you can actually use Excel to derive measures of center tendency measures of

variation and measures of shape and I also showed you how you can get simple visuals we'll also show you using

Tableau how you can actually get visuals using the same data set [Music]