In excel you can convert any range into a dedicated table object, which lets you give it a proper name, select rows/columns (with ctrl/shift+space), and use column names in formula (with `tablename[columnname]`). You can also load the table into powerquery to do things like decompositions or augmentations without modifying the original data.
^ seconded. If you learn tables with Excel you become a demigod, and if you learn Power Query with Excel tables you become a being of pure energy and are then tasked by management to fix every data problem in your organization.
>I'm not sure what "proper tables" would look like
This is the third time this has come up on Hackernews in the past week.
Excel lets you designate a specific rectangle of cells as a named "table" (this is not the same thing as a pivot table). Each named column in the table automatically gets its own named range, which belongs to a namespace of that table (rather than the global namespace). A table will automatically expands its bounds when you insert data into cells immediately bordering it. Inserting a formula into the first row automatically fills-down that formula into the entire column. There is special syntax for referring to rows, columns, and ranges of columns within a table. Look up "structured references".
They are very useful. They make big spreadsheets tractable and maintainable. Think of them as mutable, expandable, reactive dataframes. If you're dealing with inherently tabular data in Excel and you're not using the tables feature, you are doing it wrong.
LibreOffice Calc and Google Sheets do not have them, to my endless bafflement.
Excel is great. It is so good most business apps at some point ask you to replicate many of its popular features (though most of these features are not exclusive to Excel by any means).
> Instead of writing the formula =A1*B1, you can do =Width*Height like you should have been able to 30 years ago.
I don't have access to super old versions of Excel, but I just confirmed that it worked perfectly well in Office 2003. I believe it's been the case forever.
(I wrote whole apps in Excel4 super-weird macro language in 1992-93 and distinctly remember we had named zones.)
As Excel was made since the beginning as a replacement for Lotus 1-2-3, I believe it also must have had named ranges at least from its version for MS Windows 3.0.
IIRC, when I have first used Excel, which was the Windows 95 version, it had them.
Former Excel 97-XP developer here. Excel 97 had English language formulas. It automatically detected names in tables; however, it was buggy. The feature was removed in a later version (2007?).
My dream is either what you're describing here or a company like BareBones (of BBEdit) making a data crunching app with no formatting whatsoever. People send me large spreadsheets all the time and Excel (at least on a Mac) has a hard time keeping up.
I suppose I should just get better at using numpy and pandas.
once you count consumers, you get into semantics. Like is C/C++ more used than javascript, because all the javascript essentially runs in browsers compiled from C++?
By that metric, all programming languages including assembly have exactly 0 consumers, since nothing human-readable actually runs on computers.
Look at that, I have saved programming from language popularity contests, no need to thank me. I will humbly accept your bitcoins though.
---
The most reasonable definition of a language consumer is as follows: If a program text P is written by a human-level intelligence in source language L, then every user of any automatically-produced-artifact from P is a consumer of L.
Suppose a programmer use typescript to write a web app:
- The programmer, who must compile the typescript to javascript, is a consumer of whatever language the typescript compiler happens to be written in.
- The user, who must run the resultant javascript files, is a consumer of
(1) typescript, because the JS files is an artifact produced automatically
from the typescript text the programmer, who is a human-level
intelligence, wrote.
(2) The languages the browser/JS engine is source-written in, typically C++,
Rust, or Zig.
(3) Possibly javascript, because typescript is a strict superset of
javascript, and thus there is a probability that at least part of the
original text of the webapp is valid javascript, and thus qualify in the
definition. Take note that this has nothing to do with the typescript
program text compiling to javascript, this is solely due to the fact
that typescript is a strict superset of javascript, any language that is
not would not have this property.
From (3) we see that languages are not completely mutually exclusive : There is a language called Arithmetic ('+','*','-','/' and numbers) that has a vast userbase unparalled by any single programming language, since it's a subset of a lot of programming languages. Other consequences of this definition is that :
- Languages with no human-level-intelligence writers have 0 consumers
- Every producer is a consumer, since the only human-level intelligences currently writing code are humans, and human producers are always consumers as well since they run the artifacts produced from their own program texts.
- Auto-Generated code add to the consumers of the generating language, not the generated language. If part of a C++ app came from the output of a python script, every consumer of the app is a consumer of python, since some of the artifacts they consume ultimately came from python source. If the script generates (say) Rust or Fortran code that is then compiled and linked into the final executable, the only languages the users consume are C++ and Python. Auto-generated code is an automatically produced artifact.
Why not? Is writing a function to calculate a fibonacci number not programming? If no, why is it the introduction to a lot of functional language tutorials?
XKCD 1053 is relevant here. You and I knew it, but there are going to be lots of people who learn about it for the first time today, and that's a net positive.
HN still accepts new accounts. It's not an exclusive club for old people.
Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
I didn’t know about named ranges, and think it’s pretty cool. Of course, when I thought of places where I could apply them I realized that by the time I use enough Excel to use named ranges, I should probably switch to a real programming language.
Looks like the yoga button is a thing of the past :(
> After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported.
It's the learn TLA+ person :) Awesome to hear he is exploring Excel. Is that the manual version of crunching models for formal consistency?
Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet.
Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
Excel has also finally added sum() and other functions that allow the spreadsheet to do the hard work instead of making you use a calculator to add all the numbers up.
>A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
I long for the days of all-in-one word processor/spreadsheet/database apps.
Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.
Meh, this is clearly fabricated. I was following along with someone using Excel as a word processor up until they were also using Word as a spreadsheet.
decimal points all lined up (hell I can't even do that now)
In Word there are different types of tab stops, notably Left, Center, Right and Decimal.
If you turn on the Ruler (View tab, Ruler checkbox) you'll see a little bold "L" at the top left where the side and top rulers meet - that's not an L, that's an indicator for the kind of tab stop that will be created when you click on the top ruler. You can click on that tab stop type indicator to switch between tab stop and margin types - or if you double-click on the top ruler to set a tab stop it should show you the tab stop dialog that also allows choosing a tab stop type for each defined stop (along with things like setting a tab stop leader for things like a dotted line . . . . . . . . . . . . . . across to a page number in a table of contents).
My partner and I are in the process of recording an Excel course because I walked past her desk and saw her doing her accounts in Excel … with a Casio calculator. She was reading a value, doing it on the calculator, and typing it back in.
She’s smart. She’s 43. She was a medical copywriter. But Excel: no clue whatsoever.
We just recorded a lesson where I showed her VLOOKUP and she almost cried with joy. “Oh my poor accountant…”, she said. Fun moment.
Lol, literally came across this situation circa ~2012. I was an intern at a big but old school corporation. Someone asked me to help someone on a PM team with Excel, come to find out she was running some accounting numbers and was literally doing this. Reading numbers, putting them into a handheld calculator, and typing them back out. I had the pleasure of being the first person to ever show her sum(). Talk about blowing someone's mind.
Anecdotally I believe this sort of thing is terrifyingly common - in fact I wonder about the person-millennia of effort wasted globally as office workers across the globe huff and puff using Excel as a glorified typewriter.
The structure of that initial part isn't great given the headline, but I don't think its intending to claim that that's really new, just fairly unknown. And then talks about about the new stuff.
but it’s somehow underused. Often people don’t even realise they should be creating tables at all. Power query and friends are black magic at that point.
It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.
At my former company I first naivly tried to get more (non software) technical experts to use python and databases. That was quite an uphil battle, and we quickly pivoted to teach them how to make better use of excel. First and foremost: use tables. That easily leads to clearly deliniating input data, computations and output. Then we proceded to provide template sheets that use powerquery to fetch shared input data from centralised API's. This way we could let the end user do all the work, even though it might be a bit more messy/error prone. For well extablished workflows I could then take the excel program as a spec, and build a e.g. a webservice from that. Ss all the exceptions etc are already dealt with by the end-users, you can basically reverse engineer the spec from the excel sheet. As long as you can guide them to making legible excel sheets, it saves so much misunderstandings vs writing specs from scratch and building from that.
Excel excels as an exploratory / rapid prototyping tool.
Excel fails as an app development and execution platform, and specifically one integrated into a core business process.
Everyone who's worked in enterprise long enough has seen both. It'd be great if there was an enforceable "modern mode" Excel flag that kept people from going nuts with macros and programmability, while retaining all its strengths.
Bingo! Excel gives you a front row seat to the problems the business is trying to solve. It is usually not the best tool, but what they put together tells you what they need for a better solution.
I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call.
Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including:
- Multi-row headers
- Merged-cell headers
- Headers with the same name (sometimes useful)
- Formulas that cross rows (e.g. iteratively refer to the previous row)
- Different table sections (e.g. a table-width merged row with one header)
- Merged rows
The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.
Merged cells and multi row headers make data processing very difficult. These are best avoided in any sheet doing any computation. Only for reporting they are useful, especially when auto-generated as part if a pivot table. But I have never seen a legitimate use of merged cells in a computation.
Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot.
Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name
I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in.
I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
Well I work in an industry which still uses an archaic version of Excel. 2016 for Windows I guess? No XLOOKUP for me, yet. But yeah I’ll switch when it’s available.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
filtering drop-down 10k list limit is the most obvious one. There is also the hard traditional 1,048,576 limit which many hobbyist Covid tracking folks ran into.
Just an HN formatting note... To have * characters taken literally instead of italicizing the text in between, prefix each one with a backslash, like this:
Instead of writing the formula =A1\*B1, you can do =Width\*Height
which formats as:
Instead of writing the formula =A1*B1, you can do =Width*Height
Naming ranges in Excel is pretty darn easy, at least if you're doing full columns or rows - you select the range you want named (including by clicking the row/column header), then click into the field just to the left of the formula button that shows the selected cell. Just edit the name right there, it'll apply that name to the selected range.
Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells.
Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
He’s conflating it with LET and LAMBDA, which are new.
I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.
In 2022, there's probably a new generation working at those clients who have never seen the advanced features in Excel. You should put some feelers out to see if there's any demand.
For one of my first jobs, I had to spend a lot of time converting excel into python.
Excel is great for small places, but the moment you need to have oversight and accountability in an organization. You need a different centralized, permission controlled, and auditable solution.
It seems like quite a flaw in Microsoft's ecosystem that those requirements can't be met by some integration between Excel and, like, Sharepoint or whatever.
I think the fatal flaw is that there is no upgrade path towards sanity. If I start from a jupyter notebook or a script or whatever, it's trivial to build upon it. In Excel it's just an endless descent into madness.
All those funky features are just bandaids over a fundamentally wrong abstraction.
We have to acknowledge it's pretty much the only real tool for citizen developers out there, and as such it deserves some credit. But I don't really think it has any real advantage over $(your favorite scripting language); if you are proficient at coding I don't see why you would implement anything nontrivial in Excel given the choice.
The truly infuriating thing is that it wouldn't take much for spreadsheets to become literal 10x magic, but it's one of those path-dependent things. Sad.
> But I don't really think it has any real advantage over $(your favorite scripting language); if you are proficient at coding I don't see why you would implement anything nontrivial in Excel given the choice.
If you wanted to implement a spreadsheet, you'd be wasting your time with Python though. I'd rather manage and visualise my monthly budget in Excel.
I think this is the missing piece. Excel is great at user input.
Getting the same level of copy-paste, drag-drop, drag-auto-complete, "tweak until it looks right before I move on" data-entry experience is non-trivial.
> You need a different centralized, permission controlled, and auditable solution.
So, Access? ;)
But seriously, I really miss "PC" databases, Clipper etc.; Would often fill niches between spreadsheets and webapps. (Airtable etc. are trying to do part of that, but I'd say that's more a symptom than a solution)
Totally agree that audibility is an important part of the story here!
Having spent the past 2 years building a spreadsheet [1], it's really interesting how often we run into design problems that pit "audibility" against "what you expect from a spreadsheet."
A simple example: imagine you add a filter to column to remove null values, you then go and create a new formula that is dependent on this filtered column, before finally going back to edit the filter.
On one hand, effective audibility usually implies a nice, linear story you can follow and understand. On the other hand, users expect filters to update based on the most recent values - but the filter is applied in a way-old step. In practice, there's a bunch of extra state you have to store to make sure things work properly, and even then, there are lots of foot guns!
On the one hand you have all these great features, but on the other I can't even import a simple spreadsheet with checkboxes to excel online without breaking it because of active objects not being supported online.
It rides on the massive weight Microsoft market share has, so it doesn't have to be portable, open and consistent.
I know for me, I have a personal budgeting spreadsheet that is fairly complex. I'd love to simplify aspects of it with macros and checkboxes. However I can't use any macro-related features because then it becomes incompatible with the web interface, and I sometimes like to use that when working on Linux.
I really have no idea. I sometimes receive excel forms from clients and contractors that even though they are nothing fancy they won't open on excel online. I am a full time DevOps and systems architect can't work without Linux you see.
So sometimes I have to resort to a Windows VM and Excel, the real deal to fill out those forms.
I used wine a long time ago, but it would only support a very specific, very ancient version of Excel, so a VM is actually less of a hassle (when already prepared. The process of installing windows on a VM was quite frustrating for me).
But no, I don't think there is any substitute to excel. Microsoft made sure of that.
In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referring to cells containing a calculation. Not in a separate window, but in sheet, so you can use multiple cells to break it up nicely.
A B C
+-------------------------
1 | =VARIABLE(X)
2 | =A1*2
3 | =A2+1
And then you could define `MYFUN()` to be A3 and use `=MYFUN(6)` in a cell to get 13. Or maybe, `=EVALUATE(A3; A1)` or `=EVALUATE(A3; 'X')` or something.
I often have complicated, multi step calculations. Currently I copy-paste them for every row (and Excel helps with keeping the formulas in sync). But it would be great if you could do it once on an example (maybe even on another sheet) and then turn it into a custom formula.
(I used to think ancient Excel 4 macros were that from what it looked like, but unfortunately they are something completely different.)
It's not a perfect solution to your problem, but you can simplify multistep calculations with LET. So it would be:
LET(X, A1, Y, X*2, Y+1)
I just checked and the Evaluate Formula button can sequentially step through a LET. Though this doesn't give you intermediate results you can use in other calculations.
My experimental spreadsheet does something like this. You can refer to the value of a cell given some different values in other cells, and it calculates the value of the cell given just those changes. Syntax is cellname{othercell=othervalue, ...}.
So you can use a group of cells like a function with parameters (with defaults).
That’s more or less how Lotus-123 did that kind of thing.
As others said, you can use lambda, but the ‘old modern way’ to do that is to write a function in VBA and call that (https://stackoverflow.com/a/16296990)
A lot of SaaS startups with a shitty React frontend should actually exist as a PDF report that gets emailed out every morning plus a site with a button to upload a CSV/XLSX file for tomorrow's report.
Sure, now try to open a CSV that has line breaks in some cells (which is something basic and that follows the CSV spec) and tell me if Excel is still cool.
It is literally impossible. The best solution is "Import it in Google Sheets and export to Excel format"...
Excel is powerful, but it is not cool at all, the UX of even basic features is awful and some of the most used functions have annoying behaviors (VLOOKUP when the data type of the 2 columns is not exactly the same for example).
It sounds like SO isn't really an expert on Excel. The correct answer (Data > From Text/CSV) is sitting at the bottom with 0-1 votes. Most answers are from the early 2010's and outdated.
In any case, not supporting when you just double-click a CSV to open it with Excel means that it's not usable for the vast majority of users.
We faced the issue when we were exporting data to customers in CSV format, and "Replacing new lines with '/'" ended up being a preferred solution over having the user perform any action.
Supported doesn't mean current. They'll fix security holes and help paying customers use the software as it is, but that is it. Not introducing new features or backport improvements.
Yeah, pretty sure that having to use an ETL layer before Excel qualifies as "It's literally impossible to do it in Excel".
> Use XLOOKUP.
It's only available after Excel 2019, and the behavior is different. There's also nothing that seems to indicate that it doesn't have the same issue with column types?
RFC 4180 came too late. I think CSV is more commonly taken to be just a general description of the data, than a reference to a particular standardized spec (I mean it is notorious for ad-hoc implementations, right?).
Excel literally has the power to perform better than multimillion dollar consultant implementations on all kinds of things (having been on those projects and unable to supplant Excel successfully).
But is hard to audit. Web apps are committed in Git and have code reviews and client testing. If Excel made it possible to commit the formulas into Git, it would have taken over a million of accounting apps and SAP for 50 additional years.
But the lack of reliability of “a spreadsheet made by John in Accounting” sorely limits the success it can have.
I'm in energy trading and wonder what the tech setups are in other companies. Can you tell me something about yours?
We currently migrate everything to the cloud, which breaks many Excels that so far got their inputs from on-prem services or the file system.
What could previously be deployed without second thought now requires extensive, decentralized cloud knowledge in each team. Power is taken from the lay business user. What took seconds in VBA now requires a feature request to a dev team.. it's insane.
Yep. We have all of our backtesting and analysis in the cloud. Python notebooks for analysis. Java for backtesting. C++ for performance critical or for where Java API is not useful.
I'm in HFT so we have software engineers available to our portfolio managers. Everything is in code.
Live strategies are deployed via a script that ultimately ssh to a machine, plops a set of artifacts in from storage, and then runs. But everything needs to restart together.
The IT department is limited by Turing's famous Halting Problem:
Given a complete set of requirements and a working prototype, will the IT department ever finish writing software that meets those requirements?
Okay, that was sarcastic, but illustrates a real problem, which is that doing it in Excel is making something, whereas getting IT involved is managing something -- a category error. The problems of managing software development are unchanged, half a century after The Mythical Man Month.
Indeed, communicating requirements is one of the hardest parts of managing anything technical. Especially if you're working with people who are a couple steps removed from the background domain knowledge.
Isn't this why people always save excel files a certain points of time? So you can go back to it and see it's state at certain "commit" points?
For me, it often looks like this.
2023 Budget v3 - 2022.08.01.xlsb
2023 Budget v3 - 2022.08.05.xlsb
2023 Budget v3 - 2022.08.08.xlsb
2023 Budget v3 - 2022.08.12.xlsb
The dates indicate minor updates/changes to the data (eg. the database is different but the logic is the same). Where as the version number is a change of business logic &/ formulas &/ file architecture (so v3 files are usually compatible with v3 files, but v4 files may break everything when comparing back to v3).
Granted, it doesn't help any with collaboration. My team still passes around the "current" file and deal with read-only locking issues (we find shared mode to be highly broken and conducive to file corruption). But, it does help with auditing because we can always go back and find why a number was what it was on any given day. So in that sense, Excel documents are as auditable as open source software. You have to want to read the code but it's all there for the reading.
Same. Also the problems I've encountered when one file tries to autosave, triggering a calculate before save, when I am actually working in something large that is calculation sensitive (manual calculation mode is on), and my whole application freezes/locks because it calculates when I'm not expecting it. Always happens 5 minutes before a deadline too. So yeah, disable that.
It's also a Workbook level setting so, you might have it turned off, but someone sends you a file with it enabled. For that reason, I have a macro that disables it at Open for all files (same for calculate before save, I really dislike that setting too).
You can use git hooks or CI tools to unzip xlsx files when they’re committed and run an autoformatter on the resulting xml files. The xml diffs aren’t the most readable, but they’re usable.
We built xltrail[0] (cloud and self-hosted SaaS) that lets you see the diffs for sheets, VBA, and a couple other things. You put in your git repo and it just works. You can also have a manual versioning option where you upload new versions of the same file and you get the same result.
We also have the open source git extension Git XL[1] that lets you see VBA diffs locally.
Looks at the 100-line SQL query that joins across 10 tables with 7 different WITH clauses and several random COALESCE statements sprinkled in to make the query work
Sounds great until you have multi-million dollar programs running off single point of failure spreadsheets with little or no backup or overwrite protection.
Python's pandas makes working with large numbers of Excel / LibreOfficeCalc files a lot easier. Excel is very convenient for the non-programming user of course, for example making graphic visualization is far easier. With pandas you need to interface programmatically with matplotlib or similar.
If you want to up your skills in this area, Wes McKinney's "Python for Data Analysis" is pretty great.
I did a stint in document management for a company.
Let me disagree: All of Office is a very very very bad thing from a "shared information" standpoint. If you put information into an office document/spreadsheet/etc, it effectively locks it from use in the rest of the company, publishing, and programmatic analysis.
Suuuuure, there's lots of products out there that will offer services to extract data from your word docs, or all that adhoc data in your spreadsheet.
Extracting data from word documents is definitely in the "with a million lines of code you can do anything" camp. It's just a lot of ugly crap but its doable.
But... why?
Why have companies allowed Microsoft to make their storage formats utterly inaccessible and integration resistant? Well, it made them mints of money in monopolizing/stopping switchover to competitors, and allowed them to make money on the tools to extract/use their insufferable storage formats.
Soooo much efficiency lost.
Excel the programming and UI app is an amazing achievement (here's to Lotus 123 for making the first spreadsheet, as with everything, Microsoft didn't create it, it copied it).
But for a data creation, manipulation, and "database", it is a tragedy.
If their mind is blown by named ranges, they are going to faint when learning about the nice structure a proper table (Insert -> Table) gives!
It's so handy typing
=SUM(tbl_Sales_Transactions[Total])
And knowing you don't have to adjust the range of the named area as long as the table is the correct length. Far nicer than SUM(F:F) or SUM(F2:F1000) which can cause performance issues and could be exceeded when pulling in new data respectively.
Within the table, you can do this syntax to use items on the same row of data.
With power query, named tables, linked data types, lambdas, and xlookup, you can hack structs into excel, letting you write `Person("John").age` in formulas.
Is there a way to do the variable assignment inline? I mean, it's nice to have an overview of all of them, but it would be neat to get them in there by just "tagging" a cell (preferably textually).
Not sure if this is what you mean but IIRC you can give the current selection a name by just typing it into the box in the top-left (the one that shows what is currently selected).
You can name cells in the upper left corner and name ranges under `Data > Named Ranges`. You can't create a value that's not part of a cell or range (which you can do in Excel under the Name Manager.)
Excel is great until suddenly it's not. When I started building ProjectionLab, it began as an excel spreadsheet that (at first) was easy to share and explain to friends and family who wanted a better retirement plan. But as they asked for more flexibility and new features, it spiraled out of control into a complex web of advanced Excel features that became opaque and unmaintainable. No doubt Excel is powerful and feature-rich, but sometimes layering on more and more of those advanced features isn't the answer.
Would it be any different if you used a different tool and/or programmed everything manually?
In my experience, it would be 50x worse because Excel handles for you a lot of things that are extremely time consuming to code manually. (eg. error handling)
I guess it depends on what you mean by worse. In my case, I abandoned excel and rebuilt the tool as a web app using Vue.js, Chart.js, Firebase, etc., which is what ProjectionLab is now. More overall development work, but the end result is way better than a spreadsheet.
Basically on the long term the possibility to use real code that is easy to change and understand will save you incredible amounts of time. If you reach a point where your code is too complicated that you are afraid to change anything, you're basically stuck.
Excel is an extremely cool piece of software. As someone who spent the past two years of his life implementing a spreadsheet [1] (built to solve many of The issues with spreadsheets mentioned in this thread: data size limits, speed limits, and repeatability problems), it’s unbelievable how many different use cases are supported by just Excel.
One of the funnier Excel use cases I’ve seen in the wild, when talking to users, is someone who had implemented the game 2048 entirely within a spreadsheet (ofc VBA). And it wasn’t just for fun - they weren’t allowed to play games on their work computer, but they needed their daily gaming fix. If you can dream it, Excel can probably get it done…
I bet... if you use a column as a list of the wasd inputs (i.e. "w enter" records as an up move) you could get 2048 without VB. Maybe even without the new lambdas but it'd be significantly easier thanks to that. A new thing for the "I better not waste my time on this" list ;).
261 comments
[ 2.6 ms ] story [ 272 ms ] threadI'm not sure what "proper tables" would look like, but Google Sheets has JavaScript integration, which is nice (if a bit slow).
This is the third time this has come up on Hackernews in the past week.
Excel lets you designate a specific rectangle of cells as a named "table" (this is not the same thing as a pivot table). Each named column in the table automatically gets its own named range, which belongs to a namespace of that table (rather than the global namespace). A table will automatically expands its bounds when you insert data into cells immediately bordering it. Inserting a formula into the first row automatically fills-down that formula into the entire column. There is special syntax for referring to rows, columns, and ranges of columns within a table. Look up "structured references".
They are very useful. They make big spreadsheets tractable and maintainable. Think of them as mutable, expandable, reactive dataframes. If you're dealing with inherently tabular data in Excel and you're not using the tables feature, you are doing it wrong.
LibreOffice Calc and Google Sheets do not have them, to my endless bafflement.
You can get by without them through countless crappy hacks, but nobody would ever choose a language that couldn't support arrays.
IT chooses Google Sheets for whole companies every day even though they aren't sophisticated, or often even daily, users of any of the apps.
I don't have access to super old versions of Excel, but I just confirmed that it worked perfectly well in Office 2003. I believe it's been the case forever.
(I wrote whole apps in Excel4 super-weird macro language in 1992-93 and distinctly remember we had named zones.)
I think later versions of VisiCalc did, too.
As Excel was made since the beginning as a replacement for Lotus 1-2-3, I believe it also must have had named ranges at least from its version for MS Windows 3.0.
IIRC, when I have first used Excel, which was the Windows 95 version, it had them.
The name manager was added in Excel 2007.
I suppose I should just get better at using numpy and pandas.
Look at that, I have saved programming from language popularity contests, no need to thank me. I will humbly accept your bitcoins though.
---
The most reasonable definition of a language consumer is as follows: If a program text P is written by a human-level intelligence in source language L, then every user of any automatically-produced-artifact from P is a consumer of L.
Suppose a programmer use typescript to write a web app:
- The programmer, who must compile the typescript to javascript, is a consumer of whatever language the typescript compiler happens to be written in.
- The user, who must run the resultant javascript files, is a consumer of
From (3) we see that languages are not completely mutually exclusive : There is a language called Arithmetic ('+','*','-','/' and numbers) that has a vast userbase unparalled by any single programming language, since it's a subset of a lot of programming languages. Other consequences of this definition is that :- Languages with no human-level-intelligence writers have 0 consumers
- Every producer is a consumer, since the only human-level intelligences currently writing code are humans, and human producers are always consumers as well since they run the artifacts produced from their own program texts.
- Auto-Generated code add to the consumers of the generating language, not the generated language. If part of a C++ app came from the output of a python script, every consumer of the app is a consumer of python, since some of the artifacts they consume ultimately came from python source. If the script generates (say) Rust or Fortran code that is then compiled and linked into the final executable, the only languages the users consume are C++ and Python. Auto-generated code is an automatically produced artifact.
I don't count computation as programming (Sum/count/avg).
But most people use count/max/avg prebuilts, with little to no logic. If it ain't Turing Complete, it's not programming :P
"Instead of writing the formula =A1B1, you can do =WidthHeight like you should have been able to 30 years ago."
Not sure what this dude is talking about. Range names have been in Excel for decades.
Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
There's other, more useful data types, like cities and ZIP codes and stocks, I just listed the yoga one because it's the funniest.
> After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported.
https://support.microsoft.com/en-us/office/what-linked-data-...
Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet.
Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
>A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.
https://en.wikipedia.org/wiki/E._E._Cummings
In Word there are different types of tab stops, notably Left, Center, Right and Decimal.
If you turn on the Ruler (View tab, Ruler checkbox) you'll see a little bold "L" at the top left where the side and top rulers meet - that's not an L, that's an indicator for the kind of tab stop that will be created when you click on the top ruler. You can click on that tab stop type indicator to switch between tab stop and margin types - or if you double-click on the top ruler to set a tab stop it should show you the tab stop dialog that also allows choosing a tab stop type for each defined stop (along with things like setting a tab stop leader for things like a dotted line . . . . . . . . . . . . . . across to a page number in a table of contents).
She’s smart. She’s 43. She was a medical copywriter. But Excel: no clue whatsoever.
We just recorded a lesson where I showed her VLOOKUP and she almost cried with joy. “Oh my poor accountant…”, she said. Fun moment.
Forgive the spruik but while we’re here: https://www.learnwithlucy.rocks/courses/excel?coupon=earlych...
It’s probably not for anyone reading this thread but it might be for your partner or kids. And it definitely works, because Lucy can use Excel now.
It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.
That sounds like a potentially great approach for consultancy business/product discovery.
Excel fails as an app development and execution platform, and specifically one integrated into a core business process.
Everyone who's worked in enterprise long enough has seen both. It'd be great if there was an enforceable "modern mode" Excel flag that kept people from going nuts with macros and programmability, while retaining all its strengths.
I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call.
I wrote this to make that easy: https://GitHub.com
Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including:
- Multi-row headers - Merged-cell headers - Headers with the same name (sometimes useful) - Formulas that cross rows (e.g. iteratively refer to the previous row) - Different table sections (e.g. a table-width merged row with one header) - Merged rows
The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.
Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot.
Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name
I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in.
I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
I like it more than index match.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
They somehow cooked up a spreadsheet that make them hit the limits much earlier.
https://www.bbc.com/news/technology-54423988.amp
Excel has grown hugely since then.
You can’t exceed these limits without being heavily alerted/warned by the application too.
Every spreadsheet application and database has these sort of limits anyway, eg:
* Google Sheets limits total cells, although can only handle a tiny fraction of what excel can.
* Postgres limits columns to 1600 (much less than excel)
* Mongo limits document size
Instead of writing the formula =A1*B1, you can do =Width*Height
MS Excel 2.0 (1987) could do the same, but entering names for ranges was not as easy as in Multiplan. Look in the menu for Formula | Names…
[1] https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...
Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells.
Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.
For one of my first jobs, I had to spend a lot of time converting excel into python.
Excel is great for small places, but the moment you need to have oversight and accountability in an organization. You need a different centralized, permission controlled, and auditable solution.
All those funky features are just bandaids over a fundamentally wrong abstraction.
We have to acknowledge it's pretty much the only real tool for citizen developers out there, and as such it deserves some credit. But I don't really think it has any real advantage over $(your favorite scripting language); if you are proficient at coding I don't see why you would implement anything nontrivial in Excel given the choice.
The truly infuriating thing is that it wouldn't take much for spreadsheets to become literal 10x magic, but it's one of those path-dependent things. Sad.
If you wanted to implement a spreadsheet, you'd be wasting your time with Python though. I'd rather manage and visualise my monthly budget in Excel.
Getting the same level of copy-paste, drag-drop, drag-auto-complete, "tweak until it looks right before I move on" data-entry experience is non-trivial.
So, Access? ;)
But seriously, I really miss "PC" databases, Clipper etc.; Would often fill niches between spreadsheets and webapps. (Airtable etc. are trying to do part of that, but I'd say that's more a symptom than a solution)
Having spent the past 2 years building a spreadsheet [1], it's really interesting how often we run into design problems that pit "audibility" against "what you expect from a spreadsheet."
A simple example: imagine you add a filter to column to remove null values, you then go and create a new formula that is dependent on this filtered column, before finally going back to edit the filter.
On one hand, effective audibility usually implies a nice, linear story you can follow and understand. On the other hand, users expect filters to update based on the most recent values - but the filter is applied in a way-old step. In practice, there's a bunch of extra state you have to store to make sure things work properly, and even then, there are lots of foot guns!
[1] https://trymito.io
On the one hand you have all these great features, but on the other I can't even import a simple spreadsheet with checkboxes to excel online without breaking it because of active objects not being supported online.
It rides on the massive weight Microsoft market share has, so it doesn't have to be portable, open and consistent.
So sometimes I have to resort to a Windows VM and Excel, the real deal to fill out those forms.
I used wine a long time ago, but it would only support a very specific, very ancient version of Excel, so a VM is actually less of a hassle (when already prepared. The process of installing windows on a VM was quite frustrating for me).
But no, I don't think there is any substitute to excel. Microsoft made sure of that.
Lots of people thought AOL WAS the Internet.
And to this day many think EXCEL is everything.
In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referring to cells containing a calculation. Not in a separate window, but in sheet, so you can use multiple cells to break it up nicely.
And then you could define `MYFUN()` to be A3 and use `=MYFUN(6)` in a cell to get 13. Or maybe, `=EVALUATE(A3; A1)` or `=EVALUATE(A3; 'X')` or something.I often have complicated, multi step calculations. Currently I copy-paste them for every row (and Excel helps with keeping the formulas in sync). But it would be great if you could do it once on an example (maybe even on another sheet) and then turn it into a custom formula.
(I used to think ancient Excel 4 macros were that from what it looked like, but unfortunately they are something completely different.)
So you can use a group of cells like a function with parameters (with defaults).
Fizzbuzz example: https://visibot.com/webdemo/#/%2Fexamples%2Ffizzbuzz.vb Use right and left arrow keys to scrub through the program's execution. The help link in the NE corner gives more details.
(This is still evolving, and uses WASM to run in the browser so it's desktop only. LMK if it doesn't work for you.)
As others said, you can use lambda, but the ‘old modern way’ to do that is to write a function in VBA and call that (https://stackoverflow.com/a/16296990)
https://stackoverflow.com/questions/2668678/importing-csv-wi...
It is literally impossible. The best solution is "Import it in Google Sheets and export to Excel format"...
Excel is powerful, but it is not cool at all, the UX of even basic features is awful and some of the most used functions have annoying behaviors (VLOOKUP when the data type of the 2 columns is not exactly the same for example).
In any case, not supporting when you just double-click a CSV to open it with Excel means that it's not usable for the vast majority of users.
We faced the issue when we were exporting data to customers in CSV format, and "Replacing new lines with '/'" ended up being a preferred solution over having the user perform any action.
You can do this in Powerquery.
>VLOOKUP when the data type of the 2 columns is not exactly the same for example).
Use XLOOKUP.
Yeah, pretty sure that having to use an ETL layer before Excel qualifies as "It's literally impossible to do it in Excel".
> Use XLOOKUP.
It's only available after Excel 2019, and the behavior is different. There's also nothing that seems to indicate that it doesn't have the same issue with column types?
Use INDEX and MATCH.
CSV of all types can be imported with power query.
But the lack of reliability of “a spreadsheet made by John in Accounting” sorely limits the success it can have.
Asking for software is like a gestapo interrogation - your always not right and in the end all motives are questioned.
We currently migrate everything to the cloud, which breaks many Excels that so far got their inputs from on-prem services or the file system. What could previously be deployed without second thought now requires extensive, decentralized cloud knowledge in each team. Power is taken from the lay business user. What took seconds in VBA now requires a feature request to a dev team.. it's insane.
I'm in HFT so we have software engineers available to our portfolio managers. Everything is in code.
Live strategies are deployed via a script that ultimately ssh to a machine, plops a set of artifacts in from storage, and then runs. But everything needs to restart together.
Given a complete set of requirements and a working prototype, will the IT department ever finish writing software that meets those requirements?
Okay, that was sarcastic, but illustrates a real problem, which is that doing it in Excel is making something, whereas getting IT involved is managing something -- a category error. The problems of managing software development are unchanged, half a century after The Mythical Man Month.
For me, it often looks like this.
2023 Budget v3 - 2022.08.01.xlsb
2023 Budget v3 - 2022.08.05.xlsb
2023 Budget v3 - 2022.08.08.xlsb
2023 Budget v3 - 2022.08.12.xlsb
The dates indicate minor updates/changes to the data (eg. the database is different but the logic is the same). Where as the version number is a change of business logic &/ formulas &/ file architecture (so v3 files are usually compatible with v3 files, but v4 files may break everything when comparing back to v3).
Granted, it doesn't help any with collaboration. My team still passes around the "current" file and deal with read-only locking issues (we find shared mode to be highly broken and conducive to file corruption). But, it does help with auditing because we can always go back and find why a number was what it was on any given day. So in that sense, Excel documents are as auditable as open source software. You have to want to read the code but it's all there for the reading.
We want to save after Big Change X has been made, not at a random interval in time.... so everyone at the office rightly disables autosave
It's also a Workbook level setting so, you might have it turned off, but someone sends you a file with it enabled. For that reason, I have a macro that disables it at Open for all files (same for calculate before save, I really dislike that setting too).
We built xltrail[0] (cloud and self-hosted SaaS) that lets you see the diffs for sheets, VBA, and a couple other things. You put in your git repo and it just works. You can also have a manual versioning option where you upload new versions of the same file and you get the same result.
We also have the open source git extension Git XL[1] that lets you see VBA diffs locally.
[0] https://www.xltrail.com/
[1] https://www.xltrail.com/git-xl
Spreadsheet Compare, an official diff tool, is excellent
Yup, so easy to audit.
sales-2005-(copy)-Final-2015-(copy)-real-final-(copy).xlsx
https://support.microsoft.com/en-us/office/overview-of-sprea...
If you want to up your skills in this area, Wes McKinney's "Python for Data Analysis" is pretty great.
And Wes (the creator of the Pandas library) has made the book available for free on his website!
Let me disagree: All of Office is a very very very bad thing from a "shared information" standpoint. If you put information into an office document/spreadsheet/etc, it effectively locks it from use in the rest of the company, publishing, and programmatic analysis.
Suuuuure, there's lots of products out there that will offer services to extract data from your word docs, or all that adhoc data in your spreadsheet.
Extracting data from word documents is definitely in the "with a million lines of code you can do anything" camp. It's just a lot of ugly crap but its doable.
But... why?
Why have companies allowed Microsoft to make their storage formats utterly inaccessible and integration resistant? Well, it made them mints of money in monopolizing/stopping switchover to competitors, and allowed them to make money on the tools to extract/use their insufferable storage formats.
Soooo much efficiency lost.
Excel the programming and UI app is an amazing achievement (here's to Lotus 123 for making the first spreadsheet, as with everything, Microsoft didn't create it, it copied it).
But for a data creation, manipulation, and "database", it is a tragedy.
Are you talking about some specific use case, such as data that should be in an ERP or CRM being stored in a spreadsheet instead?
Please elaborate.
It's so handy typing
And knowing you don't have to adjust the range of the named area as long as the table is the correct length. Far nicer than SUM(F:F) or SUM(F2:F1000) which can cause performance issues and could be exceeded when pulling in new data respectively.Within the table, you can do this syntax to use items on the same row of data.
https://m.youtube.com/watch?v=uNjxe8ShM-8
In my experience, it would be 50x worse because Excel handles for you a lot of things that are extremely time consuming to code manually. (eg. error handling)
Basically on the long term the possibility to use real code that is easy to change and understand will save you incredible amounts of time. If you reach a point where your code is too complicated that you are afraid to change anything, you're basically stuck.
One of the funnier Excel use cases I’ve seen in the wild, when talking to users, is someone who had implemented the game 2048 entirely within a spreadsheet (ofc VBA). And it wasn’t just for fun - they weren’t allowed to play games on their work computer, but they needed their daily gaming fix. If you can dream it, Excel can probably get it done…
[1] https://trymito.io