Weekly Logistics List | 5 Excel functions that every logistics manager should know

This week we look at 5 Microsoft Excel functions that every logistics manager should know how to use.

If your anything like me then you are probably using excel on most days either reading reports or creating them to monitor or measure the performance of your logistics operations.

Over the years I have discovered a number very useful excel functions but the 5 listed below are the ones that I most regularly use.

Microsoft Excel

  1. VLOOKUP
  2. TRIM
  3. CONCATENATE
  4. NETWORKDAYS
  5. WEEK BEGINNING DATE

A summary of what each function does and a practical example of how to apply each of the functions is described below.

To download a FREE copy of a spreadsheet that provides practical examples of each of the functions – Sign Up to the Weekly Logistics List by completing the subscription form below.



VLOOKUP

VLOOKUP is by far the function that I use the most. The VLOOKUP function allows you to find things in one sheet and display them in another.

For example if you have an inventory list with the number of units on hand for each SKU (stock keeping unit) in one spreadsheet but the price for each SKU is stored in another sheet. Assuming the part number is exactly the same in both lists then you can use the VLOOKUP function to find the price for each SKU and insert it into the inventory list sheet. Or vice versa – add the units on hand to the price list.

The basic syntax is =VLOOKUP (cell content of item to find, range in which to find the item, column in range to return,true or false)

For more information – CLICK HERE

CONCATENATE

The CONCATENATE function allows you join the data in one cell with the data in another.

For example, many SKUs will have variants of the base SKU e.g. size and color for apparel and footwear. This can be useful in the example above if the SKU format in the price list is different to the SKU format in the inventory list and you need to make them the same in order to perform the VLOOKUP function

The basic syntax is = CONCATENATE(content of cell 1, content of cell 2, content of cell 3)

Other option is to use the amersand & sign to get the same result =(content of cell 1 & content of cell 2 & content of cell 3)

The CONCATENATE function has  been replaced by the CONCAT function in the lastest versions of excel

For more information – CLICK HERE

TRIM

Quite often when you receive or download data from service provider applications the data in each cell can include irregular spacing between elements or spaces at the the start or end of each cell. This is becomes problematic when using the VLOOKUP  function (see above) as the function relies on the format of the data in each cell to be exactly the same in order to get a match.

The TRIM function removes all spaces from the data element except for single spaces between the data in the cell and ensures that you can find the data you are looking for.

The basic syntax is =TRIM(cell conetent)

For more information – CLICK HERE

NETWORKDAYS

The NETWORKDAYS function returns the number of business days between two dates, excluding weekends and, optionally, the holidays you specify. The start and finish dates must always be entered and holiday dates are optional.

As an example, this formula is great for calculating the number of work days (Monday to Friday) it takes from the time an order is received to the time it is delivered. Typically carriers do not deliver on the weekend and therefore the total transit time will be adjusted to take this into account i.e. an order dispatched on Friday and delivered on Monday will calculated as being delivered in one day as opposed to three.

The basic syntax is =NETWORKDAYS(start_date, end_date, [holidays])

For more information – CLICK HERE

WEEK BEGINING DATE

Let me first point out that WEEK BEGINNING is not actually an Excel formala – its an adaption of another formula that I found in a post over at the MrExcel forum (see link below).

Using this function has become invaluable for my weekly reporting as it assigns a common data element to each line of the report which can then be used for filtering data in the spreadsheet and creating graphs or pivot tables based on a weeks activity.

To be honest, I don’t understand the maths behind the formula but one thing I do know is that it works and that it has drasticly reduced the time it takes to prepare my weekly weekly reports!

The basic syntax is – =date – MOD(date 2,7) – date being the actual date of activity

For more information – CLICK HERE

Another one of my go to resources for all things excel is the Excel Addict – Francis Hayes sends out a weekly email  “Spreadsheet Tips From An Excel Addict” and has massive archive of practical tips on various excel functions and for creating spreadsheets.


The “Weekly Logistics List ” is a weekly email  especially curated for logistics and supply chain managers.

Try it out. You can unsubscribe at any time but hopefully you’ll see some value in what I send out each week.

To sign up to the Weekly Logistics List and to download a FREE copy of the spreadsheet that provides practical examples of each of the functions described above enter your details below.



Feel free to provide feedback on the above list or share your “Logistics Lists” with the 3plmanager.com community by making a comment below

Leave a Reply