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.

**Table of Contents**hide

## 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.

- 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.