Working with Excel can be daunting, and even more so when it comes to managing inventories: hundreds of products, codes, prices, specifications, incoming and outgoing stock channels, etc.
One way to keep your product inventory in order and save time when locating, adding and editing data is to use formulas that provide shortcuts for finding and combining data, as well as extracting analysis conclusions.
We will now give you 20 formulas that every product manager should use, plus an extra: a downloadable Excel template for a product inventory that you can use to try out all these formulas.
And if you’d like one more secret, here’s number 21: a PIM system where you can import all your Excel documents to automate your product management.
20 Excel formulas for managing your product inventory
One of the most basic formulas for managing products and data in Excel.
The SUM formula lets you add up values in a row or column simply by selecting the last cell in the desired row or column and pressing Alt+.
The aforementioned basic formula can be made slightly more sophisticated: with SUMIF you can add up all the values that meet certain criteria. For example, the total value of products for a specific customer, or the total sales for a product in a particular channel.
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.
It’s essential to manage the number of units for each product that comes into your inventory. This will prevent any disparities between the numbers from your sales channels and your actual inventory, and will allow you to manage your stock level appropriately.
=SUMIF(INCOMINGS[PRODUCT CODE];[@[PRODUCT CODE]];INCOMINGS[QUANTITY])
The second key part of inventory management is the quantity of stock sold and shipped, to avoid overselling problems.
The two earlier values can be applied to another formula that enables you to manage the current stock level for each type of product in your inventory.
These are other basic formulas for browsing your inventory in Excel, particularly useful if you have a lot of products or 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])
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,
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))
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)
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.
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 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)
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.
Data omission is one of the main problems when managing product catalogs and inventories. A PIM system (Product Information Management) allows you to locate missing or incorrect data in your product range automatically.
But if you’d like to use a manual function in Excel, the =COUNTA formula will identify any cells that are empty.
This is a complementary formula to the previous function, which counts the number of cells that meet a determined criteria.
=COUNTIF (range, “criteria”)
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.
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 when you need to perform operations in Excel that require numeric values.
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.
Similar to the previous formula, this function will show you the maximum figure from the cells that meet the determined criteria.
Additional functions to manage your product data with Sales Layer
In addition to the formulas that you can use in your Excel spreadsheets, when you import them into the Sales Layer PIM system you will have access to an automatic form of management that avoids all common manual errors, with 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
And there are many more that you can try for yourself if you request a Sales Layer trial right now.
Save yourself hours of learning and, more importantly, hard work from using Excel formulas, and make your life easier with a more efficient, secure and precise automated PIM system.