Track inventory stock-in and stock-out automatically in your inventory list in Excel. To automate stock tracking, we'll build on the basic inventory list template we created in the Create & Track a Basic Inventory List in Excel video. We’ll enhance the basic inventory list template by adding automation for stock tracking, setting up a stock transactions tracker, and creating a variance tracking tab for your physical inventory audit counts. This system will help you streamline your inventory management in Excel and stay on top of your stock levels efficiently.
Also learn how to set up Pivot Tables, Slicers, Timelines, Drop-down lists, and special formulas. Here is the formula used on the Master Inventory sheet to auto updated Quantity in Stock from the Stock Tracker sheet:
=SUMIFS('StockTracker'!E:E, 'StockTracker'!B:B, A4, 'StockTracker'!D:D, "Stock In") - SUMIFS('StockTracker'!E:E, 'StockTracker'!B:B, A4, 'StockTracker'!D:D, "Stock Out")
Get a jump start on this project with my automated inventory template that we create and use in this video, available for purchase: https://creatoriq.cc/44qUfjx
TIMESTAMPS ⏰
00:00 Create Inventory Stock Tracker in Excel
00:36 How to set up a stock-in and stock-out tracker in Excel
02:27 Set up a drop-down list in Excel
04:00 Set up a Pivot Table for Stock Transactions in Excel
05:15 Insert a Slicer for Pivot Table in Excel
05:50 Insert a Timeline for Pivot Table in Excel
06:24 Automating inventory quantity in stock updates in your master inventory list
09:21 Setting up a variance tracking sheet for audit counts
12:28 Analyze Inventory Data in Excel
COURSE 🎒
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro: https://www.youtube.com/playlist?list=PLb80efKTbWuNzAyHVMYrPOS7itBm5gWVs
WATCH NEXT 📺
Create a Basic Inventory List in Excel: https://youtu.be/GYChuAor3Zk
Analyze Inventory Data in Excel: https://youtu.be/cjQO6L9VXYQ
CHANNEL LINK 📺
https://www.youtube.com/@SharonSmith
Visit my Channel page on YouTube to see all my videos, playlists, community posts and more!
TEMPLATES 📄
Check out my helpful list of templates available for purchase: https://creatoriq.cc/43c51cv
Thank you for supporting my channel! 🌟
FREE DOWNLOADS ⬇️
✦ Visit https://mailchi.mp/6a0859ea0844/sharonsmithhrformulasdownload to sign-up for my e-mail list and get FREE downloads of super helpful spreadsheet formulas, dashboards and Org Chart templates for HR professionals.
CONNECT WITH ME 📎
Visit my website: https://www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: https://www.linkedin.com/in/sharonsmithhr
Twitter: https://twitter.com/SharonSmithHR
Instagram: https://www.instagram.com/sharonsmithlearning/
Facebook: https://www.facebook.com/SharonSmithLearning
GEAR ⚙️
🎙 Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
🖱 Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!)
🎥 Screen Recording Software: https://techsmith.z6rjha.net/NZG5b
📗 Green Screen: https://amzn.to/2DnHsY2
📸 Camera: https://amzn.to/39KvpQA
🔌 Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
RESOURCES 📚
✏️ JotForm: https://www.jotform.com/pricing/?utm_source=sharon-smith&utm_campaign=jf1&utm_medium=blog
🖥️ TechSmith Camtasia Screen Recording: https://techsmith.z6rjha.net/SharonSmithHR
Links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
SUPPORT THIS CHANNEL 🙌
- Hit the "$Thanks" button on any video, or
- Donate through my PayPal link: https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url
If you found this content helpful, please consider donating to my channel. Your donation, no matter what amount, is greatly appreciated and goes towards producing more content that enhances your productivity and elevates your skills.
You can also support my channel just by watching, liking, and sharing all my videos! Thank you so much! ❤️
PLAYLISTS 🎥
➤ Jotform Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuO84pPbnHdTdgurmxHgZcyD
➤Create A Letterhead in Word: https://www.youtube.com/playlist?list=PLb80efKTbWuO0RyeiYB0fEb55601yL4OT
➤Creating Fillable Forms: https://www.youtube.com/watch?v=YktZ3B-tyqs&list=PLb80efKTbWuOe1LfFMNExEabkRkB5ehcv
➤PowerPoint Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMcFRbsZO5wkXgYjBG4meRM
➤ Excel Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuNjHDGgNeuGrGwOvh4JQC90
➤ Excel Tips & Tricks: https://www.youtube.com/playlist?list=PLb80efKTbWuPI7fEWtroXHvJHhx5FnVMW
➤ Word Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMotoYEXBFMlB3PtsBlFn45
#ExcelInventory #AutomatedInventoryList #StockInStockOutTracker #ExcelTutorial #InventoryManagement #StockTracking #VarianceTracking