It happens every time I see a new company, employee, or private coaching client: they open up their ‘working’ spreadsheet — you know, the one that runs everything — and their data is laid out horizontally. I discreetly calm myself down and put my hands behind my back, in my lap, on a notebook…anywhere I can to keep from reaching out and taking the keyboard and mouse away from them. I remind myself it is an easy fix, and the opportunity to change the way a person looks at data forever. They have violated my number one rule of functional Excel:
Always Scale Data Vertically.
The fault isn’t theirs. If you’ve ever been asked to sort name cards on a conference table or tried to organize the pages of a dropped 100 page document (*haunts me to this day), you have probably started in the top left and laid out whatever you needed to left to right, THEN top to bottom. It’s how we read, and sort. It makes sense that we would intuitively lay things out this way as well.
In data pretty much everything is built to search DOWN. NoSQL and flat files are a noteworthy exception. But until you store massive amounts of data in Word instead of Excel, let’s keep focused on columns and rows of data. This makes sense with how we read as well: you look across the top of something to find the data you’re seeking, then you look down to find the next part. Excel essentially does this at a cell-by-cell level. Structured databases also mimic the across, then down method.
Excel is useful for storing information and accessing information in a table/chart/list sort of way. It only becomes powerful when you can analyze it, manipulate it: when you can create with it. And Excel needs your data to be vertical to do really make do it well.
Take a purchase order (PO). At some point in our careers we have probably been requisitioned by or used a purchase order made in Excel. If you’ve only ever used ERPs or QuickBooks, imagine a form submitted in Excel for a moment. A well done Excel PO template does everything you’d wan a purchase order to do (totals, shipping, automatic summation, etc.) and it looks beautiful. But if you’ve ever had to go back through the past six month’s purchase orders, it’s not fun. (Doesn’t matter that they’re all in the same folder, workbook, etc.). And trying to calculate frequency of order, number of times MOQ was ordered (vs. EOQ or next price break)? Maybe next quarter?
If your purchase orders looked great and ALSO went into a PO line ledger (a nicely organized table with all the purchase order and PO line information across and each individual PO line down), you’d be able to do all that other fun Excel stuff (sort, filter, sum, pivot table, etc.)
If you get it and think this is a great idea or useful. That’s perfect, please give it a like so that others can share in the joy of vertical data.
If not, here’s a more visual example of what I suggested above.
Example of Vertical Data
So. Say you have a PO form you made in Excel. Snappy, sexy, functional:
Looking through two or three dozen of these does not for a happy Buyer or Buyer’s boss make. How can we make this more “vertical”? Let’s start with that nifty little “Sheet1”. The data we need is our PO number, Date, our Ref. Num., the Vendor, expected Delivery Date, Buyer or Authorizing manager, and the individual PO Line’s information: Item, Qty, UOM, $/UOM, Line Cost. We can use some fancy Past Links in Sheet1 to capture this data in an un-sexy but eminently more functional block of data as you can see below:
Now when we create a PO for our vendors, we can send them a PDF of the Purchase Order tab, but we can still have a vertically-arranged block of data that can scale and aggregate beautifully. Having sent the order out, our Buyer then copies the relevant line(s) of Sheet1 to our PO Lines History Document:
About this time, The President asks how much money we’ve been spending on materials purchases for this past few weeks. Our crack QuickBooks accountants are, of course, outsourced and at lunch from now until about 2 or 3pm. Some quick formula thinking and we can answer this question very quickly:
Our PO Lines History Excel document lets us easily total up the information that is most important to us with the tools those great guys at Microsoft put in place for us. Here, we sum the total PO Lines to come up with $8,280 total RM spend.
Our president gets this number from us and we get an email that says something like, “KEVIN! You spent $967 on room service!?” Hmmm… Why DID we spend so much money? Doing a quick review, it looks like we have the right total ratio of labels to trays (LB.123948 and PK.2034). We filter for those items:
Maybe we could have done this most easily by putting a subtotal in there, but after filtering for labels and trays, we’re sure those are right. It’s only when we filter for the most expensive ingredient (IN.059564) that we realize we ordered close to 20,000 units’ worth of ingredients more than we did labels and trays — about $2,000 more than we needed. Now that we know where’s a problem, we can do what we do best: manage and improve performance in our operations.
Recap
- Whenever you’re storing data that you might need to summarize and synthesize with later, make it vertical. It’s how Excel and most data processing tools are geared towards.
- If you have to sacrifice verticality for aesthetics, back yourself up with a hidden or background sheet that can store data vertically. Make sure you’re storing like data together and in the same place vertically.
- When there is a hiccup in the status quo or when you want to improve things you will already be positioned to analyze and take advantage of your data. Making management decisions and adapting will be easier, and you’ll be able to move faster.
If you would like help applying this to your purchasing process or any other process you report on, track, or find yourself needing to audit in the future, let me know here!
Also posted on Medium.