17 Excel Functions to use in 2017.

          Versatility of Excel’s built-in functions is undeniably one of the main reasons behind this program’s popularity. As users, we have the flexibility to compose complex formulas incorporating multiple functions in our solution to achieve substantial gains in productivity. Most people have their own go-to Excel functions, be it: financial, date & time, math & trigonometry, statistics, look up & reference, database, text (manipulation), or logical. We’ve covered some of these functions already, but below is a compilation of 17 relatively under-used Excel functions you might want to add to your professional repertoire in the new year. Fair warning, you might need to have Office 365 version of Excel for all of the functions to work.

          1. PMT
          2. DATEDIF
          3. LARGE
          4. REPT
          5. LEN
          6. TRIM
          7. SUBSTITUTE
          8. TEXT
          9. IFS
          10. CONVERT
          11. NETWORKDAYS
          12. HLOOKUP
          13. INDIRECT
          14. OFFSET
          15. TRANSPOSE
          16. DSUM
          17. RAND
          BONUS: SUMPRODUCT

1. PMT function.

          As we approached the end of the year and start reflecting on the future, personal finances might take the central stage for many of us. If anyone is in the market for a new home, car, boat, motorcycle, ATV, RV, or even an exotic pet, the PMT function can help us calculate a monthly loan payment for the loan we are contemplating on undertaking. Chevy Spark is one of the least expensive cars on the market today, let’s calculate if we can afford it. PMT functions requires input of loan interest rate, number of payments you will be making, and loan amount. Two important things to keep in mind with this function: 1) while you are likely making monthly payments, your interest rate is typically annual ; 2) since loan payment is considered to be a cash outflow, it’s a negative number by default. In our example, making a 10% down payment on a $ 13,000 car purchase and taking a 5-year 3% APR loan, we would be making 60 $ 210.23 payments until the car is fully paid of, and the bank collects $ 914.02 in interest over the life of this loan.

2. DATEDIF function.

          While the DATEDIF function is one and possibly the only “undocumented” Excel function (there is no official Microsoft documentation to be found on this subject), it is still a highly useful gem in calculating date/time intervals : Start Date , End Date , and time interval: D for days, M for months, Y for years, etc. :

3. LARGE function.

          Surely you’ve used the MAX function to retrieve the highest number in the range, what about the third largest? This is where the LARGE function comes in. You need to specify your array of values and the n-th descending order to make this function work. Courtesy of the US Social Security administration, we can look up the most popular baby names in Michigan :

4. REPT function.

          Who knew that you could visualize your data using Excel functions? Indeed, the REPT function (which repeats a certan character n number of times), in conjunction with the special font such as Webdings allows you to create impressive-looking in-line pseudo-charts. Why not use the baby name data to plot the most popular baby names by gender:

5. LEN function.

          Text-manipulation functions are unsung heroes of Excel calculations. Naturally, anyone can calculate number of letters in good, ole’ U.S. of A, what about “Democratic Republic of the Congo”?! This is where the LEN function (calculates number of characters/letters in a word) comes to the rescue:

6. TRIM function.

          Another interesting text-manipulation function is the TRIM function, which easily cleans up visible and hidden extra blanks occurring during data imports from an external data source.

7. SUBSTITUTE function.

          There are not many things in our lives that are absolute constant and never change. Why not use the SUBSTITUTE function to replace old values and/or text with the new ones:

8. TEXT function.

          Modestly named, TEXT function is a real formatting powerhouse of the Excel functions, able to take on text, number, and date formats with ease:

9. IFS function.

          The newest kid on the block and one of the most anticipated Excel functions of all times, the IFS function helps make the nested IF constraint a memory of your worst nightmare. This function is so new, it wouldn’t even work in Excel 2015, you would need to have updated version of Excel 365 to use it.

10. CONVERT function.

          If you’re like most people, you’ve never heard of this function, yet can easily guess what it does. Temperature, distance, volume, weight, and so many other types of conversions can be performed using the CONVERT function:

11. NETWORKDAYS function.

          To calculate the number of days passed between two dates, we can either use a simple subtraction, or previously mentioned DATEDIF function. This task becomes more complicated if we need to exclude weekends and company-recognized holidays. With the NETWORKDAYS function we could accomplish this goal by specifying Start Date , End Date , and the array of holidays:

12. HLOOKUP function.

          You might not realize this yet, but the infamous VLOOKUP function is not the only lookup type function in Excel. HLOOKUP function allows us to perform horizontal lookups. Let’s say we needed to retrieve the fifth ranking country in terms of medals won in 2012 Summer Olympics :

13. INDIRECT function.

          In case you’re ever required to retrieve a value of one cell based on cell reference from another, you will be pleased to know that there is an Excel function for that, it’s called: INDIRECT.

14. OFFSET function.

          Anyone who is looking for an impressive function to use during their job interview process to differentiate themselves from the VLOOKUP and INDEX/MATCH crowds, can turn to the OFFSET function:

15. TRANSPOSE function.

          It’s hard to believe, but Excel has a dedicated function to rotate columns and rows: the TRANSPOSE function. Since this is an array type of function, it must be entered using the CTRL+SHIFT+ENTER key combination.

16. DSUM function.

          Among other function types, Excel has a database type. A cousin of the SUMIFS function, DSUM function adds the numbers in a database or list that match conditions you specify in your worksheet:

17. RAND function.

          Random number generation can come in handy during the creation of secure passwords, however you would have to get inventive to bypass 0 to 1 range limitation of the RAND function, or why not customize your own range, via the RANDBETWEEN function? Keep in mind that both of these functions are volatile and will recalculate dynamically.

BONUS (!). SUMPRODUCT function.

          There is not much need to sell you on using one (!) function that can sum, count, or find an average, minimum, maximum, product, standard deviation, variance: all on its own. If still not convinced in utter usefulness of the SUMPRODUCT function, consider the fact that it actually recalculates based on the filtered rows:

          Did I miss any of your favorite functions?!

          You can follow all of the examples listed above via the file download .