This is the second part of the advanced Excel series. The first part of the series can be accessed by clicking here.
Array Formulas and Functions
- To convert the normal formulas to Array formulas, press ‘Ctrl+Shift+Enter. The formulas will be surrounded by Parantheses {}, which signifies that the it has been converted to an array formula
- Can be used for SUM, AVERAGE, etc.
- To create a frequency distribution for different entries, use FREQUENCY function
- Transpose can be done in 2 ways:
- Transpose with paste special – this can be achieved by Ctrl+Alt+V
- The numbers on this transpose can be changed
- Transpose with TRANSPOSE function
- The numbers on this transpose can’t be changed. Use this when you have to maintain the numbers from the original table
- Transpose with paste special – this can be achieved by Ctrl+Alt+V
- For regression analysis, use TREND and GROWTH functions
- These functions give the exact data points whereas the trend lines do not
- Use the INDIRECT function for data validation.
Text Functions
- FIND and SEARCH both look for characters in a cell; however, FIND is case-sensitive, SEARCH is not
- Use a combination of MID and FIND to enhance the capabilities of Flash Fill
- LEFT and RIGHT functions allow copying cells from the left or right side respectively.
- Tip – ‘Flash Fill’ removes the requirement to remove both the above functions. This can be accessed from Data -> Flash Fill
- TRIM removes leading, trailing and multiple consecutive spaces
- Use CONCATENATE to combine text from different cells
- Remember ‘Flash Fill’
- CONCAT performs the function of CONCATENATE more efficiently
- Use TEXTJOIN function to add a delimiter in the final combined text
- LOWER, UPPER and PROPER convert the name to the required cases
- REPLACE and SUBSTITUTE perform a similar function; however, SUBSTITUTE gives the opportunity to remove selective characters from the text