will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
See Formulas on an Excel Worksheet
Last week I was testing a client’s workbook, and had filled in all the data entry cells, to make sure everything was working correctly.
Before sending the workbook back to my client, I wanted to clear all the data entry cells. Instead of selecting each cell individually, and clearing it, it would be easier to clear groups of adjacent cells where possible.
However, some cells had formulas, and I didn’t want to accidentally clear any of those. If the formulas are visible, that would prevent the problem.
To see the formulas in Excel 2003:
- On the Tools menu, click Options
- On the View tab, add a check mark to Formulas.
To see the formulas in Excel 2007
Click the Office button, then click Excel Options
Click the Advanced category
In the Display Options for This Worksheet section, add a check mark to Show formulas in cells instead of their calculated results.
Show or Hide Formulas with a Keyboard Shortcut
The keyboard shortcut to show or hide the formulas is Ctrl + ` (accent grave, which may be above the Tab key on the keyboard)
___________________
October 7th, 2009 at 4:28 am
“I wanted to clear all the data entry cells”
How about Edit, Goto, Special, Constants? or just constants that are Numbers?
Or pick the final formula, select precedents all levels, then do the above?
October 7th, 2009 at 3:21 pm
If you set up your input cells with data validation, you can Edit, Goto, Special, Validation, All or Same.
The validation doesn’t even have to be restrictive or visible. You can make it “Allow any value”, then choose an input message, while unchecking the box that displays the input message. Now it’s bothering no one but has tagged the cells you want with a special name you can Goto any time.
You can choose different names, and for each name have one labeled cell on the sheet you focus on before doing Goto Same Validation.
None of this helps you with cleaning the sheet if you haven’t first set it up that way, of course
October 8th, 2009 at 4:02 pm
Thanks Patrick, your suggestions would work very well in many workbooks, but this one has labels that are also constants. The data entry is a mixture of numbers and text, and only a few are used as precedents.
Derek, thanks, selecting data validation cells is an interesting approach, and one that I hadn’t thought of. About 80% of the data entry cells in this workbook have some type of data validation, so it wouldn’t clear everything.
I’ll keep these ideas in mind when setting up future workbooks.
October 19th, 2009 at 12:02 am
[...] Or even worse, fixing errors in other people’s workbooks. After commenting on my article See Formulas on an Excel Worksheet, Patrick O’Beirne, author of Spreadsheet Check and Control , asked if I’d like a review copy of [...]