Excel vs Database (2024)

Hanif S.

7y

Great points Andrew. Using databases also allows for more flexibility with regards to integrating information from different data-points. It also reduces the chance of duplicated information. Many processes are data-driven including the use of strategy, and the use of queries (or software) that allows for analytics to be undertaken which would be more challenging if all information is stored using Excel.

James McHugh

IBM and Gordion Knot Performance slayer

8y

Properly architected a more exacting mode of memory management, response time, presentation etc not limited by proprietory limitations

James McHugh

IBM and Gordion Knot Performance slayer

8y

PHP and SYLK ...

Tim Heng

Director at SumProduct, Microsoft MVP (Excel)

8y

I would argue that any system or process can and will be dangerous when it's not set up optimally. Andrew, you've talked about daisy chains of links in Excel workbooks, but an arguably similar problem applies when you have SQL queries in a datamart that refer to other queries in a data warehouse that refer to other queries in another database... It might not be as obvious since people who write SQL queries are a bit smarter than to create such unnecessary links, but the issues in Excel are only highlighted because the of the overall scale of usage - people who are trained to use Excel properly don't create external links, much less chains of workbooks that are linked.The trend that is slowly picking up these days is to leave the data in the source systems and instead, use PowerPivot in lieu of Access. As Mary has pointed out, PowerPivot is perhaps Microsoft's big step to slowly phase out Access and incorporate more database-like functionality into Excel. As far as I can tell, aside from the form / data entry side of Access, there is not much that the average Access database does that now cannot be replicated in Power Query and Power Pivot (or equivalent Excel 2016 Power BI tool names: Get and Transform?).As an Excel trainer / consultant primarily, most of our clients may have the raw data stored in a database attached to a core business system. However, to invest in the infrastructure and expertise (people are expensive!) to develop and maintain that database, for a small-medium business, represents poor value compared to using the new Power BI tools in Excel to work with that data. Of course you will still want the database there to maintain data integrity. However, most of your analysis, unless that data is in the tens of millions+ of records, can and should be done in Excel instead since it's more transparent*.While Power Query doesn't have quite the same power as a proper SQL database environment, it does what most businesses require (though you might need to research and code it up, rather than follow the GUI entirely).* Excel - more transparent than databases and SQL code? Yes, absolutely! The key thing is training - it is much easier to maintain and enforce a consistent spreadsheet standard than it is to train up a team in SQL or other programming language, whereupon they will be just skilled enough to as dangerous as they are in Excel. I've been saying for years - the only reason Excel is seen as being dangerous and problematic is because of a lack of appropriate training.

Mary Pat Campbell

Insurance Industry Researcher at Conning

8y

The reason so many people use Excel as their default is not just because they're more familiar with Excel (though that is a big part) -- it's that most database software is set up to give aggregate results, and are not very good when you have to do number-crunching beyond summing subsets selected on particular attributes. People don't like all those interim steps and would like to go right to the number-crunching.Michael Failor's remark on building Excel VBA applications is getting closer -- if people can learn how to suck in the data they need from the databases (so they don't mess up the raw data) to do the number-crunching, that would improve processes immensely.FWIW, Microsoft has been trying to merge Excel & Access for years (I think Powerpivot is one of their steps for that), but perhaps they've given up.