Speaker 1: In today's session, we are going to discuss if you have a database, you have conducted a research or a study or a survey and you have the data entry you have done already. So you want to do analysis like this on the frequencies and the overall bi-locations, bi-genders or the overall analysis of all questions. So how you can do that with the formulas function and how you can do that with pivot table also. So I will explain both of the methods here and here you can see in this database we have independent variable like gender and location. So how you will bifurcate the data by gender and by location and you can see here these are the overall frequencies responses these are by the location and these are by gender. So how to do this? Let's start our session. Likert scale is also used. You see here strongly agree, disagree. And we have this another type, satisfied, much satisfied, mostly satisfied. We have this scale also, always, often, sometime. And we have this yes, no, somewhat type of questions. And then at the end, we have a question like, on a scale of one to 10, how comfortable do you feel giving a feedback, et cetera. So this is also a scale used. So there are different types. and if you want some other type so on that I have done already video tutorial you can see the link in the description. So how we are going to analyze this data first we will need to see our database on the gender side and then on the location side and do all the cleanings if there is any irrelevant data so we should clean the data first. that we should go ahead. So this is a simple small database I have used it just to make it easy for you to understand and then you can do the analysis here also or you can do that in another sheet for that you will have to develop a sheet and give it a name analysis sheet. Now in this database first let's see what type of information we have. We have gender and location so in the gender we have male female and in the location we have a b c three locations. So now these are the questions in the first question we have in the first three question we have similar type of responses strongly agree so you should first collect all those questions with similar options. So, these three have similar options, strongly agree, disagree, somewhat agree and all these options I will take them into the analysis sheet. So, we have these types, so in these types let's develop in the analysis sheet first. Here we have these options, we can say scales, so the first one would be, you can also take these options from the tool that you have already developed, there would be all the options available. So first you need to develop this interface type for your analysis. So we have the first three questions on the same options. So we have developed the option list. Now here the first thing that you need to do is frequency. What is the frequency of each option? many respondents have selected these options and then you should have a percentage. You can do it like this but if you see in the data we have some independent variable like location and gender, so we should do it by that also. This is the overall one, so here we can say this is the overall analysis. Now the next one that we are going to develop is to develop it by location. So this is location A. Then the same we would copy paste for location B. This is copy paste for location C. We have I think three locations. So this is for the location and then by gender also. So here we can take it and say male and then here we can say female. So this is the overall analysis, now let us merge this one to fit it and let us select and give it a border. So this is the first, this is for the first question. Now if you do not want by location all these, so you can do this in this way also. Here you can type your questions, the areas and here you can bring these strongly agree disagree neutral agree disagree to this place and how you can do that just copy this, click here, go to paste, paste special, transpose, so you can see all these options are now here in the columns and the first three questions you which are these three questions so just copy them from here go to the analysis sheet click here go to paste now again I have to transpose it because these are in the columns and I want them in the rows so just like these were in the rows and I wanted them in the column so I use transpose now here also I will use transpose, go to paste special, transpose and just keep it values so that the colors etc. are not here. Click. Now you can see here these are those three questions available here. So you can do the analysis in one table for all the similar type of questions or you can do for each question like this. This is in detail, overall frequency, then by locations and then by gender. So here we We can type, we can add another row, for example, this one is URL. So let us merge it. This is URL. Now these are by location. So select these areas and merge and say by locations. And now here these four row columns are by gender. So this one would give you the detailed analysis along with the analysis by the independent variables like location and gender. And this one can give you the overall summary. So first you can develop such type of summary also and then if someone is interested in the in-depth analysis so you can do it like this also. Now how to do this? Let's first do it in this simple one and then we will go to the advanced one. So these are the three questions which have the similar type of response. Strongly disagree, disagree, neutral, agree and strongly agree. Now here for the first question, I am going to calculate the frequency of these options from the respondents. So go to the DB. Here you see this is the C column. Now the simple way that you can do it is let me do it here. For example, that you have these options. You can do this in this same sheet also if the data is not that much but if the data is lengthy so you should use another sheet. If you want to do it here in this sheet so that you can do simply copy these options go to db and paste them here. And now let's do it first here then we can take them there easily. Now how I am going to calculate the frequency of these options in this column, simple come here is equal to now since this is a text so we will use count ifs small bracket range this column from start to end of the data and what I want to search here I want to search here the number of strongly disagree, so comma and just click this strongly disagree. If you do not want to click it, if these are digits, so you can type here in the quotation marks also strongly disagree. But if you range it, so it would be very easy for you, range this and close the bracket. So you can see the frequency of strongly disagree is 3 means 3 persons have selected strongly disagree against this question. Now I want to drag it below also to obtain disagree, neutral, agree and strongly disagree. So what I will have to do, I will have to lock this range of the column so that if I drag it down so this may not go down with that. This one I will not lock because I want it to go down to select the other options but this range I want to lock. So I will select this range and press F4. Now if you want that onward also I have the similar type of columns and I want to go by columns but not by rows so you can lock it in another way also. This is the complete lock. You see a dollar before the column, the C column and a dollar before the row number also. So if you want to lock it only by the row number, so remove this dollar from here and this dollar from here. That my column should not be locked by but rows should be locked. So here only rows are locked and click enter. Now you can copy and paste it below so you can see that 3 persons have selected strongly disagree, 2 disagree, 5 says neutral, 9 says agree and 9 says strongly agree. So you can obtain these frequencies in this way but let us do it in the analysis sheet. Now here, I would go to column F small, break it, go to the database, I want to do it on the first question. So, this is in column C. So, select that, apply dollar rate now, F4, so that this column range is locked, comma, go to the analysis sheet and select this one, strongly disagree. calculate strongly disagree and then close the bracket you see again that's similar type of 3s here now i want to obtain these also so i would drag it like this and that is simple because i have already locked the column so the column will not be dragged only this will be dragged whenever i go to the next column so that's okay i should not lock this copy paste paste paste paste so here you can see we have these numbers against this area. Now, I want the similar type for this also. So, this was column C. Now, I would like to do that in column D. This is column D. So, go to the analysis sheet here, count f small bracket, go to the database. Here, this is column D, select it, apply F4 so that it is locked, comma, go to the analysis sheet and select the same column heading, strongly disagree and then I will drag it and close the bracket. So here also make it center, paste, paste, paste, paste, so here also you can see. Now let us take a sum of these responses so that we may verify whether it is giving us the whole number 28. And you can see in the database also we have 28 respondents. Now, this one also done. Now, I want it for this third question. So, go to the DB. Here, I have this in the E column. So, pound E. So, this is column E selected. If 4, 4 comma go to the analysis sheet and select the criteria and enter. Now here you can see we do not have any strongly disagree, copy, paste, paste, paste, paste. Now let us drag this sum and see whether every question respondents are 28, yes, this one is 27. Why? There might be an answer which is not, there might be some issue of the range I think. We have agree, disagree, neutral, somewhat agree, this is a wrong option, sorry. This was, now all the three are 28, remove this. This is the overall sum analysis. Now we can apply some charts etc. So, you can see here I am true to myself in most situation network these are the four areas let's select it so we can obtain percentages also for this the same and how you can do that these are the numbers let's copy and paste it here and here we can say percentages these are numbers so what would be the percentages now the percentages are simple the total respondents are 28 this is 28 so what I am going to do is equal to this divided it by 28 or you can say this divided by the sum of this you can do it in both ways sum of this or you can simply type 28 because this number is not going to change now the data is locked 28 now copy paste paste paste paste and similarly copy and paste and paste and now select it go to home and apply percent. So here you can see these are the percentages against each of the area. So in the first one you see the mostly the respondent have selected agree and disagree and here you can see mostly the respondent have selected disagree and this way also. So these are the percentages. Now if you want to make a chart etc. so you can do that on the numbers also or you can do it on the percentages also but it's same so let's select this go to insert then to the charts and in the charts let's select this bar now here you can see we have a chart where we can see strongly agree disagree I want that these questions should be here and the legend should be for the agreed not agree neutral etc I want here the question So the simple way is here in the chart switch rows column when you click this so here you can see now these are the questions the areas and here strongly agree disagree neutral and these are on the legend. For example you see strongly agree so this is the color of strongly agree in the first question strongly agree is as the is the highest in the next one you see this disagree as the highest and in the third one you can see that strongly agree and agree are the highest. So you can use this type of charts also for this analysis. You can change it to another type of chart also for example let's go to change chart types and here you can see this stack bar. Now this one is good because it will not give you these different lines it would be in one line it will explain everything. So, here you can see strongly agree. So, there is this one strongly agree this one. So, you can see this is the highest one and in this one, this one is the highest. So, it is disagree and all these colors you can see here and easily identify the trends and your data. Against which question, which of the response or which of the scale is the highest one. So, you can use this stacked bar to see and you can add here the elements like the air chart elements and data labels. So, you can say in the center, keep them in the center. So, here you can see these are the number of the respondents who are saying agree or who are responding with agree or not agree and neutral etc. So you can do it like this also. Now if you want the detail analysis and to see whether your information is correct or not so that also you can do. The video length was increasing so I have done this in the second part which is part two and the link is given in the description please there you will see the detail analysis by locations by gender and then what are the shortcuts because this one table is only for the first question so how easily we can do the same for the other questions and there are simple shortcuts where you can develop such type of table in seconds for the rest of the questions so watch part two the link is given in the description. Dear viewers we have started this YouTube channel. There are free of cost learning tutorial on if you want to learn Microsoft Excel, Microsoft Access, PowerPoint, MS Word, other learning tips. So we have detailed playlist and there are hundreds of tutorials on Microsoft Excel, Microsoft PowerPoint, how to design presentation, Microsoft Access, how to develop databases and manage data, online data collection, learning Google Forms, Kobo, MS Forms. If you visit this channel you can find lots of video tutorials. Kindly support us, visit the channel. For example in MS Excel you will find these sessions in a proper sequence. For example in MS Excel playlist we have this basic session and then session 2, 3, 4. So if you start learning these sessions, so from the start you can go ahead and you will find in a sequence sessions on microsoft basics data analysis conditional format tables use of if concatenate formulas dependent drop down list validation name managers we look up x lookup function f function advanced use of count f in some f's how to find duplicates tricks and tips related to microsoft excel how to develop search boxes searchable drop down list aggregate functions, tracking performance of the projects or any other analysis of research that you have conducted, for example, other such studies. There are sessions on how to develop beautiful charts, attractive charts, speedometers, dashboards in Microsoft Excel. Similarly in PowerPoint also you will find how to design beautiful presentation in PowerPoint, how to do an MS Access also you will find all these sessions in Prop Sequence. The Access Tutorials playlist, There are almost eight sessions. In session one, you will find the basics, how to create tables, queries, forms, and then you will go on with advanced options in Microsoft Access. Similarly, you will find tutorial on how to collect online data, Google Form, Kobo, MS Form. You will find also the use of MS Word, the mail merge functions. There are sessions on SPSS also, so you can learn SPSS if you need to do some analysis in SPSS. There is a series on project management also how to conduct need assessment studies. Kindly support us, visit the channel, watch, learn and improve your productivity. Thank you so much.