Used to store data and create reports, Microsoft Excel is an invaluable tool for all kinds of supply chain professionals.
Excel has so many functions, many of them specialized, that it would take a full-time job to master all of them. People working in the supply chain only need to master a few functions to get a lot out of this fantastic program. Below is a list of five key Excel functions for supply chain professionals.
5 Functions in Excel for Supply Chain Professionals
One of the most powerful and commonly used Excel functions, VLOOKUP but you locate data in one sheet and display them in another sheet.
For instance, if you have a list of inventory units per SKU on one sheet and a list of prices per SKU on a different sheet, you can use VLOOKUP to locate the price for each SKU and insert the prices into the sheet with inventory units. Or you can look up inventory units by SKU to be inserted into the price sheet.
To use this function, simply type, “=VLOOKUP (target content, range of cells to look, column within range for value to return, ‘true’ for exact match or ‘false’ for approximate match)”
This function lets you combine strings of characters (numbers and letters) in multiple cells. CONCAT can be useful if, for instance, you are trying to combine different SKU numbers for the same or similar inventory items, such as items with different colors that are otherwise identical.
To use this function, use the format “=CONCAT (cell1……)”
Although the global supply chain operates 365/24/7, supply chain professionals often deal in terms of workdays, or “business days”.
The NETWORKDAYS function is a convenient way to identify the number of work/business days between two dates. This can be a powerful tool when it comes to identifying the length of time it typically takes an order to ship from one location to another.
To use this function, use the format “=NETWORKDAYS (start date, end date, holiday(s) to exclude)”
Averages are a powerful data point when it comes to determining efficiency, which makes them a good tool in the supply chain.
If you’re familiar with the SUM function, you should know how to use the AVERAGE function: “=AVERAGE (cell1….)
Unfortunately, information for Excel sent from clients and customers often doesn’t come formatted as you might like, and one common problem is the inclusion of extra spaces, at the start or at the end of the cell. This isn’t just an aesthetic problem. Extra spaces can confuse functions like VLOOKUP.
The TRIM function is a quick and easy way to eliminate these extra spaces in selected Excel cells. The function removes any extra spaces not located between two items inside a cell.
To use this function, simply use the format, “=TRIM (cell1…)”
We Can Help You Flex Your Supply Chain Skills
At ZDA, we help job seekers take the most of their supply chain abilities. Please contact us today to find out how we can help you.