Excel Challenge 37: Keeping Track of Inventory

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. 
2. Download the Excel worksheet. 
3. Complete the challenge tasks.
4. Share your solution, discuss it, or ask for help in our Slack channel.

The challenge 🧠

The tasks 🏋️

Here’s the scenario to be solved from the download file:

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!

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!