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.
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.
- 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!