Working with Excel can be challenging, and even more so when it comes to managing your product inventory. Handling large libraries of products, codes, prices and specifications can often be a significant drain on your product team’s resources, and can lead to the input of errors or duplicate data.
One way to overcome this obstacle is by making use of Excel formulas for inventory management. These can save your product team time when locating, adding and editing data, providing shortcuts for finding and combining data. In addition, with the correct use of formulas within your inventory management spreadsheet, it’s easier to extract analytic conclusions and better inform business decisions.
We’ve compiled a list of the best 20 Excel formulas that product managers should utilize, as well as a downloadable product inventory spreadsheet template.
It’s also worth considering a PIM system for your product management. This platform allows you to import all your Excel documents to centralize, enrich and automate your product data. Sign up for a free trial today to find out how PIM can save you time, improve your product experience and drive revenue.
20 Excel formulas for managing product inventory
SUM
One of the most basic formulas for managing products and data in Excel.
The SUM formula allows you to add up values in a row or column simply by selecting the last cell in the desired row or column and pressing Alt+.
SUMIF
The SUM formula can be expanded with SUMIF, which enables you to add up all the values that meet certain set criteria. For example, the total value of products for a specific customer, or the total sales for a product in a particular channel.
=SUMIF(RANGE,CRITERIA,[sum_range])
SUMPRODUCT
This is another basic function for adding up, subtracting, multiplying or dividing products from a range. You can use it to calculate average shipments and returns, average sales prices by product or profit margins for each product.
=SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL
INCOMING STOCK
Managing the number of units for each product that arrives into your inventory is essential to avoid any disparities between the numbers from your sales channels and your actual inventory. With this formula you can manage your stock level appropriately.
=SUMIF(INCOMINGS[PRODUCT CODE];[@[PRODUCT CODE]];INCOMINGS[QUANTITY])
OUTGOING STOCK
The counterpart to INCOMING STOCK, this formula measures the quantity of stock sold and shipped, to avoid overselling issues.
=SUMPRODUCT(($B5=item)*(movtype=“Outgoing”)*(quantity))
STOCK LEVEL
The two previous values can be applied to this formula which enables you to manage the current stock level for each type of product in your inventory.
=[@[INITIAL STOCK]]+[@INCOMINGS]-[@OUTGOINGS]
FIND/LOOKUP
These are other basic formulas for browsing your inventory in Excel, and are particularly helpful if you have a large catalog of SKUs.
This function allows you to isolate specific data (FIND) or perform a wider search (LOOKUP). For example, with FIND you can find cells that contain the exact word “sock”, while if you enter “sock” with LOOKUP, it will also show you results of cells that contain “sock”.
=FIND(TEXT,WITHIN_TEXT,[START_NUMBER]) OR =SEARCH(TEXT,WITHIN_TEXT,[START_NUMBER])
VLOOKUP
This formula enables you to locate data and also combine them with another value. For example, you can look up the total value of an order and link it to the product units that it contains.
A new version is XLOOKUP,
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, [RANGE_LOOKUP])
INDEX and MATCH
A limitation of the VLOOKUP formula is that it only reads data from left to right.
If you need to find data in any location in a spreadsheet and from various different columns, INDEX and MATCH may be more helpful. For example, you can use it to find out the yearly net profit provided by a product by using its code cell.
=INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
LEFT/RIGHT
This function enables you to extract data from the beginning and end of a cell, according to the type of data it contains.
=LEFT(SELECT CELL,NUMBER) =RIGHT(SELECT CELL,NUMBER)
RANK
With this formula you can select a range of values and see how they rank, in ascending or descending order. For example, you can check which products are selling the most, which have the highest stock levels or which require the largest orders.
=RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])
AVERAGEIF
This is similar to the previous formula, but it is used to extract the average from cells with certain criteria.
=AVERAGEIF(SELECT CELL, CRITERIA,[AVERAGE_RANGE])
CONCATENATE
=CONCATENATE enables you to combine different types of data (numbers, text, dates) in one single cell. It is a function that is commonly used in Excel for generating SKUs.
=CONCATENATE(SELECT CELLS YOU WANT TO COMBINE)
LEN
The =LEN formula is an Excel function that allows you to find out the number of text characters in a cell. This shortcut is useful for identifying different types of product codes according to their length.
=LEN(SELECT CELL)
COUNTA
Data omission is one of the main problems when managing product catalogs and inventories. A PIM system allows you to locate missing or incorrect data within your digital product catalogs automatically.
However, if you require a manual function in Excel, the =COUNTA formula will identify any cells that are empty.
=COUNTA(SELECT CELL)
COUNTIF
This is a complementary formula to the previous function, which counts the number of cells that meet a determined criteria.
=COUNTIF (range, “criteria”)
TRIM
This function allows you to delete content from a cell. It is mainly used to locate excess spaces between words, which could cause problems later when performing internal searches for terms.
=TRIM("text")
VALUE
This formula allows you to change the format of the data in a cell. For example, a number formatted as text may be located and changed to a number format. This function is important for performing operations in Excel that require numeric values.
=VALUE("text")
DAYS
With this formula you can calculate the number of days between certain dates in cells. This is useful information for analyzing the life cycle of products in your inventory and calculating the average number of days between stock orders. This enables you to estimate when you will need to place new stock orders or have them ready for your distributors or customers.
=DAYS(SELECT CELL, SELECT CELL)
There is also a complementary formula if you just want to obtain the number of working days between two dates:
=NETWORKDAYS(SELECT CELL, SELECT CELL,[numberofholidays])
MINIF and MAXIF
This formula will show you the minimum figure from a set of values, such as the lowest purchase or sales price for a product.
=MINIFS(RANGE1,CRITERIA1,RANGE2)
Similar to the previous formula, this function will show you the maximum figure from the cells that meet the determined criteria.
=MAXIFS(RANGE1,CRITERIA1,RANGE2)
Additional functions to manage your product data with Sales Layer
In addition to the above Excel formulas for product managers, importing them into the Sales Layer PIM system also provides you access to more than 50 formulas that make it quicker to locate, edit and extract data.
What kinds of formulas can you use in Sales Layer?
- Concatenate chains, fields and functions
- Filter data by modification date
- Format text
- Check or replace values
- Add labels to a list
- Extract data from table fields
- Insert or modify data in a table
- Obtain URLs and image names for exporting
- Execute mathematical formulas
- Delete excess spaces
- Change currency
- Locate references
Aside from Excel formulas, there are countless reasons to consider switching to a PIM system for your product and inventory management. Combine thousands of spreadsheets, save your product teams hundreds of hours, and centralize your data in one platform with Sales Layer.
Sign up for a free 30 day trial today and find out how PIM can make life easier for your product teams and better for your customers.
