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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

How to Sum Values by Category in Excel

How to Count by Group in Excel