How to Convert Categorical Data to Numeric in Excel

Often you may want to convert categorical data to numeric data in Excel to perform some specific type of analysis.

For example, suppose we ask 20 individuals to provide a categorical rating for some movie but we would actually like the categories to be converted to numerical values:

The following step-by-step example shows how to do so.

Step 1: Enter the Data

First, enter the data values into Excel:

Step 2: Use the IFS Function to Convert Categorical Values to Numeric Values

Next, we need to use the =IFS() function to convert the four categorical values of Great, Good, OK, Bad into numerical values of 4, 3, 2, 1.

In our example, we’ll type the following formula in cell C2:

=IFS(B2="Great", 4, B2="Good", 3, B2="OK", 2, B2="Bad", 1)

The following screenshot shows how to use this function in practice:

We can see that the first categorical value of Great has been converted to the numerical value of 4.

Step 3: Drag the Formula Down to All Cells

Lastly, we’ll simply drag the formula in cell C2 down to every remaining cell in column C:

From the output, we can see:

All Great ratings have been converted to a numerical value of 4.
All Good ratings have been converted to a numerical value of 3.
All OK ratings have been converted to a numerical value of 2.
All Bad ratings have been converted to a numerical value of 1.

We can now proceed to perform some numerical analysis on the data.

For example, we might decide to calculate the mean value in the Numeric_Rating column to get an idea of how the average individual rated the movie.