The Pivot Table Gateway Drug—Don’t Let This Be You
But then next month, someone asked you to combine that data with a dump from another system. What harm could there be? Just a simple VLOOKUP? You didn’t even have to copy and paste data between spreadsheets—just specify the file you wanted in the formula and everything was fine.
But then, the pivot table wasn’t enough. Someone wanted special formatting. And you thought, it’s really time to get this into the system. But the report writers were backed up. And IT had other priorities. But with just a few more formulas and GETPIVOTDATA, and you could be the hero. So, you worked through the weekend, and the numbers were fine.
But then, you went over the edge. Someone asked for adjustments that you couldn’t get into the current system. You weren’t just combining data from someplace else. They wanted you to track data that existed only in your “system.” IT said it would take six months to get it done. And they really wanted those numbers now. So, you thought about it. You had Access on your desktop as well as Excel. Maybe, you could hook your spreadsheets up with Access and get everything they wanted. And it worked. You were riding high. You could do no wrong. Sure, you had a few jitters now and then, but you always managed to get it done.
The high didn’t last. One month some numbers didn’t make sense. You spent hours combing through spreadsheets to find the bad data. The next month, you missed a reporting deadline. And then, (cue Darth Vader music) internal audit showed up. It wasn’t pretty. And now they’re making you use Google Docs.
Don’t let this be you. Use pivot tables as they were intended—for quick, ad hoc analysis. They shouldn’t form the linchpin of your financial reporting process. Invest in system reports. And remember: Friends don’t let friends rely on Excel.
Seriously though, I love Excel when it’s used properly. (See previous posts on my top 5 reasons for using Excel for reporting and top four reasons for not using it. Also see my post on going over to the dark side in recommending Microsoft SSRS.)
But as happens regularly, last month I spent considerable time working on client spreadsheets. While I can quickly answer almost any question about any report we’ve developed over the years, I can spend hours piecing together other people’s spreadsheets—and usually under tremendous pressure.
I get that people do what they need to do to get the job done, which drives the creation of these spreadsheets. But these monstrosities don’t solve the problem. They just kick it down the road. As with so much in business and in life, if you don’t pay now, you’ll have to pay later. And when you pay later, it’s going to cost you more.