Excel remains one of my most used pieces of software, and one where I feel like any education/training has re-paid for itself multiple times over. The more you learn, the more productive you are (and sometimes more informed, thanks to data visualizations).
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!
i recently got burned because at some point in the last few years Excel decided it would be better if it attempted to use localized thousands and decimal separators.
item,cost,currency
"Socks",1.234,USD
Represents socks costing about a dollar in countries that use '.' as a decimal (most) and about a thousand dollars in countries that use a ','. In locales using a decimal period '.' double clicking will open the CSV just fine. In locales using a ',' it comes in as text but the default settings for text to columns use localized number parsing but still let you choose a now ambiguous comma as a column separator. I'm 99% sure it didn't used to do this as we've been making CSVs for the same clients in the same regions (Netherlands) for years and only more recently have they had this trouble.
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 have long suspected that Excel's treatment of CSV files was intentionally antagonistic. For example, opening a CSV file will start an importer function where you must specify how fields are delimited within the file, and "comma" is not automatically selected, ever, despite "comma-separated" being literally named in the file extension.
Surely the folks programming it are not that short-sighted. It's just a punishment for not using their default format.
Modern excel is better at this because it has this "powerquery" thing integrated to csv import which allows bit more control how data is imported. The classic csv import is really bad though. It is bit hidden feature, you have to click "transform data" button when importing the file
The classic "Text File Import Wizard", going back decades, is even easier than "PowerQuery" for the common and basic "set the type for each column so that Text is Text and not General" but it's now hidden and you have to search for it. (Admittedly, a lot of people had to search for it even when it was on the Ribbon.)
I've always wanted to investigate if there is a current way to do this on a URL without resorting to scripting but have never really gotten the energy up to do it. Being able to send users to https://www.example.com/foo/your_data.csv and have that send the right headers for caching, refresh and authentication so they can use Excel as a mini BI front end without screwing around with ODBC or similar etc would be a nice way to answer the "integration" question for clients that are too small to have a IT department to help them with database connections, firewalls etc.
Excel has an entire ETL engine called PowerQuery tucked up under the hood. Originally part of SQL Server Analysis Services, it got strapped into Excel about a decade ago, and more recently tons of other products[1].
The Web[2] 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[3] 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.
My pleasure! Feel free to reach out, you have any questions (email in profile).
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.
I believe this exists. Under data, “get data”, then the url/http feature. You can also manually recursively limit a list of urls but by that point you’re privacy going beyond what the tool should be used for.
Excel's notion of what is "CSV" and syntax of its formula language is locale dependent for as long as I care about that. Notably, for locales that use ',' as decimal point excel uses semicolons as separators in both its "C"SV format and formulas.
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.
Also stop using locale settings for importing CSVs, or at least make a setting for it. I had to change my PC's locale to English (US) just to decently import CSV files in Excel
> 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!
Amen! Also removing the leading zeros from US ZIP codes.
Would also say corrupting gene names, but in this case the scientists literally decided to rename genes to not conflict with Excel's treatment of date-like strings.
My first job in 2000 was converting files from the "PC" so they can be used on the Mainframe - and this was one of our biggest issues. Some things never change. Used to rename csv files to anything but csv to import them so you'd get the wizard.
Hey, but luckily they removed the import option recently! /sarcasm
> 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.
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.
Second the disagree. As someone who supports users on Outlook on a daily basis, my anecdata is that it’s a buggy, abused, flaming dumpster fire
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
Respectfully disagree. Desktop Outlook is largely abandon-ware at this point, with no real development or improvements taking place for over ten years and Microsoft's efforts being almost exclusively focused on their online/webmail offers (e.g. Outlook.com/Office 365).
> no real development or improvements taking place for over ten years
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.
You know, being controversial here and coming from a finance perspective, I've wondered if Excel has done more harm than good to the finance / M&A / investing industries.
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.
I don't know if Excel has done more harm, but it sounds like the fact that you don't do critical thinking about investment rationale is the problem. It seems like you need to do both.
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).
Market growth that typically has high error margins precise to the decimal... I am with you and I would not give too much credit to Excel in 2020 and I used to work in finance. I think what people don’t realize is that code forces you to focus on the model and less on the “form”. Numbers should be numbers, not forced into telling a specific story, this type of work is for text, power point, and similar.
I don't know who or why is downvoting you. You are advancing a perfectly rational discussion and shame on anyone who just disagrees by downvote.
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.
Please don't break the site guidelines by going on about downvoting. https://news.ycombinator.com/newsguidelines.html As they say, it never does any good and makes boring reading. Moreover, users often give corrective upvotes, as they did above, which means that the complaint about downvoting is now factually false, and lingers on like uncollected garbage in a thread.
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.
> 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.
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.
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’ve seen Dan Bricklin in the local Whole Foods on several occasions. I want to yell out: “see that guy! He changed the world!” but then I let him pick an avocado in peace.
Microsoft released the first version of Excel for the Macintosh on September 30, 1985, and the first Windows version was 2.05 (to synchronize with the Macintosh version 2.2) in November 1987.https://en.wikipedia.org/wiki/Microsoft_Excel#Early_history
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.
One of the best Excel tips video I’ve seen is by Joel Spolsky - aptly named “You suck at Excel”. I’ve been an Excel user forever and this video taught me about features that I never knew existed.
Came here to post essentially the same thing. Considering how much of most people's Excel usage (in my work experience) is basically making very simple tables/databases, the stuff in here should be required.
I've always thought that Excel is the greatest piece of software ever written. The ease of entry, the extensibility, the impressive collection of built-in tools/formulas/charts. It gave regular people the ability to do data analysis and in so doing became perhaps the most ubiquitous business tool in the world aside from email. Happy birthday, Excel.
I'm consistently amazed at the ubiquity and versatility of spreadsheets in both small and large businesses. What other no/low-code tool can be shaped into solutions for finance, accounting, project management, event planning, scheduling, data collection, etc.? The concept of rows+columns+cells is powerful, flexible and easy to understand; when combined with formatting, fills and functions the possibilities are endless.
The interesting thing is that rows and columns wasn't the groundbreaking concept that energized Bricklin to implement it, the fundamental novelty is the automatic recalculation feature. The origin story of how visicalc first formed in his mind is varied, but every version the fundamental insight is that you write expressions symbolically and the computer substitutes so when you change the value of the symbols the computer automatically recalculates.
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.
Tabulating data in rows and columns goes back to some of our earliest examples of cuneiform writing, I believe. I always assumed the grid of rows and columns was simply modeling the world as tabulators knew it rather than coming up with a new concept for how to present information. That particular presentation is easy for users to understand because it’s been with us forever.
Heavy use of Excel has literally changed the way I think, and it's what piqued my interest in learning how to program. Using macros was cool, but being able to record and see your actions "transcribed" to visual basic code was pure magic. Excel will always be one of my favorite bits of software.
"All the logic for our process is in these Excel Spreadsheets."
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.
I wish Excel used newer, easier programming languages like Python for scripting and macros. I don't like learning an entirely new programming language just to make use of one software feature.
You can program MS Office in Python. And Lua, and Ruby, and Fortran, and others, and mix'em all in a single spreadsheet. That's the beauty of Active Scripting:
That's the thing, if they change it to the language du jour every jour, it wouldn't be a stable environment.
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.
Sad to see they're leaving the Excel add-in development scene behind with .NET 5. VBA is cross platform (PC/Mac) but ugly, old, and very limited. They've hinted at Python scripting support for years, but have never done anything. And now they say they'll focus their future efforts on web add-ins, but from what I've seen web add-ins have been limited to read-only and embedded external content. Plus, most people I know still prefer the desktop version.
I saw one of the streamers I followed was playing Eve Online and went to check it out, to be greeted with a stream of Excel (for Eve Online manufacturing purposes). I had to take a screenshot.
Probably via something posted to HN, I saw somebody's rule that "It doesn't matter what the users say they want, they want Excel." This seems to be the case often.
SQL is then the programming language. I am aware that this differentiation is outdated; but if one sees a scripting language as something to write a small, self-contained job and a programming language something bigger more thought through and interoperable, (and with a fair bit of hand waving), I think that analogy fits.
Of course here we do not only have a program but tabular data and operations on them.
I mainly develop with C# but still use Excel to quickly build classes and SQL queries to split out the fieldnames and data types and substitute with requirements
Most used VBA function:
Function SP(s,d,i) as string
SP = split(s,d)(i)
end function
A little off-topic, but maybe someone can answer this. I sometimes use Excel when I want to see a CSV file formatted nicely, but apparently Excel has bad support for CSV files, opening them unparsed (e.g. each line of the CSV taking up a single cell.) Apparently you have to use the "Import CSV" wizard. When building apps that export CSVs, many times customers have had issues with special characters appearing weirdly, so I've needed to format the files using some sort of DOS character set.
Apple Numbers and Quick Look opens CSV files without any issues whatsoever.
My assumption is that it is a low priority format for MS. Perhaps because it does not further .xls / .xlsx lockin, perhaps because most ordinary users rarely need to open a .csv file.
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.
Hey HN! We're building a networked spreadsheet to make it easy to connect, sync and query data without needing to know how to code. We're hiring a lead backend developer to join us. JD here: https://sourcetable.com/jobs#backend-engineer
(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
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!