Overview

Last week, I had a conversation with Olivia Tomoff and Bill Tomoff regarding extracting data from the internet into Excel.

Olivia asked if there was an easy way to “create a linked list in Excel of any YouTube playlist?”

That’s a great question, and one that I decided to solve.

Using two of my favorite Excel add-ins — ASAP Utilities and Power Query, I was able to do this very quickly (yes, even I was surprised!).

Here’s how:

The Process

Copy Playlist into Excel Worksheet

• Search YouTube for a playlist (any topic that is of interest to you). For this example, I searched for “Computer Science”.

• This playlist contains 649 videos. Highlight the selection and press “CTRL +C” to copy it. Open Microsoft Excel and paste it into a blank worksheet (CTRL + V).

Extract Web URL’s Using ASAP Utilities

• Let’s extract the web URL’s embedded in the pasted information. Fortunately, ASAP Utilities gives me this option. :-)

• Select options and click “OK”

• Save this Excel file as a CSV format file and open up a new Excel file — time to get to work!

Now we have the data we need — the next challenge is to “transform” it

Transform Data Into Our List with Power Query

• Via the Excel Power Query add-in, import the CSV file created above. Then enter “Edit query” mode.

• Work through the process of cleaning the data. I don’t review the detail here, but if you are comfortable with Power Query, it’s a relatively straight forward process (and I expect there are easier ways to do it than what I did!).

• Now, “Load” the data into Excel.

• Finally, clean up the presentation and functionality in Excel and you have a basic user dashboard.

Steps performed:

• Add hyperlink column for easy “Click to Open”
• Hide existing URL column — we no longer need it
• Add “Date Cmpltd” column to track progress
• Add “Notes” column if desired (I didn’t)

This is basic, but that is it. And it is done QUICK!

Basic knowledge of the functionality provided by two Excel add-ins enables a tedious, time-consuming task (which you probably wouldn’t undertake…) to be accomplished in minutes!

Thanks to Olivia and Bill for the thought provoking discussion last week — and last, a shout out to Olivia for throwing out the specific question. That was a fun one!

About Don

“It’s time for DIFFERENT”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat