MS Excel

[wobs]

They can take my spreadsheets from my cold dead hands!:

Excel: Workers cling to the software despite shift to AI

Companies are trying to wean staff off Excel spreadsheets to centralise control of their data.

www.bbc.co.uk

Seriously though, while I do recognise it can be over used, it is more useful to play around with data than a database. I get so many requests to provide data in different ways, I need that flexibility.

 

[jt512]

Excel has its place. It is a very useful tool for manipulation of small, simple datasets. But, especially for large data sets, anything you can do with Excel you can do at least as well (usually better) with dedicated database, graphics, or statistical software. It is extremely easy to introduce errors into an Excel spreadsheet, and usually extremely difficult to find them once they are introduced, as there is no audit trail to follow. As a statistician, I have had to deal with many Excel "databases." I can count on, like, one finger how many of those were error free. It was common to spend more time correcting the data than doing the actual analysis.

 

[Worm]

Excel very much has its place, it gets relied on too much, particularly when it comes to large data sets, but it's a great tool to allow general access to data tools for non-tech adept people. The greatest failing of Excel I see is that most people don't use about 80% of its capabilities. I'm guilty of this myself, and I'm always aware of not introducing 'clever' or complex processes into a spreadsheet that other people are going to have to maintain later on, but every time I see a report with charts included using 'default' excel settings, a little part of me dies.

 

[jt512]

Excel very much has its place, it gets relied on too much, particularly when it comes to large data sets, but it's a great tool to allow general access to data tools for non-tech adept people. The greatest failing of Excel I see is that most people don't use about 80% of its capabilities. I'm guilty of this myself, and I'm always aware of not introducing 'clever' or complex processes into a spreadsheet that other people are going to have to maintain later on, but every time I see a report with charts included using 'default' excel settings, a little part of me dies.

Every time anyone views an Excel graph a little part of them dies. They just don't know it.

 

[wobs]

I'm trying to wean people off storing source data in annual Excel spreadsheets, but this isn't the greatest horror I've come across.

These include, but not limited to:
Storing source data in annual spreadsheets, but each month is on a separate sheet, so linking to them required annually updating links for each month. I have managed to get this onto a continuous sheet, and data is downloaded from our system, so the data should be correct now.
There is still a spreadsheet on site that has some monthly totals taken from another spreadsheet for data analysis. This is ok, but..... The months are in alphabetical order! Its been like this for years, and I don't have write access to it, I can only view it.

 

[Wudang]

The worst my wife saw was a Excel "database" that kept being expanded by the person who wrote it. But each new use would mean acting as if it was a new app so each person had their address, for example, entered 14 times.
I inherited an Excel tool written in VBA. The bank we worked for had an approval process for new software. I used it once and held a little party each year on the anniversary as I waited for it to be approved. The tool was quite interesting except he used 1 char variable names and has never heard of "pass by value" so it had lots of fun bugs. Once he retired I did a major code clean up.

 

[Brainster]

And if you tried using Excel as a word processor you'd probably run into lots of other problems. Excel has its uses and does have some simple database-type functions like sorting, and at times you can tease out data using "if-then" formulas. If you really want to do more sophisticated data analysis, it's much better to use Access. I commonly used Excel to fill in forms that I then pasted into Access, ran a few queries and pasted the results of those queries back into Excel so I could present it however I wanted.

 

[Trebuchet]

When the only tool you have is Excel, every problem looks like a spreadsheet.

 

[arthwollipot]

Speaking as someone who spent a career in IT support, the bigger an Excel file gets, the more difficult and time-consuming fixing it when something goes wrong is. And the bigger it gets, the more likely it is that something goes wrong.

 

[TragicMonkey]

Excel isn't for storing or retrieving data, it's for supplying and presenting data. And it is incredibly good at its proper tasks if the user is sufficiently skilled. The new things added in the last several years are extremely good, but a lot of people don't know about them.

I admit that I didn't know them myself until recently; I'd been using Excel for years by just dumping data into it and formatting it to look pretty, doing a few pivot tables for convenient slicing. But in the last couple of weeks I have learned so much just from YouTube videos. Dynamic arrays! Self-updating tables and lists! Dependent dropdowns! Hidden slicers fueling multiple tables! And don't speak ill of Excel charts, they've gotten a LOT better from how they were twenty years ago. They can now be things of beauty --useful beauty-- if you set them up right.

 

[Orphia Nay]

Nobody knows how to use Excel. It's a beast. It's got functions even the current admins don't know about.

 

[TragicMonkey]

And if you tried using Excel as a word processor you'd probably run into lots of other problems. Excel has its uses and does have some simple database-type functions like sorting, and at times you can tease out data using "if-then" formulas. If you really want to do more sophisticated data analysis, it's much better to use Access.

They added two things to Excel in recent years: Power Query, and Power Pivot. Stupid names, I know. But the former lets you create an ETL process where you can manipulate the data on import. The latter lets you set relationships between the data you've put into your spreadsheet so different tables can be used together -- just like a relational database.

I've not yet had occasion to use the latter but Power Query? Holy crap I used the hell out of that. A couple of months ago I used it to take fifty-plus old monthly reports (separate spreadsheets) and import the data into a single table in Excel, adding useful fields of my creation. I only had to set up the definitions once: now whenever I want to add more data I just place it in a folder on my drive and hit "update all" in Excel. It brings it all in and combines it exactly how I told it to.