Have you noticed there’s no way to create bookmarks within a YouTube video? I’ve always thought that it would be a great feature. Well, I have created an Excel template that can do this!

 

How Did I Get This Idea?

I wanted to do a post about Matt’s spreadsheet comedy. Writing down quotes was tedious so I pasted the transcript into Excel!

 

 

 

 

 

 

 

 

 

Download My Excel Template

YouTube Video Transcript Template.xlsx and (a) watch my video or (b) read instructions below.

 

YouTube Video (making a transcript of Matt’s video!)

Watch my YouTube video ! Or you can read the steps below.

 

Follow Steps Below To Set-up The Template

 

Get Video Transcript

Select a video (Not all videos have transcripts. There may be an option to create one).

 

Click the 3 dots and then click ‘Open Transcript’.

 

 

 

Select all text starting with “00:00” in the top left until the bottom right.

 

 

 

 

 

 

Paste (CTRL V) in cell A9

 

 

 

Timestamp and text should all be in column A. Formulas in columns B to F re-arrange it.

 

Copy video’s url & paste it into cell E1

 

 

 

 

How Does The Template Work?

Now that you’ve pasted in transcript text and url link you should see your video’s time & text like this:

 

 

 

 

 

 

Jump to specific parts of the transcript in Excel

While watching the video add comments in Excel. Enter minutes & seconds, click ‘Hyperlink’ and enter comments in column G.

 

 

 

 

 

Read the transcript and jump to specific parts of the YouTube video!

You can read the transcript and jump back into the video! Click ‘Video Link’.

 

 

You might have to watch an add so consider manually changing the end of the url (change 401s to 723s. ‘s’ is for seconds).

 

Add comments

In field ‘Text Flag/Comment‘ (column G) you can add comments.

 

Keyword search

Enter a search word in cell H6. Formulas below will display this word each time it’s found.

 

Use slicers to filter data

Once you’ve entered a search word and/or comments use the slicers to filter the transcript!

 

 

 

 

 

 

Make it more readable!

You’ll see some check boxes in column P. Check them to hide text in columns A to D. Or hide the entire columns by clicking the “-” symbol above column E.

 

Conclusion

I thought that this template would be helpful for those who want to keep track of favorite parts of long videos. If you liked this template then please share it, like it and mention it at parties!

 

 

I’m Always Learning Excel !

I’m enrolled in Power Query Academy. Why not learn with me? DISCLAIMER: I’m a student and an affiliate.

 

 

 

 

 

 

 

 

 

 

About Me

My name is Kevin Lehrbass. I live in Markham and work in Toronto as a data analyst.

This is my personal blog about Excel. I’ve been working data since before it was cool.

I have two dogs. This is my dog Cali. She is REALLY mad that I’m still on the computer. She hates Excel. I’d better shut things down and go spend time with them.