exceltoxl.com

Hi Folks,

  Share on Facebook 

We all know there is a feature in Excel called arrays. This is an interesting topic to learn and very challenging either because its an amazing and powerful concept of Excel features. Arrays are like ladies costumes, because it is difficult to understand or to explain to some one about the concept of arrays, similarly the costumes available in the market for ladies. Simple example here is we go for shopping with our girl friend(s) (rarely with wife, right? ;) ) and we really can't expect the budget they spend and the new company brands they choose every time we go. In addition, even companies are also introducing many products to make our ladies look like Angels, right?. In one sentence, Excel Functions create magic with Array features similarly, the ladies create magic with costumes. I agree, do you???

However, let us try to understand the functionality of arrays the way we understand our ladies. ;) Now, let us see few topics about Excel Arrays with repect to Excel Functions.

...

• What is an Array and its Definition?
• What is One-Dimensional Array? 
• What is Two-Dimensional Array?
• What are the Rules to follow ODA and TDA?
• What are the Advantages working with Array Formulas?
• What are the DisAdvantages working with Array Formulas?
• What are the Rules to follow Array Ranges? 
• What are the Advantages using Array Ranges?
• How to find Duplicate list using Arrays?
• How ARRAY FORMULA works and its Tips?
• What are the Excel Functions that supports Array Features?
• Show One Example Array Function with Pictorial Representation?

ADVANTAGE WORKING WITH ARRAY FORMULAS:

• These can perform complex calculations which are not possible with standard formulas.
• These will do multiple calculations in a single cell there by reduce the size of a workbook.

DISADVANTAGE WORKING WITH ARRAY FORMULAS:

• Using too many array formulas will slow down the application speed.
• Other users may not understand the array formulas used in the shared workbook.

RULES TO FOLLOW ARRAY RANGES: 

• Select the range of cells before you push CTRL + SHIFT + ENTER to get the results
• Changing the contents of an individual cell in an array range is not possible
• To make changes, convert the array formula into a normal formula by selecting a cell in the array range, pressing CTRL+/ to expand the selection to the entire array range, pressing F2 and then CTRL+ENTER.
• We cannot move or delete part of an array range.
• We cannot insert blank cells into an array range.
• An array range cannot contain merged cells.

ADVANTAGES USING ARRAY RANGES:

• We cannot alter the array range by parts or by overtyping accidentally by the user.
• A range array is more accurate and fast when compared to individual formulas in each cell inside the range. 
• This will also reduce the size of the workbook.