In an Excel sheet, you can use different kinds of formats based on your need. Percentage is one of the number formats which is used to show a relative value that indicates the 100th part of any quantity. But sometimes you may need to remove the percentage while keeping the value as it is or you need the decimal value. In this article, I’m going to explain, how to remove percentage in Excel.
For the demonstration purpose, I’m going to use a dataset that contains information about an employee. The dataset has 4 columns; these are Employee Name, Salary, Bonus, and Percentage.
.
Here, I calculated the Percentage by dividing the Bonus by Salary.
How to Remove Percentage in Excel: 5 Ways
1. Remove Percentage Using General Format in Excel
One of the easiest ways to remove percentages is using the General format but the problem is it converts the percentage values into decimal points.
Let me show you the procedure,
To begin with, select the cell or cell range from where you want to remove the percentage.
➤ I selected the cell range E4:E11.
Open the Home tab >> from Number Format >> select General
Hence, you will get the Percentage values in General format without a percentage sign.
2. Remove Percentage Using Number Format
Another way to remove percentages is by using the Number format.
To start with, select the cell or cell range from which you want to remove the percentage.
➤ I selected the cell range E4:E11.
Open the Home tab >> from Number Format >> select Number
As a result, the Percentage values are formatted as Numbers while ignoring the sign.
3. Remove Percentage Using Paste Special in Excel
To keep the percentage values and to remove the percentage sign you can use the Paste Special.
To demonstrate the procedure, I’ve taken an additional column where I kept the values 100. As I want to remove the percentage sign so, I’ll need to multiply the value by 100.
The logic behind this is, (5.00/100)*100
To begin with, select the cell where you kept the value 100.
➤ I selected cell G4.
Then, right-click on the mouse and select Copy from the context menu.
You can also use CTRL + C to copy the value.
Now, select the cell or cell range from where you want to remove the percentage sign and keep the value as it is.
➤ I selected the cell range E4:E11.
Again, right-click on the mouse and double-click on Paste Special from the context menu.
A dialog box will pop up. From there select the Operation.
⏩ I selected Multiply.
Then, click OK.
Hence, it will remove the Percentage sign but the value will remain the same.
4. Remove Percentage Using Custom Format
By using the Custom format and Wrap Text you can keep the percentage values while removing or hiding the sign.
Let me show you the procedure,
To begin with, select the cell or cell range from where you want to remove the percentage sign while keeping the value.
➤ I selected the cell range E4:E11.
Open the Home tab >> from Number Format >> select More Number Formats
A dialog box will pop up. From there select Category.
⏩ I selected Custom.
Then, selected 0.00% as the Type.
Now, place the cursor before the percentage and press CTRL + J.
CTRL + J will create a new line.
Here, the percentage is removed from Type then click OK.
Now, select the cell range to apply Wrap Text.
➤ I selected the cell range E4:E11.
Open the Home tab >> from Alignment >> select Wrap Text
Hence, it will apply Wrap Text to the selected cell.
As I don’t want the percentage sign, so I will hide the line where the percentage is.
To do that, I will decrease the Row Height.
First, select the cell or cell range from where you want to remove the percentage sign.
➤ I selected the cell range E4:E11.
Open the Home tab >> go to Cells >> from Format >> select Row Height
➤ I inserted the height as 16.
Then, click OK.
Therefore, it will hide the percentage sign only the values will be visible.
5. Using Power Query to Remove Percentage in Excel
You also can use the Power Query to remove percentages while keeping the same value.
To start with, select the cell range
➤ I selected the cell range B3:B11.
Open the Data tab >> select From Table/Range
A dialog box will pop up. It will show the selected range.
➤ Mark on My table has headers.
Then click OK.
A new window will appear.
From Add Column tab >> select Custom Column
A dialog box of Custom Column will appear.
⏩ In the New column name I used Percentage Value.
⏩ In the Custom column formula I used [ Percentage ]*100.
Then, click OK.
Hence, it will add a new column name Percentage Value showing the values.
In case you want to load the table in the worksheet you can do it.
Open Home tab >> from Close & Load >> select Close & Load To…
A dialog box will pop up. From there you can choose the place to put the data.
⏩ I selected the New worksheet.
Then, click OK.
Therefore, it will import the data in a new worksheet.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained examples.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have shown 5 ways to remove percentage in Excel. These ways will help you to remove percentages easily. Feel free to comment down below for any types of queries and suggestions.
Related Articles
- How to Calculate Total Percentage from Multiple Percentages in Excel
- How to Calculate Percentage of Month in Excel
- How to Calculate Percentage of Percentage in Excel
- How to Calculate Percentage Based on Conditional Formatting
- How to Calculate Percentage in Excel Based on Cell Color
- Percentage Showing as Thousand in Excel
- Why Are My Percentages Wrong in Excel?
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Percentage Complete Based on Dates in Excel
- How to Calculate Error Percentage in Excel
- How to Calculate Cumulative Percentage in Excel
<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!