J-Walk & Associates, Inc.

Excel 2010 Book List Updated

The list of my Excel 2010 books is now up-to-date, with cover images and links to Amazon.

You'll notice a new one: Excel Dashboards and Reports. It's really Mike Alexander's book but I contributed a few chapters and got listed as a co-author with the Data Pig.

The publisher and I still haven't decided if a new edition of Excel Charts will be added to the list.

Excel Hero

I follow every English language Excel blog on the planet. There are lots of good ones, but the one I like best is Daniel Ferry's Excel Hero. His downloads are simply awesome.

From his "About" section:

I cut my Excel teeth on very early versions in the early 1990s. Before that I used Lotus123. I am completely self taught in the Excel arena and gained most of my knowledge while creating systems to run a large logistics company that I managed for 15 years. I've had hundreds of clients and successfully completed thousands of projects since.

Is he an Excel MVP? I missed the last two MVP Summits, and I hardly keep up with MVP stuff any more, so I don't know. If he's not, he should be.

Problems With Euro Currency Tools Add-In

If you're experiencing some inexplicable errors with your VBA code, check your add-ins list (press Alt+TI). If Microsoft's Euro Currency Tools add-in is installed, uninstall it. That add-in should be banned. Euro Currency Tools is the only add-in I've seen that can cause errors in VBA code that's in a completely different workbook.

Try this in Excel 2007...

First, make sure the Euro Currency Tools add-in is installed. Activate a worksheet that has a few formulas, and then run this macro:

Sub TestEuroTool()
  Dim WorkRange As Range
  On Error Resume Next
  Set WorkRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
  Debug.Print WorkRange.Count 'formula cells
  Debug.Print Err.Number, Err.Source
End Sub 

Err.Number should be 0, since the sheet has formulas. But it's 9 (Subscript Out of Range), and the source of the error is Euro Tool (the name of the VBA project in EUROTOOL.XLAM) . At least a dozen PUP users have notified me of an error -- and uninstalling the Euro Currency Tools add-in fixes the problem.

It seems to be fixed in Excel 2010 beta.

Hiding & Unhiding With Shortcut Keys

According to the Excel Help system, you can use these key combinations to hide and unhide rows and columns within a selection:

• Hide rows: Ctrl+9
• Unhide rows: Ctrl+Shift+9
• Hide columns: Ctrl+0 (that's a zero)
• Unhide columns: Ctrl+Shift+0 (also a zer0)

They all work for me except the last one. It fails for Excel 2003, Excel 2007, and Excel 2010 (running Vista).

A bit of Googling tells me that others have the same problem. One theory is that the OS is intercepting that key combination before it gets to Excel.

Does Ctrl+Shift+0 work for you? If so, which OS and Excel version do you use? Note that to unhide columns, you must select a range of cells that includes columns to the left and to the right of the hidden columns.

Mini Books

Somehow, Mike Foster figured out a way to miniaturize books, so they fit in a shirt pocket. He sent a photo:

Adventures In Book Marketing

Yesterday Chandoo (Pointy Haired Dilbert) made a blog post: 101 Excel Secrets - Recommended E-Book.

It was a link to an ebook by someone named Francis J Hayes. Chandoo was upfront, and stated that he's an affiliate seller for the book.

I'm always interested in Excel secrets, so I took a look at the site.I was appalled. It looks exactly like any of thousands of scam sites on the Web, run by sleazy "experts" trying to make a quick buck from idiots. I normally don't like to such sites, but if you want to take a look, here it is: 101 Secrets of a Microsoft Excel Addict ebook.

Who knows? It might be the best Excel tips books in the world. But anyone with half a brain will click the Back button as soon as they see the site. It just reeks of sleaze.

Francis J Hayes might make a sale or two based on this post, but I posted it because I'm curious. Does that type of marketing actually appeal to Excel users?

By the way, who is Francis J Hayes? He claims to be an expert, but I couldn't find anything on the Web that didn't require payment or a subscription.

35 Years Of Microsoft

Preston Gralla reminisces: Microsoft turns 35: Best, worst, most notable moments.

As Microsoft celebrates its 35th anniversary, I've decided to take an idiosyncratic and opinionated look at the best, worst and most notable moments, technologies, products, decisions and people in the company's history.

Excel gets only two mentions:

Smartest software bundling

Clearly the smartest software bundling move Microsoft ever made was combining Word, Excel and PowerPoint into Microsoft Office, first for the Mac in 1989 and then for Windows in 1990.

Microsoft Word, which Microsoft originally (internally) called Multi-Tool Word, was released in 1983 for MS-DOS, in 1985 for the Mac and in 1989 for Windows. Excel was launched in 1985 for the Mac and in 1987 for Windows. Also in 1987, Microsoft released PowerPoint for the Mac, essentially a version of an application called Presenter that was created by Forethought, a company Microsoft had purchased that year. In 1990, PowerPoint for Windows was released.

Excel Cat

Daniel sent a photo, and a brief review:

My cat enjoyed your book.

I should note that cats really like the later editions.

If you have any photos of my books out in the wild, send 'em my way, please.

Arranging Your Data

Last week, someone sent me a workbook because he was having some problems with it. Here's a small section from the file:

It was arranged in categories, and each category had tasks below it, arrange in a row. Under each task name was date to indicate when the task was performed. Many of the date cells contained a cell comment to clarify (3,810 comments in all). Summary formulas were entered to count the dates. And each formula was hand-crafted because the layout was so haphazard.

I was very surprised to see such a poorly organized worksheet. But, after giving it some thought, this sort of thing is probably not at all unusual. The typical user, I think, probably starts entering data without giving the organization much thought. At first, it's easy makes sense. But after a few years of data entry, you end up with a complete mess.

I suggested that this person spend a day or two and copy/paste the data into a normalized table with four column headers: Category, Task, Date, and Comments. Data entry would be much easier, and the information in this table can be easily sorted, filtered, or summarized with a pivot table.

To a typical user, it probably seems very inefficient to repeat the category name and task name for every entry in the table. But it's actually the most efficient way to store data.

New Shortcut Key Discovered

Pointy Haired Dilbert compiled a comprehensive list of Excel keyboard shortcuts.

And that reminded me of a new shortcut I discovered last week:

• Ctrl+D makes a copy of an embedded chart (Excel 2007 and Excel 2010 only).

To help you remember, think "D for Duplicate." I couldn't find it in the Help system, and a cursory check of the Web leads me to suspect that I'm the first person in the world to discover this.

Before Excel 2007, the easiest way to copy a chart is to press Ctrl while you drag the chart with your mouse. That action was removed in Excel 2007, but Microsoft provided an even easier method -- and didn't even tell anyone about it!

Greg Likes The Book

Yet another unsolicited bit of fan mail:

Just a quick note to tell you how impressed I have been, for a very long time, with your Excel books. I bought one several years ago (maybe Excel Power Programming With VBA?) and, at the time, it put me ahead of all of my colleagues in terms of practical, ready to use knowledge about MS Excel.

Skip forward at least a decade and I had a current need to update my ss skills and went to Barnes & Noble to get some up-to-date resource technical materials. As I was scanning the available materials, my wife said "how can you ever manage to figure out which Excel book to pick?" At that moment in my scanning, I saw just "Walkenbach" and I pulled it out and replied "Right here, this guy is an Excel genius. I recall reading one of his earlier Excel books and it was just incredible." I think she thought I was kidding.

Well, I took Excel 2007 Bible home over the weekend and cracked it open this morning to get up to speed on charts. I wrapped up the 'Getting Started Making Charts' intro on charts and am just part way into 'Learning Advanced Charting' and I am very impressed! Your exceptionally clear writing style, focus on how to do the task at hand, easy transition from introductory to advanced materials are all just top-of-the-line.

So, I thank you, man, for putting out such a truly great product and I wish you the very best. I can't say enough good stuff!

Wishing you the best,
Greg S., NY

I'm glad you like it, Greg.

Excel 2010 VBA Enhancements

At the official blog of the Microsoft Excel product team: Migrating Excel 4 Macros to VBA.

Excel has a macro facility, known as Excel 4 macros (XLM for short) that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA.

The article lists quite a few things that, in the past, required an XLM macro. The most important new feature is the ability to provide argument descriptions for user-defined functions. These are the descriptions that appear in the Function Arguments dialog box.

I posted a new tip that demonstrates how to do this: User-Defined Function Argument Descriptions In Excel 2010.

14 Years Old

Today is the 14th anniversary of The Spreadsheet Page. How quickly they grow up!

Here's how it looked in 1997:

Click to enlarge, or try navigating it at archive.org.

I'd say my site design skills have improved over the years.

Joe’s A Fan

Here's another unsolicited fan mail:

Mr. Walkenbach,

My name is Joe P. I'm a 22 year old college senior. I've been working on a co-op for about a year with a local company in Quality Assurance. When I started, the demands of the job went beyond my capabilities: they needed someone who could write Excel macros, and a lot of them. Determined to fulfill the role, I went to Barnes and Noble and picked up "Excel VBA Programming for Dummies."

That was about 9 months ago. Since then, I've learned the amazing things Excel can do thanks to your easy to follow guide. Not only has it expanded my capacity at work, but I've taken my Excel VBA skills and like to make fun spreadsheets in my spare time. I spent about 4 or 5 months writing a program that plays and analyzes the game of Craps in a little over 1 MB.

My time on co-op is almost up. When I leave, I plan to start up a consulting firm. I'm going to contract my services at building spreadsheets to small businesses in the area. Excel programming has become one of my favorite things to do, and I'm hoping I can find a market for my skills... I can't imagine a better way to make a living.

So I just wanted to send you a sincere thank you for writing your book, and wanted to let you know, I've put the information to great use, both professionally and personally.

Thanks for the kind words, Joe. When I started reading your email, I just assumed that the book you used was Excel Power Programming. Glad to hear that the For Dummies book also works.

Chess Game Viewer

This is one of the most creative Excel apps I've ever seen. Created by Daniel Ferry, At Excel Hero: Excel 2007 Chess Game Viewer.

It uses no macros. Unprotect the worksheet and try to figure out how it work. Some hints:

• Dynamic Charting with No VBA
• Combination of XY (Scatter) and Stacked Column chart types
• Marker Fill with picture files.
• Conditional Chart Formatting for chessboard style
• No IF() functions. Not even one. REPT() and boolean logic as alternative to IF() INDEX/MATCH