While working with Microsoft Excel, cells may hold a wide range of data and may format in a variety of ways. Sometimes, when users enter a number into particular cells, a % sign is immediately inserted, users may be seeking a means to stop such action. In this article, we will demonstrate some suitable ways to remove percentage symbol in excel without changing values.
Download Practice Workbook
You can download the workbook and practice with them.
8 Suitable Ways to Remove Percentage Symbol in Excel Without Changing Values
When we enter a value with a percentage, it will format the cell as a percentage cell, so that whatever number we enter will be displayed with a % sign. This is actually automatically notified by Excel. But many times, we don’t want the percentage symbol to be shown in the data.
To remove the percentage symbol in excel without changing values, we are using the following dataset. The dataset contains some student names in column B, their obtain mark in column C, the total marks in column D, and the percentage they acquire in that particular subject in column E. Now, we want to remove the percentage symbol from the percentage column.
1. Change Format as General to Remove Percentage Without Changing Values
There are only two fundamental requirements for the General format. If a value has decimals, they will be presented corresponding to the highest value that suits the column. We can remove the percentage symbol by formatting it with general settings. For this, let’s go through the procedures down.
- Firstly, insert a new column to compare after removing the percentage symbol the values will remain the same.
- Secondly, copy the entries from the Percentage column to the Percentage (Without Symbol) column. But use the paste option as Value, otherwise, this will copy the percentage with the formula, as we use the C5/D5 formula in the Percentage column.
- Now, select the cell range F5:F10 from the new column F and go to the Home tab from the ribbon. You can see in the Number category, this will format as Percentage.
- Further to change the format, click on the drop-down menu. And select General.
- Now, you can see the cell ranges are now displaying the value without the percentage symbol.
- However, their values are not the same. To keep the value as same as column E, multiply each of the data by 100 to place the value without changing and eliminate the percentage symbol.
Read More: How to Remove Symbol in Excel (8 Ways)
2. Apply Number Formatting for Removing Percentage in Excel
A number format is a method of representing numbers with representational symbols that employ a base value to arrange numbers in a simple format. We can change the format to a number to remove the percentage symbol. To apply number formatting for removing the % symbol in excel we need to follow the steps down.
- Likewise, the previous method, first, adds a new column for comparison. After deleting the % sign the numbers will remain the same.
- Second, copy the Percentage column’s contents to the Percentage (Without Symbol) column. However, use the paste option as Value; otherwise, because we are using the C5/D5 formula in the % column, this would copy the percentage with the formula.
- Next, go to the Home tab on the ribbon. When you pick the cell range F5:F10 from the new column F, this is formatted as Percentage, as you can see in the Number category.
- Click the drop-down menu again to alter the format. And then go to Number.
- And, you can see, the value is now shown without the percentage indicator in the cell ranges.
- Although, the values are not the same. Similarly, as before, multiply each of the data by 100 to position the value without changing it and remove the percentage symbol to retain the value the same as column E.
3. Utilize Format Cells to Omit Percentage Without Changing Values
Format Cells allow us to modify the appearance of a cell number without changing the actual number. Format Cells tool is the best option to remove the percentage symbols without changing the value of excel data. To use this feature for removing percentage signs, we have to look at the process shown down.
- Similarly, the first technique adds a new column for comparison. The figures will remain the same after removing the percent indication. Transfer the contents of the Percentage column to the Percentage (Without Symbol) column.
- Use the paste option as a value instead of copying the percentage with the formula because we are utilizing the C5/D5 formula in the percentage column.
- In the second place, select the new column and go to the Home tab from the ribbon.
- Then, click on the tiny icon, under the Number category to open the Format Cells dialog.
- Instead of doing this, you can just select the cell range and right-click on your mouse then, click on the Format Cells.
- Alternatively, you can use the keyboard shortcut Ctrl + 1 to display the Format Cells window.
- Use any of those approaches to open the Format Cells dialog box.
- Then, go to the Number menu and click on Custom.
- In the Type field, you will notice that 0.00% formatting has already been provided.
- Further, place the cursor immediately before the percent symbol in the Type field, and then press Ctrl + J.
- You will note that the formatting in the Type field is now altering to 0.00 only, omitting the percentage sign.
- Now, press the OK button to close the Format Cells dialog box.
- You will notice that the % symbols are still there in the figures.
- To fix this, click Wrap Text from the Home tab and choose the range of cells F5:F10.
- Finally, you will see that the percentage sign is absent from cells F5 through F10.
4. Multiply Specific Number for Removing Percentage Symbol
To omit the percentage symbol we can multiply a specific number which is 100 with the data containing the % symbol. For this, suppose we insert the number in cell H5. Let’s see the step down for this.
- Firstly, select the whole range F5:F10 and go to the Home tab to change the format to Number.
- Second, insert the formula in cell F5.
- Thirdly, press Enter.
- Furthermore, to copy the formula over the range, drag the Fill Handle down or double-click on the plus (+) icon.
- Finally, you can see the percentage symbol is now removed and the value still remains the same.
Note: Here, we use the dollar $ sign, cause we want the cell as an absolute reference. As we don’t want to change the cell. While using the absolute reference the cell will remain the same if you duplicate the formula anywhere else.
5. Remove Percentage with Excel Paste Special Feature
With the Excel Paste Special feature, we can maintain the formatting as per our requirements. We can also use this tool to omit the percentage symbol. For this, again we need a specific number which is 100. So, we insert the number in cell G5.
- In the first place, copy the cell G5.
- Then, select the cell range E5:E10.
- After that, right-click on the mouse and select Paste Special.
- This will display the Paste Special dialog box.
- In the Operation section select Multiply.
- Next, click on the OK button to close the dialog box.
- And, that; it! You can now see the percentage symbol removed and the values remain the same.
6. Use Excel Formula to Take Off Percentage in Excel
To remove the percentage symbol without changing the values, we will use a combination of VALUE, lEFT, and TEXT functions in excel. The VALUE Function belongs to the Excel Text Functions category. It will transform a number-representing text string into a numeric one. The Excel LEFT function removes a specified number of characters from a text string’s left side. The TEXT function allows you to modify the appearance of a number by using format values to impart formatting to it. To utilize the formula for removing the percentage symbol, follow the steps down.
- In the first place, choose the cell where you want to remove the percentage symbol without changing the values. So, we select cell F5.
- Then, enter the following formula there.
- After that, to complete the operation hit the Enter key.
- Further, to replicate the formula throughout the range, drag the Fill Handle downward. To AutoFill the range, double-click on the plus (+) symbol.
- By doing this, you will get the result.
🔎 How Does the Formula Work?
- TEXT(E5,”00.00%”): This function accepts the content of cell E5 as an argument and outputs a text string in the format 00.00.
- LEFT(TEXT(E5,”00.00%”),5): The 5 characters on the left side of the strings returned by the TEXT function will be extracted with this method.
- VALUE(LEFT(TEXT(E5,”00.00%”),5)): This returns the number from the left function’s string format.
Note: The arguments inside the functions should be entered with caution in this formula. The rounding value, which is 2 in this case, is represented using the format 00.00. You must specify a rounding value that is appropriate for your needs.
7. Excel VBA Macro to Remove Percentage Symbol Without Changing Values
With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. We can use the VBA macro to remove the percentage symbol and keep the value the same.
- Firstly, go to the Developer tab from the ribbon.
- Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
- This will appear in the Visual Basic Editor where we write our code to create a table from a range.
- Thirdly, click on Module from the Insert drop-down menu bar.
- This will create a Module in your workbook.
- And, copy and paste the VBA code shown below.
Sub RemovePercentageSign() Dim r, c As Range Set r = Range("E5:E10") For Each c In r c.NumberFormat = "General" c = c.Value * 100 Next End Sub
- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- Finally, this will remove the percentage symbol without changing the values.
Note: While using VBA Macros, make sure you save your file as a macro enable file. And the extension for the file will be .xlsm.
8. Apply Power Query to Remove Percentage Symbol in Excel
The main benefit of utilizing Power Query for many customers is it’s fast and easy to implement. You may modify data by importing or connecting to external data. Power Query is regarded as a very strong tool for frequently running commands in Excel. This, however, will not be a quick procedure. We can apply it to remove the percentage symbol without changing values. Let’s follow the procedure down.
- Firstly, go to the Data tab from the ribbon.
- Secondly, from Get & Transform Data click From Table/Range.
- This will appear Create Table dialog box.
- Now, in Where is the data for your table?, select the range $B$4:$E$10. Make sure to tick My table has headers, to let Excel know that the first row of your table is the header.
- Further, click OK.
- This will open the Power Query Editor.
- Next, go to Add Column from the menu bar.
- Under General group, click on Custom Column.
- The Custom Column window will display, set the New column name; Remove Percentage Symbol.
- Furthermore, in the Custom column formula field, enter the following formula.
- Finally, click the OK button to finish the process.
- This will add a new column without changing the values. The new column contains all of the numbers from the Percentage column, but without the percentage symbol.
- Now, close the Power Query Editor, while closing it a window will appear asking Do you want to keep your changes?. Click on Keep.
- This is it!. The table from the power query will now be structured with percentage values on the worksheet.
The above methods will assist you to Remove the Percentage Symbol in Excel Without Changing Values. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!