Faysal Easy Excel

This function is available in: MS Excel 2007 and above version, MS Excel 2011 for Mac and above version, Android, iPhone, Online etc. There are 7 popular lookup functions in MS Excel. One of them is Choose(). This function returns a value from a list (maximum 254 items in a list) whose nth number has given. This function requires a position number and a list of range. Then the function will return the value from list of range by using the mentioned position number. Functions Structure: The structure of this function is: =CHOOSE(index_num,value1,value2,value3,......,value254) or =CHOOSE(index_num,list_range) There are 2 main arguments or parts in this function. These are: (a) index_num: Need to mention a position number 1 to 254 to return that position's value. (b) value1: This is the 1st item name of list. And this could be maximum value254. You can use a range as list_range also. Examples: Assume that, below is sample table B3:C8 and you are reque

HLookup function (Horizontal Lookup) is commonly used in MS Excel to get values from a data table by matching a value in lookup row and return the value from the matched column. The match type can be define as Exactly match or Approximate match. It works like an English T shape. It matches a value in a row and returned value from another row of same column where value matched. Function Structure: The structure of this function is: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) There are 4 arguments or parameters in this function. The first 3 arguments or parameters are essential. That means if you want to use HLookup function, then you must mention the first 3 parameters. And the last parameter is optional. By default the last parameter is set to True (Approximate match). But for a better result you need to set the 4th parameter. All of these parameters are briefed here: lookup_value: Need to mention which value to lookup. table_array: Range of data tab

There are 4 different ways in Module to call a procedure. To show it how it works, a Module taken from Insert | Module as Module1 and again taken from the same way Module2 in VBA Environment. Way 1 - Written in Module1: Simple procedure calling in a Module: Sub new_player () Range( "A1" ).Value = "Balak" End Sub Way 2 - Written in Module1: Call a procedure withing another procedure in the same Module: Sub player_come_coach ()    new_player End Sub Way 3 - Written in Module1: Call a procedure withing another procedure but new calling technique in the same Module: Sub coachRetired ()    Call new_player    Range( "H1" ).Value = "Was a Captain of Germany Football Team" End Sub Note: You can test your procedure in Immediate window. Image 1: Calling a procedure within a same Module (Module1) Way 4 - Written in Module 2: Call a procedure withing another procedure from different Module: Sub coachR

There are 2 types of Procedures in Visual Basic. These are, Public and Private. Image 1: Types of Procedure Public Procedure: Public procedure is accessible from any module of the same workbook. By default procedure is Public Procedure in Excel VBA. If you run the macro dialog box, then you will see the Macro list which is recorded or written as Public procedure. Image 2: Two different types of Public Procedure Example Public Procedure can accessible from any other module or any others opened workbook. If we write with a Sub code then by default it counted as Public Procedure. Public Procedure always displayed in Macro list box: Image 3: Public Procedure always shows in Macro list Private Procedure: Private procedure is only accessible with the same module where the private sub procedures present in the same module of the same workbook. You will see the Private procedures only while you select the Personal workbook from macro dialog box. Image 4: Private Procedure Examp

The Lookup function is used for finding a specific result from a single row or a single column, based on a lookup_value with same range of single row or single column. It does not allow 2 or more rows or columns range to find the lookup_value or returned value like others lookup function VLookup, HLookup do. Lookup Function: The structure of this function is: =LOOKUP( lookup_value , lookup_vector , result_vector ) lookup_value:   The value which you want to look up in a specific row or column range. lookup_vector: It is a single row or single column range that contains the values to be looked up. These values must be in ascending order. That means before using this Lookup you must sort the lookup_vector range as Ascending order. Descending order will return the incorrect value. result_vector: It is the single row or single column range from which you want to return the value. The range size need to keep same as lookup_vector. Example: Table B4:D7 is a point ta

In a macro there will be lot of codes and a lot of sections. It is very much possible for you to develop or modify it in future. To develop the specific code you need to find and go to that line. If you use a Macro Comment, you can easily understand why this codes are written for. And easily find the target code with the help of comments and edit or develop it. Macro Comments and simple code written in Excel VBA. It is not for execute with others Excel VBA Code. It is just for taking a note, that what is the code for. It is becomes more useful while your codes increases more. You can use multiple comments. It is very easy to Deleting or Writing. Example: Sub comments_writing1 ()    'This is a test of displaying 2 words the cell A1 and cell B1 in active sheet    Range( "A1" ).Value = "Messi"    Range( "B1" ).Value = "Ronaldo" End Sub After running the above program, the active sheet will display 2 names like below: Ima

A VBA Procedure is also define as Macro, is a set of codes which makes MS Excel to perform task within very short time as an action. The length of this code is depends on your requirement. Example: Image 1: VBA Programming example Here in this code: hello_1() : is the sub procedure. msgbox : is the inner task written by programmer, depends on his requirement. End Sub : is the end procedure. The result is: Image 2: VBA Message Working Process: Run: To start the working process of VBA Procedure, it needs to run. Input: Under VBA Procedure if any "user input" related code written, then it would ask the user to enter data. Calculate: hen it will work on it automatically as per code written in VBA Code window. Result: Finally it shows the result as the programmer desired. Image 3: Working process of VBA Types of VBA Procedure: 1. Sub Procedure(): All codes are written under a Sub Procedure. it is written into Module in Code window an

Rank function used for ranking a value among a range and return a ranking number. The ranking number which will return, depends on which option you would like to mention. There are 2 options. For largest to smallest (0), and another one is smallest to largest (1). Rank Function: The structure of this function is: =Rank(number,ref,[order]) Function Details: There are 3 arguments in this function. First 2 is essential and last one is optional. These are described as below: number: It is mandatory. This is a single number, which you would like to find what is the position of this number. ref: Reference is another mandatory part. It refers a cell range. The number will find the position comparing this ref range. [order]: It is optional. You can mention 0 or 1 if you wish. Or else it will select 0 (zero) by default which means: Large number is number 1. If you mention 1, then this will work as smallest number is number 1. Example # 1: Below table A1:B5 shows the salary of the footb

(1) Define Your Task: Before writing macro, you need to decide, what exactly are you going to write. Because Under a single task there will be new task arrived. And VBA executes steps by steps task which is written. So you need to follow, which task you want to write first. Best way is draw a sketch in a hard white paper with pencil and after taking the final decision then start writing in Excel VBA. (2) Layout of Macro Writing: Every program has a specific structure. In VBA while we write macros, also need to follow a specific structure. But this is a sample structure. 1 thing you should memorized that, Procedure starts with a function declaration. Then inner task commands, some comments for taken note if in future require editing, and finally closing the procedure. Some thing like this flow chart: Image 1: VBA Programming simple diagram (3) Writing Macros pretest: First we will look what we wish to do in a simple English language, and how VBA writes it, in VBA langu

Real Life Problem: One of my friend used VLOOKUP() for his daily calculation in corporate office. Many things he can return with VLOOKUP() . One day I have asked him, " return the left side value " by using VLookup . He tried many times but failed. Finally told me to teach him Index and Match. I've done this. Now he is using Index-Match for his daily task in various MIS Reporting. Only 1 limitation I've found in VLookup() and that is, it can't return the left side (-1) column's value. Example Data Table: Assume that, D4:G7 is the range of players data table. D3 = " Player ", E3 = " Rank ", F3 = " Goal ", G3 = " Country ". It contains 4 rows. VLookup() can't return the players name with the help of Rank number. But Index()-Match() can return the Player Name using the same Rank number. I am just going to show you, Index-Match can do that, what a VLookuo() can't. My friend used the formula in E11 Ce

Basic Excel Shortcut Key: CTRL+A Select all in a worksheet CTRL+B Font bold CTRL+C Copy all selected cells CTRL+D Copy the value or formula to down CTRL+E Series entry auto fill CTRL+F Find CTRL+G Go To CTRL+H Replace CTRL+I Font italic CTRL+K Inser a Hyperlink in current cell CTRL+L Create a table based on current cell selection data table CTRL+N Take new workbook CTRL+O Open a old workbook CTRL+P Print CTRL+S Save CTRL+T Create a table based on current cell selection data table (Same as CTRL+L) CTRL+U Font underline CTRL+V Paste all CTRL+W Close the current workbook CTRL+X Cut CTRL+Y Redo CTRL+Z Undo

Real Life Problem: In Corporate Office, there is a common problem, if you are going to print a complete employee list, then it will go for 2nd page. Or any data or report which takes more than 1 pages, then a question is in mind, "how to print the row title or column title on each sheet"? Example Data Table: Assume that A1:D40 is the data table, where A1 = Employee Name, B1 = Designation, C1 = Salary, D1 = Date of Joining. And A2:D40 contains the 39 records of 39 employees. Solution: To print the row and column title on each sheet, you don't need to re-design the table. In Excel you can do it through "Page Setup" dialog box. 1) Go to sheet tab from this dialog box 2) Click on Rows to repeat at top and click on the row number which you want to print as header on each page. 3) Do the same thing for Columns to repeat at left text box. 4) Make sure the page setup 5) Finally click on Ok button to set it. 6) Then use print command to pr