Alok Paul
The percentage is the fraction of two numbers multiplied by 100. Mathematically, we need to multiply a number by 100 to convert that into a percentage. But Excel has other options to convert a number to a percentage without multiplying that by 100. In this article, we will discuss those methods with proper illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Methods to Convert Number to Percentage Without Multiplying by 100 in Excel
We will discuss 5 methods including different shortcuts and formulas to convert numbers to percentages without multiplying by 100. Look at the below section for details.
We will consider the dataset which contains profit percentage months of the year 2021 in decimal form. We need to convert that into percentile.
1. Using Keyboard Shortcut
The easiest way to convert the numbers of column Profit Percentage is to apply a keyboard shortcut. Just in the blink of an eye, we will see the result. Let’s do it.
📌 Steps:
- First, select Range C5:C16.
- Then, press Ctrl+ Shift + % on the keyboard and look at the dataset.
We can see all are showing the percentile form.
Read More: How to Calculate Percentage of a Number in Excel (5 Easy Ways)
2. Using the % Button from Ribbon to Convert Number into Percentage
In this section, we will show a ribbon shortcut to convert numbers into a percentage.
📌 Steps:
- First, select the data range.
- Then, click on the Percentage symbol showing in the Number group.
- Look at the dataset now.
Numbers are converted into a percentage. We can increase the decimal places in the result if they are lost.
- Again, select the dataset. Then, click on the Increase Decimal option from the Number group.
We can see hidden decimal points are shown in the result.
Read More: How to Use Excel Formula to Calculate Percentage of Grand Total
3. Applying Custom Format
In this section, we will convert the number into a percentage using the Custom section of the Format Cells window.
📌 Steps:
- Like the previous methods, select the data range first.
- Then, press the Ctrl+1 to get the Format Cells window.
- The Format Cells window appears.
- Choose the Custom option from the Number tab.
- Input 0% in the Type section.
- Now, press the OK button and look at the dataset.
We can see the result is shown in percentage form. But notice that no decimal point is showing in the result.
- Again, enter the Format Cells window.
- Change the previous Type and replace that with 00%.
- Again, look at the dataset.
Lost decimal places are retrieved here.
Read More: How to Calculate Percentage Based on Conditional Formatting (6 Ways)
Similar Readings
- How to Calculate ROI Percentage in Excel (6 Easy Ways)
- Calculate Productivity Percentage in Excel (3 Useful Cases)
- How to Calculate Absenteeism Percentage in Excel (2 Easy Ways)
- Cap Percentage Values Between 0 and 100 in Excel
- How to Calculate Mean Percentage Error in Excel
4. Using CONCAT Function to Convert Number into Percentage
In this section, we will use the CONCAT function. This will add a percentage symbol to the data. Previously, we used CONCATENATE function. This CONCAT function is the updated form of CONCATENATE function available in the latest Excel versions.
📌 Steps:
- First, we add a new column on the right side to show the result converting to percentage.
- Put the following formula on Cell D5.
=CONCAT(C5/0.01,"%")
In this formula, we divide the numbers of Column C by 0.01 and then add the percentage symbol (%).
- Now, press the Enter button and drag the Fill Handle icon.
We successfully convert numbers into a percentage.
Read More: Percentage Formula in Excel (6 Examples)
5. Convert Number to Percentage in Excel Pivot Table
This method is based on the Pivot table. A pivot table is one of the most useful features of Excel. Have a look at the below discussion.
We were given a dataset of each month’s profit of 2021 of a super shop. First, we will calculate the profit percentage for each month based on the total profit of the year.
📌 Steps:
- Put the following formula on Cell D5.
=C5/SUM($C$5:$C$16)
Here, we used the SUM function to get the total profit for the year 2021. Then, use this value to divide each month’s profit. We use the absolute cell reference in the SUM function as months this range is fixed.
- Now, press the Enter button and drag the Fill Handle icon.
- Now, we will proceed to the Pivot Select the whole dataset.
- Then, click on the Pivot Table option from the Insert tab.
- A new window appears.
- We choose the Existing Worksheet option to place the Pivot table.
- Then, select a cell in the worksheet for the Location section.
- Then, press the OK button.
- The PivotTable Fields section appears.
- Tick the Month option and place that in the Rows field.
- Again, tick the Profit Percentage option and place this in the Values field.
- Look at the dataset.
We can see the result is in decimal form.
- Move the cursor to the Sum of Profit Percentage cell and press the right button of the mouse.
- Choose Value Field Settings from the Context Menu.
- The Value Filed Settings window appears.
- Choose Show Values As option.
- Then, click on the drop-down symbol as marked in the image.
- Choose the % of Column Total option.
- Finally, press the OK button.
The number of our dataset is now converted to a percentage.
Read More: How to Calculate Total Percentage in Excel (5 Ways)
How Do I Remove Percentage Symbol in Excel Without Changing Values?
In this section, we want to remove the percentage symbol in Excel without any value. For that, we will use a formula based on the combination of VALUE, TEXT, LEFT, and LEN functions.
- The VALUE function converts a text string that represents a number to a number.
- The TEXT function converts a value to text in a specific number format.
- The LEN function returns the number of characters in a text string.
- The LEFT function returns the specified number of characters from the start of a text string.
Look at the below section. We have the following dataset for this operation.
📌 Steps:
- Just go to Cell D5 and insert the following formula.
=VALUE(LEFT(TEXT(C5,"00.00%"),LEN(C5)-1))
- Then, press the Enter button and drag the Fill Handle icon.
We can see percentage symbol has been removed without changing the values. We can also say this is the percentage value without a symbol.
Formula Breakdown:
- TEXT(C5,”00.00%”)
This converts any value in the given format in the formula.
Result: 30.00%
- LEN(C5)
This defines the length of Cell C5.
Result: 3
- LEFT(TEXT(C5,”00.00%”),LEN(C5)-1)
This returns characters from the start. How many digits will appear depends on the LEN function.
Result: 30
- VALUE(LEFT(TEXT(C5,”00.00%”),LEN(C5)-1))
The VALUE function converts text strings into numbers.
Result: 30
Read More: Excel Formula to Add Percentage Markup (3 Suitable Examples)
Conclusion
In this article, we described 5 different methods to convert a number to a percentage without multiplying by 100 in Excel. We also showed how to remove the percentage symbol without changing the value. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.
Related Articles
- How to Calculate Cumulative Percentage in Excel (6 Methods)
- Calculate Percentage Difference Between Two Numbers in Excel
- How to Calculate Dividend Growth Rate in Excel (3 Methods)
- How to Use Profit and Loss Percentage Formula in Excel (4 Ways)
- Why Are My Percentages Wrong in Excel? (4 Solutions)
- How to Calculate Contribution Percentage with Formula in Excel
