Popular YouTube Tutorials

There are different situations where you need to set an Expiry date to your Excel File and prevent a user from working on it. (please Read the Warning here below)
In this tutorial I show you how to do that in 2 different Scenarios.
If you have never created a code in VBA, it’s your chance to learn by following me step by step.
If you still have difficulty, then I wrote the code for you on a separate hidden worksheet so you can just Copy and Paste in the Visual Basic Editor (ALT +F11)
Remember that when you create a VBA code you should save your file as a Macro Enabled Excel File (.Xlsm). Moreover you may consider protecting the code with a password.
You can download the Exercise files and follow along by clicking on the links:
1- Predefined Expiry Date
https://www.amazon.ca/clouddrive/share/QqTaO5LKLG2K4sB6O2dgTwaKSYTGRn91nsfx8dWHXhE
2- Calculated Expiry Date
https://www.amazon.ca/clouddrive/share/DDTmQL0sb01yNWA9ZW2hJRyuaQYqlR5OKGh0bRl6WOe

##### WARNING ###### WARNING #####
IF you forget to open the file before the set expiry date and change the date in the code, you will not be able to open the file anymore if it goes beyond the set date.
Be extra cautious when setting an expiry date and set a reminder few days before expiry!!!

To keep me motivated, don’t forget to Like… Comment… Share and Subscribe

When you have a workbook with lots of worksheets it's very useful to create an Index of All Sheet Names and be able to Navigate to any of these worksheets and back to the Index.
There are 3 methods for doing this:
Either by Using Functions
Or By using a VBA code
Or By Using Power Query

In this tutorial I am using Power Query

You can download the Start file and follow along by clicking on the link here below:
https://www.amazon.ca/clouddrive/share/ZYfNMo26BkaTxXVMrcXKjR2ipKtdjS6qVs6JPNAeHV7

To watch the tutorial in which I use Functions Click on the Link:
https://www.youtube.com/watch?v=FeKgWSD8IWc

To watch the tutorial in which I use a VBA Code Click on the Link:
https://www.youtube.com/watch?v=V0KfbMZa_pY

If you find value in this tutorial give it a thumb up, and hit the “Subscribe” button to be notified when new videos are posted

We all need to track our Income and Expenses over the year by creating a Personal Budget.
In this tutorial I show you how to track your expenses over 12 months. I'll be using a hidden Pivot trick to build my sheets, input some data and create simple calculations. If my actual expenses go above the planned values, a conditional formatting rule will alert me and a column chart will visualise the numbers. We'll be using a VLOOKUP with a SHEET function to extract the sheet names and create dynamic labels.
You can download the start file and follow along by clicking on the link here below:
https://www.amazon.ca/clouddrive/share/gPpPaZDyAivcd4WS004U6CbSKSFzZsfDnkBAq0bIUJb

If you find value in this Tutorial give it a thumb up and don't forget to hit the big subscribe button, to be notified when new tutorials are posted

In this Amazing tutorial I show you how to unstack records: What is Unstacking?
We commonly have data in Excel where records are piled on top of each other in one column. unstacking means organizing your data in a list where each complete record is on a different row. By unstacking data, we are able to sort, filter or create pivot tables to analyze our data
You can download the exercise file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/UqGI5ZP7EeHS8ENC5Z1459ddrVSL5KslzrgEk6nHtA9

and I’ll be using four different Methods
I will show you how to do that by using an index and Match functions in a very creative method.
If you have never created a code in VBA I will guide you step by step on creating a simple code that can unstack your data
I will then unstack my records by using the magic of Power Query.
And finally, the fastest, laziest and most robust method by using Dynamic Arrays.
You can go directly to a specific method by clicking on the Timeline as follows:
1- Using Functions 01:08 min
2- Using a VBA Code 07:42 min
3- Using Power Query 22:43 min
4- Using Dynamic Arrays 29:13 min

In the VBA worksheet you will find the code I created, you can simply copy it and paste it in the visual basic editor.
Here is the VBA code another time:

Sub FixRecords()
Dim MyRecords As Integer
Dim FixRange As Range
Dim PasteCell As Integer
Dim Loopcounter As Integer
Range(“E2“). Resize(200,10).ClearContents
MyRecords = Range("B1", Range("B1").End(xlDown)).Count
Range("B1").Select
For Loopcounter = 1 To MyRecords Step 10
Set FixRange = Range(Cells(Loopcounter, 2), Cells(Loopcounter, 2).Offset(9, 0))
FixRange.Copy
PasteCell = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row + 1
Cells(PasteCell, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Next Loopcounter
Range(“E2”).select
End Sub

I added an Extra worksheet “Did You Watch” with links to some of my popular video Tutorials.

Finally, it motivates me to read your comments ...and don’t forget to hit the Subscribe button to be notified when new tutorials are posted… The Best is yet to come!

This tutorial is a Festival of Date Formulas & Functions!
With over 40 examples that will allow you to hold a firm grasp of Date Math.
I also included on a separate sheet a full definition of each function with the arguments required to use them.

You can Download the Exercise file and Follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/OXjKJM5vyCcb0dbKj4qDX69Uauv4XJRXsky8HaLnXrk

As a Bonus I created a customizable full year calendar that you can copy to any of your Excel projects. Just type any date in cell E1 and a Full year calendar is populated.

If you still find Date Functions confusing then I am offering you a FREE Gift: the “Quick Reference Guide” that wraps up all the Date Functions in a memorable way and make them available at your finger tip.
To Claim your Free Gift kindly do the following:
1. Write in a comment which function would be most useful to you! Then,
2. Send an email to info@OfficeInstructor.ca with the Subject line “Comment Done…Send my Free Gift”

You can go directly to specific topic by clicking on the timeline as follows:
Entering Dates 2:50 min
AutoFill 5:00 min
Formatting 6:50 min
Difference between 2 Dates 12:25 min
Splitting Date 17:00 min
Building Date 20:55 min
End Of Month 26:20 min
Returning a Date 28:18 min
First Day of Month 30:08 min
Last Day of Month 32:13 min
First Day of Year 34:19 min
Which Day of the week 36:36 min
Week Start Date 39:30 min
Week Number 43:40 min
Working Days 44:56 min
Networkdays 48:50 min
Mondays 52:10 min
COUPDAYS 59:55 min
Definitions 1:05:10
Calendar 1:05:22

Don’t forget to hit the Thumb up and the big subscribe button.

A Drop Down List is extremely useful in Excel. However, If we have hundreds of values it becomes difficult to find the specific value we are looking for. In this tutorial you'll learn how to shrink your list to fewer options by typing few characters and your drop down list will show only options relevant to what you typed.
You can download the exercise file by clicking on the link:
https://www.amazon.ca/clouddrive/share/7cb7Cpgba8vWzRnoVoKoEivSqxb6uuHz7Y4tabBZ9Ua
or
https://www.amazon.ca/clouddrive/share/6MAMOUY4uHsQIlgN2XdQMXOyayBwuI2YfhbAvuHlRBN
Don't forget to subscribe to be notified when new videos are released.

In this Training Video I'll show you how to create a Pivot Table in Excel with dynamic Progress Doughnut Chart and a Dashboard.
Impressive result with an easy to use step by step guiding.
you can download the start file and follow along by clicking on the link
https://www.amazon.ca/clouddrive/share/8nZ7zkfT6gxM6p9vuQofUxCmQkEh1gopblLwwUnfFpK

******* COMPLETE COURSE *******
Watch my latest Tutorial, which is a Complete Course on Dashboard Creation using Form Controls:
https://www.youtube.com/watch?v=BVAEsemQM30



Watch How to create a searchable drop list in Excel Just like Google
https://www.youtube.com/edit?o=U&video_id=8gqodEiDoJ8

Currency Exchange rate are now dynamically extracted at run time with the new enhancements of the stocks data type.
In this project we combine some of the best Excel tools in building a Live currency converter: Power Query, Data Validation, Vlookup Function, Macro Recording, Stocks Data Type and Worksheet Events.
You can download the start file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/S3mJfJYmGcKTboncaOOsOg7XRjBq8gEByaal9jrVADh

Jump to a specific Topic on the Timeline:
Get & Transform with Power Query 1:57 min
Create Data Validation Lists 5:01 min
Create VLOOKUP Functions 6:07 min
Record a Macro for Stocks Data Type 7:50 min
Attach the Macro to the Change Event 12:47 min
Edit the Macro in VBA 15:28 min

In this tutorial I will be writing a very simple code and attach it to the worksheet event. Should you wish to copy that code, here it is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A2").Address Or Target.Address = Range("B2").Address Or Target.Address = Range("C2").Address Then
Call Convert
End If
End Sub

If you found value in this tutorial, give it a thumb up and subscribe to my channel to be notified when new videos are released.
Music Credit :
Electro Cabello by Kevin MacLeod is licensed under a Creative Commons Attribution license (https://creativecommons.org/licenses/by/4.0/)
Source: http://incompetech.com/music/royalty-free/index.html?isrc=USUAN1400048
Artist: http://incompetech.com/

We know that a chart uses source data. In this tutorial I show you how to use an OFFSET function to store multiple source ranges in a defined name. Since our Defined name is controlled by a Drop List, Changing our selection from the Drop List automatically changes our Chart. It's an amazing technique for creating unlimited charts for a Dashboard
You can download the start file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/8DmOhMIfDrYrnYWOs69z3Pb3DK1z2KiIb2SLQz2rQFL

We have Formulas in Excel to extract The Year, Month or Day portion of a Date. However, we do not have a single function to extract the Quarter of a Year.
In this tutorial I Show you 5 methods to calculate the Quarter from a Date. I’ll be using a VLOOKUP function with a nice trick, Then I use a CHOOSE function, a ROUNDUP, a CEILING function and Finally a mind blowing technique with the MONTH function.

You can download the Start file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/tq3Qv84gfzEKhveaeb8tdNczeRadxQF5Nqmgtk0mzr7

Let me know in a comment which of the 5 functions you prefer.
I also added a Bonus sheet with links to my popular Tutorials.
In a previous video I covered all the Date Functions in Details, and here is the link:
https://www.youtube.com/watch?v=qIZxeOq-QDk

Finally, If you enjoy this training Video give it a thumbs up and consider subscribing to this channel to be notified when new tutorials are released. The best is yet to come.

Filtering is one of the most commonly used functionalities in Excel… So in this tutorial I show you how to filter fast with a single click. Learn how to save time and filter your data on the fly by using these 3 powerful filtering hacks. They will simply change the way you use Excel
You can download the Exercise File and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/TqaB30RUWUmcbpBHb7qcKYAgJYY9ajw67lZSGgnDFkR
The code I created for clearing the extracted records and filter based upon new criteria, is already in your visual Basic Editor.(ALT + F11)
Here are some of the shortcuts I used in this tutorial:
ALT + 4
CTRL + SHIFT + L
CTRL + T
ALT + A + Q
ALT + A + C
You can support my channel just by subscribing and you will be notified when more videos are posted…. The best is yet to come.

In this tutorial I show you how to add numbers based on 2 conditions using a SUMPRODUCT function. Although we'll be comparing text and Numbers, we'll see how to handle this situation.

You Can Download the Exercise file and follow along by clicking on the link
https://www.amazon.ca/clouddrive/share/BUbHgGoQxXw8lTkfDHfbaiyjiFhjFyNfaUMV5H3YbiH

You can read my blog articles by clicking on the link:
www.See-how.Ca
You can also check the upcoming live webinars and Join for Free
I you find value in this tutorial, give it a thumbs up and consider subscribing to my channel to be notified when I post new videos, the best is yet to come...
Now, let's watch the tutorial

When you import data in Excel that includes dates in a format different than the central settings of your computer, Dates might not be recognized as numbers, as they should be. Accordingly, you can’t analyze your data by using dates that are left aligned.

In this Tutorial I’ll show you how to fix this problem in three different methods and analyze your date values in Excel

You can Download the exercise file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/p27hiqdVWNeTYVlf2zWi3YjkQSeS14MldqXYRmXZwCV

You can Read the blog article on my blogsite
www.See-How.ca

To book a Corporate Training visit my website
www.OfficeInstructor.ca

Don't forget to subscribe to my channel to be notified when new tutorials are posted... The Best is yet to come.

In this Tutorial show you how to attach an Employee or Student Picture to a record without obstructing the view. You can also attach a copy of a receipt or an Invoice...
I'll show you how to enter tens or hundreds of Picture comments with a single click.

You can download the start file and follow along by clicking on the link here below:
https://www.amazon.ca/clouddrive/share/j3SdSWDCACvhzd13k5T74LyeIr7VjmG9aUZniGTUnSg

You can learn more about comments and download a Quick Reference Guide for Shortcuts for dealing with comments by going to my blogsite:
www.See-How.ca

To Book a Corporate training visit my website:
www.OfficeInstructor.ca

I definitely prefer that you follow me step by step for creating the code to insert Pictures in Bulk. However, if you just need to copy and paste my code into your VBE, then here is the code for you.:
Remember: You MUST change the path (to the left of "IMG" by YOUR OWN PATH, for the code to work:

Sub AddPicture()
Range("H2", Range("H2").End(xlDown)).Select
Selection.ClearComments
For Each cell In Selection
ThisPicture = "C:\Users\nabil\Pictures\Staff\IMG_" & cell.Value & ".jpg"
With cell.AddComment
.Shape.Fill.UserPicture ThisPicture
.Shape.Height = 125
.Shape.Width = 100
End With
Next cell
End Sub


Your feed back is much appreciated and by subscribing to my channel you motivate me to keep creating more tutorials

A comparison dashboard enables you get insights about your data and compare between 2 items based on multiple criteria.
In this tutorial I show you how to create the comparison dashboard by combining the functionality of Pivot Tables with Conditional Formatting.
Along the way we’ll explore the GetPivotData function, Slicer Properties and protecting your dashboard while maintaining the interactivity.
You can download the Exercise File and follow along, by clicking on the link:
https://www.amazon.ca/clouddrive/share/Y0sjs5pt18L8bEbu4yYVTBrmle27C40aiEJk3npFQLY

You can learn more Excel Topics and download many Quick Reference Guides for Shortcuts by going to my blogsite:
www.OfficeInstructor.com (or www.See-How.ca)

To Book a Corporate training visit my website:
www.OfficeInstructor.ca
Your feedback is much appreciated and by subscribing to my channel you motivate me to keep creating more tutorials

When you have over 17 Billion cells in a any worksheet, you might need to deal with data scattered throughout this huge area. In this tutorial I show you how to quickly work with four separate parts of the same worksheet and navigate between them.
You can Download the Exercise file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/zWkKIFpNCFaf1XEYHABVlQH2GQefC4v8lgG5zof66bn

If you want to become more confident in using Excel, make sure you read my articles & join my newsletter at
www.See-How.ca

Book your Corporate Training with me at:
www.OfficeInstructor.com

Your feedback motivates me so don't forget to Like , Share and comment... above all hit the subscribe button to be notified when new videos are released... The Best Is Yet To Come.

Learn about the Hidden Secret of the VLOOKUP function.
In this Tutorial I show you how to delete one of the main arguments of our VLOOKUP functions and have the function work as usual.
You can Download the Exercise file and follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/aBqzH9MjebdPuMSNGRWUPhtRFO5T4JQcLKT20ziLQzd

If you want to become more confident in using Excel, make sure you read my blog articles & join my newsletter at
www.See-How.ca

Book your Corporate Training with me at:
www.OfficeInstructor.com

Your feedback motivates me so don't forget to Like , Share and comment... above all hit the subscribe button to be notified when new videos are released... The Best Is Yet To Come.

If you decide to rotate a list and have the current set of Row Labels, running across the Columns, don't ever think about re-typing your list.
Although Transposing is the declared topic, however this Tutorial has a much greater benefit, as you’ll learn how to combine different functions and functionalities in achieving a specific goal in Excel. You could apply the concepts to many other situations.
I’ll show you 8 methods to switch columns into rows without retyping the information, including Tools, Shortcuts, Functions, Power Query and Dynamic Arrays.
So, let’s see how we do that in Excel.


You can Download the Exercise File and Follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/DnhstWEaTTVtg3YPLpKSMosCOKFqJruss6oCq4nxnXm

To read the article go to my blog site www.See-How.Ca
To Book a Corporate Training you can visit my website www.OfficeInstructor.ca

Your Feedback, Comments and Likes are a great support to me... and don't forget to subscribe to my channel to be notified when new tutorials are released...The Best Is Yet To Come.

We are still celebrating the new XLOOKUP function released few hours ago.
The XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, it defaults to exact match and can extract from the Right or Left side of the Lookup column.
In a previous tutorial I showed you the basic functionality of the XLOOKUP function, Here is the link:
https://youtu.be/L1itpo3cMfk
In this Video, I’ll show you how the XLOOKUP can return an array instead of a single value, or it can return Cell references for another function.
And then how can I nest multiple XLOOKUP functions to replace the classic Index and Match.
You can download the Exercise File and Follow along by clicking on the link:
https://www.amazon.ca/clouddrive/share/xI392yp8DT9cS7g3NKVSlD1C1sHZzMLseNaGWoE2xW0

To read my blog articles you can visit:
www.See-How.ca (or www.OfficeInstructor.com)
To book a corporate training you can visit my website
www.OfficeInstructor.ca

I need a feedback from you about this tutorial and let me know in a comment which functionality you want to see in my future videos.
If you enjoyed this tutorial give it a Thumbs up and don’t forget to subscribe to this channel to be notified when new videos are posted: The Best Is Yet To Come.

In this tutorial I’ll show you an amazing functionality that will enable you to convert a Chart Title into a Drop List and be able to switch source data from WITHIN the chart instead of having the drop list outside the chart… Your Chart will be a lot more impressive.
This tutorial builds on what you learned in my previous tutorial, on how to create a dynamic chart and switch the source data by using a drop list. We did that by using the new XLOOKUP function and also by using the classic Index & Match functions.
You can watch this tutorial by clicking on the link here below:
https://www.youtube.com/watch?v=51yRGyOrG90
You can Download the Exercise file follow along (and read my blog as well) by clicking on the link here below.
https://www.see-how.ca/xlookup-vs-index-match-for-a-dynamic-chart/

Also in previous tutorials, I explored the basic functionality of the XLOOKUP function. You can watch these popular tutorials by following the links:
Video 1:
VLOOKUP… Gone but not Forgotten… Welcome XLOOKUP
https://www.youtube.com/watch?v=L1itpo3cMfk

Video 2:
VLOOKUP Fired… XLOOKUP Hired. The New Giant is Here
https://www.youtube.com/watch?v=xW1_mbEfq_E
To book a Corporate Training visit our website:
www.OfficeInstructor.ca
www.OfficeInstructor.com
If you found value in this tutorial, give it a Thumbs Up and consider subscribing to my channel to be notified when new videos are released: The Best is Yet To Come.