How to Convert Entire Column to Number in Excel (9 Methods)

Let’s use the following dataset, which contains some Subject and Marks. The numbers are showing errors (or error suggestions) because the format is not a number.

9 Simple Ways of Excel Convert to Number Entire Column


Method 1 – Converting an Entire Column to Numbers in Excel with the Convert to Number Option

In this scenario, the error is showing because there is an Apostrophe before the number.

Steps:

  • Select the data of the column where the error is showing.
  • Select the warning sign.

Excel Convert to Number Entire Column

  • A drop-down menu will appear.
  • Select Convert to Number.

  • The entire column has been converted to numbers.

Excel Convert to Number Entire Column


Method 2 – Convert to Number by Multiplying

Steps:

  • Add a Helper Column in the dataset.

Excel Convert to Number Entire Column by Multiplying

  • Select the cell where you want to convert to number. We selected cell D5.
  • Insert the following formula:
=C5*1

  • Press Enter to convert to number.

Excel Convert to Number Entire Column by Multiplying

  • Drag the Fill Handle down to copy the formula.

  • The formula is copied to the entire column.

  • The entire column has been converted to numbers.

Excel Convert to Number Entire Column by Multiplying

Read More: How Excel Formulas Convert Text to Number


Method 3 – Adding 0 to Convert an Entire Column to Numbers in Excel

Steps:

  • Add a Helper Column in your dataset.

Adding 0 for Converting Entire Column to Number

  • Select the cell where you want to convert to number. We selected cell D5.
  • Insert the following formula:
=0+C5

  • Press Enter to convert to number.

  • Drag the Fill Handle down to copy the formula.

  • We have copied the formula to the entire column.

Adding 0 for Converting Entire Column to Number

  • The entire column has been converted to numbers.

Adding 0 for Converting Entire Column to Number

Read More: How to Convert Text with Spaces to Number in Excel


Method 4 – Performing Division to Convert an Entire Column to Number

Steps:

  • Add a Helper Column D in your dataset.

Excel Convert to Number Entire Column by Dividing

  • In cell D5, insert the following formula:
=C5/1

  • Hit Enter.

Excel Convert to Number Entire Column by Dividing

  • Drag the Fill Handle down to copy the formula.

  • This copies the formula to the entire column.

Excel Convert to Number Entire Column by Dividing

  • You can check the results below.


Method 5 – Changing the Cell Format in Excel to Convert an Entire Column to Numbers

  • Select the cell where the error is showing.
  • Go to the Home tab.
  • Select Number.
  • Check the Cell Format. Here, you can see that it is selected as Text.

Changing Cell Format in Excel

  • Select the entire data range.

  • Go to the Home tab from the Ribbon.
  • Select Number.
  • Select the drop-down option on the selected Cell Format.

  • Select General or Number. We selected General.

  • This should convert the entire column to numbers if the values are only numbers.

Changing Cell Format in Excel


Method 6 – Using the Excel Paste Special Option to Convert an Entire Column to Numbers

Steps:

  • Copy an empty cell that is not in Text format. We copied cell B11.

Employing Paste Special Option

  • Select the data where the error is showing.

  • Right-click on the selected column.
  • Select Paste Special.

Employing Paste Special Option

  • A dialog box named Paste Special will appear.
  • Select All from Paste.
  • Select Add from Operation.
  • Select OK.

  • This will convert the entire column to numbers.

Employing Paste Special Option


Method 7 – Using Text to Columns Feature for Converting an Entire Column to Number

In the following picture we can see that the SUM function is not working. That means the numbers here are stored as Text.

Using Text to Columns Feature for Converting Entire Column to Number

Steps:

  • Select the data where the error is showing.

  • Go to Data tab.
  • Select Text to Columns from Data Tools.

  • The Convert Text to Columns Wizard will appear on the screen.
  • Select Delimited from the Original data type.
  • Press Next.

Using Text to Columns Feature for Converting Entire Column to Number

  • Step 2 of Convert Text to Columns Wizard will appear.
  • Select Tab from Delimiters.
  • Select Next.

  • Step 3 of Convert Text to Columns Wizard will appear.
  • Select General in Column data format.
  • Select the 1st cell of your column as Destination.
  • Press Next.

  • This will convert the entire column to numbers.

Using Text to Columns Feature for Converting Entire Column to Number

  • The numbers will keep their alignment to the left (Excel usually aligns numbers to the right), but the formulas work.

Read More: How to Convert Bulk Text to Number in Excel


Method 8 – Use the VALUE Function to Convert an Entire Column to Numbers

If you enter numbers with extra spaces, they will be stored as text.

Use of VALUE Function

Steps:

  • Add a new column to your dataset that will store numbers.

  • Select the cell where you want to convert the first number. We selected cell D5.
  • Insert the following formula:
=VALUE(SUBSTITUTE(C5," ",""))

Formula Breakdown

  • SUBSTITUTE(C5,” “,””): The SUBSTITUTE function will replace the extra spaces with no space.
    • Output: “5000”
  • VALUE(SUBSTITUTE(C5,” “,””)): turns into
    • VALUE( “5000”)—-> The VALUE function will convert the text format into a number format.
      • Output: 5000
      • Explanation: We got the result 5000 as a numeric value.
  • Press Enter to get the result.

  • Drag the Fill Handle to copy the formula.

Use of VALUE Function

  • This fills in the column with the formula.

  • In cell D9, insert the following formula:
=SUM(D5:D8)

  • Press Enter to get the sum.

Use of VALUE Function


Method 9 – Converting the Entire Column to Numbers with Excel Power Query

Steps:

  • Copy your dataset.

Excel Convert to Number Entire Column by Using Power Query

  • Go to the Data tab.
  • Select Get Data.
  • A drop-down menu will appear.
  • Select Launch Power Query Editor.

  • The Power Query Editor will appear.
  • Go to the Home tab.
  • Select Enter Data.

Excel Convert to Number Entire Column by Using Power Query

  • The Create Table option will appear.

Excel Convert to Number Entire Column by Using Power Query

  • Right-click on the first cell.
  • Select Paste.

Excel Convert to Number Entire Column by Using Power Query

  • Select Undo Headers.

  • Select OK.

Excel Convert to Number Entire Column by Using Power Query

This imports the dataset to Power Query. Here, you can see that the format is automatically converted to number.

Excel Convert to Number Entire Column by Using Power Query

  • Click on the marked portion in the image.

  • You will see a drop-down menu.
  • Select Whole Number.

Excel Convert to Number Entire Column by Using Power Query

  • Go to Home tab.
  • Select Close & Load.

Excel Convert to Number Entire Column by Using Power Query

  • You will get the table in your worksheet.

  • You can change the font size and edit the table as you want.

Excel Convert to Number Entire Column by Using Power Query


Download the Practice Workbook


Related Articles


<< Go Back to Convert Text to Number in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo