 Hi everyone, I am Priya Lotlicker, first year PhD research scholar from the Department of Educational Technology, IIT Bombay. Today we are going to look at data visualization in Excel. The type of visualization of Excel chart you select for your analysis and reporting depends upon the type of data you want to analyze and report and most importantly what you want to do with the data. Some operations that you would want to do on your data would be, visualize the data to make some sense out of it if it is a big data. Secondly classify and categorize the data or understand the distribution of data, determine the patterns and trends in the data or find relationship within the data or detect the outliers. Here are some most popular charts which are used for data visualizations in Excel pie chart, bar and column chart, grouped bar chart, line chart, scatter plot, stacked bar chart, histogram and hit map. We will look into this in detail in coming session. First pie chart, when to use pie chart? You use pie chart when you want to show 100% composition of your data. For example, look at this image to the left side, over here the composition of data is 100% whereas in this image the composition is 82% so this is a wrong pie chart. Use pie chart when you have to show breakdown of data into at most 5 categories that is if you create the pie chart for more than 5 like 8 or 10 categories then pie chart would look cluttered as you can see in this image. Thirdly arrange the pie slices in such a way that you look at it in clockwise direction where the biggest pie comes first followed by the next bigger slice and so on. This facilitates the ease in the readability of the pie chart. Let's see how to construct a pie chart. Here I have the student data which shows me the participation of students in annual gathering of a school. I want to visualize this data using a pie chart. So for that first I need to select the entire table, go to insert under the chart section I need to choose the pie option when I click on this there are various representations provided to me choose whichever you want I will choose the first one. So this is how the pie chart would look like but currently there are no data labels on the pie chart so to add the data labels click on the pie chart right click and click on add data labels. So now this is a complete pie chart for a given particular table here every column of the table comprises one slice from the pie chart as you can see the female participation is represented by the red color and male participation is represented by blue color. One thing to remember with the data is that rows should be sorted by numerical column in descending order so the slices will be in the size order. Next is bar chart now when to use bar chart you use bar chart when the axis labels are too long to fit in the column chart so a bar chart is basically arranged along the horizontal axis that is the y axis. Next you have the column chart. The column chart is basically used to compare the data series let's see how to construct a bar chart or a column chart for the same data. So choose the entire table under insert in the chart section select the column in this you have 2D representation as well as 3D let me choose a 2D representation. So this is how the column chart would look like. Now if I want to construct a bar chart again select the entire table insert under charts I have the bar option choose the 2D representation so this is how the bar chart for this table would look like here each row will become a separate bar so the rows should be sorted in numerical column in descending order. Next we go to grouped bar chart when to use it you use it when you want to look how the second category variable changes within each level of the first that is basically compare the totals and one part of the total. Let's see how to construct a grouped bar chart I have this data on the student participation over a period of 4 years so in order to view this in a group bar chart select the entire table go to insert under insert choose the bar option in this bar you choose the 3D bar so this would give you the bar visualization I can even choose the column representation so choose the entire table under insert in chart section column choose the 3D column so this is how the 3D bar and column representation looks like here the color coding are provided for every group so you can distinguish between this groups as you can see the male and female participation over the years are being represented with two color coding. Next we look at line chart so line chart are basically used when you want to focus or show data trends such as uptrend, downtrend, short term trend, long term trend that is changes over several months or years between the values of the data as you can see in this image. Also use line chart when order of categories is important like you can see in this the order of categories of this respective product is important over here rather than in a bar chart. Let's see how to construct line chart this is the same data that we use to construct the group bar chart so basically this is a multiple group data we can also use the line chart to represent this data so for that select the entire table go to insert under chart section choose the line option you can choose any representation you want let me choose the first one so this is how the line representation for this particular data looks like as you can see the lines red and blue represents the trend in male and female participation. Next we will look at scatter plot so scatter plot is basically used to analyze and report the relationship or the correlation between two variables so more the data points better will be the scatter plot you can see the independent variable is on the x axis and dependent variable is on the y axis. Let's see how to construct a scatter plot here I have the data which captures the height and weight of students from a class now since you have two variables this is a bivariate data so we use a scatter plot to basically compare or show relationship between the two variables so let's develop a scatter plot to compare the weight and height of students in this class for that I need to choose the columns of the two variables so choose these two columns go to insert under chart section choose a scatter plot you have various representations over here let me choose the first one if you go to see this doesn't look like a clear representation of the data the minimum value would lie somewhere 130 to 140 and maximum value will lie somewhere from 190 to 200 so let's modify the x axis to get a clear picture of this data for that click on the x axis right click format axis so over here in the axis option set the minimum value to 140 and maximum value to 190 and say close as you can see this gives a clear picture same thing we can do with respect to y axis so click on the y axis right click format axis so I can set the axis minimum value to somewhere 40 as you can see in the image and maximum value to somewhere 80 close so now this gives us a clear picture of the entire data if you want you can even add a trend line to give a more clear picture about this data so for that I can choose the data points right click and click on add trend line in this the trend line that is added is a linear close so this gives us a clear picture of a linear increase in the trend of the height and weight that is height and weight are linearly proportionate to each other next we look at stacked bar chart so you use stacked bar chart when you want to compare the numeric values between the levels of the categorical variable that is each bar indicates a numerical value so when you want the relative decomposition of each primary bar based on the levels of the second categorical variable we use a stacked bar chart that is the order of the bar is from the largest to the smallest let's see how to construct a stacked bar plot I have this data over here which gives me the number of employees required on a particular project for these respective months and the number of employees are being categorized into programmer web designer graphic designer and QA engineer so in order to have this tech look of these employees every month I need to construct a stacked bar plot so for that I need to choose the entire table go to insert under chart section choose the bar plot in bar plot I need to use the stacked bar so this is how the stacked bar plot would look like if you want you can even have a column representation of this so for that choose the entire table insert under charts go to columns you can even choose the 3d visualization of the stack so I click on this so this is how the 3d visualization of the entire data looks like so every category of the programmers that is programmer web designer and graphic designer and QA engineer are being color coded so you can see in January the total number of employees were required were 5 and so on next we will look at histogram you use histogram when you want to show the frequency of the distribution for quantitative data in order to create histogram you need to install the analysis tool pack let's see how to construct a histogram you need to install the analysis tool pack for that go to files under options click on add-ins choose the analysis tool pack click on go checkbox of analysis tool back should be checked and say okay once this is done go to data and you will see data analysis over here under analysis category now histogram gives you the frequency distribution of the data within particular ranges that is called bin I have this data over here that is the test score of students from a class and this is the bin range that I have said that is 60 to 100 so this would be 0 to 60 60 to 65 65 to 70 and so on so to construct the histogram on this data click on data analysis in the data section choose the histogram option and say okay now I need to choose the input range the bin range for the histogram input range will be the test score column so for that choose the entire column in the bin range choose the bin column don't forget to check the labels because we have also included the test code in the data now since we want the output in the same excel choose the output range and give the reference address don't forget to check the chart output since we also want the histogram chart now this is how the histogram looks like let me modify it a bit by expanding it so the value on the x-axis a bit misleading so let me modify these values 60 would be 0 to 60 this would be 60.01 to 65 then 65.01 to 70 and 95.01 to 100 there you go this gives a better clear picture I can even change the name of the x-axis so this let me write it as test score if you go to see histogram basically gives a continuous distribution of the data without any gaps so now since these gaps are over here let me modify this chart a bit click on the bars right click choose the format data series under series option you have the gap width option over here make the gap width to 0% and click on close this is an appropriate histogram for the data and the last one heat map heat map is used to represent the individual values as colors you can see the gradual change in the values strand in the data for example a heat map can be used to visualize the engagement of students in class using the modal learning logs let's see how to construct a heat map so I have this data over here which has internet facilities on one side as a one variable and second variable would be the email sign apps the join site reviews for these respective facilities now in order to construct the heat map for this I need to select the numerical values only so let me select the numerical values under home go to conditional formatting click on this and choose the color scales in this choose the first one that we format this table a bit right click format cells border I choose this border for color white and I'll say okay so this is how a heat map looks like so the heat map will give you relationship between two variables one plotted on each axis so you get to see the change in the colors through the axis you get to see the pattern in the change in trend of the two variables