Excel 2016: Function Tricks

We all have a handful of Excel tips and tricks that make data analysis simpler for the sharp marketer, whether it be v-lookups, concatenations, or a series of complex, nested IF statements that allow you to draw matched values, from a range, based upon precise logic (I.E. rules). It comes as no surprise, but the complexity of such nested statements increases dramatically with the number of values you wish to match – making the conclusion of performance insights often more difficult than need be. Here are a number of functional improvements that I’ve found handy in my day-to-day analysis tasks, specifically for Excel 2016.

  • TEXTJOIN: An easy improvement over CONCAT, used to join text strings in an XLS. Until recently, concatenation required each cell to be selected individually, with the user specifying the delimiter (e.g. comma) separating each cell. TEXTJOIN is much simpler, letting the user specify the delimiter only once, denoting whether blank cells should be ignored (TRUE/FALSE), and selecting the range. Specifically,
    • Concatenation – typically something like:
      • =CONCATENATE(A2, “, “, B2, “, “, C2,”, “, D2, “, “, E2)
      • Same, cleaner function, using TEXTJOIN:
        • =TEXTJOIN(“, “, TRUE, A2:E2)
  • IFS: Gives the user a cleaner alternative to using nested IF functions, such as “IF(IF(IF())).”The purpose of the IFS function is to simplify the task of meeting multiple conditions within a data range(s) via a function. A common example used to denote IFS functionality is exam scores, as seen below:
    • =”Grade:” & IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1<60, “Fail”)
    • The output will be a single letter grade, A-D, or “Fail” based upon the logic above.
  • SWITCH: The SWITCH function is similar to IFS; however, instead of specifying a series of conditions, the user specifies an expression and a series of values and corresponding results. When an exact match is found, the value is applied to the cell. For example:
    • =SWITCH(A2,1,”49ERS”,2,”SEAHAWKS”,3,”COWBOYS”,4,”JETS”,”No match”)
    • SWITCH would reply with “49ERS” if the value in cell A2 was “1.”
  • MAXIFS/MINIFS: similar to MAX and MIN functions, but allows you to set conditions based upon a range of values – consider this your ability to filter the data before a MAX or MIN is calculated.
    • =MINIFS(A2:A7,B2:B7,”B”,D2:D7,”>100″)
    • Answer: 13
    • Keep in mind, your min_range is A2-A7, whereas criteria ranges 1+2 are B2-B7, D2-D7.
  • What functions have you found that simplify data analysis concerning your accounts? Try the above tricks and let me know if you find them useful!

Excel Ex.

Dustin Lewis About the author
No Comments

Leave a Comment: