Instructions for formatting % in Excel

When you apply Microsoft Excel's percentage number format to a cell that already contains a number, it multiplies that value by 100. This can be a problem for data processors, especially if you want to convert all the values ​​in a column to percentage format. Here's how to format standard percentages in Excel without errors.

 

How to format percentages in Excel

If you select all the cells containing these values ​​and click the "%" symbol, all the numbers will be multiplied by 100, resulting in a larger percentage value than intended. This is because Excel defaults to expressing percentages as fractions of 100, with 1 equaling 100%, 0.5 equaling 50%, and so on.

Step 1:

First you need to enter 1% in any cell as shown below and press Enter.

 

Then copy the 1% cell and select the entire data range that you want to format as a percentage. If your data is formatted as an Excel table, hover over the column header until you see a small black down arrow, then click to select the entire column.

If your data is in regular cells, navigate to the topmost data cell in the column and press Ctrl+Shift+Down.

Step 2:

Press Ctrl+Alt+V to open Paste Special , or click Paste and select Paste Special at the bottom of the list.

 

Step 3:

Displays the special paste options interface. Check the Multiply box or press the M key. Alternatively, if you want to keep the direct formatting of the selected cells while applying percentage formatting from the copied cells, check Formulas And Number Formats . Then click OK.

 

As a result all values ​​are converted to percentages and their original formatting remains intact.

Step 4:

Click on the 1% cell , then select the Clear icon and select Clear All to delete this cell. The percentage data column will not be affected.

Step 5:

When you add data to the next blank row at the end or rows inserted in between, the new cells automatically apply percentage number formatting. This also applies to data in regular ranges, but you need to set it up manually.

Press the 3 keys Alt > F > T in turn to open the Options interface. Then click on Advanced and select Extend Data Range Formats And Formulas.