What they wanted was VLOOKUP

It started as a straightforward request. A Japanese multinational corporation (MNC) reached out, hoping to upgrade their team’s Excel skills. The request seemed simple: teach them how to use VLOOKUP and a few other worksheet functions. But when I probed further, asking which specific functions they needed, I was met with blank stares. They didn’t know what they didn’t know.

I decided to dig deeper. “What kind of work are you doing in Excel?” I asked. The team struggled to articulate their needs. So, I proposed a solution: share some of your working files. With the blessing of their managing director (MD), they handed over a treasure trove of spreadsheets. What I found was a goldmine of inefficiencies disguised as routine workflows.

Diagnosing the Problem: A Data Analytics Perspective

Armed with their files, I donned my data analytics hat and started reviewing. The tasks they were performing, while critical to their operations, were shockingly repetitive. Sales forecasting, bill of materials (BOM) forecasting, purchasing, and inventory management—these were essential processes for their supply chain and procurement activities, yet they were bogged down by manual data entry and endless formulas.

Each week, team members would spend countless hours manually copying and pasting data, trying to match records from one sheet to another. Inventory levels were tracked across multiple spreadsheets, and BOM forecasting required laborious manual adjustments. As I analyzed the files, it became clear: they weren’t just looking for Excel training—they needed a revolution in how they approached their work.

The Solution: Beyond VLOOKUP

While they had initially asked for help with VLOOKUP, I realized their true needs went far beyond basic functions. I introduced them to Power Query and Power Pivot, tools that could automate their workflows and transform their approach to data management.

Power Query became the backbone of their new system. Instead of copying and pasting data, I showed them how to connect directly to their data sources, cleaning and transforming the data in a few clicks. Now, their weekly reports were updated within one minute, eliminating hours of manual work.

Power Pivot added another layer of efficiency. By creating a data model, they could analyze large datasets without crashing Excel. Complex calculations that once took hours were now handled seamlessly through DAX formulas. For instance, they could compare sales forecasts against actual inventory levels and adjust their procurement strategies in real time.

Immediate Impact: Saving Time and Boosting Productivity

The results were almost immediate. Tasks that once took hours to complete were now done in minutes. For example:

Sales Forecasting: Instead of manually aggregating sales data, they now had a dynamic dashboard updated with a single click.
BOM Forecasting: By leveraging Power Pivot, they could forecast material needs across multiple product lines without repetitive calculations.
Purchasing and Inventory Management: Power Query streamlined the process of reconciling inventory data, ensuring that purchase orders were always based on accurate, real-time information.

The team quickly realized that this was more than just “learning Excel.” It was a paradigm shift in how they managed their supply chain and procurement activities. By automating repetitive tasks, they not only saved countless man-hours but also reduced errors and improved decision-making.

If you’ve ever felt stuck in repetitive Excel tasks or wondered how to streamline your workflows, now is the time to explore tools like Power Query and Power Pivot. With the right knowledge, you can transform your data management processes and unlock hours of productivity. Don’t just settle for the basics—discover the true potential of Excel and take your skills to the next level. Contact us and allow us to help you