Inventory Excel Formulas You Should Know

We know… You didn’t get into hospitality to learn Excel and Google Sheets. You probably didn’t get into it as an excuse to do inventory, either, we’re guessing. But that’s just part of what being a go-getting restaurateur is. Some of it is glamorous tasting events, menu-planning and bossing your kitchen staff around. Some of it (most of it) is the unsexy but very, very important work of administration and maintenance. And here you are: doing and having it all. 

Manage inventory with ease with Provi's Inventory Management Spreadsheet. Download today!

But don’t think that you need to be an expert in spreadsheets in order to use them effectively for your restaurant or bar. There are plenty of shortcuts that will help you keep track of your inventory while minimizing any human error. So to make your life a little easier, we’ve put together a list of some key inventory Excel formulas that will help you save on spreadsheet time so you can get back to sampling local whiskeys and reinventing the Wagon Wheel. 

Spreadsheet formulas for bar and restaurant inventory management

Here are a few critical Excel formulas that will help you track inventory with ease. 

Quick Sums Formulas

1. SUM - The most basic inventory formula, allows you to add together all numbers within a specified row or column. To execute, select the last value in said row or column and hit Alt+.
   
   
2. SUMIF - Allows you to add together all numbers that exist within a certain specification ie, a specified patron, item type, date, etc.
   
   Enter: =SUMIF(RANGE,CRITERIA,[sum_range])
   
   
3. SUMPRODUCT - Allows you to calculate averages across a given range of products and actions, such as shipped products, returned products, sales for specific products, etc.
   
   Enter: =SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL

Stock Formulas

1. INCOMING STOCK - Allows you to accurately calculate how much new stock is being introduced to your inventory. This allows you to maintain an accurate count of specific items within your physical inventory.
   
   Enter: =SUMIF(INCOMINGS[PRODUCT CODE];[@[PRODUCT CODE]];INCOMINGS[QUANTITY])
   
   
2. OUTGOING STOCK - Similarly to the above formula, this one helps you to accurately calculate how much stock is being taken out of your inventory. This allows you to avoid over-selling your inventory.
   
   Enter: =SUMPRODUCT(($B5=item)*(movtype=“Outgoing”)*(quantity))
   
   
3. STOCK LEVEL - This allows you to easily get an accurate idea of the current number for each individual product within your inventory by combining the output from the first two formulas.
   
   Enter: =[@[INITIAL STOCK]]+[@INCOMINGS]-[@OUTGOINGS]

Finding and Matching Formulas

1. FIND/LOOKUP - Allows you to browse your entire inventory for specific items of search terms. Use FIND to search for exact-word items or LOOKUP to execute a more general search for cells containing a similar term.
   
   Enter: =FIND(TEXT,WITHIN_TEXT,[START_NUMBER])
   
   OR: =SEARCH(TEXT,WITHIN_TEXT,[START_NUMBER])
   
   
2. VLOOKUP - Allows you to not only find specific items but also link those items to other related values, such as the individual products within a specific order.
   
   Enter: =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, [RANGE_LOOKUP])
   
   
3. INDEX - Allows you to perform a similar function to the above but can do so nonlinearly (ie from different columns, etc. rather than exclusively left to right).
   
   Enter: =INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
   
   
4. MATCH - Allows you to perform a function identical to the above. Uses the same formula.
   
   
5. LEFT/RIGHT - Allows you to pull a “substring” from a string of data within a given cell, beginning from either the left or the right of that cell. For example in a cell containing “Fosters - 12 Cases” you could pull either the product name or the case number.
   
   Enter: =LEFT(SELECT CELL,NUMBER)
   
   OR: =RIGHT(SELECT CELL,NUMBER)

Priority/Computing Formulas

1. RANK - Allows you to create an ordered list of products based on a certain specification. As a common inventory excel spreadsheet example, this means inventory levels, quantities sold, or profit margins.
   
   Enter: =RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])
   
   
2. AVERAGEIF - Allows you to calculate the average (inventory level, quantity sold, profit margins, etc.) from a list of products.
   
   Enter: =AVERAGEIF(SELECT CELL, CRITERIA,[AVERAGE_RANGE])
   
   
3. CONCATENATE - Allows you to combine data from different cells into one cell. Sometimes used to create SKUs, product description labels, etc.
   
   Enter: =CONCATENATE(SELECT CELLS YOU WANT TO COMBINE)
   
   
4. LEN - Allows you to quickly count the number of text characters within a given cell. This is helpful for specific product numbers or codes based on length.
   
   Enter: =LEN(SELECT CELL)
   
   
5. COUNTA - Allows you to discover whether any data is missing from your inventory by finding and indicating any empty cells within a spreadsheet.
   
   Enter: =COUNTA(SELECT CELL)
   
   
6. COUNTIF - Allows you to calculate the number of specific cells which conform to a specified standard.
   
   Enter: =COUNTIF (range, “criteria”)
   
   
7. TRIM - Allows you to remove data from a cell such as extraneous grammar or spacing which may make a specific cell invisible in broader searches.
   
   Enter: =TRIM(“text”)

Miscellaneous Inventory Formulas

1. VALUE - Allows you to change how something is written out within a cell, most commonly shifting a text-formatted number to a numerical value.
   
   Enter: =VALUE(“text”)
   
   
2. DAYS - Allows you to calculate the length of time passing (in days) between certain inventory events such as orders for specific products or more general inventory income or turnover.
   
   Enter: =DAYS(SELECT CELL, SELECT CELL)
   
   
3. MINIF/MAXIF - Allows you to determine the lowest or highest value within a specified criteria, such as the lowest purchase amount for a given order or the highest number of units in an outgoing shipment within the last year, etc.
   
   Enter: =MINIFS(RANGE1,CRITERIA1,RANGE2)
   
   OR: =MAXIFS(RANGE1,CRITERIA1,RANGE2)

How Provi Can Help With Inventory Management

Provi makes beverage ordering easier than ever, with an all-in-one marketplace that keeps track of every incoming and outgoing shipment and automatically modifies your inventory with each Bloody Mary poured.

Provi lets you ditch the spreadsheet (we should have opened the post with this, huh?) and perform inventory at-a-glance to know what the top sellers are, what’s running low, and even when to reorder so you can have everything you need behind the bar, just when you need it.

Sign up for a free Provi account today.