A fresh look at Excel

When I was in high school, I was studious and made good grades, but I was somewhat of a clown, especially in Spanish class. The fact that my Spanish teacher assigned me the name “Guido” only enhanced my comedic gravitas, and I milked it for all it was worth -- especially when Mr. Goodwin called on me in class, playfully saying the name “Guido” in the same tone that Jerry Seinfeld made famous a few years later when he uttered “Newman” on his show. No one appreciated my clowning like the girl who sat behind me in Spanish class. Although she was always a dependable partner when we had to pair up on assignments in class, I never took the time to look beyond her superficial qualities. She was all glasses and goofiness -- until she came back for junior year. The glasses and goofiness had miraculously vanished and the dates were plentiful.

Free IT resource Hear how top CIOs turn change into a competitive advantage. Sponsored by HP Free IT resource Attend the SOA Executive Forum: Breaking SOA Bottlenecks SOAExecForum.com/may2007 Sponsored by InfoWorld

As we get older, the amazing summertime personal reinventions that were so commonplace during high school summers are replaced by two-week vacations from corporate life where we’re lucky to just get a mild sunburn and only a 3-pound weight gain. When we get back to work, we tend to do our work in the same old ways, with the same old tools, year after year. There is no long summer pause to give new perspective on previously familiar things, and the hidden marvels of the tools we use day in and day out no longer shine through their mundane shells -- but office epiphanies are still possible. I recently found that hiding behind the evil, ugly Excel stepsisters known as SUMIF and COUNTIF was the mysterious hidden beauty of Excel: pivot tables. (Incidentally, most sources agree that the concept that ultimately became pivot tables in Excel has its roots in the long-defunct Lotus Improv product.)

I have been using Excel for as long as I can remember. I’ve done my InfoWorld budget with the same Excel spreadsheet for the past four years, and I’ve kept track of my monthly expense forecasts while hardly giving Excel more than a glance beyond the immediate task at hand. I’ve shunned even the simplest formatting, typing my headers in all caps instead of simple bolds and underlines.

During the past several months, I’ve been working with our sales team to put the final polish on a migration from a proprietary Lotus Notes-based system to Salesforce.com, leveraging its Office Edition to dump numbers into Excel for more detailed analysis. At times, the project has been like doing open-heart surgery on the business itself. When dealing with critical sales information and producing reports that cut to the heart of business operations and strategy, a CTO must operate the IT scalpel with determined precision. Useless piles of sales numbers are as close to death as IT can get in a business. That’s where the often-neglected Excel pivot table can magically save the day.

The absolutely essential Excel: The Missing Manual defines pivot tables as “just a convenient way to build intelligent, flexible summary tables -- nothing more, nothing less.” But after becoming a pivot-table master, I am firmly convinced that the world’s biggest problems can be solved with well-conceived pivot tables. Sometimes we become obsessed with buying new products to solve a pressing problem instead of looking at how we can leverage the ones right under our noses. I wish I could adequately explain pivot tables, but you’ll need to study the manual to reach pivot-table nirvana. All I can say is, “Pivot tables, where have you been all my life?”