Best Excel Functions in Supply Chain

No matter if you are just starting your professional career in supply chain or you are already working for some years in the supply chain, Microsoft Excel will be a that close friend of you,
that friend that will walk by your side all the way from your first until your last day at work.

In numbers,
Microsoft Excel has over 1 billion users around the globe. (yes – you got good the number).

I bring you in this post the Best Excel functions to use in Supply Chain

Amazon, Apple, McDonald’s, Procter & Gamble and Unilever.
Do you know what these have in common these 5 companies?
They have been recognized by Gartner in 2022 for their supply chain excellence.

What’s more,
do you know which spreadsheet program they use for their Supply Chain operations? Of course, Excel.

Knowing Microsoft Excel is one of the top 3 skills that will skyrocket your supply chain professional career – discover here the other 2 skills to unleash your potential.

First of all,
we need to understand what Microsoft Excel is about.

What is Microsoft Excel?

I am quite sure everyone knows what Excel is about.
In any case, If you hear this word for the first time,
let me tell you that Microsoft Excel is a spreadsheet program that would allow you to make any calculation that you could imagine.

The potential of Microsoft Excel is limitless.

In a more official definition,
Microsoft Excel is a software program created and owned by Microsoft that uses spreadsheets to organize data, numbers, and tables. Microsoft Excel allows you to do calculations, prepare visualizations and automate operations with VBA (VBA is a macro programming language called Visual Basic for Applications).

Useful Excel functions in Supply Chain

Supply Chain is all about tracking deliveries, monitoring stock levels, preparing risk assessments, and forecast estimations … all these actions are performed normally as a combination of two systems.
System1 could be software, such as Oracle, SAP, o9 and System2 in 90% of the companies would be Microsoft Excel.

See below the most useful Microsoft Excel functions you can use in Supply Chain

SUM:

This function allows you to add up a range of cells. (From the same sheet or other sheets/workbooks)
Format: =SUM([number1], [number2], …)
Example:
Count how many cells are filled in the range A1-A10

IF:

This function allows you to specify a logical test and then take different actions based on whether the test is true or false.
Format: =IF (logical test, [value if true], [value if false])
Example:
If the value in cell A2 is higher than the value in cell A1, write “Higher” otherwise, write “Lower”

COUNT:

This function counts the number of cells in a certain range.
Format:
Example: =COUNT([value1], [value2],…)
Count how many cells are filled in the range A1-A10

COUNTIF:

This function allows users to count the number of cells that meet specified criteria.
Format: =COUNTIF (range,criteria)
Example:In column C , count how many times the word in cell D2 (UK) appears

AVERAGE:

This function calculates the average of a range of cells.
Format: =AVERAGE([number1], [number2],…)
Example:
Calculate the average in the range A1-A10

MAX:

This function returns the largest value in a range of cells.
Format: =MAX([number1], [number2], …)
Example:
Show the largest number in the range – in this case, number 10.

MIN:
This function returns the smallest value in a range of cells.
Format: =MIN([number1], [number2], …)
Example:
Show the lowest number in the range – in this case, number 1.

CONCAT:

This function allows you to concatenate multiple text strings into one cell.
Format: =CONCAT(text1,text2,…)
Example:
In column D, combined what is written in column B and in column C.

VLOOKUP:

This function allows you to search for a value in a table and then return a corresponding value from the same row.
Format: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Example:
In column D , populate the stock level from the database in the Sheet2

If you want to learn more about how to use VLOOKUP in Excel,
these 3 minutes YouTube video will help you understand the vlookup function in detail:

Example of how to use Excel in Supply Chain

Let’s imagine you have a list of products in column A.
In column B you want to populate the description of the material, in column C the country where the code was produced, in column D the stock level and in column E concatenate the material description and the country of origin.

In cell D12 the total amount of stock and in cell D12 is the average stock for the products.

This is how our file looks like - you can download it down below

Step #1 Populate material description in column B
How will we do it?
You are right, we will do a vlookup.

Vlookup vs the sheet: Material description.

The material descriptions we are looking for are in column B of the sheet: Material description.
What’s next:
In sheet: Data_Base and cell B2 we will type: =VLOOKUP(A2,'Material description'!A:B,2,0) and we will run the formula down until cell B11.

Step #2 Populate the Country of origin in column C
We will do similar as we did for populating the Material Description.

Vlookup vs the sheet: Country of Origin.

What’s next:
In sheet: Data_Base and cell C2 we will type: =VLOOKUP(A2,'Country of Origin'!A:B,2,0) and we will run the formula down until cell C11.

Step #3 Populate Stock in column D
In sheet: Data_Base and cell D2 we will type: =VLOOKUP(A2,Stock!A:B,2,0) and we will run the formula down until cell D11.

Step #4 Combine in one cell the Material description and the Country of origin in column E

For this case, we will not use vlookup,
instead we will be concatenating both cells using the function CONCAT

In sheet: Data_Base and cell E2 we will type: =CONCAT(B2,C2)and we will run the formula down until cell E11.

Step #5 Sum the total stock in cell D12
For the total stock,
we will use the function =SUM([number1], [number2], …), for the range from D2 to D11 both included.

Step #6 Calculate the average stock in cell D13
For the average stock,
we will use the function ==AVERAGE([number1], [number2],…) for the range from D2 to D11 both included.

Rember you can download the file here to try the functions on your own.

The good news is that you can perform such calculations manually as seen in this post,
or you can do such calculations with just one click.
Imagine one click and 3 seconds later you get all results populated.
If you want to learn how to have everything automated in one click, this is here.

FAQ Excel Functions in Supply Chain

What Excel functions are used in supply chain management?

The most common Excel functions used in Supply chain management are SUM, AVERAGE and VLOOKUP.
For example when you need to calculate the stock level for a certain family of products within your scope. You would need to do a VLOOKUP vs the database, sum the total amount for the stock for the family and calculate the average stock. All this can be done with 3 simple formulas.

What do I need to learn in Excel for supply chain?

If you want to be fluent in Excel and shine in your Supply chain career, it is key that you will learn the basic Excel functions (as seen in this post), how to use Pivot Tables and how to navigate using Excel shortcuts in your keyboard.

Which are the best Supply Chain Excel courses?

There are plenty of Excel courses online you can attend, my two favourites are Coursera “Excel Skills for Business specialization” and Udemy “Microsoft Excel – from beginner to advanced”.
If you really want to progress super-fast in Excel and become an Excel guru,
I would recommend you book private sessions with Mister Paton where you can get a customized learning plan upon your needs. Here are the best Excel online courses here: