You are currently viewing Mastering ABC Analysis in Power Query and Excel

Mastering ABC Analysis in Power Query and Excel

Over the past few years, I have conducted data analysis seminars and workshops for a wide range of companies. The primary focus of these seminars has been on warehouse planning and analysis, utilizing tools such as MS Access and MS Excel (though the use of MS Access is declining), MS Excel combined with Power Query (which is becoming increasingly popular and effectively replacing MS Access), and Python.

One observation I have made is that Power Query — arguably one of the most significant recent features in Excel — is largely unknown. Even individuals who work with Excel daily and consider themselves advanced users often haven’t utilized it. During the seminars, it is fantastic to see participants gradually realize how much time and effort they can save in future analyses by using Power Query instead of struggling with standard Excel.

Another point I have noticed is that while most people are familiar with ABC analysis, they often lack knowledge of some crucial details. Moreover, even those who have previously conducted an ABC analysis find it challenging, especially with larger datasets (>1 million rows) that standard Excel cannot handle.

In light of these observations, I created a video tutorial on performing ABC analysis with Power Query and Excel as bonus material to complement the seminar materials, which do include a step-by-step guide, but only on slides.

Although there are numerous ABC analysis tutorials available on YouTube, most of them focus on very simple, small datasets that could be analyzed with a pocket calculator. While this approach is suitable for educational purposes, it is not helpful for those needing to analyze large sets of warehouse transactions or similar data.

This tutorial also serves as a teaser for my eBook “Understanding ABC Analysis for Warehouse Design Decisions“, which has just been published. The link to the German online store with instant download of the book is https://www.stellenbeschreibungen.de/understanding-abc-analysis-for-warehouse-design-decisions-englischsprachige-ausgabe.html; for the English store the link is https://logistics-academy.com/product/understanding-abc-analysis-for-warehouse-design-decisions/.

I hope you will find the video useful and it is going to save you some time and effort!