Hiatus!

A line from an old Clint Eastwood movie went, “A man’s gotta know his limitations…”.  I can relate to this, as I find myself quite enormously busy these days with many and sundry projects, classes, books, etc.  (Always a good thing…).

 It is due to the foregoing that I will be taking a hiatus from writing this blog.  If you are a Kindle subscriber, you should immediately cancel your subscription.

 I very much appreciate your readership and your fine comments of the past, and I hope we meet up again in the future.

 Meanwhile, have you ever Inherited an Excel workbook that simply didn’t make sense? As we discussed in this blog a couple of years ago, problems inevitably happen in Excel. One of the most common issues is, of course, the use of formulas (and, let’s face it, the peculiar way some users set up his or her workbooks).

Troubleshooting is, without question, a Valuable Skill in Excel!

An easy way to identify the cells with formulas (and display them) is to hold down the Ctrl button and press ~. When you press this keyboard combination, all of your formulas will appear in their cells (pressing the combination again will bring back your original view). That is a good start for determining what is going on “behind the scenes”.

Of course the foregoing does not solve all of your investigative challenges. The Vexing Problem can be trying to determine from where a formula is drawing its information. Excel contains an Elegant Tool for clicking on the formula cell and tracing its Precedents. For Excel 2003, go to Tools / Formula Auditing. For Excel 2007 and 2010, go to Formulas / Formula Auditing.

Clicking on Trace Precedents will give you a Graphical Illustration with Arrows that show you from where your information is being derived.

Ctrl + ~ and Trace Precedents are two excellent tools that can help you figure out those cryptic Excel workbooks that you inherit from others. Such a good thing…

All the best,

Bob DeLaMartre

Top Ten Favorite Shortcuts

Every year or so, I like to access what my Current Favorite Excel Keyboard Shortcuts are. This changes over time as I learn new tricks and incorporate them into my daily work with Excel.

As an Advanced Excel User, I am sure you understand the benefits of Keyboard Shortcuts. They add speed, efficiency, relief from the stress of overusing the mouse, and they are Just Plain Cool!

So, in David Letterman-style, here are my current favorite (and highly recommended) Top Ten Excel Shortcuts in reverse order:

10. Shift + F3 (Pulls up your Insert Function – Handy!)
9. F5 (Brings up the Go TO dialogue box – Great for navigating to a named range)
8. Ctrl + F4 (Closes the Current Window – Quick and easy!)
7. Ctrl + P (Pulls up the Print Dialog Box – Gotta love it)
6. F12 (Brings up the Save As dialogue – Nice!)
5. Ctrl + Z (Undo Previous Action – Oldie, but a goodie)
4. Ctrl + H (Find and Replace – I use it often!)
3. Alt + = (Brings up the AutoSum – Beats going to the toolbar)
2. Ctrl + Home (Brings you to the Start of the Worksheet – It is always good to go home)

And My Current Number One Best Shortcut Is (Drum Roll and Cymbals, Please)...

1. F11 (Creates an Instant Chart – Always a crowd pleaser!)

There you have it! Try a few of them, and see how they can make your Excel life better!

More Lines of Text

Since this is a blog for Advanced Excel Users, I sometimes hesitate writing about a technique that is quite simple. What I have found over the years, however, is that some Simple techniques have been overlooked by some truly accomplished Excel practitioners.

Although the focus of Excel is numbers, there are times when you need to enter text into cells as well. Sometimes you may even want to add another paragraph (or perhaps just a line break) in a single cell. If you are working in Word, that’s easy – just hit the Enter key.

In Excel, however, hitting Enter will just take you to the next row below. “Well, of Course, You Say!” But what if you want to Add More Lines of Text within the selected cell? The solution is so easy it will make you laugh.

All you need to do is press Alt+Enter to start a new line in the cell in which you are typing or editing. That’s all there is to it! (See if you colleagues know that one…). Cheers.