# Can't turn off the Formula option in Excel 2007 Help



## rosmari (Sep 7, 1999)

Office Button, Excel Options, Display Options, the second item "show
formulas in cells instead of calculated results" was checked and I
unchecked it, but when I work in the spreadsheet, that option is still turned on.
The little Menu behind has 3 options: Sheet1, Sheet2, and Sheet 3. The latter two stay unchecked, but Sheet1 re-checks itself as I work on the spreadsheet.

Can anybody tell me what I am doing wrong please.


----------



## The Villan (Feb 20, 2006)

Although, I do not have Excel 2007, I do beleive that the steps to get to the Show formulas option is as follows.

Click the *Office button*, and then click *Excel Options*.
In the left pane, click *Advanced*.
Click the *Display options for this worksheet *list (drop down)arrow, and then select the option (sheet1, 2,3 etc)where you want to apply display options.
Show formulas in cells instead of their calculated results. (Select to show formulas instead of results.)
Click OK.

The Keyboard shortcut option to toggle between displaying formulas or results is as follows.
A much faster way to get the same result is to press *Ctrl+`*. (That's hold down the Ctrl key while you press the accent grave, which is the backwards apostrophe just to the left of the 1 key and above the Tab key.) The shortcut is a toggle, which means that you can press it repeatedly to switch between the display of formulas and results.

Rosmari
Can you try the *Ctrl+`* option mentioned above.This may solve the issue.

Can you feedback if that helps or not.


----------



## rosmari (Sep 7, 1999)

The toggle works, but I still can't get rid of the code in that last column; the code is supposed to be replaced by the text contents of the previous two columns.
With the checkmark off, the code spans several inches beyond its own column; then, when I toggle (checkmark on), it (i.e. the start of it) is contained in its own narrow column.
Now what?


----------



## The Villan (Feb 20, 2006)

when the checkmark is off,you will see the result in the cell and the formula in the formula bar, which is how it should be.

Once the formula has been copied down the column for all the cells, you then carry out the next step, where you select all the cells that have the formula in and copy them into the clipboard.
You then have to do a paste special, values to replace the formulas with the result of the formula.

Once you have done that, you will have the text in the cell and the same text in the formula bar.

If you succeed with that, you can then delete the 2 columns to the left of that, as you no longer need them.

Hope that helps. Have a look at the help document I sent you, if you need to check out what to do.


----------



## The Villan (Feb 20, 2006)

Incidentally, you should never have the checkmark on. You only do that for auditing formulas or when you want to print out the formulas in each cell for audit purposes.


----------



## rosmari (Sep 7, 1999)

I did everything exactly according to instructions - still the code stays. Do you think there is that (hidden?) apostrophe you mentioned earlier?


----------



## rosmari (Sep 7, 1999)

I just had the idea of looking at the code on the function bar - and there is indeed an initial apostrophe! I have to go to the dentist right now, but will revert later.


----------



## The Villan (Feb 20, 2006)

OK, it sounds like the apostrophe. If you click on the cell with the formula in and then press the F2 function key (Edit), yuou can tne edit the apostrophe out, just as you would do in a word processor.

Enjoy yourself at the dentist


----------

