Could this be the future for Excel’s calculation engine?
Just an outrageous idea I’m mooting…
Ever since Excel burst onto the scene, it’s been based on a worksheet grid. Each cell in that grid accepts either a formula or constant value.
The formula bar is primarily used to input and view formulas (duh!). However, it also does the same thing for constant values.
A cell can only have one or the other, but not both — at least not yet.
The present
10 names are listed in A1:A10. To sort these in ascending order, a formula can be constructed elsewhere that references the range. In this case, C1 contains =SORT(A1:A10).
When the data is housed in a table, the data can also be reordered using the Sort A to Z or Sort Z to A button in the column’s header dropdown. This example does not have one, however.
Moreover, unlike filtering, which is toggleable and consists of blue row numbers to indicate the presence of invisible rows, sorted data can only be reverted to its original state by pressing Ctrl + Z immediately afterwards. The Clear button in the Data tab solely affects filtering.
The problem
Ever found those pesky circular references an annoyance? You’re not alone.
A circular reference occurs when a formula contains a reference pointing to the same cell it’s housed in — either directly or indirectly.
In the majority of cases, circular references are unwanted. However, they may be necessary in certain financial, engineering, or scientific models that require iterative calculations.
If =SORT(A1:A10) is used in A1 instead, not only does it override the existing value Xavier, but a popup appears, alerting you to the circularity. Clicking OK permits the formula, although the result is a crossed-out 0, indicating a stale value.
The future?
As you saw, the current solution to sorting raw data using a formula is having a separate range. This may be desirable in many cases, but not all the time.
Instead, imagine if Excel had a value bar that sat below the formula bar, making each cell capable of storing an input and output simultaneously. This would eliminate circular references and let us perform calculations directly on data.
There’d inevitably have to be some sort of indicator to distinguish between raw values and calculated ones, as it could get a bit confusing seeing the formula =SORT(A1:A10) and value Amy in the first cell of a range that’s already been sorted.
Final words
Sorting aside, this concept could be used for all kinds of other text, number, date, and logical transformations.
Power Query allows us to perform all these, but a query cannot override the original table when loaded to a worksheet.
There’s also VBA, but who wants to use that?
Regardless, obviously, I’m looking at it all from a high level. Under the bonnet, I’m sure much complexity would be involved to implement my idea, but hopefully, it’s given you some food for thought anyway.
What do you think?
