We are excited to announce the release of Formula Suggestions and Formula by Example for Excel web users – a couple exciting capabilities designed to help save you time and learn more about Excel formulas as you use them. Also for web users are suggested links, IMAGE function, and a new search bar in the queries pane. For Windows users, a new keyboard shortcut is available to open the Power Query editor, and Insiders users on Windows can now get data from dynamic arrays and create nested Power Query data types to better organize your data.
There’s this whole massive community of wizards out there, and their school of magic is Excel. It baffles me what people can do with this program, yet it’s often ridiculed and ignored despite the sheer skill needed to get the most out of it.
“and their school of magic is Excel”
And a lot of the time they should really be using a database instead of Excel.
I see your point, but sometimes notepad is the easier option than word.
And we’d have a hard time to find many office workers who believe Access (or any other database application) is more intuitive than Excel.
Excel have the advantage to visually be able to address not only one worksheet but a whole workbook and even outside Excel files, linking them together into one giant pool of programmable array.
By itself, Excel is an impressive programming language (not just the VBA macros) proven with that you can do this kind of junk with it :
https://www.gamedeveloper.com/design/3d-engine-entirely-made-of-ms-excel-formulae-enjoy-this-doom-xls-file-
https://www.gamedeveloper.com/design/microsoft-excel-revolutionary-3d-game-engine-
https://hackaday.com/2019/08/29/experiments-in-3d-graphics-via-excel/
Haha, yeah anything able to evaluate arbitrary user formulas can be abused to do great things 🙂
Clearly though this is a case of “lets do this in excel to show off what we can do in excel” rather than excel being a good choice of tools. The authors had to resort to VBA to add in interactivity. It makes me wonder how VBA and excel formulas perform relative to one another.
I used VBA in excel extensively at one of my first jobs.
A surprising amount of companies for better or worse have critical components that use excel, especially for reporting. It frustrates me when I have to figure out what some person did to fix them, but you can’t argue it doesn’t clearly fulfill a niche that access and other DBs do not. There are also security issues with excel but it’s certainly cost effective.
Also I have had to fix or replace some immense custom Access disasters. They are both tools that work for certain application when actually used correctly.
You can do pretty impressive dashboards quite easily.
https://support.microsoft.com/en-gb/office/create-and-share-a-dashboard-with-excel-and-microsoft-groups-ad92a34d-38d0-4fdd-b8b1-58379aae746e
https://www.thesmallman.com/blog/2020/9/22/excel-dashboard-designs
https://adniasolutions.com/excel-templates/excel-dashboard-layout-duo-theme-1/
Kochise,
Interesting! I’ve never used that. Is it kind of like OLE? It does seem like there is a need for dashboards like this, although I’m not sure excel would be my first choice. I think a purpose built tool might do a better job at providing portable dashboards than excel can. I don’t mind that such a tool supports excel, but ideally it would work with other technologies too.
Agreed. I work in automation and you can do some great dashboards, reporting and trending with excel and tools like this https://www.sytech.com/product-xlreporter-overview.asp. The cost difference between this and a historian server is astronomical if you just need to handle a PLC or two. There are still cases were a Historian makes more sense of course and it can tie into that as well.
kepta,
I really like excel, or at least I did when I was a user of it. It was my favorite MS software and I did so much with it. However I agree with franko’s point at the top. I’ve seen some extraordinarily complicated spreadsheets, and though technically impressive, sometimes it becomes evident that they’re using excel because they have it and they can. But it’s often not the best tool or right tool for the job. It’s just ubiquitous and more accessible to normal users than other more appropriate tools are.
At the government office where my wife works, they use a lot of excel but the reason is actually a bit funny. My wife acknowledges things would be better in databases, but getting IT to do anything requires approval involving management bureaucracy, even for something as trivial as adding a new summation or new field. The main reason excel became the tool of choice for them wasn’t because it’s the best tool for the job (from the sounds of it there are actually a lot of cons), but because it’s not under the scope of interdepartmental politics.
I can see that actually. Many complicated excel tools I’ve seen are because departments are not able to purchase better off the shelf tools. Honestly it would actually end up costing the company less with all the lost labor for the staff to figure things out then fix their tools when they inevitably break but penny wide pound foolish.