Get the Full Tutorial List to Learn Excel (and More) for Free
Download the full list HERE.
In the file, you will find the tutorials categorized into Main Topic and Category / Playlist. The Excel table includes Link to Video, as well its Length and Date when it was first published. Whenever the video is accompanied by a blog post, you’ll also find a Link to Post. This will take you to the relevant article on this website, where you can read more in-depth about the subject, download the Excel workbook with examples and follow along with me.
Remember that you can take advantage of Excel’s filtering capabilities to find a specific function or feature. Or browse through the categories to see which topics pique your interest, whether you’re interested in the beginner or advanced topics, or looking for crazy hacks. Master Excel with our free tutorials.
We aim to update the tutorials list every month. You can check back regularly to download the latest file. But there is a better way.
Instead of downloading a new version every time, connect live to the file on the site using Power Query and never miss an update.
Featured Course
Master Excel Power Query – Beginner to Pro
Create a Live Connection to the Online File with Power Query
Open a blank workbook, go to Data (tab) > Get & Transform Data (group) > From Web.
You need to grab the URL of the file; not the web page, but the file itself. Right-mouse-click on the download link to the file and copy link.
Then paste the link
https://www.xelplus.com/xelplus_tutorials_list/into the ‘From Web’ dialog box and click OK.
When prompted to select authorization method to Access Web content, go with Anonymous and click Connect.
You will see a preview of the tables and sheets available in the online workbook. There are two sheets, but we are interested in the table. Select TableTutorials and click on Transform Data.
If you loaded the table directly into your workbook, you would have the live connection and you would be able to refresh the file to include new tutorials, but the links in the file would not work. 😩
The URL is incomplete and the hyperlinks from the original file are not coming through. Alas, there is no “Link” data type in Power Query. We need to take some steps to make it work for us, hence the need to transform the data first.
Transform Data in Power Query to Create Proper URLs
By clicking on Transform Data, you open the Power Query Editor.
Select the Link to Video column, go to Transform (tab) > Text Column (group) > Format > Add Prefix
and paste the following prefix:
https://youtu.be/Then repeat the step for the Link to Post column. For Prefix, use:
https://xelplus.com/Now we have proper URLs that should work.
Before we load it to Excel, though, let’s ensure all the data types are correct. You should see Integer for #, Duration for Video Length, Date for Date, and Text for the other columns.
In order to always have the latest tutorial on the top, select the # column and Sort in Descending Order.
Now you can close the Power Query Editor and load TableTutorials to Excel. Go to Home (tab) > Close & Load.
We have correct URLs, but they still are not clickable.
You have to click inside every cell and press Enter to make the URL clickable. Not something you want to do for over 400 tutorials (with more to come). We need to find a way around this problem. Let’s turn again to Power Query.
Featured Course
Automate With Power Query – Recipes to solve business data challenges
Creating Custom Data Types in Power Query
Reopen Power Query Editor by double-clicking on the TableTutorials query on the Queries & Connections pane (or by using the shortcut: Alt + F12).
Select everything using the shortcut Ctrl + A. Go to Transform (tab) > Structured Column (group) > Create Data Type.
You need to give your data type a name. Let’s call this one Tutorials.
Next, select the Display column – the “face” of your data type. Use a unique and descriptive column that will tell you at a glance what the record is about – in this case, Tutorial Title makes most sense.
Under Advanced Options, you can add or remove columns. Since we started with all columns selected, there’s nothing for us to add, but in case you didn’t – you have an opportunity to add relevant columns here. You can also rearrange the order, for example bring the # down and both Link columns up.
After you click OK, everything gets condensed into one column. If you click to the side, you will see the details on the bottom. That’s what a custom data types does – it compresses the information from multiples columns into a single column. All the data is still there, just as a record. Each row represents a separate record.
We can now send this to the workbook – Close & Load.
The loaded table updates and now you only see the single column called Tutorials with Tutorial Titles. But notice the symbol to the left of each Title – Show Card. When you click on the symbol (or use the shortcut Ctrl + Shift + F5), a card will pop up with all the data we have on that specific tutorial.
If you hover over each item on the card, you’ll notice another icon – Extract to grid. When you click on it, a new column will be added to the table, with the requested values, e.g. “Link to Post”.
You can also see the floating icon to the right of the table – Add Column, which also allows you to extract elements of each card – the other columns of our original dataset.
So, if we go ahead and extract Link to Video and Link to Post, will we have our clickable links? Not just yet. But notice something about the extracted columns. If you look in the formula bar, you’ll see there is now a formula behind the extracted value. It references the [Tutorials] column in combination with .[Link to Video] – the extracted column (@ indicates <this row> in a multi-row table).
![Fragment of an Excel table with a "Link to Video" column. A cell selected and the formula behind the value in the cell is visible in the formula bar: =[@Tutorials].[Link to Video].](https://www.xelplus.com/wp-content/uploads/2018/12/24-Structured-formula.png)
That’s how you reference parts of the data card with a formula:
=[Data Type Name].[Column Name]And we can use that to our advantage.
Converting URL Text into a Clickable Link
Since we’re dealing with formulas, we can utilize the HYPERLINK function. Let’s wrap our data type formula inside HYPERLINK:
=HYPERLINK(link_location,[friendly_name])The link_location will be the data type formula: [@Tutorials].[Link to Video]. You can also add a friendly_name, like “Video”.
=HYPERLINK([@Tutorials].[Link to Video], "Video")As soon as you press Enter, you’ll notice the Videos are now all hyperlinked. If you click on the link, it will take you to the relevant YouTube video.
![Fragment of an Excel table of tutorials. In the "Link to Video" column, the word "Video" is hyperlinked. Formula bar populated with =HYPERLINK([@Tutorials].[Link to Video], "Video").](https://www.xelplus.com/wp-content/uploads/2018/12/25-HYPERLINK-formula-1.png)
Let’s do the same for Link to Post:
=HYPERLINK([@Tutorials].[Link to Post])And all the blog links are now clickable as well.
![Fragment of an Excel table of tutorials. In the "Link to Video" column, the word "Video" is hyperlinked, in the "Link to Post" column, a hyperlinked URL for the xelplus website. Formula bar populated with =HYPERLINK([@Tutorials].[Link to Post]).](https://www.xelplus.com/wp-content/uploads/2018/12/26-HYPERLINK-formula-2.png)
Never Miss an Update – Refresh for More Free Tutorials
If you don’t want to miss my new videos, all you have to do is Subscribe to my channel 😉. But now, you have also built a direct connection to a complete library of free tutorials in Excel. Whenever the file on the website is updated, you can simply open your own file, right-mouse-click on the table and Refresh. You can also use the shortcut Alt + F5 (when inside the table; or Ctrl + Alt + F5 to Refresh All from anywhere in the workbook).
BONUS TIP – Refresh data when opening the file
Better yet, go to the Data tab > Queries & Connections group. Expand the Refresh All options and click on Connection Properties.
In the Query Properties dialog box, place a checkmark next to “Refresh data when opening the file“.
That way, you won’t even have to bother with clicking Refresh. Whenever you open the query file, it will automatically pull the latest data from the XelPlus_Tutorials_List and you can be sure you are seeing the most up to date version.
I hope this helps you find useful videos and learn new topics. If you want to learn even more, feel free check out my comprehensive courses!
Featured Course
Black Belt Excel Package
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
