Suppose we want to build a “Preferred Vendor” column with Excel. First we build a data table of transactions, including a blank Preferred column. Then we put “Preferred” or “Bypass” in each row where it applies. We can then filter on the new column. Done!

This is a good example of why analysts are fond of Excel. It’s also why their first instinct is to extract transaction data from spend analysis systems, because BI tools (and spend analysis tools in general) can neither create new columns in the data, nor map them in real time.

So what happens when we add 10,000 new transactions? We don’t want to do all that manual processing again for the same vendors. To get around this, we can create a two-column lookup table that contains vendor name and preferred status, and change the Preferred column to VLOOKUP into the lookup table. When we encounter a new vendor, we can add it to the VLOOKUP column (adjusting ranges as appropriate).

That works, but there are often multiple versions of name for the same vendor, so we need an additional VLOOKUP table to group them together. Then we can base our "preferred" VLOOKUP on the grouped name.

Unfortunately, this sort of thing gets out of control quickly, and quality control is problematic because of Excel’s free-form nature. Furthermore, Excel is limited to 1M rows, too small for many companies. And, we’ll have to do this for every custom segmentation.

The final result is a mess — neither scalable, maintainable, nor helpful except for a clever and diligent Excel user. Imagine what that clever person could accomplish with a tool like Spendata, where all the above hard work is accomplished in seconds. Now effort can be spent on analyzing the data and finding value, rather than on spreadsheet mechanics.