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.
Find Data Entry Cells
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.
See Formulas in Excel 2003
If you’re using Excel 2003, follow these steps to see the formulas on the worksheet:
- On the Tools menu, click Options
- On the View tab, add a check mark to Formulas.
See Formulas in Excel 2007
If you’re using Excel 2007, follow these steps to see the formulas on the worksheet, instead of the formula results:
- 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 + `
The symbol at the right is an accent grave, and that key is above the Tab key on the my laptop’s keyboard. It might be in a different location on yours
The accent grave looks similar to an apostrophe, but its top leans to the left, instead of being straight up and down.
___________________
“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?
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 🙂
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.
[…] 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 […]
I’m using Conditional Formatting in excel 2003 to highlight the formula cells in spreadsheet but unable, Can anyone help.
Thx a lots.
@Terry, you could use this technique from John Walkenbach’s website to identify formula cells with conditional formatting
@Terry,
As long as your formula cells do NOT have any Conditional Formatting to begin with, then you can use the first macro below to highlight your formula cells on the active sheet using Conditional Formatting (so that any interior colors assigned to those cells will be preserved) and you can use the second macro to remove that highlight… make sure you do that before you run the first macro again on this, or any other sheet, or before you close the workbook. Copy/Paste all of the code below into a standard Module (making sure the first Dim appears before ANY other code in the Module, even code already existing in it)…
Dim FormulaCells As RangeSub HighlightFormulaCells()
Dim C As Range
Set FormulaCells = Cells.SpecialCells(xlCellTypeFormulas)
For Each C In FormulaCells
C.FormatConditions.Add xlExpression, , “=COUNTA(” & C.Address & “)”
C.FormatConditions(1).Interior.ColorIndex = 6
Next
End Sub
Sub ClearHighlightedFormulaCells()
FormulaCells.FormatConditions.Delete
Set FormulaCells = Nothing
End Sub