how to remove percentage symbol in excel

How to Delete Percentage Symbol in Excel

By Hannah Allen
AD

Have you been trying to get rid of percentage symbols (%) in your Excel spreadsheet, only to find that it's not as straightforward as you thought? You're not alone. Many Excel users struggle with this. Today, we're going to demystify this common problem and provide you with a simple, step-by-step guide on how to remove percentage symbols in Excel.

Before we dive in, let's clarify that the percentage symbol appears in two contexts in Excel: as a representation of a percentage in a cell's content, and as a formatting placeholder. The approach to remove them differs depending on which context applies. Let's explore both scenarios.

Removing Percentage Symbols in Excel Content

The first scenario involves removing the actual percentage symbol from the content of a cell. This might occur if you've copied data from another source, or if you've mistakenly inputted a percentage. Here's how to remove it:

AD
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel Without Changing Values
How to Remove Percentage Symbol in Excel Without Changing Values

The formula to remove the percentage symbol is simple: `=left(A1, len(A1)-1)`. This tells Excel to take the content of cell A1 and remove the last character, effectively stripping off the percentage symbol. To use this, first, click on the cell where you want the symbol-free data to appear. Then paste the formula into the cell. Change 'A1' to match the cell containing your data.

Using Replace Function

Alternatively, you can use the `Replace` function in Excel. This can be useful if you want to perform the operation on multiple cells at once. Here's how: After selecting the cells, go to the 'Home' tab, click on 'Replace', enter '%' in the 'Find what' field, type an empty quote ("") in the 'Replace with' field, then click 'Replace All'.

How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy
How to Remove Percentage Symbol in Excel (7 Ways) - ExcelDemy

This will replace all instances of the percentage symbol with nothing, effectively removing it from your data.

Understanding the Difference: Percentage vs. Decimal

Here's a crucial point to understand: percentages in Excel are stored as decimal values. So, if you see something like 50%, you can assume that the actual value stored in the cell is 0.5. This is why simply deleting the '%' can lead to unexpected results. The `Replace` function and the `left` function adjust for this, giving you the correct decimal value.

AD

Removing Percentage Symbols as Formatting

Sometimes, the percentage symbol appears because Excel is displaying numbers as percentages. This could be for formatting purposes, or it might be the default setting on your system. Here's how to remove the percentage symbol when it's being used for formatting:

Select the cells whose formatting you want to change. Right-click on any of the selected cells and choose 'Format Cells'. In the dialog box that opens, go to the 'Number' tab. Under 'Category', choose 'Number'. Under 'Decimal places', change the value to 0. Then, click 'OK'. This will remove the percentage symbol from the display, while still keeping the underlying data as a percentage (calculated as a decimal).

Changing Default Formatting

If you find that Excel is displaying numbers as percentages by default, you can change this in your regional settings. Here's how: On your computer's desktop, right-click on the clock, select 'Adjust date/time', click on 'Change date and time formats', then under 'locale', choose the desired locale. This will change the default formatting for numbers in Excel.

AD

Remember, changing regional settings will affect all applications, not just Excel. Be sure you want this change across all software before proceeding.

And there you have it! With these simple steps, you should now be able to remove percentage symbols in Excel with ease. The next time you encounter this issue, you'll know exactly how to handle it. Happy Exceling!

AD