Yes, it's the ubiquitous "examination of Excel" article that every BI blog posts at some point. But I think it's important to discuss the limitations of Excel, especially for readers who are just beginning to realize that they cannot continue with manual data collection and reporting forever.
Question: What is the most popular database and what’s the most popular BI tool?
Answer: Excel and Excel
This isn't surprising. Everybody knows how to use Excel and it's installed on just about every Windows machine out there. Excel is intuitive and allows users to input data and output it into simple reports and charts – stuff we all need to do our jobs. You can even do some pretty complex stuff with it if you’re willing to learn how to write macros. But just because you can do something, should you? Obvious answer: No.
For one thing, Excel is not multi-user software. It’s only meant to be used by you on your machine. Translation: It’s not a database like SQL Server or Oracle. You can store a lot of data in it, but as soon as you have to share this data with other stakeholders in your organization, things start to unravel. You realize that not everybody is entitled to see the data you’ve put into the spreadsheet (creating authorization/ authentication issues) so you’ll have to extract just their part. Then you have to get it to them via email or FTP, and any interaction they have with the data will need to make its way back to your original version for reconciliation (creating update management and versioning issues).
So, all your gains in ease of use, simplicity, and independence from IT are lost as soon as your data has to be shared. But the whole point of collecting this data was to share it with your co-workers. If you can’t share it, what good is it?
But really, using Excel doesn’t have to be such a tangled web. What if you could keep the easy part of using Excel and delegate the difficult part to a database? What’s necessary is software that will tie Excel to a database of record and use it as a presentation and data-entry tool. This software has to have full access to the database (read and write) and allow Excel to connect to it as a client. And yes, this actually does exist…and not to get too salesy, but arcplan’s got it.
A real world example of where this type of system would be critical is in budgeting and planning. The year to date (actual) numbers are in a centrally managed database, but the budgets are usually assigned to department managers who have to enter their requests into the system for approval. This is often an iterative process where hypothetical numbers are proposed and fine-tuned by the chain of command using approval workflows. By connecting an Excel worksheet to this database, arcplan allows the data of record to be automatically populated in Excel and secures access using centrally managed authorization software. Each manager will get to see his/her portion of the data in Excel, enter their desired budget numbers, and essentially use Excel as a data-entry and reporting/charting application. The data that’s entered is written back into the central system, thus eliminating the version control issues that Excel necessitates. Additionally, the users can work with their portion of the data in a disconnected mode and submit their budget numbers when they can connect back to the system, or once they’re approved by upper management.
Excel is not all bad, and it would be foolish to act like it is, especially since we use it all the time at arcplan. But it has limitations that smaller companies may be able to overcome due to the price point, but organizations that have to do a lot of data collection, reporting, and budgeting/planning need a better tool that maximizes the familiarity of Excel while eschewing the bad stuff.
Are you looking to move away from manual reporting in Excel? We’d love to hear about the issues you’re facing and what would be necessary in a product for you to make the switch!