14 Must-know Tips and Formulas from ‘Excel 2016: Advanced Formulas and Functions’ [Part 2]

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

1. 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
   1. Can be used for SUM, AVERAGE, etc.
2. To create a frequency distribution for different entries, use FREQUENCY function
3. Transpose can be done in 2 ways:
   1. Transpose with paste special – this can be achieved by Ctrl+Alt+V
      1. The numbers on this transpose can be changed
   2. Transpose with TRANSPOSE function
      1. The numbers on this transpose can’t be changed. Use this when you have to maintain the numbers from the original table
4. For regression analysis, use TREND and GROWTH functions
   1. These functions give the exact data points whereas the trend lines do not
5. Use the INDIRECT function for data validation.

Text Functions

1. FIND and SEARCH both look for characters in a cell; however, FIND is case-sensitive, SEARCH is not
2. Use a combination of MID and FIND to enhance the capabilities of Flash Fill
3. LEFT and RIGHT functions allow copying cells from the left or right side respectively.
   1. Tip – ‘Flash Fill’ removes the requirement to remove both the above functions. This can be accessed from Data -> Flash Fill
4. TRIM removes leading, trailing and multiple consecutive spaces
5. Use CONCATENATE to combine text from different cells
   1. Remember ‘Flash Fill’
6. CONCAT performs the function of CONCATENATE more efficiently
7. Use TEXTJOIN function to add a delimiter in the final combined text
8. LOWER, UPPER and PROPER convert the name to the required cases
9. REPLACE and SUBSTITUTE perform a similar function; however, SUBSTITUTE gives the opportunity to remove selective characters from the text