How to Add Single Quotes in Excel

To wrap a cell’s value in single quotes, use a formula like ="'"&A1&"'", then copy it down a column with the fill handle. To show quotes without changing the underlying value, apply the custom number format \'@\'. A leading apostrophe ('text) is different — Excel reads it as a text-format marker and does not display it, so it cannot be used to literally show a quote.

Microsoft Excel is a powerful tool used to analyze, organize, and present data. Single quotes are an essential component in certain Excel functions and formulas. They are used to denote text values and ensure that Excel does not misinterpret them as numeric values. Therefore, learning to add single quotes in Excel is crucial for anyone using Excel for data analysis or management purposes. In this post we will explore several reliable ways to add single quotes in Excel, including a few shortcuts and tips to make the process more efficient.

Adding single quotes to cells in Microsoft Excel is a simple process that can be done in numerous ways. We’ll explore some of the most commonly used methods so you can choose which one works best for you.

The Critical Distinction: Leading Apostrophe vs. a Literal Quote

Before choosing a method, understand the single most confusing point. A leading apostrophe and a displayed single quote are not the same thing.

When you type an apostrophe (') at the start of a cell — for example 'coffee or '00123 — Excel treats it as a text-format indicator. It tells Excel “store everything after this as text,” and the apostrophe itself is not displayed in the cell and is not part of the value. This is the trick people use to stop Excel changing numbers into dates or dropping leading zeros, and it is the same idea behind adding leading zeros.

If your actual goal is to show a single quote on screen — so the cell literally reads 'coffee' — a leading apostrophe will not do it. You must either build the quotes into the cell’s text with a formula or display them with a custom number format. The methods below cover both intentions, so pick based on what you really need: format-as-text, or visible quote characters.

Method 1: Wrap a Value in Single Quotes With a Formula

The most reliable way to surround a value with single quotes is a formula. Because a single quote is a normal character (not a structural symbol like the double quote), you can concatenate it directly.

In a blank cell, enter any of these:

="'"&A1&"'"

=CONCAT("'",A1,"'")

=CONCATENATE("'",A1,"'")

Each returns the value of A1 wrapped in single quotes — for example, coffee becomes 'coffee'. The "'" part is a text string containing one single-quote character, and & joins the pieces together. See our guides to CONCATENATE and combining cells for more on joining text.

If you want to quote and join several cells at once, TEXTJOIN is ideal because you can quote each piece:

=TEXTJOIN(", ",TRUE,"'"&A1&"'","'"&B1&"'","'"&C1&"'")

This produces output like 'apple', 'banana', 'cherry' — handy for building SQL-style lists. For a broader tour of joining and formatting text, see our Excel text functions overview.

Method 2: Apply the Formula to a Whole Column With the Fill Handle

Once your formula works in one cell, copying it down a column takes seconds:

1. Click the cell containing your formula (e.g., ="'"&A1&"'").
2. Hover over the small square in the cell’s bottom-right corner — the fill handle — until the cursor becomes a thin plus sign.
3. Double-click the fill handle to fill down automatically to the bottom of the adjacent data, or click and drag it down over the range you want.

Excel adjusts the reference for each row (A1, A2, A3, and so on), wrapping every value in single quotes. To keep only the quoted text and discard the formulas, select the result, copy it, then use Paste Special → Values over the same range. This is the same workflow used to add a prefix or add text to cells.

Method 3: Add Single Quotes With Flash Fill

Flash Fill (Excel 2013 and later) detects a pattern from your examples and fills the rest — no formula needed:

1. In the column next to your data, type the desired result for the first row by hand, e.g., 'coffee'.
2. Begin typing the second result. Excel usually shows a grey preview of the whole column.
3. Press Enter to accept, or press Ctrl + E to trigger Flash Fill manually.

Flash Fill produces static text values immediately, so there is nothing to paste-special afterward. Double-check the output, since Flash Fill occasionally guesses the pattern incorrectly with mixed data.

Method 4: Add Single Quotes With Find & Replace

Find & Replace can wrap existing text quickly when the surrounding content is predictable:

1. Select the cells you want to change.
2. Press Ctrl + H to open the Find and Replace dialog.
3. In Find what, enter the exact text you want to wrap — for example, coffee.
4. In Replace with, enter the same text surrounded by quotes — 'coffee'.
5. Click Replace All.

The catch: Find & Replace needs a known search term, so it suits a single repeated word, not a column of distinct values. For varied data, use the formula or Flash Fill methods instead. (To wrap every value regardless of content, the formula in Method 1 is the dependable choice.)

Method 5: Display Quotes Without Changing the Value (Custom Number Format)

Sometimes you want the cell to look like 'coffee' on screen while the stored value stays plain coffee — so formulas, lookups, and sorting still work on the real text. A custom number format does exactly this:

1. Select the cells.
2. Press Ctrl + 1 to open Format Cells.
3. On the Number tab choose Custom.
4. In the Type box enter:

\'@\'

5. Click OK.

Here @ is the placeholder for the cell’s text, and each \' displays a literal single quote. The cell now shows 'coffee', but click into it and the value is still coffee. This is the cleanest option when the quotes are purely cosmetic — for instance, in a report you’ll print or screenshot but still need to calculate against. Remember this is display-only: copying the cell elsewhere as a value will not carry the quotes.

Why Add Single Quotes in Excel?

You may be wondering why it’s necessary to add single quotes in Excel. As a leading apostrophe, the single quote tells Excel that a value is text and not a number. Without it, Excel could interpret an entry such as 1+2 or 00123 as a formula or trim it as a number, resulting in unexpected results. As displayed quotes around a value, single quotes are often used for readability or to build lists for databases and code.

Take note that a leading apostrophe is not required for ordinary text that does not start with a number, an equals sign, or a minus sign. For a list of names or addresses, it is unnecessary. It becomes valuable when an entry would otherwise be misread — see how to stop Excel changing numbers for the most common cases.

Using Single Quotes with Formulas in Excel

Inside a formula, a single-quote character is just text, so you reference it as "'" (a double-quoted string containing one apostrophe). To combine the text in cell A1 with other text, you would write:

="'"&A1&"'"

The & (ampersand) connects the pieces. Note that wrapping a cell reference itself in apostrophes — like 'A1' — does not treat A1 as text; apostrophes only carry that meaning around sheet names in references (e.g., ='My Sheet'!A1). To force a value to be treated as text inside a formula, use the TEXT function or concatenation instead.

Frequently Asked Questions

What’s the difference between a leading apostrophe and a displayed single quote in Excel?

A leading apostrophe ('coffee) is a text-format marker — Excel stores the entry as text but does not display the apostrophe. To make the quote characters actually appear, use a formula like ="'"&A1&"'" or the custom number format \'@\'.

How do I wrap a cell value in single quotes with a formula?

Use ="'"&A1&"'", or equivalently =CONCAT("'",A1,"'"). The string "'" is a single-quote character, and & joins it to either side of the cell’s value.

How do I add single quotes to an entire column?

Enter the formula ="'"&A1&"'" in the first row, then double-click or drag the fill handle (the square at the cell’s bottom-right corner) to copy it down. Paste the results back as values if you want plain quoted text instead of formulas.

Can I show single quotes without changing the cell’s actual value?

Yes. Select the cells, press Ctrl + 1, choose Custom, and enter the format code \'@\'. The cell displays 'value' while the stored value stays unquoted, so formulas and lookups still work.

Can Flash Fill add single quotes around text?

Yes. Type the quoted version ('coffee') next to the first cell, start typing the second, and press Ctrl + E. Flash Fill detects the pattern and fills the rest as static text.

How do I remove single quotes from cells in Excel?

If they were added by a formula or Flash Fill, use Find & Replace (Ctrl + H) to replace ' with nothing. If a quote is only a leading apostrophe marking text, change the cell’s format to General or re-enter the value to clear it.

Troubleshooting

• My apostrophe disappeared. That’s expected for a leading apostrophe — Excel hides it because it’s a text-format marker, not data. Use a formula or the \'@\' format to display a visible quote.
• Find & Replace did nothing. It needs an exact match in Find what. Curly “smart” quotes pasted from Word won’t match a straight '; retype the apostrophe directly in Excel.
• Flash Fill won’t trigger. Make sure there’s data in the adjacent column and that Flash Fill is enabled (File → Options → Advanced). Press Ctrl + E to force it.
• The formula shows quotes but I need plain values. Copy the formula cells and use Paste Special → Values to convert them to static text.
• The custom format isn’t quoting. Confirm you entered \'@\' exactly — the backslash before each quote is what makes Excel treat it as a literal character.