The Magic of Power Query & Hidden Pivot Table Trick. Don't Miss itIn 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:
Set an Expiration Date to your Excel FileThere are different situations where you need to set an Expiry date to your Excel File and prevent a user from working on it.
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
2- Calculated Expiry Date
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 NavigationWhen 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:
To watch the tutorial in which I use Functions Click on the Link:
To watch the tutorial in which I use a VBA Code Click on the Link:
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 TricksWe 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:
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. VBAIn 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:
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:
Dim MyRecords As Integer
Dim FixRange As Range
Dim PasteCell As Integer
Dim Loopcounter As Integer
MyRecords = Range("B1", Range("B1").End(xlDown)).Count
For Loopcounter = 1 To MyRecords Step 10
Set FixRange = Range(Cells(Loopcounter, 2), Cells(Loopcounter, 2).Offset(9, 0))
PasteCell = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row + 1
Cells(PasteCell, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
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:
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
Don’t forget to hit the Thumb up and the big subscribe button.
Create a Searchable Drop Down List in ExcelA 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:
Don't forget to subscribe to be notified when new videos are released.
Pivot Table with Progress Chart and DashboardIn 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 from my website www.OfficeInstructor.ca by going to Downloads
Here is the link http://www.officeinstructor.ca/downloads.html
Watch How to create a searchable drop list in Excel Just like Google
Exchange Rates are now LIVE ... Currency Converter with Power Query, Stocks Data Type & MacroCurrency 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:
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
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/)
OFFSET function to change Source Data for a Dynamic ChartWe 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:
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:
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:
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 usefulFiltering 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:
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.