When you’re troubleshooting an Excel worksheet, it may help to see the formulas temporarily, instead of the results. With the formulas visible, you can quickly check that the cell references are correct and the formulas are consistent.
Tip: The keyboard shortcut to show or hide the formulas is Ctrl + ‘ (accent grave, may be above the Tab key on the keyboard).
To view the formulas in Excel 2003:
- On the Tools menu, click Options
- On the View tab, under Windows Options, add a check mark to Formulas.
To view the formulas in Excel 2007:
- On the Ribbon, click the Formulas tab.
- In the Formula Auditing Group, click Show Formulas.
___________________
Deb, this is useful. I do something a bit different that meets my needs for blog pictures, presentations, etc.
I often want to show a formula for a single cell in a neighboring cell. For example, I may want to display the formula for A1 in cell B1. You could copy the formula and paste it into B1 using an apostrophe in front of the equals sign, but that won’t update if the formula changes. So I use a simple VBA function:
Public Function GetFormulaText(Cell As Range) As String
GetFormulaText = Cell.Formula
End Function
There’s no error checking at all, but it works as long as you supply only a single cell reference.
A quick way to look for consistent formulas is to show formulas. But change to R1C1 reference style first.
Then you’d see something like:
=SUM(RC[-4]:RC[-1])
=SUM(RC[-4]:RC[-1])
=SUM(RC[-4]:RC[-1])
=SUM(RC[-4]:RC[-1])
=SUM(RC[-4]:RC[-1])
You may be able to see that formula that doesn’t belong with a quick glance.
Tools|Options|General Tab|Check R1C1 reference style
(xl2003 menus)
In Excel 2007 you can also show formulas directly from the ribbon. Formulas Tab > Formula Auditing Group > Show Formulas
Tim and Dave, thanks, those are great tips.I’ve got at toolbar button that toggles R1C1 reference style, and that would make it easier to check a column of formulas. Now I’ve added the GetFormulaText to my collection too.
Thanks Sridhar, I’ve changed the Excel 2007 instructions in the post.
And remember the hotkey to toggle this view:
Control+’
(control + the key immediately above your tab key)
27 march 2009
i am verymuch interested in excel..
this informations is very very useful for my everyday home training.
now my age is nearing 50 years.
iam working as a tech. in signal deportment in indian railways. iam not taken any training in regular computer course. [email protected]
thankingyou
Thanks, It works:)
To view the formulas in Excel 2007:
On the Ribbon, click the Formulas tab.
In the Formula Auditing Group, click Show Formulas.
thank you