Whenever working with Excel conversion to numbers is often needed. The main focus of this article is to demonstrate how to convert to number for an entire column in Excel. I will explain 9 different ways to do this.
I will use the following dataset to explain the whole article that contains the Subject and Marks. Here, the numbers are showing errors because the format of the numbers is not right. I will explain 9 simple ways for Excel to convert to a number entire column.
1. Converting Entire Column to Number in Excel with the Convert to Number Option
In this scenario, the error is showing because there is an Apostrophe before the number. And, for this reason, the number is stored as text.
Now, I will show you how to convert to number entire column in Excel using Convert to Number option.
Let’s see the steps.
Steps:
- Firstly, select the data of the column where the error is showing.
- Secondly, select the warning sign.
Now, a drop-down menu will appear.
- After that, select Convert to Number.
Here, you will see that the entire column has been converted to number in Excel.
Read More: How to Convert Green Triangle to Number in Excel
2. Convert to Number Entire Column by Multiplying
In this method, I will explain how you can convert to number entire column by multiplying in Excel.
Let’s see the steps.
Steps:
- Firstly, add a Helper Column in your dataset.
- Secondly, select the cell where you want to convert to number. Here, I selected cell D5.
- Thirdly, in cell D5 write the following formula.
=C5*1
Here, I multiplied the number in cell C5 which is stored as text by 1. As a result, the value will not change but the format will be changed to a number. The formula will return the same value as the number.
- Finally, press ENTER to convert to number.
- After that, drag the Fill Handle to copy the formula.
Now, you will see that you have copied the formula to the entire column.
Here, in the following image, you can see that the entire column has been converted to number.
Read More: How Excel Formulas Convert Text to Number
3. Adding 0 to Convert Entire Column to Number in Excel
In this method, I will explain how you can convert to number entire column by adding in Excel.
Let’s see the steps.
Steps:
- Firstly, add a Helper Column in your dataset.
- Secondly, select the cell where you want to convert to number. Here, I selected cell D5.
- Thirdly, in cell D5 write the following formula.
=0+C5
Here, I added the number in cell C5 which is stored as text with 0. As a result, the value will not change but the format will be changed to a number. The formula will return the same value as the number.
- Finally, press ENTER to convert to number.
- Next, drag the Fill Handle to copy the formula.
Now, you will see that you have copied the formula to the entire column.
Here, in the following image, you can see that the entire column has been converted to number.
Read More: How to Convert Text with Spaces to Number in Excel
4. Performing Division to Convert Entire Column to Number
In this method, I will explain how you can convert to number entire column by dividing in Excel.
Let’s see the steps.
Steps:
- Firstly, add a Helper Column in your dataset.
- Secondly, select the cell where you want to convert to number. Here, I selected cell D5.
- Thirdly, in cell D5 write the following formula.
=C5/1
Here, I divided the number in cell C5 which is stored as text by 1. As a result, the value will not change but the format will be changed to a number. The formula will return the same value as the number.
- Finally, press ENTER to convert to number.
- After that, drag the Fill Handle to copy the formula.
Now, you will see that you have successfully copied the formula to the entire column.
Here, in the following image, you can see that you have converted the entire column to number.
5. Changing Cell Format in Excel to Convert Entire Column to Number
The next reason why this type of error is shown is the cell format is set as Text. In this case, the numbers are also stored as text.
To check this,
- Firstly, select the cell where the error is showing.
- Secondly, go to the Home tab.
- Thirdly, select Number.
- After that, check the Cell Format. Here, you can see that it is selected as Text.
In this method, I will explain how to convert to number entire column in Excel by changing Changing Cell Format.
Let’s see the steps.
Steps:
- Firstly, select the data where the error is showing.
- Secondly, go to the Home tab from the Ribbon.
- Thirdly, select the Number
- Then, select the drop-down option on the selected Cell Format.
Now, you will see a drop-down menu.
- After that, select General or Number. Here, I selected General.
Finally, you will see that I have converted the entire column to number.
6. Using Excel Paste Special Option to Convert Entire Column to Number
In this method, I will show you how to employ the Paste Special option to convert to number entire column in Excel.
Let’s see the steps.
Steps:
- Firstly, copy an empty cell that is not in Text format. Here, I copied cell B11.
- Secondly, select the data where the error is showing.
- Thirdly, Right-click on the selected column.
- After that, select Paste Special.
Now, a dialog box named Paste Special will appear.
- Firstly, select All from Paste.
- Secondly, select Add from Operation.
- Thirdly, select OK.
Finally, you will see that you have converted the entire column to number.
7. Using Text to Columns Feature for Converting Entire Column to Number
If numbers are not stored as number then Functions will not work with them. In the following picture we can see that the SUM function is not working. That means the numbers here are stored as Text.
In this method, I will show you how to use Text to Colums feature to convert to number entire column.
Let’s see the steps.
Steps:
- Firstly, select the data where the error is showing.
- Srcondly, go to Data tab.
- Thirdly, select Text to Colums from Data Tools.
Now, Convert Text to Colums Wizard will appear on the screen.
- Firstly, select Delimited from the Original data type.
- Secondly, select Next.
After that, Step 2 of Convert Text to Columns Wizard will appear.
- Firstly, select Tab from Delimiters.
- Secondly, select Next.
After that, Step 3 of Convert Text to Columns Wizard will appear.
- Firstly, select General from Column data format.
- Secondly, select the 1st cell of your column as Destination.
- Thirdly, select Next.
Now, you have converted the entire column to number.
At this point, anyone can get confused because of the left alignment of the numbers. But, in the following image, you can see that the SUM function is working properly. That means you have converted the entire column to number successfully.
Read More:How to Convert Bulk Text to Number in Excel
8. Use of VALUE Function to Convert Entire Column to Number
Another reason for numbers do not work as numbers is having extra spaces in the numbers. If you enter numbers with extra spaces then the numbers will be stored as text. In the following image, you can see that the SUM function is not working because of the same reason.
In this method, I will explain how to convert to number entire column in Excel using the VALUE function and the SUBSTITUTE function.
Let’s see the steps.
Steps:
- Firstly, add a new column to your dataset that you will convert to number.
- Secondly, select the cell where you want to convert the 1st number. Here, I selected cell D5.
- Thirdly, in cell D5 write the following formula.
=VALUE(SUBSTITUTE(C5," ",""))
Formula Breakdown
- SUBSTITUTE(C5,” “,””)—-> Here, the SUBSTITUTE function will replace the extra spaces with no space.
- Output: “5000”
- VALUE(SUBSTITUTE(C5,” “,””))—-> turns into
- VALUE( “5000”)—-> Here, the VALUE function will convert the text format into a number format.
- Output: 5000
- Explanation: Here, I got the result 5000 as a numeric value now I can do any calculations with this value.
- VALUE( “5000”)—-> Here, the VALUE function will convert the text format into a number format.
- Finally, press ENTER to get the result.
- After that, drag the Fill Handle to copy the formula.
Here, you will see that you have successfully copied the formula to the entire column.
- Now, secret the cell where you want to calculate the summation. Here, I selected cell D9.
- Next, in cell D9 write the following formula.
=SUM(D5:D8)
Now, the SUM function will return the summation of values in cells D5 to D8.
- Finally, press ENTER to get the result.
Here, you can see that the SUM function is working properly. That means I have converted the entire column to number successfully.
9. Converting Entire Column to Number with Excel Power Query
In this method, I will explain how you can use Power Query to convert to number entire column.
Let’s see the steps.
Steps:
- Firstly, Copy your dataset.
- Secondly, go to the Data tab.
- Thirdly, select Get Data.
Now, a drop-down menu will appear.
- After that, select Launch Power Query Editor.
Here, you will see that the Power Query Editor has appeared on the screen.
- Firstly, go to the Home tab.
- Secondly, select Enter Data.
Now, the Create Table option will appear.
- After that, Right-click on the 1st cell.
- Next, select Paste.
- Now, select Undo Headers.
- Finally, select OK.
Now, you have imported your dataset to Power Query. Here, you can see that the format is automatically converted to number.
If it does not convert to number then you can convert it easily.
- Firstly, click on the marked portion in the image.
Here, you will see a drop-down menu.
- Then, select Whole Number.
- Now, go to Home tab.
- Next, select Close & Load.
Now, you will get the table in your worksheet. Here, you can see that you have converted the entire column to number.
After that, you can change the font size and edit the table as you want. In the following image, you can see that I have edited my table.
Download Practice Workbook
Conclusion
In this article, I tried to cover how to convert to number Excel convert to number entire column. Here, I explained 9 simple methods of converting to number entire column in Excel. I hope this article was helpful to you. Most importantly, I recommend you practice a lot to get a clear idea in Excel. Lastly, if you have any questions let me know in the comment section below.
