We can programmatically control the features of ms-office application using VBA. This customization is done in Visual Basic 6 editor which is built-in feature of all office application like MS Excel. We use Visual Basic language to write programming code. Any piece of code written in VBA is called a MACRO. (more…)
VBA stands for visual basic for applications. VBA is a programming language which is used to customize MS Excel features through piece of code. AS its name suggests, VBA infact is Visual Basic style of programming. (more…)
Vlookup function in Ms excel allows us to search in first column of a given data range and return a value from any column in corresponding row.
If exact match is not found, vlookup function can return value by approximate match.
Syntax: (more…)
It is important that the data you enter in spread sheet is accurate and error free. Microsoft Excel provides a Data validation tool to ensure that the entered data follows some pre-defined standards. For Example, suppose you are creating a result card of students and you know that no subject has total marks more than 100. This means that you can set a rule that no student can get marks more than 100 in any given subject. This can be done using Data Validation in excel. Every time you violate rule, a message will appear to stop you. (more…)
Excel Advanced Filter allows to apply complex filtration criteria. It has all capabilities of AutoFilter plus some enhanced mechanism to filter data. For example if you want to filter only those products which have “juice” in their name or if you have a criteria on the basis of result of a function, this can be achieved by Advanced Filter.
Following are some examples of Advanced Filter
Example 1
Criteria: Find all products which have “juice” in their name, the aim is to get sale of all juices on a given date.
We shall begin with data shown in figure below. Advanced filter is different from Auto Filter. In Advanced Filter, criteria are given in cells above the actual data. As you can see in picture below our data is in range from A6 to E15 but we have similar headings above the data. Doing this we have created a criteria range available from A2 to E4. Remember we must keep an empty row between our data and criteria.
It is important that you have ability to filter data into subsets. If you are working with large set of data, you need some mechanism to create a subset of data to perform effective analysis. Excel filter feature allows you to limit data in excel worksheet according to some criteria. Excel provides you two versions of filter, AutoFilter and Advanced Filter. In this post I will explain AutoFilter and in next post “Advanced filter”. This post is written and tested in Microsoft excel 2003. It is same for Office XP and Ms Excel 2007( excel 2007 has little change in menu layout). (more…)
If you want to search for a specific string within another string in any version of Microsoft excel, this can be done by combining a number of Excel formulas . Microsoft excel contains number of useful function to perform string related operations. For example you may want to find a string “esp” in string “yespakto”. Suppose this string is in cell D7. To solve this problem use following formula (more…)