How to Fix Excel SUM Not Working When Numbers Are Stored as Text
Question details
The SUM function fails to calculate a total because the numerical values within the selected cell range are stored as text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying data from an external source, importing a CSV file, or entering data that gets automatically formatted as text, preventing mathematical operations.
- Observed behavior
- The SUM formula returns an incorrect total or 0. Changing the cell format to 'Number' from the ribbon does not automatically convert the existing text strings into calculate-able numeric values.
Verify your cell formatting by looking for a small green triangle in the top-left corner of the cells, or check if the numbers are automatically aligned to the left, which indicates they are currently stored as text.
Use the 'Convert to Number' Error Checking Command
The quickest and most straightforward way to fix numbers stored as text using Excel's built-in error checking indicator.
When Excel detects numbers formatted as text, it flags them with a small green triangle in the corner of the cell. You can use the built-in warning menu to batch convert these cells instantly.
Highlight all the cells containing the text-formatted numbers that you want to include in your SUM calculation.
Look for the yellow diamond-shaped warning icon with an exclamation mark that appears next to your selected range. Click on it to open the options menu.
Select 'Convert to Number' from the dropdown list. Excel will immediately change the text strings into true numerical values.
Check your SUM formula cell; it should now display the correct calculated total automatically.
Multiply Values by 1 Using Paste Special
This method forces Excel to evaluate text strings as numerical values by performing a simple background mathematical operation.
Use the VALUE Function
If you are working with a large dynamic dataset, you can use the VALUE function to create a new column of true numerical values.
Convert Text to Numbers and Calculate Sums Easily in WPS Spreadsheet
WPS Spreadsheet provides intuitive error-checking tools to instantly detect text-formatted numbers and convert them to numeric values for accurate SUM calculations, fully compatible with Microsoft Excel.
- 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing the calculation error.
- 2. Highlight the problematic cells: Select the cells with numbers that are aligned to the left or have the green error triangle.
- 3. Click the error indicator: Click the yellow warning icon that appears next to the selection box.
- 4. Select Convert to Number: Choose 'Convert to Number' from the context menu to fix the data format instantly.
Frequently Asked Questions
Why doesn't changing the cell format to 'Number' fix my SUM calculation?
Changing the cell format from the ribbon only alters how the cell is displayed, not the underlying data type. For Excel to recognize the change, you must force a re-evaluation by either double-clicking the cell and pressing Enter, or by using a conversion tool like 'Convert to Number' or 'Text to Columns'.
Can I use Text to Columns to fix numbers stored as text?
Yes. Select the entire column of text-formatted numbers, go to the Data tab, and click 'Text to Columns'. Without changing any settings, simply click 'Finish'. This action forces Excel to re-evaluate the column values, automatically converting them to numbers.
How can I prevent imported data from being stored as text in the future?
When using the Data Import Wizard to bring in CSV or external data, carefully review the column data format step. Ensure you set the format for numerical columns to 'General' or 'Number' rather than 'Text' before you finish the import process.
