While Project Planners and Schedulers tend to disagree on which scheduling software is the best in the market, one area of universal agreement is the importance of Microsoft Excel in the life of a Project Planners or Scheduler. So today, I am going to present the Microsoft Excel functions (in-built predefined formulas) that I would recommend to a Junior Planner or Scheduler to help improve their proficiency in Excel.

XLOOKUP

One of the most common use schedule data export to Excel is to look up a value and find another value. And while LOOKUP, VLOOKUP & HLOOKUP have all served us well in the past, there is a new kid on the block named XLOOKUP which is faster, efficient and addresses all the limitations of the other 3 lookup functions. XLOOKUP can search in any direction (up, down, left, right) and by default returns exact matches.

IF & IFS

This is my most favourite Excel function as it allows Planners and Schedulers to easily check if schedule data meets a specified condition and if it does, instruct Excel to do something but if does not meet the specified condition, again ask Excel to do something else.

The IF function is a powerful and allows you to have either the simple 1 IF statement in a formula or a nest of up 64 IF statements in a formula. While nested IF statements might be tempting to implement, my recommendation is to go IFS function when you need to test more than one condition. The IFS statement returns the value of the first condition that is TRUE and should always be used in place of nested IF statements.

An IFS statement with multiple conditions is much easier to read and maintain than nested IF statements, which are more prone to errors as the logic needs to calculate correctly through each condition.

NETWORKDAYS & EDATE

Almost all schedule data exported to Excel include dates fields and 2 functions to have in your toolkit are NETWORKDAYS and EDATE.

If you have got the standard 5-day week calendar in use in your schedule, then should be using NETWORKDAYS to calculate durations in Excel as this will give you durations aligned to the 5-day week calendar in your scheduling software.

And if you would like to calculate the number of months before or after a date, EDATE is your go to function. You cannot go wrong with this function when it comes to determining delivery date for a procurement item based on lead-times given in months.

SUMIF & SUMIFS

These 2 functions come in handy when you need to add up data, e.g. resource units or cost, based on certain conditions. Use SUMIF for summation when you only want one condition or criteria met and use SUMIFS for summation when you want multiple conditions or criteria met.

SUBTOTAL with SUM as 1st argument

I love the SUBTOTAL function because by default it excludes rows that are filtered out in Excel and therefore removes the danger of adding up values that are filtered out. The SUBTOTAL works with 11 other functions, but I mainly use it with the SUM function, which has function_num of 9. The formula usually looks like this = SUBTOTAL(9, range_to_sum_up).

A practical example of using SUBTOTAL with SUM as 1st argument, is when you a cell that totals up resource units or cost and you would like this cell to only show the total for the rows displayed when a filter applied.

COUNTIF & COUNTIFS

These functions are like the SUMIF and SUMIFS functions but instead of summing up values, they count the number of cells that meet a given condition or criteria (COUNTIF) or meet multiple conditions or criteria (COUNTIFS). A use case for these functions will be to count number of milestones achieved by cut-off date or number of milestones forecasted in the next reporting period.

TEXTJOIN

TEXTJOIN is the last but not the least function I would recommend to budding Planners and Schedulers. It allows one to join data from 2 or more columns together to form one new set of data. And there are so many schedule data use cases for these functions. You can combine Activity ID (Task ID) with Activity Description (Task Name) or Resource Name with Activity Description (Task Name), etc. You could just about perform any type of combination you wish. The best part is it provides you with delimiter and option to ignore empty columns.

Final Word

When next you are manipulating schedule date in Excel, give these 10 functions a shot. I bet you are bound to find use cases for these functions but if you are struggling to find one, either leave a comment below or send me a message via the contact form, with a brief description of your schedule data and what you want to achieve.

To the experienced planners reading this, is there any other Excel function you would recommend to a budding Planner or Scheduler?