Categories
Excel

The difference between XLSM and XLSB extensions in Excel

In this part I will explain about XLSB extension which is useful but many users are not aware of its benefits. As you know the default format for data which are stored in Excel is XLSL extension, and if your file includes VBA code or a macro it will be stored in XLSM format. There is another format in Excel for the files which include macro, named as XLSB, but is used rarely. In the following we will learn more about XLSB format and understand when it ought to be used.

Both XLSM and XLSB are in fact compressed files which you can open them in software such as WinRAR. Typically, XLSM format uses XLM to store the data, while XLSB includes a binary workbook which helps significantly to reduce the file size. Here we have discussed the main advantages and disadvantages of using XLSB compared to XLSM format. All in all, if you have a large and complex Excel file, you’d better to store it in XLSB format.

The advantages of XLSB over XLSM:

  • Excel will run the file faster
  • Much Smaller size in comparison with XLSM format

Disadvantage of XLSB format:

  • Not compatible with other Microsoft software such as Openoffice, etc.

 

Categories
Excel

Combination of INDEX and MATCH functions in Excel

INDEX and MATCH functions can be used together to create flexible and powerful formula in Excel. As said before, the INDEX function can return the value at the juncture of a given row and column. Now instead of manually setting value for the column and row, we can get help from the result of MATCH function to solve the problems with providing formula. By combining INDEX and MATCH functions in Excel, we may search for the lookup value in a column or a row and return a value in the corresponding cell. In the following we will learn more about the applications of these two functions in Excel. Consider the table below to understand easier:

1

If we want to find the name of the product according to its number 1398, for instance, we may use a VLOOKUP function formula like VLOOKUP(1398,A:B,2,FALSE).

But now consider the table below:

2

Here we do not use VLOOKUP function, but rather a combination of INDEX and MATCH functions. Because we cannot set the third parameter as -1 in VLOOKUP function. Therefore, we use INDEX MATCH function as follows:

  • First we write an INDEX function and determine its first parameter as A: A, since we are going to find the name of the product which is located in Column A.
  • we could give the row value to INDEX function before. The row value for the number 1398 is equal to 4. But now we want to find the row value automatically by the Excel rather than manually. So we do this using a MATCH function. Function MATCH(1398,B:B,0) will find the number 1398 in Column B and will return the corresponding row value which is equal to 4.
  • Then we assemble the two functions and get this formula:

INDEX(A:A,MATCH(1398,B:B,0))

Now we can change the number 1398 with another cell or number to find the corresponding product name.