New Text and Array Functions for Excel

> I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell.

I had a go at writing a DLL plugin for Excel that did this years ago. I ended up with a kind of SQL, where each cell has a result set of records. The purpose was to make a functional language for consultants starting with a familiar environment to them. I even integrated a system where you clicked the cell and a pop up would show the data records. It was an ugly proof-of-concept, using strings that just identified each result set, and using custom functions. Excel is beautifully functional, with some nice parallels with SQL, and your data flow/dependencies are naturally visible. Excel is far less scary to most consultants than imperative programming is. I wanted to be able to model the data flows, use sheets for consultants to define custom pure functions for our system, and the final outcome was a reactive data system where data updates could flow (push) into outputs. I failed to get it delivered because I failed to get the COM interfaces working working: I failed to tie together Excel automation as a library engine (Excel COM API), Excel custom functions (plug in DLL), Delphi 7, and my own code.

Agreed, but I can’t control half the sheets I interact with.

Users really like to highlight rows, or use coloring to track their progress, or do some insane multi-color-mixed-with-other-formatting system to indicate complex statuses.

I’d like to be able to work with that terrible data.

I think it would be nice if they added a feature where you could visually tell what cell you have highlighted. Bigger screens nowadays, I always have to look in the upper left to see what cell I am in, and then find the row and the column on the left and then trace across and down and voila, there is the highlighted cell.

Making it a substantially different color outline or something would be a nice feature. Maybe a 2032 feature.

I somewhat agree, but I've bent Excel to do things that aren't "excel thinking" for literally decades.

Reshaping is something could have used many times in the past. I used to pull data into PERL first to reshape it but that took a bit of code, then I learned about numpy doing it in one line. But I still have to import back into Excel. The fact that it is in there now is useful to me.

I 1000% agree with your matlab retval suggestion however. I hate how Excel fudges array return values by just blasting a range of cells one time!

My excel knowledge has been somewhat stagnant in the past decade. Have they added an ARRAYFUNC() like in Google Sheets, or do I still need to hit "ctrl-shift-enter" to designate one?

Excel makes me feel like such a novice. I often just write a python script instead out of frustration. I don't think little of people that write complex formulas and macros with it. I am still waiting for "python in excel".