• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

MS Excel

wobs

Illuminator
wobs
Joined
Oct 7, 2011
Messages
3,705
Location
Hull
They can take my spreadsheets from my cold dead hands!:
www.bbc.co.uk

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 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.
 
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.
 
Last edited:
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.
 
Worm said:
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.
Click to expand...
Every time anyone views an Excel graph a little part of them dies. They just don't know it.
 
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.
 
Last edited:
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.
 
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.
 
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.
 
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.
 
Nobody knows how to use Excel. It's a beast. It's got functions even the current admins don't know about.
 
Brainster said:
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.
Click to expand...
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.
 
Orphia Nay said:
Nobody knows how to use Excel. It's a beast. It's got functions even the current admins don't know about.
Click to expand...
I bet these guys do:
www.bbc.co.uk

Meet Ireland's 'LeBron James' of Microsoft Excel Diarmuid Early

Ireland's 'LeBron James' of Microsoft Excel has won the 2025 world championship in Las Vegas.
www.bbc.co.uk www.bbc.co.uk

More broadly though, you don't need to know everything about Excel to use it. Most uses don't need the more obscure functions and work arounds.

I think one of the things about it that people here are critical about, is that it can be self taught. You can start making calculations with only the most basic of knowledge, and as your needs expand, you can find out each function as you go, and its all very intuitive. Of course, this can lead to bad habits, which can lead to the problems above. But also relying to much on pivot tables, which can cause so many problems.

Compare this with creating a database - I wouldn't know where to start, and I once went on a one day Access course. I just don't have the time to sit down and learn from scratch how to build one, and I'm currently looking at buying one for our site as our old Access database is struggling. The alternative is finding someone to make one from scratch, but I'm not sure our large company would go with that.
 
Last edited:
jt512 said:
Just for old times' sake, anybody here remember Lotus 1-2-3?
Click to expand...
Convert the entries in 1-2-3 to Excel "back in the day". A three-letter "Bureau" based in D.C. wanted to migrate. "How long to coI jnvert?" "One hour." "We have 800+ entries!" "Okay, one hour and two minutes." I could hear the "yeah, sure" in thy reply to that. I delivered.

"What the ◊◊◊◊?"

"I just put an ## at the beginning of each entry and exported the data to a text file. Each field after that started with one #."

"We were told it would take a few weeks and the price quoted was scary!"

"Those guys must think nobody pays for those kind of deals. But why the '2 minutes?

"Coffee break."
 
A regular feature of my audit planning is looking at the spreadsheets that are still used in decision making in my organisation. Some of the horrors I’ve seen explain very well my distinguished grey hair look.
 
You must log in or register to reply here.

Back
Top Bottom