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.
Update: okay check out 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)
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.
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.
But the fact is that it was missing from macos Excel for a while and they wanted people to migrate to VSTO: https://searchwindevelopment.techtarget.com/tip/On-migrating...
I wish someone (MS, ...) would do something, but I work on Linux so no Excel for me. And I do like very large datasets to be in the cloud anyway and not killing my laptop.
There should be more competitors in the space. I know there are a few, but they are not really competitors, just more niche / boutique products that attack a specific case, so you need to go back to Sheets or Excel anyway.
Didn't they add Javascript support in 2018?
I agree though. They brought up that they were considering Python3 integration like 2 years ago and haven't said a word about it since.
https://pypi.org/project/pywin32/
http://timgolden.me.uk/pywin32-docs/html/com/win32com/HTML/d...
Intgrating COM into Python is one approach, but another approach is integrating Python into Active Scripting. (The age old extending/embedding debate.)
https://docs.python.org/3/extending/index.html
And Active Scripting (1996) let you plug different "in process" interpreters into the web browser and other multi-lingually scriptable applications, and call back and forth between (many but not all) ActiveX components and OLE automation interfaces more directly, without using slow "out of process" remote procedure calls. (Some components still require running in separate process, like Word, Excel, etc, which work, but are just slower to call).
https://en.wikipedia.org/wiki/Active_Scripting
https://ipfs.io/ipfs/QmXoypizjW3WknFiJnKLwHCnL72vedxjQkDDP1m...
>The Microsoft Windows Script Host (WSH) (formerly named Windows Scripting Host) is an automation technology for Microsoft Windows operating systems that provides scripting abilities comparable to batch files, but with a wider range of supported features.
>It is language-independent in that it can make use of different Active Scripting language engines. By default, it interprets and runs plain-text JScript (.JS and .JSE files) and VBScript (.VBS and .VBE files).
>Users can install different scripting engines to enable them to script in other languages, for instance PerlScript. The language independent filename extension WSF can also be used. The advantage of the Windows Script File (.WSF) is that it allows the user to use a combination of scripting languages within a single file.
>WSH engines include various implementations for the Rexx, BASIC, Perl, Ruby, Tcl, PHP, JavaScript, Delphi, Python, XSLT, and other languages.
>Windows Script Host is distributed and installed by default on Windows 98 and later versions of Windows. It is also installed if Internet Explorer 5 (or a later version) is installed. Beginning with Windows 2000, the Windows Script Host became available for use with user login scripts.
https://developer.microsoft.com/en-us/office/blogs/office-ex...
> 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.
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.
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.
How I wish there existed software designed specifically for that organic discovery, instead of having one which was grown from a tool for accountants.
Spreadsheets is the best end-user development model we know, but it is limited by not having a concept of instantiating new objects.
That's true, but it doesn't necessitate different tables living in the same row/column grid.
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.
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...
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.
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.
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.
Yep, the problem is that I have this conspiracy theory that Microsoft has designed Excel to be the ultimate booster of Dunning-Krueger effect so that most people think they are advanced users [1] while they actually have no clue what they are doing. All the while giving no protection whatsoever against those Dunning-Krueger cases.
[1] I am very much afraid I belong to this group.
2. It's a default formula that gets repeated, but you may be able to override.
> 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.
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)
https://en.wikipedia.org/wiki/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.
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.
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?
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.
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)
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.
Not sure what you specifically want for b)
I'm not sure you fully appreciate why Excel is so successful.
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.
It's all possible though, if you know where to look (I think excel even has table sheets now?)
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.
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?"