How to Convert Percentage to Number in Excel

How to Convert Percentage to Number in Excel

Converting a percentage to a number in Excel is straightforward. You simply need to remove the percentage formatting from your cell. You can achieve this by changing the cell format from ‘Percentage’ to ‘General’ or ‘Number,’ which will display the value as a whole number or a decimal. This process only takes a few clicks and can be done in seconds.

Step-by-Step Tutorial on How to Convert Percentage to Number in Excel

In this section, we’ll walk through the specific steps needed to convert a percentage to a number in Excel. By the end of these steps, your percentage values will be displayed as numbers.

Step 1: Select the Cell or Range of Cells

First, you need to select the cell or range of cells that contain the percentage values you want to convert.

Click on the cell you wish to change. If you have multiple cells, click and drag to highlight them all. This ensures you’re making changes to all the desired cells at once.

Step 2: Open the Format Cells Dialog Box

Next, you’ll need to open the Format Cells dialog box to change the cell formatting.

Right-click on any of the selected cells and choose ‘Format Cells’ from the context menu. This will open up a new window where you can adjust the formatting options.

Step 3: Change the Cell Format to General or Number

Inside the Format Cells dialog box, you need to change the format type from ‘Percentage’ to ‘General’ or ‘Number.’

In the Number tab, select ‘General’ or ‘Number’ from the list on the left. If you choose ‘Number,’ you can also specify the number of decimal places.

Step 4: Click OK to Apply Changes

After choosing the desired format, click OK to apply the changes.

This will close the dialog box and your selected cells will now display their values as numbers instead of percentages.

Step 5: Check the Result

Finally, you want to verify that the conversion has been done correctly.

Look at your cells to make sure they now show the raw numbers without the percentage sign. If they do, you’re all set!

Once you complete these steps, your percentages will be converted into numbers. This is useful when you need to perform further calculations or analyze the data without the percentage format.

Understand What You Will Get

Excel stores a percentage as a decimal and only displays it with a % sign. A cell showing 25% actually contains 0.25. So changing the format to General or Number shows 0.25, not 25. Decide which result you need:

• A decimal (0.25): change the format, as in the steps above. Use this for calculations, because multiplying by 0.25 is the same as taking 25%.
• A whole number (25): multiply by 100, as shown below.

Convert Percentages to Whole Numbers (25% to 25)

With a formula

In an empty column, enter =A2*100 and fill it down. Format the result as Number or General. For a clean whole number, use =ROUND(A2*100,0); see how to round in Excel.

In place with Paste Special

1. Type 100 in an empty cell and copy it with Ctrl + C.
2. Select the percentage cells.
3. Right-click and choose Paste Special, select Multiply, and select OK.
4. Change the format of the selected cells to General or Number. Delete the 100 helper cell.

Convert Percentages Stored as Text

Data imported from websites or other systems often contains percentages as text, which are left-aligned and do not calculate. Convert them with:

• =VALUE(A2) or =A2*1 to turn “25%” into 0.25.
• =NUMBERVALUE(A2), which also handles different decimal separators.
• =SUBSTITUTE(A2,"%","")*1 to get 25 instead of 0.25.

Alternatively, select the column and use Data > Text to Columns > Finish, which makes Excel re-read the values as numbers.

Go the Other Way: Number to Percentage

To show 0.25 as 25%, select the cells and press Ctrl + Shift + 5, or click the % button in Home > Number. If your cells contain 25 rather than 0.25, divide by 100 first, or the result will display as 2500%. For calculations with percentages, see how to multiply by a percentage in Excel.

Troubleshooting

Changing the format shows 0.25 instead of 25

That is expected; multiply by 100 to get 25.

The cells will not change format

They are text. Use VALUE or Text to Columns first.

Results show many decimal places

The original percentages had hidden decimals, such as 25.37%. Set the number of decimal places in Format Cells, or use ROUND.

Tips for Converting Percentage to Number in Excel

• Use Keyboard Shortcuts: Press Ctrl + 1 to open the Format Cells dialog box quickly.
• Double-check Cell Values: Sometimes, converting can lead to unexpected values; always double-check.
• Copy Before Converting: Make a copy of your data before converting, just in case.
• Use Formulas: You can also use formulas like =A1/100 to convert percentages.
• Understand the Basics: Knowing why and when to convert can help you make better decisions with your data.

Frequently Asked Questions

What happens to my data when I change the format?

When you change the format from percentage to number, the underlying value remains the same, but it is displayed differently.

Can I convert multiple cells at once?

Yes, you can select a range of cells and change the format for all of them simultaneously.

Will this affect any formulas I have?

Changing the cell format will not affect formulas that reference these cells. However, the displayed values will change.

Is there a way to automate this?

You can use VBA macros to automate the formatting change if you have to do it frequently.

Can I change the default cell format in Excel?

While you can’t change the default cell format for all new workbooks, you can create a custom template with your preferred settings.

Related: change the number format in Excel.

Related: calculate a weighted average in Excel.

Related: how to make a pie chart in Excel with percentages.

Related: How to Run Descriptive Statistics in Excel.

Related: how to calculate commission in Excel.