xl30This week Microsoft Excel celebrates its 30th birthday and there’s a lot going on.

“Microsoft really bet its future on two programs at right about the same time: Excel and Windows,” Klunder said. “If both of them had failed, Microsoft wouldn’t be here today. Both of them succeeded. It really helped cement Microsoft’s role.”
In this blogpost, I’ll share my earliest memories of Excel, and let you know about a change to look for in Excel 2016.

What was going on 30 years ago in 1985?

Popular television shows in the US included:
The A-Team
Dallas
What’s Happening Now!
227
Magnum, P.I.
She-Ra: Princess of Power

Popular Songs:
Klymaxx – Meeting in the Ladies Room
Sheila E – The Glamorous Life
Frankie Goes to Hollywood – Relax
Aha – Take On Me
Lisa Lisa and Cult Jam with Full Force – I Wonder If I Take You Home
Ready for the World – Oh Sheila

 ALSO IN 1985

  • In 1985 the Chicago Bear Defense was terrorizing other teams. The Bears lost just 1 game during the 18985-86 season and their easy Super Bowl victory over the New England Patriots was fun to watch.
  • Manhattan’s Times Square still hadn’t been cleaned up yet. (But we can’t talk about that until after 10pm.)

In 1985, I joined the US Navy. My first encounter with Excel was still 13 years away, in 1998. It was during a 1-day temp assignment at a toy manufacturer. Another lady and I were there to do plain ol’ data entry. Nothing interesting. Around 2001 is when  I started developing a relationship with Excel.

VLOOKUP, POWER QUERY, and EXCEL 2016

In 2001, a task I absolutely hated was monthly matching up employee IDs with employee names so that I could calculate bonuses. That task consisted of Find/Replace … one ID at a time … something like 50 times. What’s worse is when I’d do it all, and then someone would call and tell me that the underlying data wasn’t complete. WHAT? “I’ve got to rerun the report and manually match this stuff up again?!”

Eventually, when I learned about Excel’s VLOOKUP and pivot tables, they would make that task much easier. The pivot tables made the bonus calculation easier, but VLOOKUP was the function that made the core task o matching IDs and names instant and accurate.

So many similar tasks were simplified with VLOOKUP, and it’s second only to IF as most important Excel functions. (See VLOOKUP and a pivot table in this video)

One exciting aspect of Excel 2016 is the Get & Transform feature. It was formerly called Power Query and was an addin that had to be either downloaded and installed (Excel 2010) or enabled (Excel 2013). In 2016, Get & Transform is hot and ready on the Data tab of the ribbon. Though … the renaming of Power Query to “Get & Transform” is a bit of a nuisance for those of us who’ve been using it and have created tutorials for “Power Query.” Still, the power is not to be missed!

GT

  Check out this video: ExcelTV episode 25 where I show how Power Query (now, Transform & Get) is used to separate names and professional designations. The challenge is when there are names like:
James Russell
Phyllis C. Walters, DDS
Karen Myers, PhD, MD
Dylann Li, Chaplain, USN
Norman Ireland, MBA

If we want the names separated from the designations, there are ways to do it in Excel, but they have drawbacks:

  • Text-to-columns creates a mess by splitting along all commas, spreading Dylann and Karen’s names across 3 columns.
  • Using formulas requires a delicate cocktail of SUBSTITUTE, REPLACE, LEN and RIGHT

So, check out the video and see how it’s done in modern Excel. Then, go ensure that you’re up to date with your version of Excel because, if you’re getting and transforming data in Excel, the modern methods are so much easier, cleaner and more accurate.

For more info about Power Query/Get & Transform: Ken Puls and Miguel Escobar will take you deep deep in via the ExcelGuru blog and their online courses!