Sometimes you find yourself wanting to start a cell with an "=", "-", or a "+" sign, but don't want Excel to create a formula. There are a few ways we can do this.
Contents:
Beginning a Cell with the = + or - Sign
When working with Excel, entering an "=" (equals), "-" (minus), or "+" (plus) sign at the beginning of a cell is usually the prelude to a formula, and Excel will automatically convert that cell into a formula.
Sometimes this will throw errors even though you know what you're entering doesn't need to be a formula. To trick excel and start a cell with these symbols, there are three different ways we can go about this.
1. Preceding the Sign with an Apostrophe
The simplest way to input text in an Excel cell that starts a =, -, or +, is by starting the cell with an apostrophe (').
This tells Excel to treat the content of the cell as text rather than a formula or numerical value.
To do this, click on the cell where you'd like to enter the text, then type ' followed by your text. So "+ 1 point" would become "'+ 1 point".
The apostrophe will disappear from view when you click out of the cell.
2. Using the TEXT Formula
For those who prefer a more formulaic approach or need to automate text entry through functions, the TEXTÂ function can handle this.
To use a formula, click on the cell where you need to insert the text, and use the following formula, where "your_text"Â is your string starting with =, -, or +. For example =TEXT("+example","").
=TEXT("+your_text","")
The text string can be a hardcoded value like the formula above, or cell references can be used.
3. Formatting Cells as Text Before Entry
If you plan to input several values starting with a sign, formatting the cells in advance can be easier
First, select all of the cells on your workbook that you wish to format as text.
Then, head to the home ribbon, and on the right side under "Number", click the drop down that says "General".
From there, a menu with different cell formatting options will pop up.
the bottom, "Text" option.
All selected cells will now act as strictly text cells. No formulas or calculations will be performed in them, allowing you to enter in any symbol to start a cell that you wish.
Excel will treat all cells as text.