When is ' used in Excel?

Answer 1

You can use it to change the formatting of a cell to text, even when it contains a formula or number. If you want to type a phone number into a cell that begins with zero, then the spreadsheet will treat it as a number and won't show the zero. By putting the ' before it, it treats it as text and so the zero remains. You never do calculations with phone numbers, so it doesn't matter that it is being treated as text, although Excel can actually add text that consists purely as digits.

Another time you might use it is to show a particular formula in a cell in the way it is typed in. All formulas begin with the equals sign, so an equals sign will automatically trigger a calculation and the equals sign will not show in the cell if there is something following it. By putting the ' before the equals sign, it is now treated like text and you will see the equals sign and the rest of the formula. This can be handy if you want to show the result of the formula in one cell and in another cell show the actual formula. It is possible to get Excel to show all formulas, but then you won't see the results. So if you want to see the formula and the result, type the formula into one cell and type the formula with the ' before it in another and then you will see the result in one cell and the formula in another.