# Excel Challenge 37: Keeping Track of Inventory

Deb Ashby

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

If you run a small business and don't have the fancy software to keep track of your stock, you can leverage Excel to help you with this important task. Try this challenge and check out our solution!

To get started,

1. Watch the challenge video.
4. Share your solution, discuss it, or ask for help in our Slack channel.

Take the challenge

## The challengeπ§

We are in charge of keeping track of stock levels at our company. It is our responsibility to know when we are running low on certain products and when we need to order more.

To facilitate this, we need to set up a stock inventory database.

### 1. Create a filter

First, we need to create a filter in cell B5 that allows us to select ‘OK’, ‘Low Stock’ or ‘Order Now’ from a drop-down list.

• The filter should use cell references.

### 2. Determine the status

Next, we need to determine the status of each product’s current stock level.

• Use logical functions to do this.
• The status is determined by the value in the ‘Left’ column and the values in cells E4:E6.
• Copy the formula down.

### 3. Make the stock inventory dynamic

We need to ensure that if a new product is added to the stock inventory, any formulas automatically update.

### 4. Filter the stock inventory

Add a formula to cell B10 that filters the stock inventory list depending on what status has been selected in the drop-down list above.

• The status column does not need to be included in the filter.
• Use “No Records” as the optional last argument.

• Choose a different status from the drop-down menu and ensure the correct results are being returned.

### 5. Add a new product

Finally, check that the stock inventory is working correctly and it’s dynamic.

• Add another product to the bottom of the table.
• Ensure that the filter updates and the new record is included.

## The clues π΅οΈ

Here are some resources that might come in handy for this exercise.

 Read - A Breakdown of Excel's 10 IF Functions Read - How to Return Multiple Values from an Excel Lookup

Got it? Good!

Take the challenge

Tell us how you would solve it, then check out our solution below!

## The solution π‘

We hope you enjoyed taking part in this challenge!

## Hungry for more? π

Can't get enough Excel? π We recommend:

1. Trying our previous challenges to sharpen your other Excel skills. π€Ή
2. Subscribing to our YouTube Channel π» so you never miss another tutorial or future challenge.
3. Joining the GoSkills Slack community πͺ to connect and chat with other Excel nerds.

And check out our expert-led Basic to Advanced Excel course, where you can get certified in Excel at your own pace!

Ready to become a certified Excel ninja?