I was watching a video on Excel formulas and the author was showing a formula in one cell, and the result of the formula in another cell. Both cells had the same formula bar text. I could not figure out how she did this. Can someone please explain?

One cell was formatted as text

I tried formatting one cell as text. It still displayed the result and not the formula.

Okay, if I typed a formula in a cell and then changed the format to text afterwards, the result is displayed. However, if I change the format to text before I typed the formula in, the formula is displayed. I would think it should work the same either way, but at least I know a way to do this now. Thanks.

When you change the format of cell/range, the change will be actual only after you edit the cell(s). Until then the old format is applied really.

So when you have e.g. a column with numbers or dates formatted as text, and you format the column to General or Date, after formatting the column you have to edit all entries in column. The fast way is enter 0 or 1 into some free cell elsewhere, copy the cell, and then PasteSpecial>Add or PasteSpecial>Multiply the formatted column.

When you want to format numeric column to text, a foolproof way is after formatting to enter the formula into free column, copy it down, copy the range with formulas and overwrite values in formatted column with PasteSpecial>Values.

With one or couple of cells only, simply double-click on cell, or activate it and press F2, and then press Enter.

Maybe the attached can be of some use.

Attached Files

Formula_Text.xlsm (10.2 KB, 11 views)