5 powerful Excel functions you are not using

 When it comes to usability and application of Excel, it is almost as true as if it is an acknowledged fact. There is almost no profession in management of which Excel can’t help on way or the other.

However, I observed that majority of Excel users don’t progress beyond certain functions and their combos, keyboard shortcuts or some features. And all this still very basic.

Comparing the progression of Excel users with the rest of the humanity sometimes make me think we are still celebrating the invention of wheel whereas the children of humans are discussing us as ancient tribes in their history books.

For example:

1. We are successfully orbiting around the Jupiter these days and at our end VLOOKUP vs INDEX+MATCH is still a debate
2. We have self driving cars and working on hyperloop and we are still unaware of full potential of named ranges let alone structured referencing system Excel tables.
3. Already know about Olympus Mons but Excel users still don’t know about PowerPivot.

Not blaming Excel users, its probably the fact that one is interested in learning Excel only to the extent needed. If basic knowledge does the needful one hardly cares to learn more.

But today we are looking few functions that are extremely versatile and yet hardly used by Excel users. Here is my list of FIVE functions you must immediately have on your finger tips to deploy as and when needed!

So lets get started!!

1. DSUM – Read it as “THE SUM” function

You must have learnt SUM in your early days of Excel and later might have learnt about nested IF statements to do conditional sum and you might have already learnt about SUMIF and SUMIFS.

You think you can do everything with these functions without having a nervous breakdown? Lets put it to test and consider following data:

But there is a sum function that to me is as powerful as all these sum functions combined i.e. DSUM.

• To learn about DSUM in detail with examples head over to this tutorial: Excel DSUM Function – “The” Sum function you MUST know

2. DATEDIF – Save yourself some time and time functions

To me it is DAT-DIF i.e. “date differential” and not DATED-IF as I don’t think this formula meant to solve conditional arguments.

Though this function is meant for compatibility purposes and is so hidden/ignored that even if you type it in Excel you won’t see any tooltip for this formula or function. It is the simplicity of this function that I love it especially if one is calculating difference between dates and doing ageing analysis or simply calculating age of asset/instrument or humans.

• Check out this tutorial to see this function in action and how it can be used: Making Birthday and Age Calculator in Excel [How To + Dedication + Prayers]

3. NETWORKDAYS / WORKDAY – It shouldn’t be much of a work

Calculating number of working days between two dates can be cumbersome as non working days include weekends and holidays and both tend to change with different jurisdictions.

To make this task easy Excel has NETWORKDAYS function. One just has to mention the start and ending dates with the option mentioning holidays as a range of cells. Even better formula is NETWORKDAYS.INTL which let you choose which day of the week is weekend.

• For more on this read this detailed tutorial: Calculate number of Work Days in Excel using formula

WORKDAY function on the other hand determines the working day from a certain date after considering specific number of working days. This helps managers in many ways. For example determining due date of certain invoice.

WORKDAY.INTL adds more to flexibility and let you choose which day(s) of the week is(are) weekend(s).

Here is one situation:

Mr. A sent invoice of 10,000 to Mr. B payable in 20 (working) days. Invoice was issued on January 10, 2017. What is the due date of invoice?

By simply adding 20 to said date will render a wrong due date for not excluding weekends:

=”1/10/2017″+20 = 1/30/2017

or better yet use DATE function to enter dates as entering dates as text can be problematic:

=DATE(2017,1,10)+20 = 1/30/2017

WORKDAY function will help us get the correct due date considering the weekends during calculation:

=WORKDAY(“1/10/2017”,20) = 2/7/2017

WORKDAY function considers Saturday and Sunday as the weekends. But not all jurisdictions have the same weekend. Sometimes it is just Sunday or it is Thursday and Friday. For such situations we use WORKDAY.INTL in which we have the option to use weekend combination.

Consider the same example as before with slight change as following:

Mr. A sent invoice of 10,000 to Mr. B payable in 20 (working) days. Invoice was issued on January 10, 2017. What is the due date of invoice? Weekend is Friday only.

For this situation formula will be as following:

=WORKDAY.INTL(DATE(2017,01,10),20,16) = 2/2/2017

4. SUBTOTAL – A multi-tool of functions

Normally the term subtotal refers summing up partial set of figures out of larger group, however Excel’s SUBTOTAL function can perform one of eleven operations which users can select through numbers against function_num argument.

Main benefit of using SUBTOTAL over SUM, AVERAGE or other functions is that it excludes filtered out rows/columns from calculation.

Syntax of SUBTOTAL function is as follows:

SUBTOTAL(function_num,ref1,[ref2],…)

Function_num helps determine the type of operation user is intended to perform using numbers 1 through 11 or 101 through 111. Functions against 1 through 11 and 101 through 111 are same but their application is a little different as 1 through 11 includes manually hidden rows whereas 101 through 111 excludes manually hidden rows from calculation. Filtered out results are always excluded from calculation.

Here is one example:

In the above illustration you can see that if results are not filtered, sum operation through SUBTOTAL function works just like SUM function of Excel.

However, once you filter the results, sum will change and will consider only visible rows as shown following:

However, as we used “9” as function_num therefore, by manually hiding a row, it won’t change anything unless we edit the formula and make it 109 so that formula exclude not only filtered out results but also manually hidden rows. Following illustration explains:

5. INDIRECT – Superhero when nothing directly works

A bitter sweet and not a strong love affair for many, INDIRECT function is one of those that are avoided most of the time by Excel heads. I don’t blame INDIRECT function for its patchy nature as the scenarios that require its use are dodgy in itself.

But what I love about this function is that many a times it just just does the job like no other. The basic task of this function is to return cell reference by processing text string. This ability is much valuable.