How to Remove Leading Apostrophes in Excel (2024)

If you have worked on Excel files extracted from a database or a system, you probably have encountered cells with leading apostrophes. If you’re like me, you too might have wondered why in the world they’re there.

It turns out that the apostrophe (or single quote) (‘) that you see at the beginning of a cell is actually a special character. It tells Excel to treat the remaining contents of the cell as text.

A cell is automatically treated as a text (regardless of its contents — may it be a date or a number) if it begins with an apostrophe.

What’s interesting about this character is that you don’t immediately see it when you look at the cells. You only see it from the formula bar (as shown below).

Leading apostrophe in a cell can only be seen from the formula bar

Adding this special character is useful if you intend to:

  • Retain the leading zeroes in numbers (e.g., 000421).
  • Add the month and year without having Excel automatically convert it as a date.

On the other hand, if you intend to use these cells on formulas or if you want to change the formatting of these cells, you will need to first remove the leading apostrophes. This step is crucial. Missing this will cause your formulas or cell formatting to not work as expected.

Below are varying methods that you could choose from to remove these leading apostrophes. Each of them has its pros and cons – so choose whatever best fits your needs.

1. Using“Text to Columns” option

This method is perfect if there’s only one column you need to convert or remove the leading apostrophes from. If you need to convert more than one column, please proceed to the next options.

Example of a column with leading apostrophes

1. Highlight the entire column you need to convert (or remove the leading apostrophes from).

Highlight the entire column to convert (or remove the leading apostrophes from)

2. Click on the Data menu. Go to the Data Tools section and click on Text to Columns.

Steps to access Text to Columns option

3. The Convert Text to Columns Wizard will appear.

From the Convert Text to Columns Wizard, select Delimited and click Finish.

From the options, select Delimited and click Finish.

4. That’s it! All the leading apostrophes inside the cells should be removed, and your column should now be in its correct format.

Sample output after running the Text to Columns wizard

In my case, the column has been converted into a date format.

2. By multiplying the cells by 1

If you’re looking at removing the leading apostrophes from cells containing numbers, this method is for you.

IMPORTANT: This method does not work on cells containing dates and texts.

Example of cells containing numbers and leading apostrophes

1. Type ‘1’ to any cell in your worksheet.

Type '1' in any cell on your worksheet

2. Select that cell and press CTRL + C to copy.

Copy cell containing '1'

3. Select all the cells with leading apostrophes.

Select all cells with leading apostrophes

4. Press CTRL + ALT + V to open the Paste Special menu.

From the Paste Special menu, select Multiply and click OK.

From the Operation options, select Multiply and click OK.

5. That’s it! You should see the selected cells converted into numbers.

Sample output after multiplying cells by 1

Related Tutorial: How to Remove Commas in Excel

3. By copying and pasting the cells as values

This method is probably my favorite as it works on any cell content – a number, a date, or a text. It also works on multiple columns.

The only requirement for this method is that you must copy the cells to new columns. I would recommend pasting them all together onto a new sheet so that the formatting of the cells is in the default format, which is General.

1. Highlight all the cells with leading apostrophes. You could include the headers if you want to.

Highlight all cells with leading apostrophes (including the headers)

2. Press CTRL + C to copy.

3. Go to a new sheet.

Go to a new sheet

4. Select the topmost cell of the column where you’d like to add the cells with no apostrophes.

Select the topmost cell of the column where you’d like to add the cells with no apostrophes.

5. Press CTRL + ALT + V to open the Paste Special menu.

From the Paste Special Menu, select Values and click OK.

From the Paste options, select Values and click OK.

6. That’s it! All the leading apostrophes should now be removed from the cells.

Sample output after pasting the cells as values

You can now add the appropriate cell formatting for each column.

You can now also use these cells in your formulas.

Related Tutorial: How to Remove Leading Zeroes in Excel

4. Using the VALUE() and DATEVALUE() functions

If you prefer to preserve the cells with leading apostrophes, and instead, add formulas to convert them, you can make use of the VALUE() and DATEVALUE() functions in your formula.

IMPORTANT: This method only works on numbers and dates. It does not work on texts.

These two functions are practically the same. The only difference is that the DATEVALUE() function only works on cells that contain texts that look like dates.

The VALUE() function can work on both numbers and dates.

These two functions basically convert a text string into its numerical version. 

In my example below, notice that cells A to D are all in text format because they have a leading apostrophe in the cells.

Example of cells with leading apostrophes

My goal is to get the total amount by multiplying the No. of Units with the Price without changing the format of these two columns.

To do this, I’ll use this formula: =VALUE(C2) * VALUE(D2)

Sample formula using the VALUE() function to convert cells in text format

As you can see, the correct value still appears even if the cells referred to are in text format.

On the other hand, if you want to convert a date in text format into an actual date, you can use either the VALUE() or DATEVALUE() function.

In my example below, I used the DATEVALUE() function. Notice that the result are all numbers.

Sample formula using the DATEVALUE() function to convert cell in text format

These numbers represent a date in a Microsoft Excel date format, between 1/1/1900 or 1/1/1904 (depending on the workbook’s date system) and 12/31/9999.

The next step is to convert this column into date format so that they will all look like actual dates.

Select the entire column and press CTRL + 1 to open the Format Cells menu.

From the Format Cells menu, select the appropriate date format and click OK.

From the list of Categories, select Date.

Then select the type or date format that you prefer and click OK.

Sample output after changing the cell format to Date

That’s it! The dates in text format are now converted into actual dates.

5. Automatically remove leading apostrophes using VBA

If you regularly receive files with leading apostrophes and would prefer having a macro to do the conversion or the apostrophe removal for you, then this final method is for you.

1. Activate the worksheet that contains cells with leading apostrophes.

2. Press ALT + F11. This should open the VBA Editor.

3. Click on the Insert menu and select Module.

From the VBA Editor, click Insert and select Module.

4. A new module will be inserted.

5. Copy the following code and paste it into this module.

Sub RemoveApostrophesInActiveSheet()
    Dim cl As Range
    
    With ThisWorkbook.ActiveSheet
        For Each cl In .UsedRange
            If cl.Value <> "" Then
                cl.Value = Replace(cl.Value, "'", "")
            End If
        Next
    End With
End Sub
Paste code into the new module

6. Once pasted, press F5 to run the code.

7. Close the VBA editor. You should see all the leading apostrophes removed from the active sheet.

If you want to do it on another sheet, activate that sheet first.

Open the VBA Editor again by pressing ALT + F11.

Press F5 to run the code. That’s it!

Related Tutorial: How to Remove Parentheses in Excel

Conclusion

The apostrophe at the beginning of a cell serves the special purpose of converting the cell content into text. If, however, you prefer converting the cell into its actual format, you can easily remove the apostrophe using any of the options provided above.

How to Remove Dollar Sign in Excel (4 Easy Ways) – 2024

Dollar signs ($) in Excel are great for quickly identifying cells that contain monetary values. There are times, however, that it would be better to remove them.

In this article, I’ll show you four different ways to do this. Each approach has its pros and cons, so choose whichever fits your needs.

The first two methods will make the changes directly to the cells containing the dollar sign, while the last two will only extract the number from these cells and paste them on a different column.

1. Changing the Cell’s Number Format

1. Select all the cells that contain the dollar sign.

Select all cells containing the dollar sign

2. Go to the Home tab and click on the small arrow next to the Number section.

Steps to open the "Number Format" menu.

If you prefer keyboard shortcuts, just press CTRL + 1

This will open the Number Format menu.

3. Once the “Number Format” menu appears, choose either Number, Currency, or Accounting as the number format.

Choose either Number, Currency or Accounting as the number format.

These 3 formats are practically the same, except that:

  • The “Number” format doesn’t have any currency in it.
  • The “Currency” and “Accounting” formats give the option to add a currency symbol.

If you select Number, you are given the option to configure the number of decimal places to show and to specify whether you’d like to use the comma as a separator for thousands or not. Choose whatever is necessary and click OK.

Options available after selecting "Number" as the number format

If you select either Currency or Accounting, select “None” as the Symbol and click OK.

Select "None" as the Symbol whenever you select "Currency" or "Accounting" as the number format.

You might be wondering — what’s the difference between the “Currency” and “Accounting” formats?

The answer? Text alignment.

If you select the Accounting format, the number will always be on the right side of the cell, no matter what. Even if you change the text alignment to left or center, the numbers will stay on the right side of the cell.

On the other hand, if you choose the Currency format, you can easily change the text alignment to either left, right, or center.

And that’s it! The dollar signs should now disappear from the selected cells.

Sample output after changing the cells' number format

2. Using Find and Replace

If changing the number format does not work, there’s a high chance that the cells you are working on are in text format.

To test this out, add this formula to an empty cell: =ISTEXT(D3)

Replace D3 with one of your ranges containing the dollar sign.

If the formula returns TRUE, then your cell is indeed being treated by Excel as a text.

Add the ISTEXT() formula to check if cell is formatted as text

If this is the case, the next best option would be to use Excel’s Find and Replace.

1. Highlight all the cells containing the dollar sign.

Highlight all cells containing the dollar sign

If you intend to remove the dollar sign from all cells inside the current worksheet, you may skip this step.

2. Go to the Home tab. From the Editing section, click the “Find & Select” button, and click Replace.

Steps to open the "Find and Replace" menu

If you prefer a keyboard shortcut, you can press CTRL + H.

This should open the “Find and Replace” menu.

In the "Find what" field, type the dollar sign ($). Leave the "Replace with" field blank.

In the Find what field, type the dollar sign ($).

Leave the Replace with field blank.

NOTE: If there’s a space between the dollar sign and the number in your cells, remember to also add the space after the dollar signs in the Find what field.

Example: “$ “

3. If you intend to only replace the dollar sign in selected cells, ensure that you click on the Options << button.

The options should be hidden from the “Find and Replace” menu (as shown below).

The "Find and Replace" menu if the Options are hidden.

On the other hand, if you plan to replace the dollar sign in all cells within the worksheet, click on the Options >> button to bring back the Find and Replace options (as shown below)

The "Find and Replace" menu if the Options are visible.

You should see the “Sheet” value in the Within field.

4. Once done, click on the Replace All button.

That’s it! The dollar signs should now be removed from your cells.

Sample output after doing a Find and Replace.

3. Using the Flash Fill

This next option is great if you prefer to have a separate column of cells containing the same values, but without the dollar sign.

1. Go to a blank column next to your data set.

Go to a blank column next to your data set.

2. Type the first number (but without the dollar sign).

Type the first number (but without the dollar sign).

3. Next, select the cell on the second data row.

Select the cell on the second data row.

4. Press CTRL + E to trigger the Flash Fill.

If done right, you should see Excel automatically filling up the remaining cells in that column.

Sample output after running the Flash Fill.

If you have other columns to remove the dollar sign from, repeat the same steps on a different column.

Sample output after running the Flash Fill on the next column.

IMPORTANT: You should perform the Flash Fill on a blank column nearest to the dataset. Otherwise, Excel might not recognize the number pattern you are trying to generate.

4. Using Excel Formulas

This last method still allows you to keep cells with dollar signs intact. It gives you the option to extract the numbers without the dollar sign.

1. On a blank cell, type either of the following formulas:

FORMULANOTES
=VALUE(D3)   
=SUBSTITUTE(D3,”$”,””)   
=SUBSTITUTE(D3,CHAR(36), “”)  CHAR(36) represents the dollar sign.  
=RIGHT(D3,LEN(D3)-1)  Use this if there’s no space between the dollar sign and the number (e.g., $100).  
=RIGHT(D3,LEN(D3)-2)  Use this if there’s a single space between the dollar sign and the number (e.g., $ 100).  

Remember to change D3 with your actual cell.

Add the VALUE() formula to the first cell.

2. Select the cell where you have added the formula.

Hover your mouse over the small green square on the right side of that cell. Wait until your mouse cursor turns into a black cross (as shown below).

Hover your mouse over the small green square on the right side of the cell. Wait until your mouse cursor turns into a black cross.

3. Drag your mouse until you reach the last cell in that column.

Drag the mouse until you reach the last cell in the column.

This triggers Excel to copy the formula down to the remaining cells.

And that’s it! You should have a column containing the same values but without the dollar sign.

Sample output after adding the formula to all cells.

Conclusion

The dollar sign symbol inside cells is a great way to specify that the cells contain monetary values, and that they are in dollar currency. There are, however, instances when we need to remove them. I hope the steps described above will help you format the data the way you want them to be.

How to Remove Panes in Excel [2024]

Splitting a worksheet into panes or sections is great for keeping certain contents within the sheet (like the headers) to be always on display even when we scroll to other areas in the sheet.

Excel allows us to split the worksheet into two or four panes. Once a worksheet is split, you can scroll through each section independently.

There are times, however, when we need to remove these panes so we could view the worksheet again as a whole and do other things within the sheet.

There are four simple ways to do these.

1. Double Clicking on the Dividing Bar

If you have four panes added to your worksheet and want to remove just one of the panes, all you need to do is double-click on the dividing bar that you want to remove.

Double-click on the dividing bar to remove panes

That’s it! You have a single pane removed.

On the other hand, if you want to remove all the panes at once, double-click on the intersection between the dividing bars.

Double-click on the intersection between the dividing bars to remove all panes

2. Clicking on the “Split” button

Another quick method to remove all panes at once is by going to the View menu and clicking on the Split button.

Click on the Split button from the View menu

3. Unfreezing the Panes

If you have added panes in your worksheet by freezing the rows and/or columns, you can easily remove the panes by going to the View menu.

From the Windows section, click on Freeze Panes and select Unfreeze Panes.

From the View menu, click Freeze Panes and select Unfreeze Panes

4. The Keyboard Shortcut

For keyboard warriors out there, the fastest way to remove the panes is by pressing ALT + W + S.

Related Tutorial: How to Remove Dashes in Excel

Conclusion

Splitting a worksheet into panes is a huge help whenever we need to compare the values in different areas of the worksheet. There are, however, instances when we need to view the worksheet again as a whole. As you have seen in the examples above, with just a few clicks or keyboard entries, you can easily remove these worksheet panes.

How to Center a Worksheet Horizontally in Excel?

When we print a document in Word, we don’t usually pay much attention to the page setup. The printed output would nearly always match how we see it on the computer screen.

With Excel, however, it’s different. We need to be able to configure the right page settings to be able to print the worksheet in the format that we want.

In this tutorial, I will show you quick and easy steps to center a worksheet horizontally in Excel.

Center a Worksheet Horizontally

Intuitively, we usually start entering the data at the leftmost part of the worksheet (e.g., cell A1). When we print it using the default page settings, the data stays on the leftmost side of the page (as shown below).

Sample print preview when you use default page settings in Excel

To move the contents of the worksheet to the horizontal center of the page, we need to make some changes to the Page Setup.

But before doing that, we have to highlight the area you intend to print.

Select Print Area

1. Activate the worksheet that you want to print.

Activate worksheet to be printed

2. If you don’t intend to print all the contents inside the worksheet, highlight only the area you plan to include. Otherwise, you can skip this step.

Select print area

Open the Page Setup

There are two ways to open the Page Setup:

  • from the File menu
  • from the Page Layout menu

The second option may be the quickest method, but the first option allows you to view the result instantly. 

Configuring the Page Setup from the File menu

Go to File >> Print >> Page Setup.

Steps to open Page Setup from the File menu

Notice that the Print Preview is on the right side of the screen.

That allows you to view how your selected print area will appear on a page with your current Page Setup. 

Once the Page Setup form appears, go to the Margins tab.

From the “Center on page” section, tick the Horizontally checkbox and click OK.

Steps to configure page setup to have worksheet contents centered horizontally

And that’s it! The contents of your worksheet will move to the center of the page horizontally (as you will see on the Print Preview).

Sample Print Preview after centering worksheet contents horizontally

You can now proceed with printing the page(s) or go back and make additional changes to the sheet.

Configuring the Page Setup from the Page Layout menu

Activate the worksheet you intend to print, go to the Page Layout menu and click on the small arrow on the rightmost area of the Page Setup section.

Steps to open Page Setup from the Page Layout menu

The Page Setup form will appear. Go to the Margins tab.

From the “Center on page” section, tick the Horizontally checkbox.

Steps to configure Page Setup to center worksheet contents horizontally and to preview output after

If you want a preview of what it looks like on the page, click on the Print Preview button.

Otherwise, click on the OK button.

That’s it! You have moved the selected print area to the horizontal center of the page.

How to Center a Worksheet Horizontally and Vertically in Excel?

If you want to move the contents of the worksheet to the center of the whole page (horizontally and vertically), do the same steps as above and add this additional step:

From the “Center of Page” section, tick on the Vertically checkbox too.

Steps to configure Page Setup to center worksheet contents horizontally and vertically

That’s it! The contents of the worksheet are now in the horizontal and vertical center of the page.

Sample Print Preview when worksheet contents are centered horizontally and vertically

Conclusion

When I didn’t know about this Page Setup feature, I used to painstakingly move the cells inside the worksheet and adjust the column sizes for me to move the contents to the page’s center. Had I known this option was available, I wouldn’t have wasted so much time! I created this tutorial so you won’t have to make the same mistake. I hope this helps you!

Also Read: How to Remove Password from Excel?

How to Convert Decimal to Percentage in Excel?

Decimals, fractions, and percentages are all ways to describe parts of a whole.

Percentages, however, are preferred by many over other formats. I guess it’s because whole numbers (with a percent sign at the end) are easier to interpret over decimals and fractions.

In this article, I’ll show you ways to convert decimals to percentage format in Excel.

Even though the steps are pretty straightforward, there’s one crucial thing we must first understand. We must know how Excel converts decimals to percentages. Skipping this step may result in us not getting the result we want.

By default, Excel converts a decimal by multiplying it by 100 and adding the percent sign at the end of it. If you are only looking for a way to add the percent sign at the end of your pre-calculated decimals (and not multiply them by 100), don’t worry. There’s a method for that too.

I have split this article into two methods:

  • The first one converts the decimals to percent by multiplying them by 100 before adding the percent sign.
  • The second one only adds the percent sign at the end of the decimals.

How to Convert a Decimal to Percentage in Excel?

Note that this method will multiply the decimals in your cells by 100. Let’s say you have 0.53 as your decimal. With this method, the resulting percentage will be 53%.

1. Select the cells containing the decimals you want to convert into percentage format.

Select the cells containing the decimals you want to convert into percentage format.

If you like, you can also select the entire column where these decimals reside by selecting the appropriate column letter on top (see the example above).

This way, the numbers you will enter later will be automatically converted into percentage format.

2. Next, proceed with one of the methods described below. Each of them has its advantage over the others. So, I suggest you read them all to find the most appropriate method for your situation.

Convert Decimal to Percentage in Excel Using the Percent (%) button from the Home tab

The quickest way to convert decimals to percentages is to go to the Home tab and click on the % (percent) button in the Number section.

Click the percent (%) button from the Home tab.

If you want the keyboard shortcut, you can press CTRL + SHIFT + %.

That’s it! Your numbers are now converted into percentages.

By default, the percentages will not show any of the decimals.

If you want to have some of the decimals visible, simply click on the “Increase Decimal” button (the second rightmost button in the Number section). Click on it as many times as you want. A single click corresponds to one decimal added. 

The "Increase Decimal" button from the Home tab.

On the other hand, if you want to decrease the number of decimals shown, click on the “Decrease Decimal” button. Like the “Increase Decimal” button, a single click corresponds to one decimal removed.

The "Decrease Decimal" button from the Home tab.

Convert Decimal to Percentage in Excel using the Format Cells menu

This method is the “traditional way” of converting a cell’s format to a percentage.

From the Home tab, click on the small arrow button on the right side of the Number section.

Click the small arrow button on the right side of the Number section in the Home tab.

If you want the keyboard shortcut, simply press CTRL + 1.

The Format Cells menu will appear.

From the list of Categories, select "Percentage". Type the number of decimal places you want to appear and click OK.

From the list of Categories, select “Percentage”. Type the number of decimal places you want to appear and click OK. That’s it!

What’s great about this method is that it allows you to enter the number of decimals you intend to include. There’s no need to click the “Increase/Decrease Decimal” buttons multiple times to adjust them. 

Sample output after converting decimal to percentage using the Format Cells menu.

Convert Decimal to Percentage in Excel using a Custom Number Formatting

This method is perfect if you want the negative percentages to stand out from your dataset. Here, you get to decide how to format them. You could enclose them in parentheses and have them in red font.

1. Open the Format Cells menu by pressing CTRL + 1.

Select "Custom" from the list of Categories in the Format Cells menu.

2. From the list of Categories, select “Custom”.

3. In the Type textbox, we are going to enter the Custom Number Formatting.

You can choose from the following custom number formats or use them to create your own.

0%;(0%)No decimals are displayed; Negative percentages are enclosed in parentheses.
0.00%;(0.00%)Two decimals are displayed; Negative percentages are enclosed in parentheses.
0%;[Red](0%)No decimals are displayed; Negative percentages are enclosed in parentheses and are in red font. 
0.00%;[Red](0.00%)Two decimals are displayed; Negative percentages are enclosed in parentheses and are in red font. 

The first two parameters in a custom number format are: [Positive Number]; [Negative Number].

Zero (0) serves as the placeholder for a digit. So, if you want to add more decimal places, simply add more zeroes after the decimal point.

[Red] represents the red font color applied.

Enter the custom number formatting in the Type textbox and click OK.

4. Once you’ve entered your custom number format in the Type textbox, click OK.

That’s it! The cells that you have selected should now be formatted as percentages, with the negative percentages enclosed in parentheses and/or highlighted in red font.

Sample output after converting decimal to percent using a custom number formatting.

How to Add the Percent Sign (%) at the End of a Decimal?

Note that this method will retain whatever number is in the cells and will only add the percent sign at the end of each. So, if you have 0.53 as the decimal, the resulting percentage will be 0.53%.

1. Select the cells where you would like to add the percent sign.

Select the cells where you would like to add the percent sign.

2. From the Home tab, click the down arrow of the dropdown list under the Number section and select More Number Formats.

From the Home tab, click the down arrow of the dropdown list under the Number section and select "More Number Formats".

(If you like, you can also just press CTRL + 1 for the keyboard shortcut.)

3. The Format Cells menu will appear. From the list of Categories, select “Custom”.

Select "Custom" from the list of Categories in the Format Cells menu.

4. In the Type textbox, enter either of the following custom number formats.

0.00\%;(0.00\%)Two decimals are displayed; Negative percentages are enclosed in parentheses.
0.00\%;[Red](0.00\%)Two decimals are displayed; Negative percentages are enclosed in parentheses and are in red font. 

In case you’re not yet familiar with custom number formats (or if you haven’t read the other methods described above):

  • The first two parameters in a custom number format are: [Positive Number]; [Negative Number].
  • Zero (0) serves as the placeholder for a digit. So, if you want to add more decimal places, simply add more zeroes after the decimal point.
  • [Red] represents the red font color applied.

The backslash (\) in the custom number formats is a special character that tells Excel to simply add the percent sign and not convert the numbers by multiplying them by 100.

After entering your custom number format in the Type textbox, click OK.

Enter the custom number formatting in the Type textbox.

5. And that’s it! The percent sign should now be added at the end of your decimals.

Sample output after adding the percent sign at the end of the decimal using custom number formatting.

Conclusion

As you can see, there are plenty of ways to convert your decimals into percentage format. But you must first identify whether you only need to add the percent sign (%) at the end of your decimals or you need to have them multiplied by 100 to properly convert them.

You can go to the next tutorial: Round up Decimals to Nearest Whole Number