Microsoft Excel

3 minute read

Excel Challenge 37: Keeping Track of Inventory

Deb Ashby

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

Take the challenge

Download the file to get started.


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. 

Stock inventory challenge

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.

closeup screenshot

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. 

stock inventory Excel challenge

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. 

stock inventory Excel challenge

  • 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.

 Excel image    

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

Download the file to get started.

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?

Start learning for free with GoSkills courses

Start free trial

Join the Excel conversation on Slack

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

Deb Ashby

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.