8 Excel Functions Every Logistics Manager Should Know in 2023

Does the thought of Excel spreadsheets make you cringe? It might be time to rethink your attitude on the tool.

If you work in supply chain logistics, this handy tool can help you in a lot of ways, from creating inventory reports to measuring key performance indicators (KPIs) of your warehouse management.

Long story short: Microsoft Excel is NOT just for accountants. 

You can use Excel to help monitor and manage various steps of the material handling process.

With the right formulas, you can save yourself stress, time, and effort. 

For example, you can use the Excel FORECAST function to predict data values based on existing values. 

So, you might use it to predict inventory turnover for coming months — based on the inventory turnover values for previous months.

You can then use this information to improve your inventory planning, reordering products as needed.

In addition to supporting good capacity planning, Excel can help with tasks like organizing and finding information.

But there are literally hundreds of Excel functions in existence (seriously — check out this list from Microsoft for a full roundup).

The good news? You don’t need to know all of them. 

VLOOKUP function 

Let’s start with something simple: VLOOKUP — short for “vertical lookup.” 

This lets you find and add a certain value in your Excel spreadsheet.

Why might you use this function?

For example, say you have one spreadsheet that lists the price lookup (PLU) code for each type of inventory. 

A second spreadsheet tracks your inventory volume — how many pieces of each type of inventory you have.

You can use VLOOKUP to find the PLU for the relevant piece of inventory and add it to the inventory count list. 

Or, switch it around, and add the number of pieces of inventory you have for each inventory PLU to the inventory PLU list.

How do you use the formula?

Here’s the basic VLOOKUP formula you’d enter into your Excel cell:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value)

You have the additional option of specifying whether you want an approximate match (TRUE) or exact match (FALSE), in which case your formula will look like this:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, TRUE)

or

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, FALSE)

If you don’t specify TRUE or FALSE, the default value will be TRUE (approximate match).

Here’s a tutorial on using VLOOKUP.

IF function 

The IF function or IF statement lets you determine whether a selected piece of data meets certain conditions. 

You can use it to make logical comparisons between an actual and expected value.

Why might you use this function?

Say you have a spreadsheet tracking inventory levels. 

When inventory is less than 100 pieces, you want to reorder.

You can set up an IF function so that if you add a number less than 100 into the column tracking inventory numbers, you get an easy-to-identify alert.

How do you use the formula?

Here’s the most basic type of IF formula you might put into your Excel cell:

=IF(AND(Something is True, Something else is True), Value if True, Value if False)

Here’s a tutorial on using IF.

FORECAST function 

The FORECAST function lets you predict a future value based on existing values already added into your Excel spreadsheet.

The FORECAST function will create a new worksheet with tables showing the past values it’s drawing data from and tables with the predicted values.

Why might you use this function?

Say you want an estimated forecast of inventory turnover for the month of December 2022. 

The holiday season can mean more sales, and you want to be prepared.

You can use FORECAST to calculate a figure based on previous data from December 2021, December 2020, December 2019, and so on. 

The formula can use inventory turnover numbers from those months to forecast December 2022 turnover.

How do you use the formula?

Here’s the most basic type of FORECAST formula you might put into your Excel cell:

=FORECAST(x, known_y’s, known_x’s)

Here’s a tutorial on using FORECAST.

CONCATENATE function 

The CONCATENATE function lets you merge two columns of text. 

Instead of retyping text, you can just use this function to merge columns.

Why might you use this function?

Say you have one column with delivery drivers’ first names and another column with delivery drivers’ last names, and you want to merge the two. 

You can use the CONCATENATE function to bring them together into one column quickly.

How do you use the formula?

Here’s the CONCATENATE formula you might put into your Excel cell if you’re combining two columns of text:

=CONCATENATE(text1, text2)

Here’s a tutorial on using CONCATENATE.

TRIM function 

You can use the TRIM function to get rid of free spaces in a cell (except for single spaces between words).

The TRIM function is useful if you need to pull data from your Excel spreadsheet but extra spaces are cluttering the spreadsheet — making it impossible to pull legitimate data.

Why might you use this function?

Say you want to run a VLOOKUP search for an employee name, John Doe. 

But John Doe’s name has been added with spaces around it (“ John Doe “). 

This means the VLOOKUP won’t identify “John Doe” (without spaces on either side).

Before you run your VLOOKUP, you can run the TRIM function to nix those extra spaces. 

Then, your VLOOKUP for John Doe will be a success.

How do you use the formula?

Here’s the TRIM formula you might put into your Excel cell to get rid of extra spaces:

=TRIM(“ Text Where You Want Spaces Removed From Sides ”)

Here’s a tutorial on using TRIM.

Transpose function 

The TRANSPOSE function lets you change rows into columns or change columns into rows. 

If you prefer to view your data one way or the other, this can be a quick way to get your preferred view.

Why might you use this function?

Say you’ve added three rows of data into a spreadsheet: inventory ID number, description, and count. 

You have 100 pieces of inventory, so you have to scroll from left to right, on and on and on, to view the details about all 100 pieces.

You find it faster and easier to scroll down using your computer mouse, so you decide to TRANSPOSE the data. 

Now, you have columns instead of rows.

How do you use the formula?

Here’s how the TRANSPOSE formula would look:

=TRANSPOSE(row or column number where your data is:row or column number where you want your data to be instead)

Here’s a tutorial on using TRANSPOSE.

COUNT function 

The COUNT function figures out the total number of cells containing numbers within a given set of data. 

It only counts cells with numbers. 

You might want to use the COUNT function to organize data more quickly and easily.

Why might you use this function?

Say you have a spreadsheet of inventory stock keeping units (SKUs — unique numbers to help you identify inventory), plus other data like product descriptions and inventory names. 

But the only data you’re interested in is the SKU numbers.

You can use the COUNT function to figure out how many SKUs you have in the document and pinpoint them more easily.

How do you use the formula?

Here’s how the COUNT formula would look:

=COUNT(start point of data you want to search for number-containing cells:end point of data array you want to search for number-containing cells)

Here’s a tutorial on using COUNT.

NETWORKDAYS function 

You can use the NETWORKDAYS function to determine the number of business days between two dates. 

You can set it to exclude both weekends and holidays if you want.

Why might you use this function?

You might use NETWORKDAYS to figure out when a product will reach a customer after it’s shipped.

For example, if you ship a product on a Thursday and it’s supposed to get there in three business days, you can use NETWORKDAYS to determine the arrival time — as the formula will automatically exclude the weekends.

How do you use the formula?

Here’s how a basic NETWORKDAYS formula would look:

=NETWORKDAYS(start date,end date)

Here’s a tutorial on using NETWORKDAYS.

How is Excel most commonly used in supply chain management?

As you can see from the above examples, Excel formulas can help with many aspects of supply chain management. 

With these functions, warehousing and logistics services managers can:

• Organize and find information (for example, you might use functions like  TRIM, CONCATENATE, or VLOOKUP to find identifying information like employee names, inventory SKUs or PLUs, ID numbers, and more).
• Evaluate data (for example, you might use a function like IF to determine when it’s time to order fresh inventory).
• Improve capacity planning (for example, you might use a function like FORECAST function to determine if you should stock up on more inventory for the holiday season).

Circuit for Teams helps you quickly and easily manage drivers and delivery routes

As a logistics manager, you have a lot to keep track of. 

In addition to managing drivers, you have to manage details like making sure products ship on time in the right packaging and using the right shipping labels.

Any tool that can help make your life easier is a plus, right? 

Excel can help you save time and streamline your logistics and supply chain management.

You can use Excel templates to organize and find information, evaluate data, and improve capacity planning.

But Excel isn’t the only tool you should be relying on to streamline your work.

Another handy piece of technology every logistics manager should know about: Circuit for Teams.

Circuit for Teams plans the fastest sequence of delivery stops for your delivery drivers. 

The software takes into account real-world data like traffic jams and road patterns to find the fastest routes for your drivers.

With faster, more efficient routes, you can help drivers avoid time-wasters like traffic jams — cutting down on idling in traffic and reducing mileage. 

This is one way to help reduce transportation costs for your delivery services.

With Circuit, you can also give customers estimated delivery time windows and real-time notifications on when they can expect their deliveries. 

Customers enjoy the added convenience this brings.