How much does your company rely on Excel? And I don’t mean rely in a passive sense like “we need it to open attachments”. I mean storing business critical data in Excel.

We’ve all been there…

• Hmm, these numbers don’t make sense – crap, it looks copying and pasting missed the new rows in my sum formula.
• Wow our forecast looks great – crap, why wasn’t that field in the formula?
• Excel crashed when you tried to print something – CRAP… And now you’re praying that your recovered document list has your latest changes.

That’s not to say that excel is fundamentally flawed. It is a powerful tool, simple to pickup the basics, and can turn around short term analysis with ease. Excel is a great program, and has made many things possible for companies but have you ever stopped to think how it could be holding you back? Or worse yet how it could set you back?

We hope that this guide will provide some perspective into how you’re currently using Excel and if there are better ways to treat your data. After all you’re paying people to collect data, why not start making dividends on that investment rather than it being a time sink for your staff to sort through data and create reports.

Excel is the perfect tool…

We understand. There’s lots of reasons you’ll end up with a myriad of spreadsheets tracking important aspects of your business. Excel is convenient for your staff – they are already trained enough to do most things in it, and you’ve already got your go-to guru in house who’s a wizard at pivot tables and able to deliver what you need. But that convenience comes at a cost – it’s easy to throw together some data, but it’s also easy to throw it all away.

Excel has a purpose, but it’s purpose should be restricted within a limited set of use cases.

• Adhoc data analysis or prototyping a concept
• Investigating financial or data forecasting
• Short lived documents or data that is only important for one person
• Modifying one off reports

This might seem like a pretty small set of tasks that excel should be used for — and it is. As IT strategists we are very biased with how you treat your data, as we believe that having trust and visibility in your data is paramount to business success.

Excel is the perfect noose!

To be blunt, using Excel to track the critical data for your company leaves you vulnerable. It’s a massive issue and likely too late when you find out it truncated your data, or that dates are being handled incorrectly. It’s also limited in control so your staff could accidentally modify a formula that could change the meaning and results of the data completely.

Do you find yourself sending an email asking staff for the most updated spreadsheet? Or you realizing your spent the last hour reviewing data only to be sent a new version, and having to start over? That’s one frustration of excel is it lives in an uncontrolled distributed fashion. Sure you can solve that with windows file sharing but then you could you run into Excel nicely letting you know that a user has locked the spreadsheet and you now need to wait for them to finish with it, or make a copy and hope you can merge the changes afterwards.

You waste time and resources storing the same data over and over again, but more importantly you don’t have full control over the data:

• At a glance do you fully trust whatever this does? IFERROR(VLOOKUP(M9,D2:E22,1,TRUE), “N/A”)
• Are messages like #NAME?, #VALUE!, #REF! showing up making you question the data?

You’re missing out on data integrity in if leaving it in the hands of a spreadsheet. Sure you can enforce some data integrity in Excel – but it’s like lipstick on a pig. You’re going to end up with a bunch of mysterious macros, formulas and conditional formatting that you cringe having to modify or update to keep up with business goal changes

But I really like Excel!

Don’t take this the wrong way, as there are definitely places for excel in real information systems. Excel is great as import format (when integrating with external vendor data that doesn’t have another export structure). We can’t control what everyone else does in excel, but guaranteed there’ll be an excel document somewhere you’re company is expected to interact with. That doesn’t mean the pain needs to creep into your organization though. It’s also quite useful to export your database into excel so you can do some one off analysis on the data, or delivering customer reports in Excel if they need a copy of the raw data.

We use excel for lots of things internally that meet the above criteria. It’s way easier to throw together a quick pivot table that proves a concept then spending a few hours of precious development time for something that will be short lived.

Is Changing Our Business Workflow Worth Doing?

How do you know if there’s room for improvement?

If you are using an excel document for tracking historical data and keeping a list of records you likely have room for improvement.

If you are using excel as a document layout tool, and having staff filling out forms in excel you likely have room for improvement.

If you have got entire non-IT positions in your company that are for processing, manipulating, or typing out data, you definitely have room for improvement.

One of the worst thing about excel is it’s the underlying ongoing labour costs that it has on your organization. It’s only a couple of steps better than writing things out in a document.

How much do you spend on labour?

What are the labour costs in your business? Likely a massive aspect of your balance sheet. How much of that labour is inflated because of technical problems that could be reworked? This is where having a clear perspective on what drives your businesses bottom line and how that aligns with your technology platform can mean life or death to your business.

Make Your Data Work For You

But what other options do I have ?

We can store your data in either a structured manner (structured schemas and databases, or in a more schema-less adhoc approach (if your data contains a lot of different varying elements like user generated content, or geospatial data). We will focus more on structured data as it fits most business needs (but look forward to a similar whitepaper on schema-less document based databases).

When data lives in a well thought out database schema instead of an unstructured document it’s immensely more useful. A database even if it’s only a few tables is relational and normalized. That means we store a person’s name and birth date once, we store the history of a single purchase and that references the customer, the shipping address etc. It means we only have to update something once and it’s updated everywhere.

Relational databases gives meaning and depth to your data.

Here’s a rule of thumb – if data has importance in your company for more than a month, it likely belongs in a database, not in an document.

Another benefit of using a relational database is it’s easier to migrate that into a star schema. Think of a star schema like a pivot table on steriods – we can take 1 fact (customer sales, inventory stock, etc) and link it with any parameter (customer demographics, frequency of purchases, promotions, lead sources, etc). With a database we can compare the information readily. Want to compare all Q1 sales for the last 4 fiscal years? Easy… Want to break that down by customer channel or franchise owner? No problem! If that data is in excel though you’re going to fight with it.

So what do I do?

There are far better low risk and long term solutions to storing your important data. You could start now with an open source relational SQL databases like MySQL or Postgres, or commit to a licensed based model such as Microsoft Sql Server, Oracle. Other options depending on business use cases could be a nosql document based database like mongodb, derby, or something similar. Don’t worry this may sound daunting without an internal IT team to manage the infrastructure – you can get most of these options in a hosted service, or have a trusted partner do the heavy lifting and worrying.

We’d love to hear how excel is holding you back, drop us a line. Or better yet, prove me wrong that Excel is the best tool for the job!

Ian Barton
ian@opid.ca
http://www.opid.ca