It is quite easy to convert number to percentage in Microsoft Excel. Technically Excel will convert any input data into a percentage by multiplying it by 100 and adding a percentage symbol (%) on the right if you opt to choose percentage formatting. But you can also convert a number directly to a percentage value without letting it be multiplied by 100 in Excel. Here are some useful & basic methods I’ve tried to illustrate how you can do them both.
Download Practice Workbook
You can download the worksheet here to practice which we’ve used to prepare this article.
3 Suitable Methods to Convert Number to Percentage in Excel
In this section of the article, we will learn 3 suitable methods to convert number to percentage in Excel.
Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Using the Percent Style Button from Number Group
Suppose a business company has decided to determine the Profit Percentage for 12 months in a particular year. They have calculated the amount of profits subject to cost prices which are displayed as decimals in the column named Percentage Profit. Now, we will convert these data into percentages. Let’s follow the steps outlined below to do this.
- Firstly, select all cells containing the decimals and numbers in the Percentage Profit column.
- After that, under the Home tab, click on % (Percent Style) from the Number group of commands.
There you go! You’ve just converted all decimals into percentages right away, as shown in the picture below.
2. Utilizing Custom Number Format Option
Now let’s assume, the company has already calculated its percentage values, and now they just want to add Percentage Symbols beside all values without changing the data. So, here is the dataset that has the percentage values. Let’s use the steps mentioned in the following section.
- Like the previous method, select all the cells of the Percentage Profit column first.
- Following that, under the Home tab and from the Number group of commands, click on the Number Format option.
As a result, a new tab box named Format Cells will appear.
- Now, select Custom formatting from the Number tab.
- Then, click on the General option inside the Type box.
Note: Also, you can press CTRL + 1 to open the Format Cells dialog box.
- After that, type the following format code in the Type field.
- Now, click on OK.
Consequently, you’ll get all the values in percentage format at once, as demonstrated in the following picture.
If you want to add decimal places then follow the procedure outlined below.
- Firstly, select the cells of the Percentage Profit column and go to the Home tab from Ribbon.
- Then, click on the Number Format option from the Number group.
- Now, replace 0\% with 0.00\% if you want to add 2 decimal places.
- Finally, click OK or press ENTER and you’re done.
Now you’ve got all the percentage values with 2 decimal places.
3. Converting a Resultant Fraction or Number to Percentage in Excel
Now we have a datasheet containing the Cost Prices & Selling Prices over a year for a business company, and we have to find the Percentage Profits by converting the calculated profit values into percentages. Let’s follow the instructions outlined below to do this.
- Firstly, use the following formula in cell E5.
Here, cell D5 refers to the cell of the Selling Price column, and cell C5 indicates the cell of the Cost Price column.
- Following that, press ENTER.
As a result, you will have the following output on your worksheet.
- Now, follow the steps mentioned in the 2nd method to obtain the following output in cell E5.
- Now, in Cell E5, point your mouse cursor onto the right bottom corner & you’ll find a ‘+’ icon there which is known as Fill Handle.
- After that, select the Fill Handle icon & drag it to cell E16.
- Finally, release the mouse button & the whole Percentage Profits for all Months in the specific year will be shown right away.
- How to Show Percentage in Excel Pie Chart (3 Ways)
- Display Percentage in an Excel Graph (3 Methods)
- How to Make a Percentage Bar Graph in Excel (5 Methods)
- Convert Percentage to Basis Points in Excel (Basis Point Calculation)
- How to Use Phone Number Format in Excel (8 Examples)
How to Convert Number to Percentage in Excel Pivot Table
While working in Excel, we frequently come across Pivot Tables. In the pivot table, we often need to convert numbers to percentage format. In this section of the article, we will learn how we can convert number to percentage in Excel pivot table.
Let’s say, we have the Yearly Profit Analysis data of a business company as our dataset. In the dataset, we have the Percentage Profit of each Month with respect to the Total Yearly Profit of the company. Our aim is to convert the numbers in the Percentage Profit column into percentage format.
Let’s follow the steps mentioned below to do this.
- Firstly, select the dataset and go to the Insert tab from Ribbon.
- After that, select the Pivot Table option from the Tables group.
- Subsequently, choose the New Worksheet option from the dialogue box named PivotTable from table or range.
- Then, click OK.
As a result, the Pivot Table Fields dialogue box will be available on your worksheet as shown in the following image.
- Now, select the Month option and drag it into the Rows section.
- Similarly, select the Percentage Profit option and drag it into the Values section.
Consequently, you will have the following outputs on your worksheet.
- After that, right-click on cell C4 and click on the Value Field Settings option.
- Now, in the Value Field Settings dialogue box, go to the Show Values As tab.
- Then, click on the drop-down icon.
- Subsequently, choose the % of Column Total option from the drop-down.
- Finally, click on OK.
Consequently, you will have the numbers converted to percentages in the Excel PivotTable as demonstrated in the image below.
How to Convert Number to Percentage in Excel Charts
In Excel, we can convert the numbers in the chart axis to percentage format by following some simple steps. Here we will learn these simple steps to convert number into percentage in Excel charts. Let’s follow the instructions outlined below to do this.
- Firstly, select the dataset and go to the Insert tab from Ribbon.
- Following that, select the Insert Column or Bar Chart option from the Charts group.
- Then, choose the Clustered Column option from the drop-down.
As a result, you will have the following chart on your worksheet.
- Now, double-click on the vertical axis of the chart as marked in the following image.
As a result, the Format Axis dialogue box will be available on your worksheet.
- After that, in the Format Axis dialogue box, click on the drop-down icon under the Category field in the Number section.
- Then, choose the Percentage option from the drop-down.
Congratulations! You have successfully converted number to percentage in an Excel chart. It’s that simple!
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to calculate percentage values or convert any type of number into a percentage. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions & formulas on our website ExcelDemy.
- How to Round to Nearest 100 in Excel (6 Quickest Ways)
- Round Numbers to the Nearest Multiple of 5 in Excel
- How to Custom Number Format in Excel with Multiple Conditions
- Round Numbers in Excel Without Formula (3 Quick Ways)
- How to Add Currency Symbol in Excel (6 Ways)
- Put Parentheses for Negative Numbers in Excel