Excel VBA’s role is very important in the MS Excel application. Microsoft Excel is main application in the MS Office. Because Office related tasks like MIS activates are getting done in the MS Excel. In order to automate this Excel tasks, VBA Macros are best choice. And we can control any application from the Excel VBA programming. Hence VBA programming has significant role in the MS Excel automations. Usually when user is preparing some report and he is taking around one hour to do that which is not a judgmental task. Since it is copy paste work, you can automate with VBA Macro programming and save that one hour time. User has to spend time only on reviewing the report. This is what the best advantage of the Excel VBA Macros.
VBA Macros programming can be written with in the excel environment. In order to see the VBA program environment, you have two ways. One is Alt + F11 short cut key from Excel workbook. Or you can click on view menu and then see the macro option then view option as shown in the below screen shot.
Then either you can create new by giving the name or you can select the existing one, if you already written. Once you are in the VBA environment, you can see the coding, if you have already written. Else you can start writing the code in the white screen. Here there is an option called record macro. This is very useful option for the beginners. Sometimes when you are not familiar with VBA programming, this option can be used to see the exact code for particular desired thing that you want to do in the front end. This is main feature of the excel macro programming.
Record Macro option:
This option works like a tape recorder. Just click the record macro option and start doing some activity in the Excel, you can see the pre written code in the VBA module of that excel workbook. Even intermediate users can use this option as a help to know, when one particular piece of code. You can find the record macro option in the above screen shot.
Writing VBA Code:
When you press Alt +F11 option, you will be in the VBA Macro environment of that particular Excel workbook. Then you can insert module by clicking in the Insert menu. Once you done it, you need to start subroutine.
Within the below codes, we have to write the macro coding part.
I am going to write small VBA Macro for you to understand yourself.
Adding two numbers program
Macro Sample program
Dim I, j, k as integer
K= I + j
Range(“A1”).value = k
Similar way you need to write the vba code for any other tasks in the Excel. VBA programming is ocean to automate the things which would be available in excel. Almost every application we can control with VBA programming. All you need to do is adding the DLL of other application. Even in this programming language we can not automate the judgmental cases. This is only the drawback in this programming language. If we are effective programmer, we can just save the lot of FTEs ( Full Time Employees ) work. It would be converted into money. Sometimes If you are in six sigma projects, then we are eligible to take the green belt projects.
Hope you understand about the Excel VBA Macro the way how you can write the coding part within the Excel.
Now a days in the USA and Uk countries prefer to outsource the work for reducing the cost to their companies. Here Freelancer concept came into the picture. And it became wonderful opportunity to the people who wants to work from the home. Also freelancer can work at any time based on his interest where employees can only work in one particular point of time. In order to work as freelancer, we have many websites which would work on commission basis. Here i am going to provide some name from those. Fiverr.com, Upwork.com, Freelancer.com etc,,
Since my website is belongs to excel based, here i am going to brief you about the Excel VBA Freelancer tasks. First thing all excel related works can be done by this freelancer. Also before you give any work to Excel freelancer, you must need to discuss with him about your activity that you want to automate or formulate. Then excel vba freelancer checks the feasibility study on that activity and confirm you the timeline. And let you know in case if there any challenges to do that. Then as a buyer, you can negotiate that particular piece of part to do in a alternate manner. This is how it goes.
I am also an Excel VBA Freelancer and you can find my profile in the below mentioned link https://www.fiverr.com/sivaramakris170 . Being as a excel vba freelancer, I got some relief from the daily basis job activities. In day to day employee life I did not have the freedom wherein I am getting lot free time and able to earn better that what I was earning as an employee. Hence, if any one has the outstanding skills in any aspect, you can take my help, in order to become as a freelancer. That would become an awesome step in your life.
As a excel vba freelancer, i am getting works like formulating the workbooks, automating the Excel reports using VBA Macros, in order to save the processing time of the user. Using the Excel VBA, we can do almost many things like communicating with other applications, fetching the data from websites to excel. If you are a Excel VBA freelancer, you must need to clarify the user, as what you are going to automate or formulate and it would be helpful to use that particular tool. Also need to support to the beginner users as they will have lot of confusions until they get habituated with your tool. Once you do 2 or 3 tools for the users and you get positive feedback from them. Then you will become very busy with offers. Feedback system or reviews are very important for the freelancer. Hence as a freelancer, you should be more specific on what you are going to do to the user and how it is going to helpful in future.
Hope you people understand that what exactly is freelancing job and how it is going to be worked out. You can comment in the below in case, if you have any questions. Also i will give you the referral link to enroll in the Fiverr website. If you are interested, you can signup and check you luck. http://www.fiverr.com/s2/2e6e2eefb0
Sumif formula has significant role in MS Excel. It is very useful to create the dashboard. Also it can be used with combination of formulas based on the requirement. When you need to sum the values based on the one particular criteria (value), you can use this Sumif formula. This can be used as an array formula, if necessary.
Here we are going to provide you detailed information about what is MIS, where MIS activities can be used, what exactly to be done in the MIS, How to manage MIS Reports and MIS Excecutives / Data Analysts, how to get MIS Executive Job for freshers, experienced people, Qualities of the MIS Executive / Data Analyst and MIS job advantages and disadvantages.
We also discuss some important MIS Daily Reports like Head count report, SLA Report, Procure to pay report ( P2P Report ), AP Dashboard, AR Dashboard, GL Dashboard Invoice Summary Report, Payroll team related.
What is MIS?
MIS is nothing but managing the huge information in a sequential order in order to retrieve the required information. Whenever we require the particular information from the huge database, you should have the proper plan to maintain the data.
Where MIS activities can be used?
MIS activities can be used in the corporate companies, government sectors and many other places where the huge data involves.
What exactly to be done in the MIS?
As discussed in the above, we have to maintain the huge data to retrieve again on need basis. Hence usually if the data is limited we can go head with the MS Excel. And MS Excel is compulsory to use in the MIS. Because calculations purpose MS excel is the excellent application. Usually in the MIS, Information would be stored as reports, dashboards and in the other data formats. In order to store the data in the sequential order, we will maintain in the reports wise. If MS excel is not capable to maintain the data, then we can go ahead with the MS Access to store the data. IF data is more 1 GB in one single file then we have to choose the SQL or other database model.
Excel single file capacity 30 MB. if exceeds more than 30 MB, then we have to choose the MS Access
MS Access single file capacity 1 GB. If exceeds more than that, then we can choose SQL database or any other database oracle based availability.
How to manage MIS reports, Dashboards ?
Managing the MIS Reports:
Usually we prepare the reports for the daily, weekly, monthly, quarterly, half-early or annually in a single file for the respective day/ week/ month etc.. we will keep the important cleaned data in a particular order column header wise to pick the important numbers ( Business drivers) to show the activity related status in a report. These reports to be cascaded to top level management and they take the decisions based on these numbers also they come to know the present situation of the activity.
Managing the MIS Dashboards:
When you create the dashboards, you can keep the data sheets in the hidden mode and show only the display sheet which have important numbers and graphs related sheets. based on the frequency data can be empty and refill the data into the dashboard template.
Here one important thing is we need to have the formulas if we are going to prepare in the MS Excel. Excel formulas would play the key role in the MIS Reports/ Dashboards. This is one excellent advantage with the MS Excel. Any difficult thing can be achieved with formulas. we have the good formatting look in the MS Excel. That’s why management prefers to show the data in the Excel than any other applications.
MIS Executives / Data Analyst :
MIS People are decision makers who can advise the awesome ideas to the top level management. Operation manager must have the MIS skills. MIS executives are also called as Data Analyst.
Qualities of MIS Executives / Data Analyst :
This job role is very crucial for the company. All the confidential information will be there with the MIS Executive. Hence it is very important to hide the information from the non related people. Data Analyst must hide the lot of information from non related people.
Assume that John is the MIS Exective and he is supporting to 4 departments in the same team. Hence he has all the confidential information of 4 departments. In such case one of the departments manager Rock came and asked about the total income for the month for the other department. Then John will be in trouble, he can not reject his request and at the same time he should not provide that information to Rock. Then John should say, “i am yet prepare the report, once it is done, I will let you know”. That’s how john should escape from Rock’s request. Because it is not related to Rock’s department. Due diligence is very important quality for the Data Analyst.
Payroll Team Related MIS Activities:
Pay role team also has the MIS activities. Since this team maintains the salaries of the all employees, it is highly confidential task. Also it is very challenging job to process the salaries on monthly basis. I will provide another example which was happened to my colleague. He has process one guy salary to another guy because of the naming confusion. Two employees have to same name. That’s why he got confused and interchanged their salaries. It has become very big mistake in the Payroll MIS team.
Similar way finance team also has MIS activities. In this team all the reports will deal with the money related. Hence it is very challenging job to do. Every time MIS guy would deal with lot of money related numbers. If any report goes with wrong number, then company would be in trouble. Hence in this department, management would do double check all the MIS activities. Still sometimes errors occurs. whenever error occurs in the finance team, management gets many calls from their higher level like from clients. Hence It is very tough thing to manage, sometimes need to pay the penalty to client by company.
How To Get MIS Executive Job as a fresher or experienced?
Educational qualification of the MIS Executive is a graduate from any bachelors
Hands on experience should be there in the MS Excel mainly and better to have power point presentation skills.
Should be capable to speak English fluently without any MTI ( Mother tongue influence). Means when people speak their mother tongue should not be appeared in the spoken English. Because sometime we may need to handle client calls at that point of time. Client may face problem to understand because most of the clients are from the USA, UK or Australia. If the client is from India that is fine. If we keep practice on daily basis, it is easier to remove the MTI from our spoken English
It is better to have 30 wpm minimum typing speed. Because sometimes there are requirements to type some information on need basis. It is very rare but good to have the typing skills
Better to have the Analytical knowledge, since always MIS executive would work the huge data. Freshers can ignore this point as they will not have any idea at the beginning stage
MIS Executive always plays with the huge data. Hence should be well versed with data analysis knowledge. Means when executive receives the data, need to check the what exactly the information and what exactly to be helped to management and what are the business drivers in the given data as such.
Good to have 7 QC tools (1 Pareto Analysis, 2 Histogram, 3 Fishbone analysis (cause and effect diagram), 4 check sheet, 5 Control chart 6 Scatter diagram, 7 Stratification) knowledge but not compulsory.
Pivot tables preparation is must for MIS Job position. Because most of the time need to handle with the huge data. When we have huge data, it is very difficult to understand the whole data at once. Hence if MIS job holder has capability of the creating pivot tables, It helps him a lot in understanding the whole data in a easy manner. Also helps him in creating the reports and dashboards
Should be capable with Excel dashboard creation, Excel reports, Excel template. When you say dashboards or reports. These are involved the excel formulas and other excel techniques like hide in the excel sheets and all. In order to learn this either you can see the older dashboards or google it with the excel dashboards and get the sample dashboards to understand. Freshers can ignore this point as they just need only excel formulas skills.
Excel Charts or Graphs would play key role in the MIS job. As management only looks these charts when they have no time to see whole information. Based the given data, MIS Executive would select the graphs like bar graph, pie chart or any other which suits to existing data points
The above points are very important for experienced guys and freshers who wants to join as MIS Executive. Few points are not compulsory to fresher as i have already mentioned in those respective points.
MIS Reports / Dashboards:
Here we can learn about some important MIS reports and dashboards with examples.
Head count report:
This report conveys the number of people working in the respective team or in the organisation. HR will have this head count report for entire company as he/ she is responsible to maintain this report. If the team size is bigger more than 50 people approximately. Usually MIS Executive or HR prepares this report on monthly basis as they have to know the number people in the respective team or in the company. Now we can learn how to prepare this report. At first you need to have the all the employee details in the one worksheet. Like First Name, Last Name, Email address, Reporting manager, Joining date etc. As shown in the below
In the above list, whenever employee leaving the organization or team change, we need to capture. Then we can find the opening balance for the next month, closing balance for the current month and department wise number of employees.
Every month end you have to prepare this report, then you will come to know the exact head count on monthly. When you do this, you also find the attrition percentage. This is confidential report.
Procure to pay report ( P2P Report ):
This report is mainly for vendor payments and invoice ageing status. When we are producing one product, we require some raw materiel to produce. or if we have to provide some service also we require some things in order to provide the service. To provide the service or raw material, we have to pay amount to vendors. At that point of time, this procure to pay concept comes into the place. In order to pay the amount, we will have some time and dead line. we prepare this report to know about the vendor payments mainly. We will have some service level agreement to pay the amount.
when we need to pay some amount to the vendors, we will have some duration to pay that. Hence we must maintain one report for calculating the time on day to day basis. In order to do this process, we will have the invoice copies. These are classified as purchase order, No purchase order, Travel & Expenses as such. In this case No purchase order to process, value of the purchase order payments to be captured. This report will have the below tabs ( Sheets) approximately. Similar way various business models will have their respective tabs.
Summary Dashboard sheet can be shown as in the below manner. This is just an example to understand by yourself. You can prepare the same in your own manner based on your client requirement.
SLA Report sheet has the ageing like 0-3 days, 4-7 days, 8-14 days, >14 days. Using the excel formulas, we can pull the number of days into the above ageing columns as shown in the below
Similar way we can show the Purchase order, No Purchase order, Freight, Travel and Expenses summary in different sheets. Once we prepare these sheets, we can show one consolidated worksheet to for all the overall information. Then we have to prepare the payments sheet, to show the payment information for the vendors as on that particular day.
Hope you understand the Procure to pay report in detailed.
MIS Job role Advantages:
You will not get continues flow of work. Actually speaking bit lesser work most of the times. Because 100% Accuracy required in this job. 4 hours of the work would be assigned in the 8 hours of job time.
Typing work would be very lesser comparing to other non voice jobs.
It would be very interesting and enjoyable, when you are dealing the challenges with Excel formulas or helping others to solve their excel template issues
Most of the other team members would respect the MIS Executive, because this job role would support to the team as I explained in the above point.
Once this job gets habituated with all the assigned tasks, it would be cake walk for working in daily.
Managers would rely on the MIS Executive’s report numbers to take their crucial decisions in the business
Many team members would try to catch you all the time to learn excel tricks from MIS Executive
This job role would circulate reports to top level management hence MIS Executive will have the good connectivity with top level management like BU Heads.
This job role will even send the reports to clients. Hence even in client location people also gives the respect to this job role. Since they will also send requests to prepare the Ad hoc reports
By doing all these activities, usually MIS Executive would become an subject matter expert in their respective team activities.
MIS Job role Disadvantages:
MIS guy would rely on production team member’s inputs to prepare the report, dashboards. This would little bit hectic process due to some reason, when they are unable to send the inputs, Report circulation would be delayed. Managers would ask the MIS guy for the report as they would like to know the process current situation.
Whenever clients come with ad hoc reports requirement, it would be an urgent requirement. MIS guy should prepare the daily reports as well as this ad hoc report for the client. That particular time, this MIS Executive would face a problem to work in fast pace mode. In the Workforce management team, MIS Executive has the always tasks in the queue to do.
Sometimes production would update the numbers incorrectly and the same would be taken by MIS guy and getting errors in the report after next day. That time it would be bit difficult to prove that he has done correctly.
Payroll team MIS job also bit difficult to do because it is almost similar to bank job. Every paisa should be accountable while preparing the payroll salary report or any other report in the payroll team.
Based on the above points, you can understand MIS Job would be enjoyable also hectic task sometimes. Hence MIS executive should learn how to play the safe game in the job role while dealing with the production team or any other top level management.
Index formula is equal to Vlookup and HLookup. Both the formulas capability was included in the index formula. Vlookup has a drawback like you can not pull left side value. But using this Index formula you can overcome that problem.
Index formula syntax:
within the index if you include the match function. Then this formula is stronger and best formula in the excel. Match formula can be used within the rownum or columnnum place in the above syntax. When you need to find the particular column within the data data set, then we have to use index match combination.
Index formula in reports
This can be very useful to prepare the SLA report for the project.
you can keep the data sheet hidden and pick the hidden values to display sheet in the SLA dashboard. Then keep the conditional formatting to show the red, amber and green for KPIs (Key performance indicator) and CPIs( Critical performance indicator)
Index formula is known as advanced excel formula. Since it
As discussed in the previous posts, VLOOKUP function has feature to mingle with other excel function or formulas. You are going to learn the way how we can use the IF condition with VLOOKUP Function. IF condition also very powerful formula in the excel and other programming languages.
IF condition is logical function. For example if you want to test one cell whether there is a value or not, you can use this function and know the result, if condition has two variety of options 1) True Block, 2) False Block and one condition. When condition is true then True Block executes. Similarly condition is false then False block executes. Hence VLOOKUP can be used in this either in True block or in False block depending upon our requirement. We have already given you about the VLOOKUP functionality.
Syntax : =IF(Condition, True_Block, False_Block)
Since we have already discussed about VLOOKUP in detailed manner, I am going to provide you simple example on the VLOOKUP function. It provides you the right side value as result based on given left side value. And it works column wise. Means we can give the table array as columns only.
Syntax: VLOOKUP(LOOKUP_Value,Table_Array,Column_Index_Number,Exact_Match (False or 0), Approximate_Match (True or 1))
How to use VLOOKUP and IF Condition in single formula
Vlooup errors are 3 types (1) Vlookup #N/A error, (2) Vlookup #VALUE Error and (3) Vlookup #NAME error . Here in this article we are going to discuss all these vlookup errors in depth and you are going to learn how to fix these errors instantly to get the correct results. Before you go into this article you must know about the what is vlookup function, what is the definition of the vlookup and how to when you use the vlookup once you learn these tricks then you please read this article. Else it is very difficult for you to understand.
1)Vlookup #N/A Error :
If you check vlookup syntax, it has 4 variety of ingredients are available in that 1st one is Lookup Value, 2nd Table Array, 3rd column index number and 4th exact match or approximate match
If Lookup value related value is unavailable in the given range then this error can be thrown by Vlookup formula. When you get #N/A in the vlookup, you have to check whether you have the given correct lookup values or any misspelled in the lookupvalue. Once it is done, still are you getting #N/A error?
Then you need to use the Ctrl + F option and give the lookup value related word to check whether the lookup value related word is available or not. If there are no such values then you vlookup is providing you correct error.
Sometimes due to unwanted spaces in the lookup value text, this error can be thrown, at that time you need remove the unwanted spaces from the lookup value related column in the data.
This error is known as logical error because we have write the correct formula still we did not get the result lack of look value in the data.
Troubleshooting VLOOKUP #N/A error is we need to either correct the lookup value related word like remove the trailing or leading spaces from the word. If that particular word is unavailable we can use the iferror with vlookup or isna with vlookup to bypass the error from the formula results
If you see in the below example lookup value Rajesh is unavailable in the E column but E column related value is the Lookup value. Hence we got this #N/A error in the vlookup functions
2)Vlookup #VALUE Error:
This error occurs when we write the syntactical mistake in the vlookup formula . Hence when writing the vlookup, we need to ensure that are writing correct syntax else it throws the #VALUE error in the result. This can be occurred very rare scenarios because we all know that vlookup is very simple yet powerful formula. Once we get habituated with this formula, we never ever write the incorrect syntax for vlookup. Only when we are in the initial stage, that time there is a chance to write this kind of mistakes
Fixing #VALUE error in VLOOKUP formulas:
We need to check where exactly missed our syntax and correct it. Then it works fine. In order to do that first lookup for lookup value if that is given then go for the table array whether we have given that correctly or not, if that is also correct then check whether you have given the column index number or missed. And last finally you check whether you mentioned the exact match or approximate match. If you check these all parameters it should not have any syntactical error then obviously this error will not be appeared in your result
If you see in formula bar, you can find I have missed out the Lookkup value at first block. Hence we got this error #VALUE, if we mention correctly then result can be populated.
1)Vlookup #NAME Error:
When you are writing the vlookup function and if you miss any letter from the vlooup spelling and write the formula then obviously you get this #NAME error.
Hence Vlookup #NAME Error can be fixed by just checking he spelling of the vlookup function.
if you see the formula bar in the below picture, I have written the unwanted space in the vlookup formula like vlook up. That is why we got this #Name error in the below scenario.
These all errors are common error in the vlookup function and you can bypass the #N/A error by writing the IfError function. If you write the vlookup function within the IFERROR function, you can catch the vlookup error and bypass the error message
How to fix the Vlookup errors:
Vlookup #N/A Error can be bypassed by iferror function and provide the lookup value which available exactly in the lookup value column.
Vlookup #VALUE error would be fixed by checking the syntax of the vlooup and correct that, then we can see the exact result if there is the correct lookup value
Vlookup #NAME error can be rectified by checking the spelling the vlookup function and correct parenthesis in the formula.
Note: Always it is better to sort the values of the data in ascending or descending depending on your requirement to get the results else there might be chance to get unexpected results by this formula.
Vlookup in same worksheet:
Vlookup Syntax Explanation:
Lookup Value: G4 cell is the Lookup value in the below example
Table Array: C column and D columns are Table array
Column index Number: Since we select only two columns C and D. And we require the 2nd column values as results, we are going to give the column index number as 2
Exact match: 99.99% of time we regularly use the exact-match that is denoted by zero ‘0’. Here in the below example you can find the exact-match only.
Vlookup from another sheet:
It is also same as example 1, only one difference is that you need to add the sheet name to the table array in the below syntax. Here in the below example you can find the data is in the vlookup data sheet. And result in the Vlookup result sheet. Hence the syntax would be as shown in the below picture.
It has the change that is ‘Vlookup Data’!
Vlookup from different Sheet as shown in the below
Approximate match: Very rarely we use this approximate-match that is denoted by ‘1’
The below areas can be used the vlookup function :
Userforms, subroutines, modules, textbox, listbox, combobox, worksheets (with in the same worksheets,), different excel worksheets (means one sheet to another worksheet with in the same workbook), different excel workbooks and User defined functions
Mainly this function can be used for the purpose of searching values for the respective lookup values.
VLOOKUP is simple formula and very powerful in excel spreadsheet
Many Excel formula can be used within the and along with VLOOKUP
Assigning the right values for the given lookup values as I have explained you in the approximate match example in the above.
We can customize the function by taking the support of the other excel functions like countif, sumif, count, countifs, sumifs, and sum function. This function can be used with the combination of the sumproduct, if function, nested if function, Indirect, index and match functions.
VLOOKUP can be customized in the user defined functions as and when we require. This is one of the major advantages.
This function can be used for Pivot tables and tables also to get the exact values
We can use the named ranges for range criteria in the vlookup syntax as shown in the below
Named Range in the Vlookup:
Named range is nothing but a collections of cells within the range where you can provide the name for that specific range
Named Range In Excel:
Vlookup disadvantages ( Limitations ):
Vlookup can not get the right value when there are duplicates in the lookup value related column. We must need to sort the data when there are duplicate values in the lookup value related column. Then this function can return the first value related value This is the major drawback of this function. Of course we can overcome this problem by using the different functions in the excel workbooks
Vlookup with duplicate values:
2. Using this function we can only pull the right side values as we discussed in the above. If we need to pull the left side values based on the right side value. We can use the reverse vlookup function. This is the combination of the index and match functions.
3.It only works on columns if we need to use for the rows then we have to use the Hlookup function which is very similar to Vlookup function. Hence reverse vlookup which is the combination of the index and match functions.
4. Vlookup major disadvantage is when you have more vlookup formulas within Spreadsheet or within one Excel workbook, it hangs a lot since it is an array formula. When user getting hanged in the workbook that time user need to setup formulas calculation option as manual, you can find the below screen shot to setup manual option in the excel.
Once we finish working in the excel, we need to set back as Automatic in the above option as usual
Vlookup significance is nothing but a Vertical lookup where you can search the data vertically based on the lookup values availability. As I have explained you in the above examples. Vlookup is one of the key functions within the excel Spreadsheet. Almost many MIS tasks get complete with only with this function. Still this has some limitations as we have already discussed in the above. But we can overcome this lose by taking the support of the other excel functions. Here in the excel Vlookup point of view vertical in the sense column by column
Column A, Column B wise from left to right we can search the values using this VLOOKUP formula in Excel spreadsheets. Please observe the below Screenshot for better understanding
There are many wonderful examples can be derived by Vlookup in the live projects, those also we are trying to include in this article at the earliest.
This entire article can be considered as Vlookup tutorial
Here we are going to know about the loops in VBA. loops are 4 types in VBA. Those are while loop, loop, for loop, do while loop.
Loop is concept in programming language to run continuously until it gets particular given target. For example if you need to run the 100 numbers in vba program. You must use the loop concept to do this task. Then you need not write 100 lines of the code to print 100 numbers. This is a main advantage of the loop.