Whenever working with Excel conversion to numbers is often needed. The main focus of this article is to demonstrate how to **convert to number** entire column in Excel. I will explain **9 different **ways for Excel **convert to number **entire column.

**Table of Contents**hide

## Download Practice Workbook

## 9 Simple Ways to Convert to Number in Entire Excel Column

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. Using 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 Fix Convert to Number Error in Excel (6 Methods)**

### 2. Excel 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 to Bulk Convert Text to Number in Excel (6 Ways)**

### 3. Adding 0 for Converting Entire Column to Number

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 to Number with Excel VBA (3 Examples with Macros)**

### 4. Excel Convert to Number Entire Column by Dividing

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

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

**Read More:** **How to Convert String to Long Using VBA in Excel (3 Ways)**

**Similar Readings**

**How to Convert Time to Number in Excel (5 Easy Methods)****Convert 3 Letter Month to Number in Excel (8 Suitable Methods)****How to Convert Date to Week Number of Month in Excel (5 Ways)****Excel VBA to Convert Textbox Value to Number (2 Ideal Examples)****How to Convert Month to Number in Excel (3 Easy Methods)**

### 6. Employing Paste Special Option

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 mean’s 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 String to Number in Excel VBA (3 Methods)**

### 8. Use of VALUE Function

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

**Read More:** **How to Convert Text to Number Using Formulas in Excel**

### 9. Excel Convert to Number Entire Column by Using 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
**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, **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 converted 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.

**Read More:** **How to Convert Percentage to Whole Number in Excel (4 Methods)**

## Practice Section

Here, I have provided a practice sheet for you to practice.

## Conclusion

In this article, I tried to cover 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 for 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.

## Related Articles

**How to Convert Degrees Decimal Minutes to Decimal Degrees in Excel****Convert Percentage to Decimal in Excel (7 Methods)****How to Convert Exponential Value to Exact Number in Excel (7 Methods)****Convert Degrees Minutes Seconds to Decimal Degrees in Excel****How to Convert Hours and Minutes to Decimal in Excel (2 Cases)****Convert Scientific Notation to Number in Excel (7 Methods)****How to Convert String to Double in Excel VBA (5 Methods)**