Troubleshooting

If you have been working with Excel for quite some time, you have probably Inherited an Excel workbook that was, to say the least, a bit confusing. The original creator of the workbook is, of course, no longer accessible, so you are saddled with the task of Troubleshooting.   Troubleshooting is, without question, a Valuable Skill in Excel, and possessing this skill will most certainly make you an MVP in most corporations.  

One common issue is the use of formulas (and, to be perfectly frank, the peculiar way some users set up their workbooks). An easy way to identify and display the cells with formulas is to hold down the Ctrl button and press the tilde (that squiggly character in the upper left of your keyboard: ~). When you press this keyboard combination, all your formulas will appear in their cells (and pressing the combination again will bring back your original view).

The foregoing does not solve All your investigative challenges, of course, as the Aggravating Problem can be trying to determine from where a formula is drawing its information. Happily, Excel contains a Very Helpful Tool for clicking on a formula cell and tracing its Precedents. To find it, go to Formulas / Formula Auditing.

From there, clicking on Trace Precedents will give you a Graphical Illustration with arrows that show you from where your information is being derived. “Pretty Cool!” you say? Yes, I quite agree!

Let’s face it, problems happen in Excel, and Ctrl + ~ and Trace Precedents are two excellent tools to help you figure out those cryptic Excel workbooks that you inherit from others. They can help you quickly get your Excel workbook back on track and get on with your day. You may even stop cussing old What’s-his-name who created the workbook in the first place…

Control Yourself

The unassuming Ctrl Key gives you a lot of, well, Control.  It occupies our keyboards in both the left and right positions in the lower row, making it convenient to use whenever you need it.

So, what miracles reside in the Ctrl Key?  Quite a few…

1) Speedy Navigation: When you press Ctrl and any Arrow Key (up, down, left, or right), you Jump to the last populated cell in that direction. Bamm! Just like that!

2) Fill the Contents and Format: Using the topmost cell of a selected range as reference, click Ctrl+D to Fill the Selected cells below. It’s a true Crowd Pleaser, (and I’ll bet you’ve never tried it…).

3) Hide Your Columns:  Simply select the columns you wish to hide, and click Ctrl+0.  This totally Rocks!

4) Make Noncontiguous Selections: Select any cells you want, hold down the Ctrl key while you click on a cell or drag through a range of cells. As long as you hold down the Ctrl key, you can click and select Anything You Want on the spreadsheet.

5) Print Without the Mouse: Sometimes we still need to Print a worksheet. Rather than using the mouse, another simple Crowd Pleaser is to simply press Ctrl+P. Bamm! Ready to print!

There are a great many useful Ctrl Tricks that you can use to help you Control your worksheets. Take a few minutes some time, and give them a try. Control is a good thing…