6 ms·
My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more dat
by viraptor 7y ago
My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works.
Half of the mess that makes excel hell comes from the fact it's too easy to put two tables of data + some random constants on a single sheet and refer to them by H3. Now it's hard to add more data, hard to move anything, and hard to create space which expands to the next row with existing formulas.
Airtable (and Access) implements this idea, but unfortunately sacrifices the generic, free-form spreadsheet along the way.
I'd be even happy with clippy popping up with "It looks like you're adding a new table in the same sheet. Would you like to learn about using multiple sheets?"
- dgaudet 7y agonamed ranges and DGET "sql-like" queries are in google sheets as well. (or perhaps what you meant is that few people know about these things, and don't use them, so you end up with unreadable messy code...)
- viraptor 7y agoYeah, I meant that they're not right in your face and ready to be used. I know it's hard to force people to use new methods, but if anyone could do it, MS / Excel could. It's all possible though, if you know where to look (I think excel even has table sheets now?)
- davedx 7y agoI see what you're saying, but this is actually a deliberate usability principle: make the basic features easy to access (visible without clicking into a menu), and put advanced features inside menus. Then basic users who don't need more advanced features can do what they need without getting lost in a super complex UI, and power users can still get to the advanced stuff. If people want to do advanced stuff with software, they really do need to invest the time to learn where the advanced features of the software are. I think this is reasonable.
- sheetjs 7y agoWe (https://sheetjs.com/ https://sheetjs.com/) almost exclusively deal with those types of complex applications built in spreadsheets > they could be improved a lot with minimal changes They can be improved for very specific use cases at the expense of others. > Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. There are "database functions" like DAVERAGE, PivotTables and other systems for dealing with more structured data. Getting users to use them is a challenge outside of the scope of the tool. "You can lead a horse to water but you can't make it drink" > Half of the mess that makes excel hell comes from the fact it's too easy to put two tables of data + some random constants on a single sheet and refer to them by H3. Half of the reason Excel is so popular is the loose structure and the power that it enables. > Now it's hard to add more data, hard to move anything, and hard to create space which expands to the next row with existing formulas. Most of the hairy workbooks start as a solution to a problem at hand and eventually accumulate cruft after people try expanding it, not too dissimilar to other forms of software development. > Airtable (and Access) implements this idea, but unfortunately sacrifices the generic, free-form spreadsheet along the way. You either enforce the constraints and weaken the platform, or you give users the power to do what they want.
- viraptor 7y ago> You either enforce the constraints and weaken the platform, or you give users the power to do what they want. I disagree with that. All the pieces are already there. There's nothing to weaken by education. But nothing tells the new users about them. They see the main screen and rarely ever know about multiple sheets. I've seen people using excel at work for years without knowing that. You don't have to take anything away from them, just go: hey, did you know there's a better way? I know it's possible because I introduced a few people to named ranges, sheets, and data tables. All were happy to apply them later. (On their own)
- chaostheory 7y agoYou're both right. From experience, some people are happy to learn new things. Others, as sheetsjs pointed out, are not interested.
- TBurette 7y agoThat's what Excel Tables are for. You get a table within your spreadsheet and instead of using cell references like =Sum(C2:C7), you can use structured reference like =SUM(DeptSales[Sales Amount])
- viraptor 7y agoTotally. I know about it. The problem is that approximately nobody using excel does. (Yes, finance, data processing, analysts etc. do. Compared to the numbers with excel installed - still ~nobody.)
- beefield 7y agoI have quite actively tried to avoid excel lately (which means also avoiding to learn new tricks...), and I am not that familiar with tables. Do the tables: 1. Enforce same data type per column. I.e., if you have a number in C4, you can't enter text into C5? 2. Enforce that if there is a formula, it is applied identically to each row? (By enforcing I mean that there is _no_ way around that short of copying the data to new sheet) Give me those two things (preferably within sheet context instead of table context) and a decent version control and I can reconsider that I do anything but disposable ad-hoc in excel again.
- function_seven 7y agoUnfortunately the answer is "no" to both #1 and #2. It does do something close to those. Column data types are automatically carried over to the next row when inserting or appending rows to an existing table. But you're free to override it if you want for a particular cell Same with formulas. When you enter a formula in one cell, Excel will automatically copy across the whole column. But you're able to undo that auto copy if you really just want the formula in that one cell. It'll also flag cells that have inconsistent (compared to rest of table) formulas. So, no, it doesn't enforce. But it does encourage.
- LeonB 7y agoThe warning you receive when a formula is not consistent is a nice feature, and an advanced user will quickly learn to be very sensitive to those warnings, will spot them quickly, and avoid causing them when possible. To strictly enforce that the formula must be the same in each cell of the column would not be very excel-like, I can't see it being done.
- epistasis 7y agoI'd recommend watching this YouTube video; it's only one person's take on the right way to use Excel, but Excel has many ways to handle these issues: https://m.youtube.com/watch?v=0nbkaYsR94c https://m.youtube.com/watch?v=0nbkaYsR94c
- viraptor 7y agoYup, love it, keep recommending it to people.
- J_cst 7y agoThat's a classic, definitely to be watched!
- LeonB 7y agoThe link is to "You Suck at Excel" by Joel Spolsky, which is an excellent tutorial. I sincerely wish it had a different name, though! I would love to send this video to some of my business colleagues inside a large enterprise. They need this information and they would enjou everything about this video. However, it would not be acceptable to send them a video entitled "You Suck at Excel." If it had a more enterprise-friendly name I would even have a link and description to it in my email footer inside the enterprise. It would really help a lot of people.
- TuringTest 7y agoYou can embed it in a more professional page, and hope that no one will notice the title at the overlay ;-)
- LeonB 7y agoGenius idea. I have a bunch of domains, combine it with the right subdomain... and we're onto something. Adding this to my list of things to do. Cheers! Update: okay check out https://excel.secretgeek.net/ https://excel.secretgeek.net/ I've re-badged it as "Secrets of Mastering Excel" and used absolute positioning to put a label to that effect over the video's title. Ideally I'd detect when the video starts and remove the label. Hmm. Not sure of the right approach. I recommend (at the foot of the page) that the viewer watch it in full screen (which will remove my dodgy title)
- tonygrue 7y agoI'd be over the moon to see one change to Excel. Native support for a language other than VBA. Perhaps the CLR. Perhaps Java/Typescript. Just something (optional) for programmers who want to use Excel and not want to deal with the garbage that VBA is. Some workarounds exist, but afaik they require collaborators to also have the tool install, which is dead in the water. I know MS has considered it, I'm still pretty surprised they've haven't followed through.
- dolmen 7y agoMS hasn't just considered it. They have done it. This is called Visual Studio Tools for Office. https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office#Comparison_with_VBA https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office...
- tonygrue 7y agoI've built plugins with VSTO (for Outlook) when I was at MS, but my memory is that it’s dependent on a plugin being distributed and not available for native use embedded into the excel workbooks. You have to install Visual Studio, compile a plugin, register the plugin, and then from VBA you can call out to that COM interface if you'd like. Even if Excel users could overcome those hurdles, you still break the collaboration flow (i.e. just sharing an excel file). Looking a little deeper, it looks like they're starting to support Javascript for the newer cross-platform add-in system (and also VSTO), but it looks like you still have to distribute your JS add-in via a web-service as opposed to being fully integrated into Excel and XLSM files. The bit of just being able to share the XLSM file and users using Excel with no other installs or special network access, is really the make or break for me and non-professional programmer user scenarios I've seen.
- kgwgk 7y agoMicrosoft would also like to see that change, that’s why they tried to kill VBA. The reason for not following through (in killing VBA, because they definitely support and prefer other extension mechanisms) was pushback from users.
- skrebbel 7y ago> the fact it's too easy to I'm not sure you fully appreciate why Excel is so successful.
- viraptor 7y agoNo, I fully understand that this is great for many users and that it cannot be made harder in any way to make whatever mess you want on a single sheet - or excel would die. I only want it easier to not do that. (There's nothing guiding people to better design right now)
- etatoby 7y agoThat's by design. People don't want to be "guided to better design," they want to get their math done and over with, leaving some trace (the Excel sheet) for the next time they or somebody else needs to revise that math.
- TuringTest 7y ago> I only want it easier to not do that. I'd rather want it to stay easy, but offer better refactor tools to clean up the model after the fact. My main complaint against spreadsheets is that, to get a robust and clean model, you must plan from it from the start, negating the main value of using a spreadsheet to begin with. When moving data and formulas around, it's too easy to break the formulas and not even notice.
- ChrisCinelli 7y agoI agree. Both https://airtable.com/ https://airtable.com/ and https://www.smartsheet.com/ https://www.smartsheet.com/ in my option add some value on top. Unfortunately they are not omnipresent as Google Sheets and Excel. > Half of the mess that makes excel hell comes from the fact it's too easy to put two tables of data + some random constants on a single sheet and refer to them by H3. That is a feature. If you can keep in a screen all the data you need, you reduce the cognitive overhead of having to switch back and forth between tabs. Of course if you start needing to add more data you need to "refactor". But it does not seem a lot more different that starting with a prototype and having to change everything to support the features of a real product.
- TuringTest 7y ago> it does not seem a lot more different that starting with a prototype People underestimate this point. Spreadsheets are one of the best possible approaches for prototyping data-driven applications, thanks to its dual data/code nature and the ease to build and extend data types (just add more columns to a table); it's like building the application inside a debugger. Non-developers don't have anything else that resembles a debugger, anywhere in the common approaches of software for end-users in industry. Combine this with spreadsheet applications working as integrated environments -a single tool that handles all the computing needs of a project, without having to build a toolchain-, and it's no wonder than it's the preferred method for non-programmers to build custom automation workflows when their needs aren't supported by any specific software.
- matwood 7y ago> Spreadsheets are one of the best possible approaches for prototyping data-driven applications, Bingo. Back when I worked in big corp Excel was the prototyping tool for the masses. If someone in a remote office built something in Excel that solved a problem that was useful to other offices my team would come in and use that work to build a real system. It was great because a lot of the requirements discovery work happened organically before we would even hear about the process/Excel. Admittedly, we were a small team and could only take on so much work. This led to Excel being used beyond its capabilities which leads to a host of other problems.
- pbowyer 7y ago> at least you can make named tables in excel on windows I built a spreadsheet that loaded external data sources (filtered log files from a production system) into Excel tables. I then combined two log files into another table, and created a pivot table from this, which I then filtered (date ranges etc) and analysed. That worked great at the start - until I came to update the external data and found the pivot table didn't update. Then when I force an update it lost my groupings, filtering etc. Any idea where I went wrong?
- deburo 7y agoI suggest you use Power Query to load external data. If you do, pivot tables and tables connected to those queries should all update automatically on refresh. Power Query is really the best feature in Excel to me, eventually users will stop copy pasting from Access to Excel and I'll be happy.
- kinj28 7y agoIn my opinion - "No Code" tools will fix this gap to build real world apps yet being as simple as excel. While airtable is just a DB as a service, a real no code tool would have ideally all the basic building blocks: a. DB as a service (like Airtable) b. BPM capability -> to add workflows to the data c. Ui Building to abstract data d. API integration (may not advanced learning for power users)
- collyw 7y agoExcel is the closest we have come to no code tools and its been around for years. Ms Access actually has a fairly decent way to build apps without really touching code, but it requires understanding relational databases enough to use it properly. Most (non technical) people end up using excel as a database instead.
- gmueckl 7y agoI haven't looked at Access lately, but when I was forced to use it extensively, there were limits tonhownfar you could push input validation without writing VBA. So I suspect that most Access databases have severe issues with data consistency. But at least the data is structured.
- akavi 7y agoAirtable has blocks for c) and exposes an API for d) Not sure what you specifically want for b)
- jhbadger 7y agoMy main issue with them is that they often aren't reproducible as the data transformations aren't logged. As a computational biologist I often deal with spreadsheets that experimentalists give me. I have no idea how the data were transformed from the raw data, and often the experimentalists themselves can't remember either. That's why I infinitely prefer real programming languages for use in data manipulation -- you can look at the code.
- froindt 7y agoPowerQuery is integrated into excel, gives a visual editing platform (not purely code), and is quite powerful in terms of linking up to any datasource, manipulating data, and giving you the output you need. All the steps are listed on the right side, and you can click back to any previous step in the transformation. It's not super popular, but it does serve as a nice middle ground for people in the space who aren't strong in coding.
- mamcx 7y agoMy endgame is build a Excel+Access alike dev tool. I'm working in a relational language as the foundation (http://tablam.org http://tablam.org) because Excel have another problem: The excel UI paradigm (reactive) is not the VBA macros one (almost OO). So make new functions not follow the way of excel. I think too a spreadsheet is an interface for a database/source and must not have issues handling gigabytes of data. So I think internally it must a sqlite db for example and put on top the control. Making it virtual and reactive and is done.
- Grustaf 7y ago> Half of the mess that makes excel hell comes from the fact it's too easy to put two tables of data + some random constants on a single sheet and refer to them by H3 This is certainly true, but that is also where almost all the power comes from, its generic nature. I had a startup that tried to reinvent Excel for 6 months and we kept moving closer and closer to the excel "sack of undifferentiated data" paradigm...
- jon-wood 7y agoIt had its problems, but Apple's Numbers product got this right I feel - it was a kind of blend between desktop publishing and spreadsheets, where you could scatter arbitrary tables of data around a page and then have them refer to each other. I suspect a lot of people who are throwing multiple tables of data in the same sheet are doing so because they want both of them to be visible at once.
- DonHopkins 7y agoClippy was a "Microsoft Agent" ActiveX component, that (or rather, who) you could script! https://en.wikipedia.org/wiki/Microsoft_Agent https://en.wikipedia.org/wiki/Microsoft_Agent https://docs.microsoft.com/en-us/windows/win32/lwef/programming-microsoft-agent https://docs.microsoft.com/en-us/windows/win32/lwef/programm... Clippy, Genie, Plany, Peedy the Parrot, Merlin the Wizard, Milton the Bear, Oscar the Cat, Max the Search Doggie, and all of their other happy friends were cheerfully retired in 2009, and are now merrily frolicking on a nice farm in the country side with a very loving family. Or so we are told. http://bellcraft.com/mash/chars.aspx http://bellcraft.com/mash/chars.aspx