EXCEL Advanced functions and formulas ( PART -I)

1. CTRL + ~ will show you all the formulas applied on the excel.
2. If you want to highlight all the formula cells, then go to the HOME tab select Find and Select — Formulas, the cells will get highlighted and now you can fill the color for formula fields to distinguish from other cells.
3. Trace dependents and Trace precedents help you to find the cells that are dependant on the highlighted cell. Excel connects the dependents by blue arrow line and you can do multiple clicks to find the direct or indirect dependents of the highlighted cell.
4. Proper will help you to capitalize the first letter and any letter after punctuation. For Example, =PROPER(TRIM(A2))

Here, trim will clean up the trailing spaces present in the cell.

5. If you want to copy the entire column and paste it as values only, without formulas, select paste special as values ( To get to paste special as value, either right-click the mouse and select paste special or CTRL+ALT+V which will open the paste special dialog box. )

6. To debug any formula you can press the F9 key and verify your value.

7. To test the logical condition, you can use IF or IFS for multiple conditions.

=IFS(H2>4,3000,H2=3,2000,H2=2,1000,1,””) . IFS is relatively simpler to check multiple conditions and we are less likely to make mistakes.

8. VLOOKUP is easier than HLOOKUP as the vertical table is always easier to read than a horizontal wide table. Make sure the table data for the lookup array is in ascending order and also on the same sheet for the LOOKUP formula to work properly. VLOOKUP supports exact, approximate, and wildcard matching. VLOOKUP function only looks to the right.

9. VLOOKUP for the exact match, the 4th boolean argument should be FALSE (0) and for the approximate match, it should be TRUE (1).

For Example =VLOOKUP(AR2,$AU:$AV,2, TRUE)

The default setting for the VLOOKUP is a FALSE (0)i.e approximate match.

For Example =VLOOKUP(AR2,$AU:$AV,2)

10. The CHOOSE function returns a value from the list using an index.

For Example, CHOOSE(month(8-Dec-16),1,1,1,2,2,2,3,3,3,4,4,4). Here, the month will be 12 ie in 4th quarter, so the output will be 4)

CHOOSE(2, ‘BI’, ‘Tableau’, ‘Data Studio’) returns TABLEAU as it is the 2nd value listed after the index number.

11. The SWITCH function doesn’t require an external list. It is a logical function. It compares one value against a list of values and returns a result corresponding to the first match.

For Example, = SWITCH(A2, “BI”,” BI.com”, “TABLEAU”,” Tab.com”, “Unknown”)

12. The MATCH function will return the relative position of an item in an array that matches a specified value in a specified order. If you want an exact match the 3rd argument should be 0 and 1 for an approximate match.

For Example, =MATCH(C2,A:A,0)

Here the output can be 7 ie the cell reference number if the match is found. If the match is not found you can wrap the MATCH function in IFERROR as shown below.

=IFERROR(MATCH(C2, A: A,0),” not found”)

Remember, VLOOKUP returns a value, while MATCH returns the relative position or cell reference number.

13. The INDEX function will return a value or reference of the cell at the intersection of the particular row and column, in a given range.

For Example, =INDEX($C$3:$J$7,M2,N2)

14. As we know the major shortcoming of the VLOOKUP is that it cannot lookup leftward values. But, there is a solution using the INDEX and MATCH function.

For Example, =INDEX(A:C,MATCH(G2,C:C,0),1)

15. The UNIQUE function returns the unique values from a range or array. All blank values will return a 0 if the entire column is selected.

For Example, =UNIQUE(E: E)

And if you want to count the unique values in the list, we can use the COUNTA function along with the UNIQUE function.

=COUNTA(UNIQUE(E: E))

16. The XLOOKUP includes all the capabilities of both VLOOKUP and HLOOKUP. Exact Match is the default setting.

When XLOOKUP can’t find a match it will return the #N/A error. Unlike the other match functions, XLOOKUP supports an optional argument called not_found that can be used to override the error.

17. The FORMULATEXT function can be used to show the formula in the cell.

For Example, =FORMULATEXT(A2)