-
The Magic of Power Query & Hidden Pivot Table Trick. Don't Miss it
In this tutorial I want to show you some of the magic of Power Query…I’ll be importing from a website a list of Countries and their capitals, Clean and transform this list. Then send it back to Excel where I will be creating in one click a new sheet for each country (244 Sheets) and with a simple function extract the Capital of each Country at the top of each sheet.
In this amazing tutorial I’ll be using:
• Power Query
• A Pivot Table hidden trick
• And 2 simple Functions
You can download the Start & Finish files by clicking on the link here below:
https://www.amazon.ca/clouddrive/share/ycS0kJWtH07hUdJeQhxjwM14nG7LSrZkd98vxK0MweJ -
Set an Expiration Date to your Excel File
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 -
Create a List of Sheet Names using Power Query... With 2 ways Navigation
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 -
Create a Personal Budget - With lots of Excel Tips and Tricks
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 -
Unstacking Records with 4 Stunning Methods: Power Query vs. Dynamic Arrays vs. Functions vs. VBA
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! -
Your “One Stop Shop!” for Date Functions in Excel (+Bonus & Free Gift)
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. -
Create a Searchable Drop Down List in Excel
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. -
Pivot Table with Progress Chart and Dashboard
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 -
Exchange Rates are now LIVE ... Currency Converter with Power Query, Stocks Data Type & Macro
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/ -
OFFSET function to change Source Data for a Dynamic Chart
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 -
Calculate The Quarter of the year from Any Date - 5 Different Methods (Don't miss it)
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. -
Top 3 Filter Hacks in Excel - Very useful
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. -
Sumproduct Text and Numbers
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 -
Fixing US Dates Problem - Three Methods
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. -
Insert Picture Comments in Bulk with One Click
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 -
Comparison Dashboard - Super Easy and Very Useful
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 -
Boost Your Excel Productivity - Cool Tips and Shortcuts
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. -
A VLOOKUP Secret Revealed - See How to Impress Your audience
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. -
Flip Your List In Excel- Buy One Get Eight Free
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. -
VLookup Fired...XLookup Hired - The New Giant Is Here
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. -
A Stunning Chart Title Drop List...Impress Your Clients... Don't Miss it
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. -
Add Dynamic Leading Zero in Excel… 12 Ways from Basic to Power User
In this tutorial I show you 12 different methods for adding leading zeros in Excel. Whether you want the outcome to be left aligned as text or right aligned as number to be reused in other calculations… or even, you may want to control the number of leading zeros dynamically with a Scroll Bar.
Stay tuned as I go from Basic to advanced then to mind-blowing techniques. Watch the Excel “Difficulty Thermometer” at the beginning of each technique, so that you can evaluate your current skills as you learn.
You can Download the Exercise File and follow along by clicking on the link:
https://bit.ly/LeadingZero
Although I would recommend watching all the 12 methods since this video is packed with great Excel Tips & Tricks and nice Shortcuts, however, if you want to jump directly to a specific method or a higher level of difficulty, then here is the Time Stamp:
Method 1: Custom Format (Basic) 01:37
Method 2: Format As Text (Basic) 02:41
Method 3: Apostrophe (Basic) 03:21
Method 4: TEXT function (Intermediate) 04:07
Method 5: RIGHT function (Intermediate) 04:48
Method 6: CONCAT function (Intermediate) 06:05
Method 7: Column from Selection - P. Query (Advanced) 07:59
Method 8: Text.PadStart - P. Query (Advanced) 10:16
Method 9: Number.ToText - P. Query (Advanced) 12:50
Method 10: DAX function (Advanced) 16:33
Method 11: Scroll Bar (Power User) 20:21
Method 12: VBA Code (Power User) 25:14
Let me know in a comment how many methods you already knew and which is your preferred method?
To Book a Corporate Training (Remotely or at Client site)
www.OfficeInstructor.ca
To Book a Microsoft Office Specialist Exam
www.OfficeInstructor.com
To read my step by step articles visit:
www.See-How.ca
You can keep supporting my channel by sharing the video, by writing a comment and by subscribing to my channel… and ring the bell to be notified when new videos are released… The Best Is Yet To Come.
For the full list of tutorials...