In Excel, when you are working with financial data, you will realize that currency values are automatically formatted by Excel to include a Dollar sign. It can be an inconvenience, because you might prefer to use another currency. So, in this article, you will learn how to remove Dollar signs in Excel.
Excel’s Currency Formatting
In Excel, if you want to display numbers as monetary values, the numbers should be formatted as currency. This means you have to apply the Currency format to the cells that you want to format. The number formatting options are available on the Home tab, in the Number group. Excel adds the Dollar sign by default, so to change to another currency you have to select it yourself.
How to Identify Cells with Dollar Signs
In Excel, a Dollar sign denotes a currency format.
How to Remove Dollar Sign in Excel
There are different methods one can use to remove the dollar sign from their Excel worksheet.
#2. Using the Format Cells Dialog Box to Remove Dollar Signs in Excel
Sometimes, the dollar sign is added at the beginning of your excel data. To remove it, you have to use the format cells dialog box, here’s how to go about it:
- Select the cells that have the dollar signs you want to remove.
- Right-click your mouse on the selected cells and select “Format Cells” from the menu tab.
- A Format Cells dialog box will appear, select the “Number” tab.
- Select “Currency Options” under “Category List”.
- A drop-down list next to “Symbol” will appear, then click on that and select “None”
- Click OK.
This method will remove the Dollar signs from your data.
#2. Using a Formula to Remove Dollar Signs in Excel
If the Dollar symbol was not added by formatting, then you need to use a formula to remove the Dollar sign.
If you do not know if your data is formatted as text or not, check the following:
- If the data is aligned to the left, it is most likely text. Numbers are aligned to the right.
- Check if a green triangle appears in the top left corner of the cell.
- Click on the cell to reveal a small, apostrophe sign.
- Type the formula =istext into an empty cell, and pass the reference to your cell and press enter. If the result shows True, then your cell contains text.
If your data is in text format, remove the dollar signs by:
- Navigate to a new column and type = RIGHT(A2,LEN(A2)-1).
- Press the Return key.
- All the contents of the B2 cell will be displayed, with the dollar sign removed. Double-click the fill handle (at the bottom-right corner of the cell) to do this for other cells with a dollar sign.
Advanced Removal of Dollar Signs Techniques in Excel
Apart from the two methods discussed above, there are other two methods one can explore for the same purpose:
#1. Using Excel Functions
You can use the Excel VALUE() Function in Excel to remove Dollar signs from cells in a spreadsheet.
- Start a new column.
- Type in =VALUE().
- Within the brackets, put a reference to the first cell from which you want the dollar sign removed, like A1 or B1.
- Press Return.
The value of the cell you referenced will appear with the formatting removed. Go ahead to copy this formula to the rest of the cells in the column by double-clicking the fill handle.
This method retains both the original column and the new column without the dollar signs.
#2. Using Excel’s Find and Replace Feature
The Find and Replace feature is another easy way to remove dollar signs from your data especially if you are removing from multiple cells.
- First, select all of the cells that you want the dollar sign removed from.
- Go to the Home Tab, you will find the ‘Editing’ group.
- Click on ‘Find and Select’ and click ‘Replace’on the drop-down menu.
- Add $ in the “Find what” field, followed by a space character.
- The “Replace with” field stays empty.
- Then click the “Replace All” button.
- The dollar signs will disappear in all the cells you selected.
How to Prevent Automatic Dollar Sign Insertion in Excel
To prevent automatic Dollar sign insertion in your Excel sheet.
- On your worksheet, go to Format.
- Select Number.
- Go to More Formats.
- Select Custom number format.
- Then highlight the dollar sign “$” and then delete it (also delete the quotes and dollar sign both).
- Then click apply.
FAQs
How Can I Remove the Dollar Sign from Multiple Cells in Excel?
An easy way to remove the dollar sign from multiple cells in Excel is to use the Find and Replace method already discussed in this article.
How Do I Change the Dollar Sign in Excel?
Go to the Home tab, and click the Dialog Box Launcher next to Number. In the Format Cells dialog box, navigate to the Category list, select Currency or Accounting. In the Symbol box, click the currency symbol that you prefer.
How Do You Change from Dollar to Number in Excel?
Select the cells that you want to format and then, in the Number group on the Home tab, click the down arrow in the Number Format box. Choose either Currency or Accounting.
How to Remove Dollar Sign in Excel Shortcut
Use the F4 key as the shortcut to remove dollar sign in Excel.
How Do I Remove Dollar Sign in a Specific Excel Column?
Highlight the specific cell you want to remove the sign from, before proceeding to use any of these methods.
How Do I Remove the $ Sign from an Excel Formula?
The F4 key is the shortcut key that performs this function in Excel by default.
- Go to the cell in question.
- Double click to open the edit mode.
- Press the F4 key on the keyboard once.
- The Dollar sign before the Column name will be removed.
- Press the F4 key again to remove the Dollar sign before the Row number.
You can also remove the Dollar sign manually from Excel Formula:
- Go to the cell and double-click on the cell to open the edit option.
- Go to the Formula bar.
- Remove the Dollar sign by using backspaces on the keyboard.
