Convert numbers to percentages the right way in Excel

In Excel, it’s common practice to store percentage values as numbers under the General number format; for example, 12, 6, and 16. By only including the percent symbol (%) in a column’s header, you can avoid repetition and extra clutter.

However, doing so can cause problems later on if you decide to change these to Percentage. This is because values outside of this are multiplied by 100, meaning the aforementioned figures become 1200%, 600%, and 1600%. If they started as 0.12, 0.06, and 0.16, they’d respond perfectly to the switch, but is that really a desirable format to display them in initially?

Storing percentages as Percentage is not essential and does not affect calculations in the same way as storing numbers as Text. Nevertheless, it’s good practice to store them in the way Excel is most comfortable with to avoid any issues further down the line.

Assuming you have a table of data with a column of percentage values stored as General, do the following:

1. Enter 10000% in a non-contiguous cell outside the table.
2. Copy the value (Ctrl + C).
3. Select the range containing percentage values.
4. Open the Paste Special window (Alt + E + S).
5. Choose Divide and then select OK.

The values update not only to include the percent symbol after each, but their number formats also change to Percentage.

You could just divide them all by 100 to make them decimals, but the method shown saves you from having to change the number format as well.

Clever, eh?