How to Change Data Type in Excel

Ever had your Excel SUM formula return a baffling zero, even though you've highlighted a column full of numbers? Or tried sorting a list of numbers only to have them ordered like 1, 10, 2, 20? These common frustrations almost always stem from the same culprit: incorrect data types. This guide will walk you through exactly how to identify and change data types in Excel, so you can get back to trusting your numbers and formulas.

Why Data Types Matter in Excel

In Excel, every cell has an underlying data type that tells the program how to interpret the information within it. Think of it as a label: "this is a number," "this is text," or "this is a date." When these labels are wrong, Excel gets confused, leading to some common problems:

• Broken Formulas: The most frequent issue is when numbers are accidentally stored as text. When you try to perform a mathematical operation like SUM, AVERAGE, or COUNT on text, Excel throws an error (like #VALUE!) or simply ignores the cells, giving you an inaccurate result.
• Incorrect Sorting: If you try to sort numbers that are stored as text, Excel sorts them character by character instead of by their numerical value. That's why "10" comes before "2" - Excel sees the "1" in "10" and places it before the "2".
• Pivot Table Problems: Pivot tables rely on correctly identified data types to group and summarize information. Text-formatted numbers won’t be summed up correctly in the "Values" area, leading to confusing and misleading reports.

Getting your data types right is the foundation of reliable analysis. Once you know what to look for, fixing them is straightforward.

How to Identify the Data Type of a Cell

Before you can fix the problem, you need to spot it. Excel offers a few clues to help you identify a cell’s data type.

1. Check the Alignment

By default, Excel gives you a quick visual cue:

• Numbers are right-aligned.
• Text is left-aligned.

If you see a column of what should be numbers, but they're all snuggled up against the left side of their cells, that's a red flag that they're probably formatted as text.

2. Look for the Little Green Triangle

Excel's error checking is quite helpful here. When Excel thinks a number might be stored as text, it places a tiny green triangle in the upper-left corner of the cell. If you click on the cell, a small warning icon will pop up, which gives you options to address the "error."

3. Use the TYPE Function

For a definitive answer, you can use a simple Excel function. The TYPE function tells you exactly what Excel thinks a cell contains.

Click on any blank cell and type =TYPE(A1), replacing A1 with the cell you want to check. The function will return a number:

• 1: Number
• 2: Text
• 4: Logical value (TRUE or FALSE)
• 16: Error value (#VALUE!, #N/A, etc.)

If the function returns a "2" for a cell that looks like a number, you've confirmed it's stored as text.

Top Methods for Changing Data Types in Excel

Once you’ve identified the problem, you have several ways to fix it depending on the situation. Here are the most effective methods, from quick fixes to powerful tricks.

Method 1: Change the Number Format on the Home Tab

Sometimes, the data is already a number, but it's just displayed incorrectly. This is the simplest fix.

1. Select the cells or column you want to change.
2. Go to the Home tab.
3. In the Number group, click the dropdown menu that likely says "General" or "Text".
4. Select the correct format, such as Number or General. Using "General" is often the safest bet, as it lets Excel decide the best format.

When to use this: This method is best when the underlying data is correct, but the display format is wrong (e.g., numbers are set to display as Text).

Method 2: Use the "Convert to Number" Error Checker

If you see the little green error triangles, this is the quickest method for small datasets.

1. Select the cell (or range of cells) with the green triangle.
2. A yellow diamond with an exclamation mark will appear. Click it to open a dropdown menu.
3. Select Convert to Number.

Excel will instantly convert the selected cells from text to numbers, fixing the alignment and making them usable in formulas. If you selected a whole column, this action will apply to all cells with the detected error in that column.

Method 3: The "Text to Columns" Trick

This is a powerful and reliable method for converting an entire column of text to numbers at once, especially when other methods fail.

1. Select the single column of data you want to convert. It's important to only select one column for this to work correctly.
2. Go to the Data tab on the Ribbon.
3. Click Text to Columns.
4. The "Convert Text to Columns Wizard" will appear. In Step 1, ensure Delimited is selected and click Next.
5. In Step 2, make sure all the delimiter checkboxes are unchecked. Click Next.
6. In Step 3, this is the important part. Under "Column data format," select General.
7. Click Finish.

This trick might seem strange, but it forces Excel to re-evaluate the data in every single cell in the column and re-apply a format. By choosing "General", you're telling Excel to treat numbers as numbers and text as text.

Method 4: The Paste Special Method

Another clever trick to force a conversion from text to numbers involves a simple mathematical operation.

1. Find a blank cell anywhere on your sheet. Type the number 1 into it and press Enter.
2. Click on that cell and copy it (Ctrl + C or Cmd + C).
3. Now, select the entire range of cells you need to convert to numbers.
4. Right-click on the selection and choose Paste Special.
5. In the Paste Special dialog box, under the "Operation" section, select Multiply.
6. Click OK.

This action multiplies every selected cell by 1. Since you can only do math with numbers, Excel is forced to convert each text-formatted number into an actual number before multiplying. The value doesn't change, but the data type does. You can achieve the same result by adding 0 instead of multiplying by 1.

Method 5: Change Data Type with a Formula

If you prefer a non-destructive approach that preserves your original data, you can use formulas in a new column to make the conversion.

Convert Text to Number with VALUE

The VALUE function is designed specifically for this task.

1. In an adjacent empty column, type the formula =VALUE(A2) (assuming your data starts in cell A2).
2. Press Enter. Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to the rest of your data.
3. You now have a new column with the data in number format. If you want to replace the original data, simply copy the new column and use Paste as Values over the original column.

Convert Number to Text with TEXT

Sometimes you need to do the opposite - convert numbers to text. This is common for preserving leading zeros in IDs or ZIP codes. The TEXT function gives you precise control over this.

1. In an adjacent column, type a formula using the structure =TEXT(value, format_text).
2. For example, if you have a 4-digit ID in cell A2 that needs a leading zero, use =TEXT(A2, "00000"). This forces the number to be displayed as text with five digits.
3. Press Enter and drag the formula down.

Example for ZIP codes needing leading zeros:

=TEXT(A2, "00000")

Example for converting a date in A2 to a long-form text string:

=TEXT(A2, "mmmm d, yyyy")

Final Thoughts

Dealing with incorrect data types in Excel is a rite of passage for anyone working with data, but it doesn't have to be a roadblock. By learning to spot the signs of mismatched data - like left-aligned numbers or broken formulas - you can quickly use tools like Text to Columns, Paste Special, or simple functions to get your spreadsheet back in order.

Wrangling data locally in a spreadsheet is often just the beginning. The real goal is to blend that data with information from other sources, like your Google Analytics, Shopify store, or CRM, to get the full picture. That's why we built Graphed to help. We simplify this entire process by letting you connect all of your platforms in one place - including Google Sheets for your exported Excel data. Instead of fighting with file types and formulas, you can just ask questions in plain English, and Graphed builds the real-time dashboards and reports you need, making your entire data analysis process feel less like a chore and more like a conversation.