I had a student a number of years ago who was in a bunch of hot water with her manager. She had updated a critical spreadsheet for a co-worker who was on vacation, and in doing so, entered data right over the complex, nested, multi-dimensional formulas that took eons to create and perfect. Because the calculations were integral to day-to-day operations and were referenced by multiple workbooks, she instantly became persona non grata in her small office. She never saw it coming. She never saw that formulas were camouflaged by the values the cells. Because she trusted Excel, she believed what it displayed when she opened the workbook. She felt betrayed, because – as she saw it – Excel lied to her.
Those of us who are “in the know” understand that there were many red flags and visual cues along the way that went unheeded. Moreover if protection was properly applied by the author, she wouldn’t be in this mess in the first place. But, I understand her frustration.
In case you aren’t familiar with Excel’s reputation for not always showing the whole truth, here’s a scaled-down example of what my student experienced:
|
What is in cell A3?
|
|
|
|
“The number 3″, you say? “Duh”, you say? Not so fast!
|
|
Watch what happens when I click on that cell:
|
|
|
The Formula Bar tells the truth. The value 3 isn’t really what’s in that cell; a formula that adds whatever is in cell A1 with whatever is in cell A2 is. Obviously, not knowing this nuance can lead to a pretty big oops; hence, the situation that my student found herself in. That tidbit might be elementary to you. But does that knowledge alone solve the issue? Not unless you want to click on every cell in your worksheets just to ensure that everything is as it appears.
Here’s what you may not know: To identify all of the cells that contain formulas and functions in a worksheet, hold down the CTRL key while pressing the tilde ~ key (usually located above TAB).
|
Before CTRL + ~
|
After CTRL + ~
|
|
|
|
To return to normal display, use that key combination again.
Yup, it’s that simple, and it doesn’t matter which version you use. This ability has been available since the early days of Excel. If you’ve been using Excel forever and had no idea this was available, don’t beat yourself up about it. Hey, you don’t know what you don’t know, right?
If you have questions about this topic, please never hesitate to communicate. I can be reached via hferrise@t3training.com
