This week Microsoft Excel celebrates its 30th birthday and there’s a lot going on.
- Over at the Contextures blog, Excel MVP Debra Dalgleish sent out a general call for people to submit their early memories of Excel. This week she will be posting the stories. So, put your hat on, head over there and check it out.
- Last week, Microsoft released Office 2016, aka “Excel 2016, and some other stuff.”
- Recalc or Die’: 30 years later, Microsoft Excel 1.0 vets recount a project that defied the odds is a good article where Doug Klunder and other developers of Excel 1.0 discuss the early days, their decisions and frustrations.
- Excel MVP and charting master, Jon Peltier, shares insights and more links to give you ideas about the newest in Excel 2016.
What was going on 30 years ago in 1985?
Popular television shows in the US included:
What’s Happening Now!
She-Ra: Princess of Power
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!
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:
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!