10 handy ways to get more from Excel (free PDF)
The tips, tricks, and shortcuts in this ebook will help you get extra mileage from Excel’s powerful features, generate accurate results, and save time on your worksheet tasks.
From the ebook:
Analyzing data often means spending more time getting and cleaning up data than analyzing it. If that describes you, definitely review Excel 2016’s Power Query (or Get & Transform). Using Power Query, you can load data from several sources, including the active workbook. Once data is in Power Query, you can analyze and manipulate that data quickly using specialized tools. If you think the only way to get the results you need is to use complex formulas or even VBA code, take a step back and think, “Can I do this in Power Query instead?” Often, the answer will be yes.
In this short introduction to Power Query, I’ll show you a problem that requires some fancy hoop-jumping to manage—unless you use Power Query. Specifically, I’ll show you how to create a database-style structure by separating multiple values stored in the same column into individual rows—one row for each value. It’s a common problem and not easily solved in Excel without specialized knowledge.
I’m using Office 365’s Excel 2016 (desktop) on a Windows 10 64-bit system. Power Query, otherwise known as Get & Transform, is available in earlier Ribbon versions, but you need to install it as an add-in. There’s no comparable tool in earlier menu versions. You can use your own data or download the demonstration .xlsx file.