4.9 C
London
Saturday, October 16, 2021
Home Business & Finance 14 Microsoft Excel Functions Every Project Manager Should Know

14 Microsoft Excel Functions Every Project Manager Should Know

Whisper Town Podcast Links & Player

Stream the podcast on this link >>>> Podbean (Spotify & Apple Podcast links coming soon) or on the player below

🎧

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.

1. TODAY

This function simply returns today’s date. The is very useful for creating dynamic reports relating to ‘today’s’ date.

Excel_today_screenshot

2. WORKDAY

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:

  1. Start Date
  2. 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.

workday_screenshot

An example use case: Providing the finish date of a task relating to the start date.

3. NETWORKDAYS

This function counts the number of business days between two dates.

The two mandatory parts of this function are:

  1. Start Date
  2. 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

network_days

4. MIN

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.

min_screenshot

An example use case: Finding the lowest project cost item

5. MAX

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.

max_screenshot

An Example Use Case: Finding the highest project cost item

6. AVERAGE

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.

average_screenshot

An Example Use Case: Finding the average project cost item

7. VLOOKUP

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

  1. Look value – This is the number you want to match against
  2. Table Array – This is the table that you want to lookup
  3. Column Index – The is the column which has the specific information you want to find, using the look up value as the identifier
  4. Range Look Up Type – I only ever look for ‘Exact matches’

Example of the VLOOKUP Function

Vlookup fixed

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.

vlookup_screenshot2

8. INDEX & 9. MATCH

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).

index_match_screenshot

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

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
index_screenshot_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

Match has three mandatory parts of its function:

  1.       Lookup value – the number you want to look up
  2.       Lookup array – the range where you want to find the lookup value.
  3.       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.

match_screenshot

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.

index_match_2

An example use case: Data lookups when Vlookups are unsuitable.

11. LEFT & 12. RIGHT

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.

Left_screenshot

Example Use Case: Capturing extension numbers from long telephone numbers

13. IF

This function enables you to specify an action based on the outcome of a sum.

  • 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”.

If_screenshot

An example use case: Generating a status for individual rows of data

14. COUNTIF

This function will count a range of cells based on one criterion being met.

  • Range
  • Condition

In this example below, I asked Microsoft Excel to count the number of sites that had more than 500 users.

countif_screenshot

Example Use Case: Counting the number of scores that have a particular risk rating

15. CONCAT

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.

concat_screenshot

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.

Anthony Akadiri
Anthony Akadiri
I'm a writer who doesn't like writing many words and a management consultant with a disorganised social life. I like tech, business, positive people and long holidays 😀

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

- Advertisement -
- Advertisement -

Our Writers

Anthony Akadiri
7 POSTS0 COMMENTS
Stephen Hugh-Kong
4 POSTS0 COMMENTS

COVID-19 #stayalert

All countries
215,764,450
Total recovered
Updated on 14th October 2021 7:08 pm

VIP List

Flystroke VIPs get exclusive content. Do you want to know what you’re missing?

Financial Markets

Personal Finance News & Tips

>