The Number format in Microsoft Excel is quite powerful, but sometimes it will fail to work, and that’s a problem for advanced users. When this happens, Excel will not recognize values as numbers. But you shouldn’t worry because this article will tell you what you can do if the Excel cell format is not changing to a number.
Number formatting not working in Excel
If the Excel cell format is not changing to a number because the Number formatting is not working, take these steps to fix the issue.
- Remove both the Decimal and Thousand separators
- Numbers are formatted as text
1] Remove both the Decimal and Thousand separators
In most European countries, the Dot (.) characterizes a thousand separators, while the Comma is all about the decimal point. With this being the case, Excel sometimes fails to interpret the data properly.
To get things moving in the right direction, we must remove the Dot symbol, so let us explain what must be done.
- Open an Excel spreadsheet.
- Create a column with the title, TWC Salary.
- The next step is to copy the C5:C10 range, then paste directly into the D5:D10 field.
- From here, click the D5:D10 range, then click on the Home tab.
- Look for Find & Select, and click on it.
- When the Replace option appears via the dropdown menu, click on it.
- Right away the Find & Replace window will appear.
- Ensure the Replace tab is selected here.
- Add the Dot sign into the Find What area.
- The box that reads Replace With should be kept empty.
- Click the Replace All button, and right away a prompt will appear.
- Hit the OK button on the prompt.
You should see the changes appear in your spreadsheet.
Perform the same action again but this time, remove the Comma.
The next step here, then, is to select the D12 cell, then input the following into the Formula bar:
=SUM(D5:D10)
The correct output should be showcased in the Total Salary section, effectively solving the Number Format Not Working problem.
READ: How to create a Heatmap in Excel
2] Numbers are formatted as text
Sometimes the values we want to execute are in Text Format, and as such, we must change the value to Number Format. Let us explain what you must do to get things moving.
Select the D5:D10 range.
From there, you must click on the Error icon.
You must now click on Covert to Number from the context menu.
Next, you must click on the D12 cell.
Go to the Formula bar and type the following equation:
=SUM(D5:D10)
Move along now and hit the OK button to complete the task.
READ: Excel theme not applying
Why are my numbers not formatted in Excel?
When an Excel cell is locked, there is no way to edit or format it until it is unlocked. To find out if a cell is licked, we suggest right-clicking on the cell and then select Format Cells. From the Format Cells dialog box, navigate to the Protection tab and ensure the Locked checkbox is unchecked.
How do I enable number formatting in Excel?
Press CTRL + 1 on your keyboard, then select Number. Right-click the cell or cell range, choose Format Cells, then select Number. Click the dialog box launcher, and then choose Number.
