OneNote (desktop) and Excel are the only two pieces of Microsoft Office that I regularly continue to use. They're the only reason I license it.
Microsoft Word after I graduated rarely gets used (typesetting? I don't even print!), and while I understand the value of Microsoft Access, I feel like using it is investing effort into a dead-end (i.e. people want online/real-time reports, not locked into a desktop software). PowerPoint, at least where I work, is almost a meme (as in "Power Point meeting" is a pejorative implies waste of time meeting).
As to Excel: Hopefully it won't take another 35 years for them to fix CSV opening so it doesn't auto-format long numbers into scientific notion or dates, then cause unrecoverable data loss when you save. If you don't know what the data format is DON'T GUESS!
Edit: I've used Excel for at least 20yrs. I thought i new how to use it and how to search it's docs. This is the only article I've found online that mentions how to handle this as a end user. Near the bottom under "Converting International Number Formats" where it of course assumes you have decimal comma and need a decimal period.
I did also find a 20 minute youtube eventually
Surely the folks programming it are not that short-sighted. It's just a punishment for not using their default format.
I've taken to importing CSV files using Apple Numbers, and saving as Excel
(i use Numbers primarily, Excel is just when I need to be interoperable with someone else)
Usually it is the us-en way or the highway.
Here is sort of some docs on it https://support.microsoft.com/en-gb/office/import-or-export-... (section "Import a text file by connecting to it (Power Query)")
The Web connector is what you're after. It can consume a variety of formats, including csv, xml, and json. It supports HTTP Basic Auth pretty well, but if you want to put the data source behind oauth it can get tricky (it technically supports it but depends on how novel the implementation is).
Credentials sit with the (Excel) client, so if the file gets shared with a new user it'll prompt them for authentication details when they first attempt to refresh it. It'd be pretty easy to set up a template/turnkey workbook you can just hand over to new clients with everything all set up and all they have to do is enter their credentials when prompted. Only caveat is Excel for Mac - it only gained Power Query about a year ago, and porting it over is a very involved process including an entire .NET Core rewrite and factoring out Windows dependencies from legacy cruft. The Web connector is one of the many that haven't made it over to the Mac yet, so any clients using a Mac would still need an alternative.
I'm not an expert on it, but I am a technical person currently working on the business side of the IT fence. And I frequently do (data-engineering heavy) consulting for clients who are similarly on the business side of IT. So none of the nifty ETL tools, data storage capabilities, or computing environments I have available when I'm on the IT side.
Power Query is a godsend in that scenario. It's a well featured ETL engine with integrations for a variety of systems, services, and databases (including generic JDBC/ODBC support). And has the ability to use direct HTTP calls when that's more appropriate/useful.
It also stores the data internally in a highly-compressed and optimized columnar store, independent of the "Excel data" on sheets. Which you can then either sync to a worksheet, or leave it in a state where the raw data isn't visible but can be accessed through a pivot table connected to it. So you can abuse it for far heavier work than you'd expect to be able to do in Excel.
And it's already there, sitting on virtually every business person's computer everywhere. Completely sidesteps the security, IT, procurement, and legal hassles you have to jump through to get a proper system. Not to mention user training - you can architect things in a way where all users have to do is maybe tweak a cell or two, then hit the Refresh button that's in the Ribbon. So the complexity of "learning something new" is completely absorbed on your side, and you're free of pesky support questions and hassles since you isolated them from being the new stuff (also making it harder for "accidental/I didn't press anything!" changes from users).
It's not perfect by any means and has a number of warts, usually falling short of more purpose-built solutions when those are options. But it's still pretty solid, and on balance has saved me far more frustration than it's caused. And without having to dip into the dreaded world of macros and VBA.
What changed in last few version is that excel started to distinguish whether you are "opening" an CSV file (eg. by double clicking it) or "importing" it and the former tends to lead to various kinds of random breakage in presence of the aforementioned (mis-)feature.
Amen! Also removing the leading zeros from US ZIP codes.
Hey, but luckily they removed the import option recently! /sarcasm
This has been a major issue when working with Australian address data, where the street number was quite often a range e.g. "1-12". Excel would auto-convert to "12-Jan" upon opening the file, and save it as that if you clicked save, and there was no workaround I could find. I eventually told the tester to use Libre Office Calc instead.
Connect to the CSV using Powerquery in Excel. Solves 100% of the problems of CSVs.
Perhaps it’s users who misuse it (think document management with 100gb+ inboxes) but a lot of times the solution for Outlook issues is to reinstall the whole Office suite since it’s just kinda one package now
You write this like it is a bad? Outlook does what it needs to do, send and receive email. I personally am glad that the changes which affect me have been minor. Email is a solved problem, no need for change for change sake.
Have you used Outlook365 on Desktop recently?
I wish they would leave it alone but they are changing things for change’s sake
Endless iterations, countless different scenarios, simulations of whatever what-if analysis you can think of, exact numbers to 10 decimal places... it all creates the perception of critical thinking and useful work.
Often, again in finance and investing, these things are actually only a way to make you feel like you did something useful. It is easy to do these things in excel - and you can show the numbers and the outputs that support all the "hard work" you did.
However, at the end of the day they are just numbers in a spreadsheet that you can make do whatever you want.
Critical thinking about investment rationale is lost, because it is hard and doesn't look robust because it is just words, and mindless number crunching is preferred because it is easy and looks robust.
This seems akin to writing code w/o design. Sure you can do it, but you're going down the wrong path (and I don't think I'd blame the existence of software because some people write code w/o design).
The relevant part starts at around 38 minutes in.
What you describe is an easy trap to fall into, doubly so in a world of imperfect and insufficient data like finance. You very rarely know the full picture and everything you work with is basically stochastic. Yet this stochastic nature can be easily hidden behind copious numbers that seem to add up. However, I'd like to avoid blaming the tool for this. Just as a hammer cannot be blamed just because it enables plenty of "craftsmen" to cobble together rubbish constructions.
Excel is an incredible enabler, and unfortunately it is indiscriminate in that it also enables people to do the wrong thing. The solution here is education I feel.
There's also a flipside I often encounter by the way: analysis paralysis. Data analysis - whether we talk about sexy data science with python, Julia, etc on huge cloud server clusters, or good old boring pivot tables in excel - means that a lot of stuff can now be analyzed in minute detail. I've seen so many efforts get bogged down in a depth first search of fractal-like problems because one can find new and surprising things no matter how deep they dig.
Again I think the issue is not the tools, but the practitioners who still have plenty of learning to do in producing concise results that are easy to digest. I consider this a form of art.
The rest of your comment is just fine.
Excel itself is never the problem. It user error, and sometimes even "boss of the user error," that leads to both situations you describe - analysis paralysis and overreliance.
I call out the "boss" concept because as some people get away from what leads to the numbers that are ultimately output to aid in the decision making, they can sometimes rely on the numbers too heavily relative to other, less "hard" but more important qualitative factors. It also can become an expectation that "robust analysis" is always conducted, and the time required outweighs the value created.
Obviously not always the case, but just a tendency you see in things like investment banking, corporate business development, private equity, venture capital even ---
For the record - without excel my job would be nearly impossible, so I love it. I just took the chance (and am getting the downvotes for it) to put a reminder out there for the less hard-data centric fields you should always add critical thinking to what is an impressive tool.
As someone who's both worked in education and is a designer for a living, I've seen this argument a thousand times about a thousand things, and while there's sometimes merit in it, it's usually a cop-out.
Why not bake some of the education into the product, rather than treat them as separate? You probably can't have finance 101 in there, but excel is an example of a product whose internal documentation is rarely sufficient to answer even basic questions. Most of it is also at the basic level of what some function is, rather than how to solve higher level problems or why you'd choose one approach over the other.
This approach isn't unique either, there's lots of education technology exploring the overlap between learning and doing within a single tool. The reason we don't see this more is it involves a different team composition than traditional software teams, which tend to not even consider documentation a central part of the product experience, let alone teaching.
I agree with any tool capable of doing complex enough things, there's a discipline and an art to its use. But to say Excel doesn't really have a place to improve here is false, it's just stuck in its ways like lots of mature software is.
Flash forward to 1997, and a key part of the deal for MS investing in Apple is...you guessed it, Excel on the mac.
Funny to think about next time some engineer is telling you how this or that new cloud software will 'totally replace spreadsheets.'
Spreadsheets powered the personal computing revolution - single handedly determined the winner of the OS wars (when people fled to first DOS and then windows after MSFT build Lotus into Excel).
Like em or hate em, spreadsheets are here to stay.
I remember there being dozens of different spreadsheet programs in the 1980's. Flash forward to 2020, and there is primarily Microsoft Excel, it's imitation LibreOffice Calc, Apple Numbers, and Google Sheets.
Have a look: https://youtu.be/0nbkaYsR94c
The idea of a grid came to him when he was implementing and had to define the way you reference values symbolically in your expressions, it's not a problem in traditional programming because we have variables and functions and objects and a metric ton load of other named abstractions, but he wanted a GUI. At first he experimented with arrows but the graph very quickly became too unwieldy, then he hit upon the idea of a grid to address and reference values as will as display them. Basically a two dimensional computer memory where the addresses is the (row, col) pair. Simple yet elegant.
I love Excel, and I hate Excel. I think it is one of the most interesting tools since HyperCard for the non-programmer. I just wish there was a bit more structure. I would love something more like a Lego set.
Being central to developing Excel has to be the safest IT job out there. Half of the world can be on fire and you’re still required.
As it is right now, business logic implemented in Excel executes on a more stable platform than business logic implemented on Windows itself. A spreadsheet from the Windows 3 era will load and calculate without modification, a Win16 binary won't.
You'll be recorded in history books. There's hardly any other software that had such direct impact - both positive and negative - on the day-to-day work of most people on the planet.
Of course here we do not only have a program but tabular data and operations on them.
Spreadsheets are also one of the only computer inventions post 80's (79 to be accurate) everything else was pretty much invented before the 80's.
Most used VBA function:
Function SP(s,d,i) as string
SP = split(s,d)(i)
Are you thinking of 12/31/1899, perhaps?
Apple Numbers and Quick Look opens CSV files without any issues whatsoever.
Why is CSV support in Excel so poor?
CSV isn't a proprietary Microsoft format. In fact worse: It competes with Microsoft's proprietary formats.
Microsoft would like everyone to be sending data to one another using xlsx, but not so much that they'll make generation/processing libraries and tooling free cross-platform, just enough to sabotage everything that isn't that.
Microsoft made these decisions during their "evil" years, and never back-tracked. So regardless that CSV is used in multiple industries for data exchange (e.g. financial, order/invoicing, scientific, etc) they'll just keep on corrupting them until you use what they want you to use.
(I usually wouldn't comment bomb but feels very on-point for this thread as there are plenty of spreadsheet-lovers here.)
I can be reached via eoin [at] sourcetable [dot] com