Excel for Categorical Data

When categorical data appear in textbooks, it is usually already summarized in tables or graphs. Hence, you usually do not need technology to do homework problems with categorical data. However, this leaves one underprepared for dealing with real data, so this page is for those who need to do that. We will use an example dataset small enough so you can do the calculations by hand and compare your results to the computer. Imagine a survey question with answer choices Agree, Disagree or Undecided. Suppose 25 people give these responses:

Enter the data into Excel as illustrated below.

  A
1 Category
2 A
3 A
4 D
5 U
6 D
7 D
8 A
9 U
10 A
11 D
12 A
13 D
14 D
15 A
16 U
17 A
18 U
19 D
20 D
21 A
22 A
23 A
24 U
25 D
26 A

Where's the Mode?

Most software will not report the mode. That's because the mode is rarely useful for measurements. To find it when you do need it, you have to treat the data as categorical. For categorical data, the modal category is the one with the most observations (if there is such a category). You can see by counting that there are more A's on the list above than D's or U's, so A is the modal category. This is the shortest summary for categorical data, analogous to just giving the mean or median for measurements. When we find the modal category for a group of measurements, it is called the mode. It is useful only when the measurements resemble categorical data in having values that are repeated over and over. An example might be number of children in a family. Here you might see 0, 1, 2... over and over. For more typical measurements, such as these

1.66597, 1.91566, 2.53406, 2.88043, 2.93449, 3.08816, 1.73520, 3.21908, 3.77892, 3.98208

the mode is not useful because there is none. No value is repeated.

If you need the mode, make a frequency table for the data and find the category with the most observations.

Using Excel for Categorical Data

Type the 25 values into a column. To find the frequency of A, first select Insert > Function > Function category > All > Function name > COUNTIF > Range (Select the range for which you want to find the frequency.) > Criteria (write A) > OK. Similarly apply for D and U to find out frequency for each category.

Now, to find the total first select Insert > Function > Function category > All > Function name > COUNTA > Value1 (Select the range for which you want to find the frequency. Select the first cell and drag it to the last cells.) > OK.

To find the percent for A category, if 11 is in cell D2 and 25 is in cell D5, the formula to calculate the percentage is =D2/D5. Once you have entered the formula, highlight the cell where you entered the formula (e.g. cell A5) and click on the percentage icon on the formatting toolbar, this will make the answer appear as a percentage. (If you do not get the percentage icon after clicking the formatting toolbar select View > Toolbars > (tick on)Formatting Toolbar.)

  C D E
1 Category Count Percent
2 A 11 44.00%
3 D 9 36.00%
4 U 5 20.00%
5 Total (N) 25 100.00%

The modal category is "Agree".

Bar-Chart:

In Excel- 2003:-

To get a bar chart, First select Insert> Chart Wizard > In Standard Types select Column and in second Chart-subtype select Stacked Column then click Next>. In Data Range select columns category and count without labels and click Next>. Write the Chart title and label the x and y-axis. In Axes tick the check box of Category (X) axis, Automatic and Value (Y) axis. In Gridlines select the grid lines for X and Y axis which ever you want.(We have selected Major gridlines for Y axis only). In Data Labels tick the labels which ever you want. (We have selected none for the data labels). In Data Table tick the check box of show data table if need (we have not tick on show data table). Now click Next> select the Place Chart which ever you want (We have selected as object in: sheet 1 and say Finish.

In Excel- 2007:-

First select two columns of category and count then go to Insert and select Column chart in that select Stacked column you will get following Bar-Chart..

{short description of image}

Pie-Chart:

In Excel- 2003:-

To get a pie chart, first select Insert > Chart wizard >. In Standard Types select the chart type as Pie and Chart sub-type as the first option then click Next> . In Data Range select columns category and count without labels and click Next> .In Titles Write the Chart title. In Legend tick on the check box of Show legends and the place where you want them. ( We have tick on Right).In the option Data Labels tick the option in which you want to show the data values (we have selected None). Now click Finish to get the graph.

In Excel- 2007:-

First select two columns of category and count then go to Insert and select Pie chart in that select First Pie chart you will get following Bar chart.

{short description of image}

Notice that it is obvious from the bar chart that A is the modal category. It takes sharp eyes to see this in the pie chart. The summaries above are in order of decreasing statistical quality. A table gives the most and most precise information in the least amount of space; a pie chart gives the least.