Microsoft Excel Tutorial: Quickly download YouTube Thumbnails into Excel.
TubeBuddy Link: Pricing
Formula for Edit Hyperlink: =HYPERLINK("YouTube"&B2&"/edit","Edit")
Formula to Wrap Thumbnails into 9 columns: =WRAPROWS(FILTER(F2:F2417,G2:G2417=1),9)
Welcome to episode 2634 of MrExcel's Net Cast! In this video, we will be exploring two new Excel functions that will help us quickly review over 2400 YouTube thumbnails. As we approach the 25th anniversary of MrExcel, I am grateful for the opportunity to connect with friends and followers from around the world through 67 books, thousands of articles, over 5 million posts on our message board, and 2400 videos on YouTube. If you enjoy this video, please click the like button below to help us reach more people. Thank you for your support!
As we prepare for November 21st, 2023, we have been updating all of our videos with new descriptions, a complete table of contents, and new thumbnails. However, with over 2400 videos, it can be challenging to ensure that all thumbnails have been updated. That's where these two new Excel functions come in. The IMAGE function allows us to insert a web image into a cell, while the WRAPROWS function helps us arrange a long vertical list of 2400 images into a rectangular range. These functions were unimaginable back in 1998 when I wrote my first article, but now they make it possible for us to quickly review all of our YouTube thumbnails.
If you have a YouTube channel and want to try this out for yourself, make sure you have the IMAGE function by grabbing an image from the web and pasting it into Excel. Then, use the function =IMAGE(C10) to insert the image into a cell. You may need to increase the row height to see the image clearly. This feature is currently only available on the Microsoft 365 Monthly channel, but you can try joining Office Insiders or using the consumer version of Excel at home for a better chance of having the new IMAGE function.
Now, let's take a look at how we can use these functions to review all of our YouTube thumbnails at once. Using a tool from TubeBuddy, we can export a backup of our entire channel, including the thumbnail URLs. After cleaning up the data and using the IMAGE function to display the thumbnails, we can easily identify any old style thumbnails that need to be updated. With the help of the FILTER function, we can quickly view and fix these thumbnails. However, there is one "gotcha" to be aware of - once an image has been cached by Microsoft, it will not recalculate even if we update the thumbnail. But with a little trust, we can confidently move forward and ensure that all of our thumbnails are up to date.
Thank you for joining me for this Net Cast. If you found this video helpful, please consider subscribing to our channel and ringing the bell to be notified of future videos. And as always, feel free to leave any questions or comments down below. See you next time!
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Review 2000 YouTube Thumbnails in Excel
(0:12) 25 years at MrExcel.com
(0:29) Inspirational Quote
(0:51) New IMAGE function in Excel
(2:05) Tedious to review at YouTube Studio
(2:15) TubeBuddy Backup of Channel
(2:50) Opening CSV in Excel
(3:10) Video IDs that converted to formulas
(3:45) Column J has thumbnail URL
(4:00) Using IMAGE function with YouTube thumbnail URL
(4:38) Double-click fill handle to copy formula
(4:56) Marking ones to fix
(5:11) Wrap videos into 9 columns & filter
(6:02) Filtering to videos to fix
(6:12) Adding Excel Hyperlink to edit video
(6:41) After fixing thumbnail, it won't update in Excel
(7:20) Clicking Like really helps the algorithm
TubeBuddy Link: Pricing
Formula for Edit Hyperlink: =HYPERLINK("YouTube"&B2&"/edit","Edit")
Formula to Wrap Thumbnails into 9 columns: =WRAPROWS(FILTER(F2:F2417,G2:G2417=1),9)
Welcome to episode 2634 of MrExcel's Net Cast! In this video, we will be exploring two new Excel functions that will help us quickly review over 2400 YouTube thumbnails. As we approach the 25th anniversary of MrExcel, I am grateful for the opportunity to connect with friends and followers from around the world through 67 books, thousands of articles, over 5 million posts on our message board, and 2400 videos on YouTube. If you enjoy this video, please click the like button below to help us reach more people. Thank you for your support!
As we prepare for November 21st, 2023, we have been updating all of our videos with new descriptions, a complete table of contents, and new thumbnails. However, with over 2400 videos, it can be challenging to ensure that all thumbnails have been updated. That's where these two new Excel functions come in. The IMAGE function allows us to insert a web image into a cell, while the WRAPROWS function helps us arrange a long vertical list of 2400 images into a rectangular range. These functions were unimaginable back in 1998 when I wrote my first article, but now they make it possible for us to quickly review all of our YouTube thumbnails.
If you have a YouTube channel and want to try this out for yourself, make sure you have the IMAGE function by grabbing an image from the web and pasting it into Excel. Then, use the function =IMAGE(C10) to insert the image into a cell. You may need to increase the row height to see the image clearly. This feature is currently only available on the Microsoft 365 Monthly channel, but you can try joining Office Insiders or using the consumer version of Excel at home for a better chance of having the new IMAGE function.
Now, let's take a look at how we can use these functions to review all of our YouTube thumbnails at once. Using a tool from TubeBuddy, we can export a backup of our entire channel, including the thumbnail URLs. After cleaning up the data and using the IMAGE function to display the thumbnails, we can easily identify any old style thumbnails that need to be updated. With the help of the FILTER function, we can quickly view and fix these thumbnails. However, there is one "gotcha" to be aware of - once an image has been cached by Microsoft, it will not recalculate even if we update the thumbnail. But with a little trust, we can confidently move forward and ensure that all of our thumbnails are up to date.
Thank you for joining me for this Net Cast. If you found this video helpful, please consider subscribing to our channel and ringing the bell to be notified of future videos. And as always, feel free to leave any questions or comments down below. See you next time!
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Review 2000 YouTube Thumbnails in Excel
(0:12) 25 years at MrExcel.com
(0:29) Inspirational Quote
(0:51) New IMAGE function in Excel
(2:05) Tedious to review at YouTube Studio
(2:15) TubeBuddy Backup of Channel
(2:50) Opening CSV in Excel
(3:10) Video IDs that converted to formulas
(3:45) Column J has thumbnail URL
(4:00) Using IMAGE function with YouTube thumbnail URL
(4:38) Double-click fill handle to copy formula
(4:56) Marking ones to fix
(5:11) Wrap videos into 9 columns & filter
(6:02) Filtering to videos to fix
(6:12) Adding Excel Hyperlink to edit video
(6:41) After fixing thumbnail, it won't update in Excel
(7:20) Clicking Like really helps the algorithm
Transcript of the video:
Hey, just down below the video, if you click like, that'll make sure that YouTube shows this video to more people. Thanks.
Quickly review over 2000 YouTube thumbnails in Excel.
I'm getting ready for November 21st, 2023. It'll be 25 years a quarter of a century at MrExcel. I've made friends around the world, 67 books, thousands of articles, over 5 million posts at the message board, and 2,400 videos at YouTube.
I love this quote. “If you don't have the time to do it right, when will we have the time to do it over?” Well, I'll tell you when!
In 2023, the last few months, we've updated all videos with new descriptions, a hundred percent table of contents and new thumbnails. But the big question is, did we get them all?
I needed a way to go look at all the thumbnails very quickly.
And this is only possible due to two newer Excel functions that I never would've dreamt of back in 1998 when I wrote that first article. The image function inserts a web image into a cell. And then the wrapcols function to arrange a very long vertical list of 2,400 images into a rectangular range.
Now for this to work for you. If you have a YouTube channel and you want to get your YouTube thumbnails. First thing you have to do is make sure that you have an IMAGE function. So just go grab some image.
Go right click on any image. Say copy image address and paste into Excel.
And then here, do something like equal image of C10.
And it'll give you a warning saying, Hey, it's active content and you have to accept that warning. But what you should see is an image.
The image is going to start out really small. You're going to have to increase the row height, but if you get that working, then you're home free.
Right now, November, 2023, it's only on the Microsoft 365 Monthly channel, but not on the semi-annual channel yet. If you don't have it, try joining Office Insiders, which is free, or even using the consumer version of Excel at home.
There's a better chance that that has the new IMAGE.
Alright, so let's take a look at how to view all of your YouTube videos at once.
So the goal here is to review all of the thumbnails in YouTube to look for the old style that somehow didn't get updated. And unfortunately going through YouTube Studio here, you're only seeing 20 at a time. It'll take forever to go through this.
So I'm using a great tool from TubeBuddy. They're clicking on their icon, come down here to website tools, backups, and export. We're going to ask for a backup of my entire channel and say start new backup. It says Waiting to be processed.
Two other exports ahead of you in line. So that probably means go away, do something else for 10 minutes and then come back. So pause the video.
In a few minutes, you get an email from two buddies saying that it's done.
Come back here and refresh. Successfully completed 2,801 videos in one minute and 34 seconds. We download the CSV.
There's the backup. Alright, so here's the CSV file.
It has columns with Video length out here, the date that it was published, full title. The full description has up to 5,000 characters.
The tags up to 500 characters. Channel is the same all the way down.
So that's not very useful. Now one known problem here is in the video id.
Sometimes the video ID will start with a minus sign.
And Excel, because it's pulling in a CSV file. Will change those to formulas.
To see if you have that problem, select all column A.
Home, Find and Select, Formulas. And, there, like that #NAME? error.
See – it is starting with equal minus. So I do control H, find and replace.
And I change every occurrence of equal minus to apostrophe minus.
That apostrophe is a great Excel trick that says, don't treat this as a formula, just treat it as text. Replace all and you're good to go.
Now the really important column here is the thumbnail column.
This gives us the URL of the image that's used for the thumbnail.
You have to look through here and figure out which columns you really want.
So I'll clean this data up and pause the video. Alright, I've deleted a few columns.
What do I have, I have 2,416 rows. Watch how awesome this is here, equal image of that thumbnail and see it starts out really, really small.
But what we can do is we can change the row height, I don't know, maybe to 45.
And we'll align top. A little bit wider.
So there we can see the thumbnail, make this tall enough and wide enough so that way it's good enough that you can see the thumbnail. And then I'm just going to simply double click to copy down. Right now it's going to go out to the internet and download all of those. It's going to take a while.
But I'll have a database then. And then I can sort of the entire every thumbnail, 2,400 thumbnails. It's amazing.
So I had a column here called Fix. And as I went through, just paging down, paging down, paging down. If I saw one that was the old style thumbnail that hadn't been updated yet. I would just add a one there.
So all the ones are the ones that have to be fixed.
And then over here, this is a great pair of functions that are relatively new.
The first one is WRAPROWS. So we're going to take all of that data, all of those thumbnails and wrap them into nine columns. So what we have is just kind of an easier view.
And then here the FILTER. I'm filtering all of those to where the fixed column is not equal to one. Those are the ones that should all be right or is equal to one. Which gets me just the old style thumbnails that somehow we missed. 11 of 'em, right?
So just a great tool allows you to very quickly just page through and make sure that all of these look the way that you want them to look. It's easy to tell old style versus new style.
Amazing bit of functionality here for your YouTube channel if you have Microsoft 365 with the brand new IMAGE(). Last step here, choose the Fix heading.
Turn on the filters and get rid of all the blanks. That'll get just the ones that I need to fix.
And then over here I added a new column. This does not come down from TubeBuddy.
Equal hyperlink everything up to, and then we concatenate in the video ID and then edit.
That gives me a great way to jump right to this video.
Should just be able to click edit and it opens a new YouTube tab.
And then I'm here where I can generate a new thumbnail and then upload it.
So great way to get to those 11 very quickly. Now just one “Gotcha” here.
So I'm adding the 11th new thumbnail and saving. Alright, perfect.
So I've now fixed all of these. However, there's a decision that Microsoft made about these images. Is that once they've gone out and cached this image. They don't ever go and recalculate that.
It just stays at the original values. All 11 of these are updated, but there's nothing I can do. I can't F2, Enter and have that recalculate.
I would have to actually delete all the thumbnails.
Copy the sheet to a new workbook. And then put all the thumbnails back in.
So at this point, I'm just going to trust that they're fixed.
And life is great without having that positive reinforcement of seeing 'em actually here in the spreadsheet. Well after 25 years, I want to thank you for stopping by. We'll see you next time For another Net Cast for MrExcel. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Quickly review over 2000 YouTube thumbnails in Excel.
I'm getting ready for November 21st, 2023. It'll be 25 years a quarter of a century at MrExcel. I've made friends around the world, 67 books, thousands of articles, over 5 million posts at the message board, and 2,400 videos at YouTube.
I love this quote. “If you don't have the time to do it right, when will we have the time to do it over?” Well, I'll tell you when!
In 2023, the last few months, we've updated all videos with new descriptions, a hundred percent table of contents and new thumbnails. But the big question is, did we get them all?
I needed a way to go look at all the thumbnails very quickly.
And this is only possible due to two newer Excel functions that I never would've dreamt of back in 1998 when I wrote that first article. The image function inserts a web image into a cell. And then the wrapcols function to arrange a very long vertical list of 2,400 images into a rectangular range.
Now for this to work for you. If you have a YouTube channel and you want to get your YouTube thumbnails. First thing you have to do is make sure that you have an IMAGE function. So just go grab some image.
Go right click on any image. Say copy image address and paste into Excel.
And then here, do something like equal image of C10.
And it'll give you a warning saying, Hey, it's active content and you have to accept that warning. But what you should see is an image.
The image is going to start out really small. You're going to have to increase the row height, but if you get that working, then you're home free.
Right now, November, 2023, it's only on the Microsoft 365 Monthly channel, but not on the semi-annual channel yet. If you don't have it, try joining Office Insiders, which is free, or even using the consumer version of Excel at home.
There's a better chance that that has the new IMAGE.
Alright, so let's take a look at how to view all of your YouTube videos at once.
So the goal here is to review all of the thumbnails in YouTube to look for the old style that somehow didn't get updated. And unfortunately going through YouTube Studio here, you're only seeing 20 at a time. It'll take forever to go through this.
So I'm using a great tool from TubeBuddy. They're clicking on their icon, come down here to website tools, backups, and export. We're going to ask for a backup of my entire channel and say start new backup. It says Waiting to be processed.
Two other exports ahead of you in line. So that probably means go away, do something else for 10 minutes and then come back. So pause the video.
In a few minutes, you get an email from two buddies saying that it's done.
Come back here and refresh. Successfully completed 2,801 videos in one minute and 34 seconds. We download the CSV.
There's the backup. Alright, so here's the CSV file.
It has columns with Video length out here, the date that it was published, full title. The full description has up to 5,000 characters.
The tags up to 500 characters. Channel is the same all the way down.
So that's not very useful. Now one known problem here is in the video id.
Sometimes the video ID will start with a minus sign.
And Excel, because it's pulling in a CSV file. Will change those to formulas.
To see if you have that problem, select all column A.
Home, Find and Select, Formulas. And, there, like that #NAME? error.
See – it is starting with equal minus. So I do control H, find and replace.
And I change every occurrence of equal minus to apostrophe minus.
That apostrophe is a great Excel trick that says, don't treat this as a formula, just treat it as text. Replace all and you're good to go.
Now the really important column here is the thumbnail column.
This gives us the URL of the image that's used for the thumbnail.
You have to look through here and figure out which columns you really want.
So I'll clean this data up and pause the video. Alright, I've deleted a few columns.
What do I have, I have 2,416 rows. Watch how awesome this is here, equal image of that thumbnail and see it starts out really, really small.
But what we can do is we can change the row height, I don't know, maybe to 45.
And we'll align top. A little bit wider.
So there we can see the thumbnail, make this tall enough and wide enough so that way it's good enough that you can see the thumbnail. And then I'm just going to simply double click to copy down. Right now it's going to go out to the internet and download all of those. It's going to take a while.
But I'll have a database then. And then I can sort of the entire every thumbnail, 2,400 thumbnails. It's amazing.
So I had a column here called Fix. And as I went through, just paging down, paging down, paging down. If I saw one that was the old style thumbnail that hadn't been updated yet. I would just add a one there.
So all the ones are the ones that have to be fixed.
And then over here, this is a great pair of functions that are relatively new.
The first one is WRAPROWS. So we're going to take all of that data, all of those thumbnails and wrap them into nine columns. So what we have is just kind of an easier view.
And then here the FILTER. I'm filtering all of those to where the fixed column is not equal to one. Those are the ones that should all be right or is equal to one. Which gets me just the old style thumbnails that somehow we missed. 11 of 'em, right?
So just a great tool allows you to very quickly just page through and make sure that all of these look the way that you want them to look. It's easy to tell old style versus new style.
Amazing bit of functionality here for your YouTube channel if you have Microsoft 365 with the brand new IMAGE(). Last step here, choose the Fix heading.
Turn on the filters and get rid of all the blanks. That'll get just the ones that I need to fix.
And then over here I added a new column. This does not come down from TubeBuddy.
Equal hyperlink everything up to, and then we concatenate in the video ID and then edit.
That gives me a great way to jump right to this video.
Should just be able to click edit and it opens a new YouTube tab.
And then I'm here where I can generate a new thumbnail and then upload it.
So great way to get to those 11 very quickly. Now just one “Gotcha” here.
So I'm adding the 11th new thumbnail and saving. Alright, perfect.
So I've now fixed all of these. However, there's a decision that Microsoft made about these images. Is that once they've gone out and cached this image. They don't ever go and recalculate that.
It just stays at the original values. All 11 of these are updated, but there's nothing I can do. I can't F2, Enter and have that recalculate.
I would have to actually delete all the thumbnails.
Copy the sheet to a new workbook. And then put all the thumbnails back in.
So at this point, I'm just going to trust that they're fixed.
And life is great without having that positive reinforcement of seeing 'em actually here in the spreadsheet. Well after 25 years, I want to thank you for stopping by. We'll see you next time For another Net Cast for MrExcel. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Last edited by a moderator:
