See all How-To Articles

Add Percentage Style to a Number in Excel & Google Sheets

This tutorial demonstrates how to style a number with a percentage sign in Excel and Google Sheets.

 

add percentage style to numbers 7

 

Add Percentage Style to a Number

In Excel, you can format a list of numbers as percentages. Say you have the following list of numbers in Column B.

 

add percentage style to numbers initial data

 

To format numbers from Column B as percentages, you need to use a separate column (C).

1. Column C should have the values from Column B divided by 100, so in cell C2, enter the formula:

 

add percentage style to numbers 1

 

2. Position the cursor in the bottom right corner of cell C2, and when it turns to a black cross, drag it to the end of the range (C8).

 

add percentage style to numbers 2

 

3. Now you have all numbers from Column B divided by 100 in Column C. Select the range with the divided numbers (C2:C8) and in the Ribbon, go to Home > Number > Percentage (%).

 

add percentage style to numbers 3

 

As a result, the values in Column C are formatted as percentages

 

add percentage style to numbers 4

 

As you can see above, the default for percentages is zero decimal places. If you want to add decimal numbers to percentages, (1) select the range with percentage values, and in the Ribbon, (2) go to the Home tab. Here, (3) click on the Increase Decimal icon in the Number group. Every click will add one decimal position, so click twice to add 2 decimal numbers.

 

add percentage style to numbers 5

 

The reason you have to use an extra column (C) is that Excel, when formatting numbers as percentages, doesn’t adjust the whole number to a percentage value. So, if you have 34.5 and convert it to a percentage, it’ll be 345%.

 

add percentage style to numbers 6

 

See also: Formatting Numbers in Excel VBA.

Add Percentage Style to a Number in Google Sheets

In Google Sheets, you can also add the percentage style to a number. As in Excel, you need a helper column with whole numbers divided by 100 (see Step 1 in the section above).

1. Select the cells with decimal numbers (C2:C8), and in the Menu, click the Percentage symbol.

 

google sheets add percentage style to numbers 1

 

2. To add decimals, (1) select the range with percentages (C2:C8), and in the Menu, (2) click the Increase decimal places icon.

 

google sheets add percentage style to numbers 2

 

All values from Column B now have percentage style in Column C.

 

google sheets add percentage style to numbers 3