Posts

How to create Multiple Dependent Dynamic Drop-Down List in Excel ?

Image
  How to create Multiple Dependent Dynamic Drop-Down List in Excel ? DYNAMIC DROP-DOWN LISTS IN EXCEL A drop-down list is an excellent way to give the user an option to select from a pre-defined list. Worksheet used in the video: https://drive.google.com/file/d/1lrXvpWaaqtKwtjKo4mSJTPYsB3vow1TH/view?usp=sharing A drop-down list that is dynamic, can expand or contract depending on changes to the source data. Learn how to create a drop-down list in a cell using the new UNIQUE function to make the list dynamic. UNIQUE FUNCTION UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range. In other words, it removes duplicates from a data set. Learn how to create a drop-down list in a cell using the new FILTER function to make the DEPENDENT Drop-down list dynamic. FILTER FUNCTION The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria. Note- In the SOURCE FIELD(of Data Validation), Instead of id
Post a Comment
Read more

How to create a Macro Button in Excel?

  How to create a Macro Button in Excel? How to insert button in Ms Excel? MACROS IN EXCEL -  https://youtu.be/tvw6rPbJHtc If you have tasks in Microsoft Excel that you do repeatedly, you can record a  macro  to automate those tasks. A  macro  is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. Follow this link to get worksheet used in the tutorial: https://drive.google.com/file/d/1JAqx1ADxHbhPcOHNhS1dDiyYXRfkY7a3/view?usp=sharing # Some important point to remember before creating a Macro- ∆ If you want to save macros, then you must save your workbook in a macro-enabled format *.xlsm ∆ The macro name should not contain any spaces. ∆ Always fill in the description of the macro when creating one. This will help you and others to understand what the macro is doing. MACRO BUTTONS Macros make life easier. Buttons make life easier.  You can literally run through a hundred steps with just one
Post a Comment
Read more

Vlookup Function in Ms Excel

Image
  Vlookup Function in Ms Excel How to find Duplicate values with Vlookup? How to Use vlookup To find Repeated Values? Vlookup Duplicate Repeated values in Excel Follow this link to get workbook used in the video: https://drive.google.com/file/d/1zsTPjzz6STq0Bi5otmz2XGDnHNfobwN5/view?usp=sharing We can solve the Duplicate VLOOKUP function problem with COUNTIF formula that creates a unique ID as the lookup column. Once this Helper column is created, we can then use it to lookup the duplicate values. If a name is repeated many times in your data and you need to find the values against that name with vlookup then This excel tutorial Will help you How to Lookup Repeated Data in Excel. Vlookup Function: VLOOKUP FUNCTION STANDS FOR VERTICAL LOOKUP. It looks for a value in the leftmost column of a table. It has four components: 1. VALUE - The value you want to lookup from your table. 2. RANGE- The range in which you want to find the valu
Post a Comment
Read more

PIVOT TABLE IN MS EXCEL

Image
PIVOT TABLE IN MS EXCEL Pivot Table with Detailed Explanation: https://youtu.be/52xH2t9IGiQ  Follow this link to get workbook used in the video: https://drive.google.com/file/d/1Q2FCcojb3BoU-nyA55DXBiJpwDWdFIry/view?usp=sharing Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. A Pivot Table is used to summarise, sort, reorganise, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them. GROUP OR UNGROUP IN PIVOT TABLE: Learn how to group products and how to group dates by quarters, months or years. Group selected items 1 Hold CTRL key and select two or more values 2 Right click of mouse and select group Ungroup selected items 1 Right click any item that is in that group 2 Select Ungroup Watch the video till the end to learn everything in d
Post a Comment
Read more

Vlookup with match function in Excel

 Vlookup with match function in Excel VLOOKUP FUNCTION IN MICROSOFT EXCEL VLOOKUP FUNCTION STANDS FOR VERTICAL LOOKUP. It looks for a value in the leftmost column of a table. It has four components: 1. VALUE - The value you want to lookup from your table. 2. RANGE- The range in which you want to find the value. 3. COLUMN NUMBER- The number of column within your defined range, that contains the return value. 4. TRUE or FALSE- False or 0 means Exact match and True or 1 means approximate match #Different ways of using Vlookup function in excel https://youtu.be/CmowjVmGRiw #Match Function in MS Excel https://youtu.be/yRy_AdWXeIg Follow this link to get worksheet used in the video- https://drive.google.com/file/d/1XotTUZ7uZt9oPqQUYA28gEOEXQ_Phcj5/view?usp=sharing MATCH FUNCTION IN EXCEL Learn Match Function in Excel with the help of examples. The MATCH function searches for a specified item in a range of c
Post a Comment
Read more

Match function in Ms Excel

 Match function in Ms Excel MATCH FUNCTION IN EXCEL Learn Match Function in Excel with the help of examples. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. The MATCH formula uses the following arguments: Lookup_value (required argument) – This is the value that we want to look up. Lookup_array (required argument) – The data array that is to be searched. Match_type (optional argument) – It can be set to 1, 0, or -1 to return results. Follow this link to get worksheet used in the video https://drive.google.com/file/d/1-gSdzZ2HuhC2L-GOEiYEczK-JDf6VjGH/view?usp=sharing Watch the video till the end to learn everything in detail.If you have any doubts regarding this video or any other video, do let me know in the comment section. Please LIKE 👍🏻 SHARE 🤞🏻 & SUBSCRIBE 👆🏻MY CHANNEL to get notifications of all latest videos ❤️❤️ Follow me at: Facebook- https://bit.ly/2YKAOSy Telegram- htt
Post a Comment
Read more

Vlookup function in Ms Excel

Image
 Vlookup function in Ms Excel VLOOKUP FUNCTION IN MICROSOFT EXCEL VLOOKUP FUNCTION STANDS FOR VERTICAL LOOKUP. It looks for a value in the leftmost column of a table. It has four components: 1. VALUE - The value you want to lookup from your table. 2. RANGE- The range in which you want to find the value. 3. COLUMN NUMBER- The number of column within your defined range, that contains the return value. 4. TRUE or FALSE- False or 0 means Exact match and True or 1 means approximate match Follow this link to get Worksheet used in the video - https://drive.google.com/file/d/1KhigpxV9AnNVEd9dr3SVOPuDwIO6ON7O/view?usp=sharing Watch the video till the end to learn everything in detail.If you have any doubts regarding this video or any other video, do let me know in the comment section. Please LIKE 👍🏻 SHARE 🤞🏻 & SUBSCRIBE 👆🏻MY CHANNEL to get notifications of all latest videos ❤️❤️ Follow me at: Fac
Post a Comment
Read more

Multiple Dependent Drop-down list in Excel

Image
Multiple Dependent Drop-down list in Excel Multiple Drop-down list in Ms Excel means that if you create a drop-down list then the other drop-down list will be dependent on the first drop-down list. Let's understand with the help of an example, I have different brands of Mobiles and their different models. So if I select any mobile from my first drop-down list then in second drop-down list which is of different Model of all the brands of Mobiles, I can see only the models of that particular mobile. If I select SAMSUNG in first drop-down list then in second drop-down list i will see only the models of SAMSUNG Mobile. Let's learn everything about Multiple dependent drop-down list in Excel step by step. Watch the video till the end to learn everything about Multiple dependent drop-down list in Ms Excel. We will learn step by step, how to create drop-down lists. Today we are going to learn 2 ways of creating Multiple dependent drop-down lists. It would be very helpful for you.  I
Post a Comment
Read more

Complete Data Validation

Image
Complete Data Validation Hello Everyone, Welcome to Learn Xtra 😍 In this video, We'll learn Data Validation. We'll use some formulas also like Istext, Isnumber, etc. Learn how to use Custom, Date, Text Length, Decimal, etc in Data Validation. You just need to select the area when you want to edit and then go to DATA Tab and select Data Validation and then go to settings and from *Allow* select your desired word like Text Length, date, list, etc. Let's learn all these one by one- A) Text Length- It shows that what would be the length of your data. It can be either number or any text. B) Custom- You can type any kind of formula in custom. Like Isnumber formula, Istext formula and so on. C) Date- In date, you can select any date whatever you want. You can select 'Equal to' for selecting a particular data or You can select 'between' for selecting as many dates as you want between the 2 dates typed by you. D) List- You can make a list of texts or. Numbe
Post a Comment
Read more