Microsoft Excel is my preferred application for data analysis and building bespoke project dashboards and trackers. It is extremely flexible as it has countless functions and it provides a platform to develop custom functions too; I personally think it is one of the most flexible user applications ever created.
With the rapid development of sleek, easy to use applications aimed at data reporting and collaboration, Microsoft Excel is one tool, in my opinion, that still can’t be replaced.
I have gathered a list of 14 Microsoft Excel functions that feature heavily in the reports that I produce.
These functions should be helpful to Project Managers and anyone who has data analysis and reporting responsibilities.
This function simply returns today’s date. The is very useful for creating dynamic reports relating to ‘today’s’ date.
This function will return the date x number of business days ahead of a date you choose.
There are two mandatory parts to this function:
- Start Date
- Days (Working/business days)
The simplest way to use this function is to use a cell reference that contains the start date you need.
Standard date formats such as dd/mm/yy, don’t work with this function.
An example use case: Providing the finish date of a task relating to the start date.
This function counts the number of business days between two dates.
The two mandatory parts of this function are:
- Start Date
- End Date
Similar to the WORKDAY function, the simplest way to use this function is to use cell references that contain start and end dates you need.
An example use case: Providing the number of days from today until the target project completion date
This function returns the smallest value in a range of numbers.
All this function needs to work, is the range that contains the values you want to analyse.
An example use case: Finding the lowest project cost item
This function returns the largest value in a range of numbers.
All this function needs to work is the range that contains the values you want to analyse.
An Example Use Case: Finding the highest project cost item
This function returns the average (mean average) value of a range of numbers.
All this function needs to work is the range that contains the value of numbers you want to analyse.
An Example Use Case: Finding the average project cost item
Breakdown of the VLOOKUP Function
This function enables you to find values in a table.
Microsoft Excel does this by providing the value of a cell in the column you specify, based on the location of a number match.
The are four mandatory parts of this function
- Look value – This is the number you want to match against
- Table Array – This is the table that you want to lookup
- Column Index – The is the column which has the specific information you want to find, using the look up value as the identifier
- Range Look Up Type – I only ever look for ‘Exact matches’
Example of the VLOOKUP Function
If I wanted to find the number of users at site number 3, the formula in Cell B2 would return 367.
Tip:You should make sure that your lookup value are always unique!
An example of when you could use VLOOKUP?
You could use VLOOKUP to cross reference information from two separate lists which have the same unique identifiers.
If there is a match, a value will be shown. If there isn’t it will return N/A.
The formula in the image below asks Microsoft Excel to find the number 11, in the range of B5:B9. The result in G9 is ‘#N/A’ as the value could not be found.
2 Microsoft Excel functions that are stronger together
INDEX & MATCH is a combination of two Microsoft Excel lookup functions, which perform together in a similar way to VLOOKUP. Why use it then? (I hear you ask).
VLOOKUP has some limitations (which is probably why a new version called XLOOKUP has been created; however XLOOKUP is only available for Office 365 users).
VLOOKUP can only lookup values to the right of your lookup value.
In the example below, there is no way for me to use the ‘Site Number’ as the lookup value, as the Country column, is to the ‘Left’ of the lookup value column (B).
You can only look up one value within one VLOOKUP function
Vlookups can be resource intensive. If you have multiple Vlookups in a workbook, it can make Microsoft Excel perform slowly.
There two main options to consider if you want to VLOOKUP a cell, which is in a column to the left of your lookup value:
- Manually re-organise your spreadsheet
- Use Index and Match
INDEX and MATCH Explained
Although, Index and Match is a little bit more ‘fiddly’ than a VLOOKUP, it enables more advanced lookup scenarios.
I’ll start by explaining what Index does first.
Index enables you to find the value at an intersection point that you define.
There are three mandatory parts of this function are:
1. Array – the table you want to look up
2. Row – the row number you want to look up
3. Column – the column number you want to intersect with the row.
For example, if you want to find out the number of users at Lagos Castle, from the image below, you would need to specify the following in the INDEX function:
- the array is B5:D9
- the row number is 2
- the column number is 3
This is perfect if you can see the row and column you want to lookup. However, if you have a very large list, you are unlikely to know the column and row number for the index function.
Like VLOOKUP, you need a value (ideally unique) to lookup against.
The MATCH Function give us to ability to enable Microsoft Excel to generate the row number in the index function.
Match has three mandatory parts of its function:
- Lookup value – the number you want to look up
- Lookup array – the range where you want to find the lookup value.
- Match Type – I always select ‘Exact match’
In the example in the image below, I have used 2 as the unique reference. As 2 is the second entry in the range, Microsoft Excel has returned 2.
So, if we go back to the INDEX function, we need to replace 2 with the match function we just created. After doing this, Microsoft Excel will return the Number of Users at Lagos Castle, which is 197.
An example use case: Data lookups when Vlookups are unsuitable.
This function enables you to return the ‘left/rightmost’ characters within a cell.
The mandatory parts of this function, are
- Text – Indicated by cell reference
- Number of Character – the number of characters to return
In the example below, I asked Microsoft Excel to return the first three characters from the left of Patong House in Cell 5.
Example Use Case: Capturing extension numbers from long telephone numbers
This function enables you to specify an action based on the outcome of a sum.
- Value if True
- Value if False
In the example below, the function indicates, if cell B5 is less than 6, show the text “Sites number is correct”, otherwise show “Check”.
An example use case: Generating a status for individual rows of data
This function will count a range of cells based on one criterion being met.
In this example below, I asked Microsoft Excel to count the number of sites that had more than 500 users.
Example Use Case: Counting the number of scores that have a particular risk rating
This function enables you to join text strings together using text and cell references.
In the image below, I used this formula =CONCAT(“There are “,D3,” users”, ” “, “at “, C3) to produce the result in cell G2.
Use Case: Creating an automated dynamic highlight report summary with specific pre-defined data, pulled directly from your spreadsheet.
I hope that these 14 Microsoft Excel functions help you with producing reports for your stakeholders.
Leave a comment below with any functions that you think should also be in this list.