Santipara Rd Railway Colony, Rampurhat, India - 731224.
Verified 
6
Details verified of Mohammad Khalid✕
Identity
Education
Know how UrbanPro verifies Tutor details
Identity is verified based on matching the details uploaded by the Tutor with government databases.
Intro Video
+2 more
Bengali Mother Tongue (Native)
English Proficient
Hindi Proficient
Urdu Proficient
Burdwan University 2020
Bachelor of Arts (B.A.)
Indira Gandhi National Open University 2023
Post Graduation Diploma in Mass Communication and Journalism
Learn with Lokesh 2023
Excel Masterclass Beginner to Advanced
Santipara Rd Railway Colony, Rampurhat, India - 731224
ID Verified
Education Verified
Phone Verified
Email Verified
Report this Profile
Is this listing inaccurate or duplicate? Any other problem?
Please tell us about the problem and we will fix it.
1 Student Class Location
Online Classes (Video Call via UrbanPro LIVE)
Student's Home
Tutor's Home
Years of Experience in Microsoft Excel Training classes
1
Teaches following Excel features
Excel Macro Training, Basic Excel, Advanced Excel
Teaching Experience in detail in Microsoft Excel Training classes
<p>Master Microsoft Excel - Advanced.</p> <p> </p> <p>What you'll learn...</p> <p>1. Learn all about syntax, arguments and logic.</p> <p>2. How to create custom and nested functions.</p> <p>3. Learn the fastest and smartest ways of cleaning the raw data</p> <p>4. Create dynamic reports by mastering one of the most popular tools, PivotTables.</p> <p>5. Learn which chart/graph to use and when.</p> <p>6. How to automate repetitive tasks in Excel using Macros.</p> <p>7. Learn various 'must have' Excel shortcuts.</p> <p>8. Learn how to manage heavy Excel files.</p> <p>9. Amazing Bonus tricks that save tons of time and effort.</p> <p> </p> <p>This Course will include</p> <p>1. Live ZOOM Classes</p> <p>2. Recordings of the Previous Classes (In case You Can't Attend LIVE Classes)</p> <p>4. One Live QnA Session every Week to clear all your doubts</p> <p>5. Downloadable Excel Files for Practice</p> <p>6. Access on Laptop, mobile and TV</p> <p> </p> <p>Course Duration - 29 days (can be customized accordingly)</p> <p>Language - Hindi, English, Bengali (Any Language Preferred by Student)</p> <p> </p> <p>Here is my Detailed Course Plan and Chapters</p> <p> </p> <p> LOOKUP FUNCTIONALITY (BURN SIMPLE FIND TECHNIQUE)</p> <p> </p> <p>Vlookup (Exact Match) # 1</p> <p>Exercise Vlookup (Exact Match) # 1</p> <p>Type of References (Use of $ Sign)</p> <p>Vlookup (Exact Match) # 2</p> <p>Exercise Vlookup (Exact Match) #2</p> <p>Double Vlookup</p> <p>Exercise Double Vlookup</p> <p>Vlookup on Duplicate Values</p> <p>Exercise Vlookup on Duplicate Values</p> <p>Vlookup (Approximate Match)</p> <p>Exercise Vlookup (Approximate Match)</p> <p>Vlookup with IF (Conditional Vlookup)</p> <p>Exercise Vlookup with IF (Conditional Vlookup)</p> <p>Hlookup (Exact Match)</p> <p>Exercise Hlookup (Exact Match)</p> <p>Hlookup (Approximate Match)</p> <p>Exercise Hlookup (Approximate Match)</p> <p>Lookup (Don't Use This)</p> <p>Exercise Lookup (Don't Use This)</p> <p>Vlookup & Match (Create magical Vlookup)</p> <p>Exercise Vlookup & Match ( Create a Magical Vlookup)</p> <p>Match (Gives us Column & Row Number)</p> <p>Exercise Match (Gives us Column & Row Number)</p> <p>Index & Match (Made for each other)</p> <p>Exercise number 1 of Index & Match (Made for each other)</p> <p>Exercise number 2 of Index & Match (Made for each other)</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p> DATE & TIME FUNCTION (A TRICKY GAME)</p> <p>How Excel Records Date & Timer</p> <p>Now, Today & Autofill</p> <p>DateValue & TimeValue</p> <p>Calculate WorkingDays</p> <p>DatedIf</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>FINANCIAL FUNCTIONS (USEFUL FOR NON-FINANCE GUYS TOO)</p> <p> </p> <p>Loan Calculation (PMT, PPMT, IPMT etc.)</p> <p>Ex Loan Calculation (PMT, PPMT, IPMT etc.)</p> <p>Creating a Loan Table</p> <p>Ex Creating a Loan Table</p> <p>Calculate Depreciation</p> <p>Ex Calculate Depreciation</p> <p>Excel Files for practice (Please Download)</p> <p> </p> <p>SORT & FILTER (COMMON FOR ALL)</p> <p> </p> <p>Basic Sorting & Shortcut keys</p> <p>Ex Basic Sorting & Shortcut Keys</p> <p>Sorting Using Custom List</p> <p>Ex Sorting Using Custom List</p> <p>Horizontal Sorting</p> <p>Ex Horizontal Sorting</p> <p>Basic Filter & Shortcut Keys</p> <p>Ex Basic Filter & Shortcut Keys</p> <p>Filter Problem # 1 (Copy Filtered Data without hidden rows)</p> <p>Ex Filter Problem # 1 (Copy Filtered Data without hidden Rows)</p> <p>Filter Problem # 2 (Pasting Values on Filtered Data)</p> <p>Ex Filter Problem # 2 (Pasting Values on Filtered Data)</p> <p>Filter Problem # 3 (Performing Calculation on Filtered Data)</p> <p>Ex Filter Problem # 3 (Perform Calculations on Filtered Data)</p> <p>Advanced Filter (Using Complex Criteria)</p> <p>Ex Advanced Filter (Using Complex Criteria)</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>EXCEL CHARTS (ITS TIME FOR VISUAL PRESENTATION)</p> <p> </p> <p>1. Excel Charts Introduction</p> <p>2 How to Create a Chart</p> <p>3 Link a Chart Title</p> <p>4 How to Show Axis Title</p> <p>5 How to Show Data Label</p> <p>6 Column Chart</p> <p>7 Bar Chart</p> <p>8 Line Chart 1</p> <p>9 Line Chart 2</p> <p>10 Area Chart</p> <p>11 Pie Chart</p> <p>12 Pie of Pie or Bar of Pie</p> <p>13 Line Chart with Log Scale</p> <p>14 Bubble Chart</p> <p>15 Selecting Chart Elements</p> <p>16 Use of Format Dialog box</p> <p>17 Modifying Chart & Plot Area</p> <p>18 Modifying Data Series</p> <p>19 How to handle Missing Data</p> <p>20 Modifying 3-D Charts</p> <p>21 Creating Chart Templates</p> <p>22 Creating Picture Charts</p> <p>23 Creating a Combination of upto 5 Different Charts</p> <p>24 Show data with the chart in Data Table</p> <p>25 Play with the Design & Layout of the Chart</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>DATA CONSOLIDATION (MULTIPLE WORKBOOKS WORKSHEETS)</p> <p> </p> <p>Consolidate Data using Excel Inbuilt Function</p> <p>Ex Consolidate data using Excel Inbuilt Function</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>DATA VALIDATION</p> <p> </p> <p>Applying Data Validation</p> <p>Ex Applying Data validation</p> <p>Dependent Dropdown List</p> <p>Ex Dependent Dropdown List</p> <p>Using Data Validation with Vlookup & Match</p> <p>Other Data Validation Examples</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>PROTECTION OF WORKSHEET WORKBOOK</p> <p> </p> <p>Worksheet Level Protection</p> <p>Ex Worksheet Level Protection</p> <p>Cell Level Protection</p> <p>Ex Cell Level Protection</p> <p>Hiding Formulas from Formula Bar</p> <p>Ex Hiding Formulas from Formula Bar</p> <p>Protecting Workbook Structure</p> <p>Ex Protecting Workbook Structure</p> <p>Workbook Level Protection</p> <p>Ex Workbook Level Protection</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>PIVOT TABLE & TECHNIQUES</p> <p> </p> <p>Understanding pivot Table</p> <p>Using Pivot Table in Real Scenario</p> <p>Ex Using Pivot Table in Real Scenario</p> <p>Grouping in Pivot Table</p> <p>Ex Grouping in Pivot Table</p> <p>Using Slicers (A Visually attractive Filter)</p> <p>Ex Using Slicers (A Visually attractive Filter)</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>WHAT-IF ANALYSIS & SOLVER ADD-IN</p> <p> </p> <p>Goal Seek (A Reverse Approach)</p> <p>Ex Goal Seek (A Reverse Approach)</p> <p>One-Way Data Table</p> <p>Two-Way Data Table</p> <p>Scenario Manager</p> <p>Solver Add-In</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>16 AUTOMATION IN EXCEL (MACROS)</p> <p> </p> <p>Record your first Macro</p> <p>Record Another Macro</p> <p>Assigning Macros to a Shape, Button, Picture</p> <p>Editing or Deleting a Macro</p> <p>Security Settings of a Macro</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>17. New Video Tutorials</p> <p> </p> <p>*How to create a Map Chart in Excel</p> <p>*How to use the Forecast sheet in Excel</p> <p>*Excel Files for Practice (Please Download)</p> <p> </p> <p>New Chapter - Conditional Formatting</p> <p> </p> <p>*Highlight cells rules</p> <p>*Top Bottom Rules</p> <p>*Data Bars</p> <p>*Color Scales</p> <p>*Icon Sets</p> <p>*Function based Conditional Formatting</p> <p>*Exercise files for practice (please download)</p> <p> </p> <p>Bonus Videos (In Hindi)</p> <p>*Why my excel file size is huge and how to reduce Excel file size</p> <p>*Problem of unwanted names when we create a copy of the sheet in Excel</p> <p>*New chart types in Excel 2019/Office 365 || Map Chart || Funnel Chart</p> <p>*How do you change the number format in Excel?</p> <p> </p> <p>Types of Errors in Excel and How to resolve them</p> <p> </p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>Other Experiences -</p> <p>1. Taught, Motivated Students</p> <p>2. Public Speaker</p> <p>3. Conducted Webinars</p> <p>4. Conducted Students Career Counseling</p> <p> </p> <p>Let's discuss your specific needs and schedule a session at your convenience.</p> <p> </p> <p>Best regards,</p> <p>Md Khalid</p>
5 out of 5 3 reviews
Abida Khatun
"Teaching style of Khalid sir is very nice. I didn't even opened Ms office applications before I got his training. now I'm expert in Ms excel. He makes you understand complex functions in very simple way. "
Reply by Mohammad
Thanks for your Review.
Amit
Microsoft Excel Training
I loved
Audio/Video Quality
Class Content
Teaching Method
Teacher's Knowledge
Jitendra Kumar verma
Excel Masterclass: Beginner to Advanced
I loved
Audio/Video Quality
Teaching Method
Teacher's Knowledge
1. What Excel features do you teach?
Excel Macro Training, Basic Excel and Advanced Excel
2. Which classes do you teach?
I teach Microsoft Excel Training Class.
3. Do you provide a demo class?
Yes, I provide a free demo class.
4. How many years of experience do you have?
I have been teaching for 1 year.
Answered on 23/05/2024 Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel INDEX
let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given.
Now, we have this small table in front of us in which we have a list of expenses as well as for which particular column we need to find out the value for. First we need to find out the code for grocery, home phone, cable TV and tuition. Same we need to find out the expenses related to these expenses, these items in quarter 2, 3 and 4. If you see one thing that we are talking about code right now.
If you look at it, expenses are on column D and technically this is the third column in the data and code is the second column. So, this is basically a reverse pre-lookup or reverse lookup we are talking about. Now here, first I am going to show you that what INDEX do.
You know about the MATCH already. The MATCH function is that if I assign a MATCH function, just have a look. First I am going to break down the formula for you to make it easier for you to understand and then we will club it in a single cell.
So have a look. I am going to use MATCH first and then we will club it with INDEX. So, I am just creating a small table here which is grocery and code.
Now what is the position of grocery in this range? I do not know about it. So, in that case as you know we are going to use MATCH. Lookup value will be grocery comma lookup array that in which column you need to find out, you need to match this grocery word.
So I am going to click on this and I am going to say control shift down arrow and then select it till the end. I will press F4 which is somehow not required because we need not to copy and paste it somewhere comma MATCH type will be zero. So I will close down the bracket now and when I press enter, see this.
It is coming at the third position from expenses. Same thing I need to find out that where this code word is coming in this complete heading. So, I am going to say equals to MATCH lookup value will be code comma lookup array will be from this cell control shift right arrow till the end comma zero.
Bracket close and enter. See this. It is telling us that it is coming at the second position.
So, grocery and code the position are there with us. Now we need to find out the value which is the code for grocery. This is what we need to do here.
So we have two matches over here and now we are going to apply index. So index is a very simple formula guys. If you know the column number like this and row number in that case you just need to assign the complete database and you need to put the row number as well as column number in index formula and it will give you the cross value out of that.
I will show you equals to index concentrate on the first syntax which is array with row number and column number. So array is the complete range which is this cell then control shift down arrow control shift right arrow. So, I will freeze it comma now it is asking for a row number through grocery we found out the row number.
The row number for the grocery is three comma column number is this two which column we are talking about that is a code column. So bracket close and enter. See this.
The code is coming which is RG S01 O2. So that is the grocery the code for the grocery is the same that we are getting the answer. I hope this is clear to you a bit and I would really suggest you do a lot of practice on this.
Now let me club this over here and then we can copy and paste it to what's right and down and we'll get all the values for this matrix. So I'm going to say equals to index array means the complete database that I have told you and I really want you to do one thing. I really suggest you to always select it from the very first cell that must be the heading.
So it should not confuse by the counting. So I'll select it by pressing control shift down arrow as well as control shift right arrow. I'll freeze it because I need to copy and paste it down by pressing F4 key on my keyboard function F4 key comma.
Now I do not know the row number. So I'm going to say I'm going to put match tab. Now look up value for this match.
Remember this. We are standing on row number right now. Just have a row number.
You cannot shuffle this because whatever the syntax is you have to follow that exactly like that. So I'm going to say first match and I'm going to find out the row number using this match. So look up value will be grocery comma look up array will be this expenses one because we know that the grocery home phone and all the things are here in this column and representing a particular row.
So that is where this is going to be our row number. So control shift down arrow and we'll select it till the end and I'm going to press F4 again comma zero then we'll close on the bracket for the first match and we'll get the row number now and as I told you earlier also we have a shortcut to see that what would be the value of this match so you can actually select it like this. So I've selected it and now if I press F9 function F9 key on my keyboard it will tell me the result which is three and you can see it as correct that what we will get.
Now I'll have to press control Z so that will show me the formula again now column number. So column number is basically the code right now code column. So for that again we have to put match tab now the lookup value for this match will be this word code comma lookup array will be this complete range from here till control shift right arrow till here.
So I'm going to freeze it by pressing F4 key on my keyboard comma zero. So first bracket will close down for match and the other one is for index. So we are done with our formula now.
So you can see one index and two matches in between. One thing we need to freeze in this B7 which is this cell as I have discussed earlier this kind of freezing and we look up and match video so you can refer that and here we are going to freeze or put a dollar sign just before B because I need to copy and paste it towards right and I don't want to change the color. And over here C6 I need to freeze the row number six.
So I'm going to put a dollar sign just before six. So when I copy and paste it towards down it should not change the row. So now if I press enter we are getting the result which is RGS0102 that's one.
And now when I copy it and I press shift and right arrow and down arrow and when I press enter you see that we are getting the results for all. So this is how it's a two way lookup. It is one of the most useful combination in lookup functionality.
I would really prefer that you should use index and match anywhere in your work wherever you are using VLOOKUP because somehow whatever VLOOKUP can do index and match can easily do as well as something which VLOOKUP cannot do can be done using index and match. So that is this is kind of an ultimate formula in lookup functionality. So I hope you enjoyed this video.
I again I'm saying that you need to do some practice. I know we are going ahead and it is kind of a complex function but it really needs some practice and you'll feel very much comfortable when you do it couple of times. That is why in this course I have created two exercise files for this particular function so that you can get a quick hands on on that.
So just open the next file which is exercise 0613 index and match and will do it and it has two part part one and part two. So it has two examples. So you have ample of exercises for you to do it.
Thank you.
Answered on 16/03/2024 Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel Dashboard
Step 1-Select the table including the heading
2. Click on Insert Tab
3. Recommended Charts
4. All charts
5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK.
If you want to learn more, book a Demo class with me. Thanks.
1 Student Class Location
Online Classes (Video Call via UrbanPro LIVE)
Student's Home
Tutor's Home
Years of Experience in Microsoft Excel Training classes
1
Teaches following Excel features
Excel Macro Training, Basic Excel, Advanced Excel
Teaching Experience in detail in Microsoft Excel Training classes
<p>Master Microsoft Excel - Advanced.</p> <p> </p> <p>What you'll learn...</p> <p>1. Learn all about syntax, arguments and logic.</p> <p>2. How to create custom and nested functions.</p> <p>3. Learn the fastest and smartest ways of cleaning the raw data</p> <p>4. Create dynamic reports by mastering one of the most popular tools, PivotTables.</p> <p>5. Learn which chart/graph to use and when.</p> <p>6. How to automate repetitive tasks in Excel using Macros.</p> <p>7. Learn various 'must have' Excel shortcuts.</p> <p>8. Learn how to manage heavy Excel files.</p> <p>9. Amazing Bonus tricks that save tons of time and effort.</p> <p> </p> <p>This Course will include</p> <p>1. Live ZOOM Classes</p> <p>2. Recordings of the Previous Classes (In case You Can't Attend LIVE Classes)</p> <p>4. One Live QnA Session every Week to clear all your doubts</p> <p>5. Downloadable Excel Files for Practice</p> <p>6. Access on Laptop, mobile and TV</p> <p> </p> <p>Course Duration - 29 days (can be customized accordingly)</p> <p>Language - Hindi, English, Bengali (Any Language Preferred by Student)</p> <p> </p> <p>Here is my Detailed Course Plan and Chapters</p> <p> </p> <p> LOOKUP FUNCTIONALITY (BURN SIMPLE FIND TECHNIQUE)</p> <p> </p> <p>Vlookup (Exact Match) # 1</p> <p>Exercise Vlookup (Exact Match) # 1</p> <p>Type of References (Use of $ Sign)</p> <p>Vlookup (Exact Match) # 2</p> <p>Exercise Vlookup (Exact Match) #2</p> <p>Double Vlookup</p> <p>Exercise Double Vlookup</p> <p>Vlookup on Duplicate Values</p> <p>Exercise Vlookup on Duplicate Values</p> <p>Vlookup (Approximate Match)</p> <p>Exercise Vlookup (Approximate Match)</p> <p>Vlookup with IF (Conditional Vlookup)</p> <p>Exercise Vlookup with IF (Conditional Vlookup)</p> <p>Hlookup (Exact Match)</p> <p>Exercise Hlookup (Exact Match)</p> <p>Hlookup (Approximate Match)</p> <p>Exercise Hlookup (Approximate Match)</p> <p>Lookup (Don't Use This)</p> <p>Exercise Lookup (Don't Use This)</p> <p>Vlookup & Match (Create magical Vlookup)</p> <p>Exercise Vlookup & Match ( Create a Magical Vlookup)</p> <p>Match (Gives us Column & Row Number)</p> <p>Exercise Match (Gives us Column & Row Number)</p> <p>Index & Match (Made for each other)</p> <p>Exercise number 1 of Index & Match (Made for each other)</p> <p>Exercise number 2 of Index & Match (Made for each other)</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p> DATE & TIME FUNCTION (A TRICKY GAME)</p> <p>How Excel Records Date & Timer</p> <p>Now, Today & Autofill</p> <p>DateValue & TimeValue</p> <p>Calculate WorkingDays</p> <p>DatedIf</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>FINANCIAL FUNCTIONS (USEFUL FOR NON-FINANCE GUYS TOO)</p> <p> </p> <p>Loan Calculation (PMT, PPMT, IPMT etc.)</p> <p>Ex Loan Calculation (PMT, PPMT, IPMT etc.)</p> <p>Creating a Loan Table</p> <p>Ex Creating a Loan Table</p> <p>Calculate Depreciation</p> <p>Ex Calculate Depreciation</p> <p>Excel Files for practice (Please Download)</p> <p> </p> <p>SORT & FILTER (COMMON FOR ALL)</p> <p> </p> <p>Basic Sorting & Shortcut keys</p> <p>Ex Basic Sorting & Shortcut Keys</p> <p>Sorting Using Custom List</p> <p>Ex Sorting Using Custom List</p> <p>Horizontal Sorting</p> <p>Ex Horizontal Sorting</p> <p>Basic Filter & Shortcut Keys</p> <p>Ex Basic Filter & Shortcut Keys</p> <p>Filter Problem # 1 (Copy Filtered Data without hidden rows)</p> <p>Ex Filter Problem # 1 (Copy Filtered Data without hidden Rows)</p> <p>Filter Problem # 2 (Pasting Values on Filtered Data)</p> <p>Ex Filter Problem # 2 (Pasting Values on Filtered Data)</p> <p>Filter Problem # 3 (Performing Calculation on Filtered Data)</p> <p>Ex Filter Problem # 3 (Perform Calculations on Filtered Data)</p> <p>Advanced Filter (Using Complex Criteria)</p> <p>Ex Advanced Filter (Using Complex Criteria)</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>EXCEL CHARTS (ITS TIME FOR VISUAL PRESENTATION)</p> <p> </p> <p>1. Excel Charts Introduction</p> <p>2 How to Create a Chart</p> <p>3 Link a Chart Title</p> <p>4 How to Show Axis Title</p> <p>5 How to Show Data Label</p> <p>6 Column Chart</p> <p>7 Bar Chart</p> <p>8 Line Chart 1</p> <p>9 Line Chart 2</p> <p>10 Area Chart</p> <p>11 Pie Chart</p> <p>12 Pie of Pie or Bar of Pie</p> <p>13 Line Chart with Log Scale</p> <p>14 Bubble Chart</p> <p>15 Selecting Chart Elements</p> <p>16 Use of Format Dialog box</p> <p>17 Modifying Chart & Plot Area</p> <p>18 Modifying Data Series</p> <p>19 How to handle Missing Data</p> <p>20 Modifying 3-D Charts</p> <p>21 Creating Chart Templates</p> <p>22 Creating Picture Charts</p> <p>23 Creating a Combination of upto 5 Different Charts</p> <p>24 Show data with the chart in Data Table</p> <p>25 Play with the Design & Layout of the Chart</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>DATA CONSOLIDATION (MULTIPLE WORKBOOKS WORKSHEETS)</p> <p> </p> <p>Consolidate Data using Excel Inbuilt Function</p> <p>Ex Consolidate data using Excel Inbuilt Function</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>DATA VALIDATION</p> <p> </p> <p>Applying Data Validation</p> <p>Ex Applying Data validation</p> <p>Dependent Dropdown List</p> <p>Ex Dependent Dropdown List</p> <p>Using Data Validation with Vlookup & Match</p> <p>Other Data Validation Examples</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>PROTECTION OF WORKSHEET WORKBOOK</p> <p> </p> <p>Worksheet Level Protection</p> <p>Ex Worksheet Level Protection</p> <p>Cell Level Protection</p> <p>Ex Cell Level Protection</p> <p>Hiding Formulas from Formula Bar</p> <p>Ex Hiding Formulas from Formula Bar</p> <p>Protecting Workbook Structure</p> <p>Ex Protecting Workbook Structure</p> <p>Workbook Level Protection</p> <p>Ex Workbook Level Protection</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>PIVOT TABLE & TECHNIQUES</p> <p> </p> <p>Understanding pivot Table</p> <p>Using Pivot Table in Real Scenario</p> <p>Ex Using Pivot Table in Real Scenario</p> <p>Grouping in Pivot Table</p> <p>Ex Grouping in Pivot Table</p> <p>Using Slicers (A Visually attractive Filter)</p> <p>Ex Using Slicers (A Visually attractive Filter)</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>WHAT-IF ANALYSIS & SOLVER ADD-IN</p> <p> </p> <p>Goal Seek (A Reverse Approach)</p> <p>Ex Goal Seek (A Reverse Approach)</p> <p>One-Way Data Table</p> <p>Two-Way Data Table</p> <p>Scenario Manager</p> <p>Solver Add-In</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>16 AUTOMATION IN EXCEL (MACROS)</p> <p> </p> <p>Record your first Macro</p> <p>Record Another Macro</p> <p>Assigning Macros to a Shape, Button, Picture</p> <p>Editing or Deleting a Macro</p> <p>Security Settings of a Macro</p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>17. New Video Tutorials</p> <p> </p> <p>*How to create a Map Chart in Excel</p> <p>*How to use the Forecast sheet in Excel</p> <p>*Excel Files for Practice (Please Download)</p> <p> </p> <p>New Chapter - Conditional Formatting</p> <p> </p> <p>*Highlight cells rules</p> <p>*Top Bottom Rules</p> <p>*Data Bars</p> <p>*Color Scales</p> <p>*Icon Sets</p> <p>*Function based Conditional Formatting</p> <p>*Exercise files for practice (please download)</p> <p> </p> <p>Bonus Videos (In Hindi)</p> <p>*Why my excel file size is huge and how to reduce Excel file size</p> <p>*Problem of unwanted names when we create a copy of the sheet in Excel</p> <p>*New chart types in Excel 2019/Office 365 || Map Chart || Funnel Chart</p> <p>*How do you change the number format in Excel?</p> <p> </p> <p>Types of Errors in Excel and How to resolve them</p> <p> </p> <p>Excel Files for Practice (Please Download)</p> <p> </p> <p>Other Experiences -</p> <p>1. Taught, Motivated Students</p> <p>2. Public Speaker</p> <p>3. Conducted Webinars</p> <p>4. Conducted Students Career Counseling</p> <p> </p> <p>Let's discuss your specific needs and schedule a session at your convenience.</p> <p> </p> <p>Best regards,</p> <p>Md Khalid</p>
5 out of 5 3 reviews
Abida Khatun
"Teaching style of Khalid sir is very nice. I didn't even opened Ms office applications before I got his training. now I'm expert in Ms excel. He makes you understand complex functions in very simple way. "
Reply by Mohammad
Thanks for your Review.
Amit
Microsoft Excel Training
I loved
Audio/Video Quality
Class Content
Teaching Method
Teacher's Knowledge
Jitendra Kumar verma
Excel Masterclass: Beginner to Advanced
I loved
Audio/Video Quality
Teaching Method
Teacher's Knowledge
Answered on 23/05/2024 Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel INDEX
let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given.
Now, we have this small table in front of us in which we have a list of expenses as well as for which particular column we need to find out the value for. First we need to find out the code for grocery, home phone, cable TV and tuition. Same we need to find out the expenses related to these expenses, these items in quarter 2, 3 and 4. If you see one thing that we are talking about code right now.
If you look at it, expenses are on column D and technically this is the third column in the data and code is the second column. So, this is basically a reverse pre-lookup or reverse lookup we are talking about. Now here, first I am going to show you that what INDEX do.
You know about the MATCH already. The MATCH function is that if I assign a MATCH function, just have a look. First I am going to break down the formula for you to make it easier for you to understand and then we will club it in a single cell.
So have a look. I am going to use MATCH first and then we will club it with INDEX. So, I am just creating a small table here which is grocery and code.
Now what is the position of grocery in this range? I do not know about it. So, in that case as you know we are going to use MATCH. Lookup value will be grocery comma lookup array that in which column you need to find out, you need to match this grocery word.
So I am going to click on this and I am going to say control shift down arrow and then select it till the end. I will press F4 which is somehow not required because we need not to copy and paste it somewhere comma MATCH type will be zero. So I will close down the bracket now and when I press enter, see this.
It is coming at the third position from expenses. Same thing I need to find out that where this code word is coming in this complete heading. So, I am going to say equals to MATCH lookup value will be code comma lookup array will be from this cell control shift right arrow till the end comma zero.
Bracket close and enter. See this. It is telling us that it is coming at the second position.
So, grocery and code the position are there with us. Now we need to find out the value which is the code for grocery. This is what we need to do here.
So we have two matches over here and now we are going to apply index. So index is a very simple formula guys. If you know the column number like this and row number in that case you just need to assign the complete database and you need to put the row number as well as column number in index formula and it will give you the cross value out of that.
I will show you equals to index concentrate on the first syntax which is array with row number and column number. So array is the complete range which is this cell then control shift down arrow control shift right arrow. So, I will freeze it comma now it is asking for a row number through grocery we found out the row number.
The row number for the grocery is three comma column number is this two which column we are talking about that is a code column. So bracket close and enter. See this.
The code is coming which is RG S01 O2. So that is the grocery the code for the grocery is the same that we are getting the answer. I hope this is clear to you a bit and I would really suggest you do a lot of practice on this.
Now let me club this over here and then we can copy and paste it to what's right and down and we'll get all the values for this matrix. So I'm going to say equals to index array means the complete database that I have told you and I really want you to do one thing. I really suggest you to always select it from the very first cell that must be the heading.
So it should not confuse by the counting. So I'll select it by pressing control shift down arrow as well as control shift right arrow. I'll freeze it because I need to copy and paste it down by pressing F4 key on my keyboard function F4 key comma.
Now I do not know the row number. So I'm going to say I'm going to put match tab. Now look up value for this match.
Remember this. We are standing on row number right now. Just have a row number.
You cannot shuffle this because whatever the syntax is you have to follow that exactly like that. So I'm going to say first match and I'm going to find out the row number using this match. So look up value will be grocery comma look up array will be this expenses one because we know that the grocery home phone and all the things are here in this column and representing a particular row.
So that is where this is going to be our row number. So control shift down arrow and we'll select it till the end and I'm going to press F4 again comma zero then we'll close on the bracket for the first match and we'll get the row number now and as I told you earlier also we have a shortcut to see that what would be the value of this match so you can actually select it like this. So I've selected it and now if I press F9 function F9 key on my keyboard it will tell me the result which is three and you can see it as correct that what we will get.
Now I'll have to press control Z so that will show me the formula again now column number. So column number is basically the code right now code column. So for that again we have to put match tab now the lookup value for this match will be this word code comma lookup array will be this complete range from here till control shift right arrow till here.
So I'm going to freeze it by pressing F4 key on my keyboard comma zero. So first bracket will close down for match and the other one is for index. So we are done with our formula now.
So you can see one index and two matches in between. One thing we need to freeze in this B7 which is this cell as I have discussed earlier this kind of freezing and we look up and match video so you can refer that and here we are going to freeze or put a dollar sign just before B because I need to copy and paste it towards right and I don't want to change the color. And over here C6 I need to freeze the row number six.
So I'm going to put a dollar sign just before six. So when I copy and paste it towards down it should not change the row. So now if I press enter we are getting the result which is RGS0102 that's one.
And now when I copy it and I press shift and right arrow and down arrow and when I press enter you see that we are getting the results for all. So this is how it's a two way lookup. It is one of the most useful combination in lookup functionality.
I would really prefer that you should use index and match anywhere in your work wherever you are using VLOOKUP because somehow whatever VLOOKUP can do index and match can easily do as well as something which VLOOKUP cannot do can be done using index and match. So that is this is kind of an ultimate formula in lookup functionality. So I hope you enjoyed this video.
I again I'm saying that you need to do some practice. I know we are going ahead and it is kind of a complex function but it really needs some practice and you'll feel very much comfortable when you do it couple of times. That is why in this course I have created two exercise files for this particular function so that you can get a quick hands on on that.
So just open the next file which is exercise 0613 index and match and will do it and it has two part part one and part two. So it has two examples. So you have ample of exercises for you to do it.
Thank you.
Answered on 16/03/2024 Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel Dashboard
Step 1-Select the table including the heading
2. Click on Insert Tab
3. Recommended Charts
4. All charts
5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK.
If you want to learn more, book a Demo class with me. Thanks.
Want to learn from Mohammad?
Share this Profile
Recommended Profiles
Also have a look at
Reply to 's review
Enter your reply*
Your reply has been successfully submitted.
Certified
The Certified badge indicates that the Tutor has received good amount of positive feedback from Students.
Pimple Saudagar, Pune 
