This video is going to show you how to create a frequency table when we have categorical data. In this file, we have recorded the name of every truck that has been sold in a given year in the United States. And to look at this, how many will I have, if I go through all of it you will see that we have more than 233,000 records of what was sold in the United States. So by just looking at the raw data, it's very difficult to understand which trucks sold really well, which trucks did not do so well. So frequency tables are going to help us summarize this data so we can get a better sense of what has happened in the truck market. So to do that, I'm going to create the frequency table. So one of the things that I don't know is that how many unique names appeared here and one of the best ways that we can find that out is by having Excel help us do that. So I'm going to select the entire range by pressing Ctrl+Shift+Down, and that automatically selects the entire dataset. So by just holding Ctrl+Shift+Down on my cell A1, I was able to select that 233,602 rows of data at once. So then we can go to Data tab. In the Data tab, you're going to go to the Sort and Filter group and click Advanced and a dialogue box opens and here it shows the range of data we have selected. I want to copy this into a new location, so I'm going to click copy to another location and I'm going to move this because I want to put it right up top and I'm going to say copy it to, click here and select this cell for it to copy and I want only the unique records to show up. So I'm going to press here and say OK and automatically, I will get a list of specific trucks that have been sold, that have appeared in this record of more than 233,000, so that makes it really easy. So my next step is to count how many times each one had appeared. So how many times did they have Toyota Tacoma appear. So I'm going to say it's equal to and I'm going to use a function known as Countif and Countif has two inputs that it requires. It requires the range that you're using, so I am not going to click on A1, I'm going to click on A2 because A1 is just the name of my column. I'm going to click here, again, I want to pick up the entire set of data at once as quickly as possible. So I'm going to press Control, hold it down press Shift, hold it down, and now press Down arrow. So if you press F4 at this point, you would see that it would lock those cells. This is really important because now if I have it locked, I can copy it to other cells and this would be done for me automatically. So press F4 to lock the cells. So then the second one is the criteria and the criteria is count the number of entries you find in these ranges that I've given that matches the name of Toyota Tacoma. That's all I need to do. So I'm going to click on E2 and then parentheses and Return. So this will tell me immediately that I have had Toyota Tacoma in that year, appear 16,230 times. So now it's really easy. All I have to do is put my cursor at the corner, when you see the plus sign, it's ready to copy for you and you double click and it all appears. So one of the things you may want to do at this point is go ahead and order it, so we can have it in an ascending or a descending order. So I'm going to go back to home, I going to pick in the editing Sort & Filter and I'm going to say Custom Sort because I want to sort based on counts, not truck names, and I'm going to do it from largest to smallest and say OK. And right away, you would see that I have a much better idea of what is going on. I see that Ford F series was the largest selling truck model, followed by Chevrolet Silverado, and so on and so forth. So if you're interested in knowing the relative frequencies, we could do that too. So I'm going to create a column for relative frequent. And to do the relative frequency, all I need to do is to take each individual value, and divide it by the total number of trucks that are sold. And for me to know what is the total number sold, I'm going to create here, total of trucks. And it would be just simply sum of each individual trucks were sold, and again we will see that in that given year, there were 233.601 trucks that were sold. So relative frequency of F Ford series is simply this value divided by the total. Now if you want to keep on copying this without changing the cell numbers again, press F4, and the location of the total will be locked. So I would press Return and if I want this to be in form of percentages, I can press this and it will say to me that it's about 31%. If I want to keep it the way it was in terms of numbers, I can go back to number and if you want to increase the number of decimals, you can play here to change it, okay. So I'm going to just double click on this and immediately find out that 30.5% of the trucks sold were Ford F-Series followed by Chevrolet Silverado which was 23.5% and so on and so forth. That is the advantage of summarizing data using frequency tables.
