Patent Publication Number: US-2023153518-A1

Title: Methods and systems for sorting spreadsheet cells with formulas

Description:
CROSS REFERENCE 
     This application claims the benefit of and priority to U.S. Provisional Application No. 63/280,590 titled “Methods and Systems for Sorting Spreadsheet Cells with Formulas,” filed 17 Nov. 2021 (Atty. Docket No. ADAP 1011-1.) 
    
    
     RELATED APPLICATIONS 
     This application is related to and incorporates by reference the following applications: 
     U.S. application Ser. No. 16/031,339, titled “Methods and Systems for Providing Selective Multi-Way Replication and Atomization of Cell Blocks and Other Elements in Spreadsheets and Presentations”, filed 10 Jul. 2018 (Atty. Docket No. ADAP 1000-2), now U.S. Pat. No. 11,182,548, issued 23 Nov. 2021, which claims the benefit of U.S. Provisional Application No. 62/530,835, filed Jul. 10, 2017 (Atty. Docket No. ADAP 1000-1), 
     U.S. application Ser. No. 16/031,379, titled “Methods and Systems for Connecting a Spreadsheet to External Data Sources with Formulaic Specification of Data Retrieval”, filed 10 Jul. 2018 (Atty. Docket No. ADAP 1001-2), which claims the benefit of U.S. Provisional Application No. 62/530,786, filed Jul. 10, 2017 (Atty. Docket No. ADAP 1001-1), 
     U.S. application Ser. No. 16/031,759, titled, “Methods and Systems for Connecting A Spreadsheet to External Data Sources with Temporal Replication of Cell Blocks”, filed 10 Jul. 2018 (Atty. Docket No. ADAP 1002-2), which claims the benefit of U.S. Provisional Patent Application No. 62/530,794, filed on Jul. 10, 2017 (Atty. Docket No. ADAP 1002-1), and 
     U.S. application Ser. No. 16/191,402, titled, “Methods and Systems for Connecting A Spreadsheet to External Data Sources with Ordered Formulaic Specification of Data Retrieved” filed Nov. 14, 2018 (Atty. Docket No. ADAP 1003-2), which claims the benefit of U.S. Provisional Patent Application No. 62/586,719,” filed on Nov. 15, 2017 (Attorney Docket ADAP 1003-1). 
     U.S. application Ser. No. 17/359,430 titled “Methods and Systems for Constructing A Complex Formula in a Spreadsheet Cell”, filed 25 Jun. 2021, which claims priority to and the and U.S. Application 63/044,989, titled “Methods and Systems for Presenting Drop-Down, Pop-Up or Other Presentation of a Multi-Value Data Set in a Spreadsheet Cell”, filed 26 Jun. 2020 (Atty. Docket No. ADAP 1005-1.) 
     U.S. application Ser. No. 17/359,418, titled, “Methods and Systems for Connecting A Spreadsheet to External Data Sources with Ordered Formulaic Specification of Data Retrieved” filed 25 Jun. 2021 (Atty. Docket No. ADAP 1005-2), which claims priority to and the benefit of U.S. Application 63/044,989, filed 26 Jun. 2020 (Atty. Docket No. ADAP 1005-1) and U.S. Application 63/044,990, titled “Methods And Systems for Constructing A Complex Formula in a Spreadsheet Cell”, filed 26 Jun. 2020 (Atty. Docket No. ADAP 1004-1.) The priority applications are incorporated by reference herein.). 
     BACKGROUND 
     Current spreadsheets are excellent in sorting blocks of rows of values (e.g., 3, 5, apple, pear, 1/1/22 or 1/2/22) through a single (simple key) or multiple column (composite key) sorting capability. However, if a user sorts cells containing formulas referencing other cells (e.g., =A 5 −A 4 +C 3  or =SUM(F 6 :H 9 ), where the relative position of the cells changes after the sort (pretty much a given since changing relative positions is the purpose of sort) then the formula is corrupted and recalculates incorrectly. This includes sorted cells that reference a cell outside the sort area or reference a cell not in its sort row. It also includes cells outside the sort area referencing cells within the sort area. In those settings users must convert the formulas into values to retain the correct values after the sort, losing the formulas and their ability to propagate changes. Thus, losing any future change flexibility. Therein, lies an opportunity to allow users to do spreadsheet cell area sorts which do not corrupt the formulas and continue to deliver the pre-sort calculated values post the sort. 
     Our technology makes doing one or many sorts on cells containing cell referencing formulas as simple as doing sorts on values all while retaining the original formula values and automatically adjusting the formulas to avoid value corruption. 
     SUMMARY 
     The technology disclosed relates to avoiding formula corruption during sorting of blocks of rows containing formulas referencing cells not within their row of the sort block. In one embodiment our technology updates each of the referenced cells post-sort to retain their presort relationships and therefore their pre-sort calculated value. In another embodiment of our technology, where the formulas include range cell references, our technology effectively atomizes those ranges to the individual cells pre-sort and then retains their pre-sort relationships post-sort. A further embodiment of our technology then effectively collapses the individually referenced post-sort cells into one or more ranges. Our technology avoids formula corruption of cells within the sort area and cells outside the sort area referencing cells within the sort area. It works for single (simple key) or multiple column (composite key) sorts and continues to work for successive different sorts. It works for formulas using cell data, our Non-spreadsheet cell (NSC external data) formulaic data, our formulaic data from cell data and combinations of any or all of those. 
     Particular aspects of the technology disclosed are described in the claims, specification and drawings. 
    
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
       The patent or application file contains at least one drawing executed in color. Copies of this patent or patent application publication with color drawing(s) will be provided by the Office upon request and payment of the necessary fee. 
       The included drawings are for illustrative purposes and serve only to provide examples of possible structures and process operations for one or more implementations of this disclosure. These drawings in no way limit any changes in form and detail that may be made by one skilled in the art without departing from the spirit and scope of this disclosure. A more complete understanding of the subject matter may be derived by referring to the detailed description and claims when considered in conjunction with the following figures, wherein like reference numbers refer to similar elements throughout the figures. 
         FIGS.  1 A,  1 B,  2 A and  2 B  examples the formula sort corruption in Microsoft Excel. 
         FIGS.  3 A and  3 B  example the same formulas and sort in Google Sheets with the same corrupted output (as Microsoft Excel). 
         FIGS.  4 A and  4 B  example for our technology uncorrupted results for the same formulas and sort as  FIG.  1 A  through  FIG.  3 B  exampled for Microsoft Excel and Google Sheets. 
         FIGS.  5 A,  5 B,  5 C and  5 D  examples the formula sort corruption in Microsoft Excel for formulas containing cell ranges. 
         FIGS.  6 A and  6 B  example for our technology uncorrupted results for the same formulas and sorts as  FIG.  5 A  through  FIG.  5 D  exampled for Microsoft Excel. 
         FIGS.  7 A,  7 B,  8 A,  8 B,  9 A and  9 B  examples steps of the range formula atomization, sorting and recombining of our technology. 
         FIGS.  10 A,  10 B,  11 A,  11 B and  11 C  examples how our technology continues to work for additional sorts. 
         FIGS.  12 A and  12 B  examples a color coded two column sort example in Microsoft Excel showing its corrupted result. 
         FIGS.  13 A,  13 B,  14 A,  14 B,  15 A,  15 B,  16 A,  16 B,  17 A and  17 B  examples the same color coded two column sort example (as in  FIG.  12 A  and  FIG.  12 B ) in our technology giving an uncorrupted result. 
         FIGS.  18 A and  18 B  example our sorting technology working for our formulaic data using its data retrievers, our formulaic data using cell references, and non-formulaic data formulas, with the formulaic data from three different NSC and/or in-cell data sets. 
         FIGS.  19 A and  19 B  example our sorting technology working for our formulaic data using cell references outside of the sort area and non-formulaic data formulas. 
         FIGS.  20 A and  20 B  example our technology successfully sorting and recalculating cells combining formulaic data in function formulas using cell references outside of the sort area and non-formulaic data formulas. 
         FIGS.  21 A and  21 B  example our sorting technology successfully sort and recalculating cells combining formulaic data in function formulas using cell references inside and outside of the sort area and non-formulaic data formulas. 
         FIGS.  22 A and  22 B  examples an inward referencing formula with a single cell reference automatically updating via our technology for the post-sort change in the pre-sort cell location. 
         FIGS.  23 A  and.  23 B examples inward referencing cell ranges with all their cells within the sortation area and with part of the cell range within the sortation area. 
         FIGS.  24 A and  24 B  examples outward referencing formulas referencing cells outside the sort area working by themselves. 
         FIG.  25    depicts an example computer system that can be used to implement aspects of the technology disclosed. 
     
    
    
     DETAILED DESCRIPTION 
     The following detailed description is made with reference to the figures. Example implementations are described to illustrate the technology disclosed, not to limit its scope, which is defined by the claims. Those of ordinary skill in the art will recognize a variety of equivalent variations on the description that follows. 
     When spreadsheet applications were first created, they electronically emulated tabular paper spreadsheets. More recently, Microsoft Excel, Google Sheets, Apple Numbers and others have dramatically increased the breadth of capabilities and usefulness of spreadsheets. Spreadsheet applications now access data across a wide variety of sources including relational, structured and semi-structured, open data protocol (OData), Web and Hadoop among others; and these applications manipulate data—such as in pivot tables and via Microsoft PowerPivot. Additionally, spreadsheets have extensive functionality for creating charts with SmartArt and for building forms, and they even have programming languages embedded within them, such as Visual Basic (VBA in Excel), Apps Script (in Google Sheets) and Apple Script (in Numbers). 
     With all the added capabilities, spreadsheet applications have become substantially more complicated. The data manipulation and embedded programming language capabilities can be very powerful but are complicated to learn and therefore they are used by a very small fraction of the spreadsheet application user base. All this complexity has led to over a hundred books and thousands of online videos that have been published to help users understand the capabilities of Excel alone. 
     Spreadsheet providers like Microsoft Excel and Google Sheets cater to the specialized needs of users through many capabilities including the data sorting capability. That capability, typically accessed from a menu SORT selection or Ribbon button, allows users to select a block of rows and columns and sort the rows by one or more user selected column value sort order/direction (e.g., lowest to highest or A to Z). Those sort orders work in an understandable manner for cells with values (e.g., numbers, text or dates) but when those cells contain formula calculated values referencing other cells, the cells sort and then those formulas recalculate post the sort often with unpredictable (corrupted) results. This is because the current spreadsheets do not correctly post-sort reorder the formula cell references to account for the changes due to the sort, corrupting the post-sort recalculation of the values and in many situations resulting in formulas with calculation errors. Our technology overcomes those formula recalculation problems and does it for formulas using cell data, our Non-spreadsheet cell (NSC external data) formulaic data, our formulaic data from cell data and combinations of any or all of those. 
     The formulaically defined Non-Spreadsheet Cell (NSC) data variables and related technologies disclosed in “Methods and Systems for Connecting a Spreadsheet to External Data Sources with Formulaic Specification of Data Retrieval” filed previously, allow users to work with all types of numeric and text external data sets (including dates and Booleans) much larger and more complex than can currently fit in traditional spreadsheets. This external data connection creates the foundation for users to automate spreadsheet work without the use of embedded programming languages or special prebuilt data feeds, taking spreadsheets from a tool users employ to conduct one off or routine analytics to a real-time competitor of systems that automate repetitive activities. These formulaic defined data variables can also use in-cell data, as described in our previous filings. 
     The disclosed technology allows users to sort cells with formulas referencing cells and retain the presort calculated values in the post sort cells by our technology automatically adjusting the formulas for the sort. Our technology allows users to run successive sorts retaining the original values through the further automatic adjustment by our technology of the formula cell references. 
     Limitations of Existing Technologies 
     Both of the leading spreadsheets, Microsoft Excel and Google Sheets, give similar results for the sortation of cells with cell referencing formulas. Apple Numbers and LibreOffice Calc also deliver similar results employing the same relational transformation of the formulas. Zoho Sheet does a slightly different formula transformation that typically results in the generation of fewer cells with error messages but a comparable level of incorrect recalculated (corrupted) values. 
     Given Microsoft Excel is the most used spreadsheet in the world, we first example it used by a cancer researcher resorting test values. The cancer researcher has seven weeks of test results and wants to sort them so they can see which weeks had the greatest reduction in the test cancer mass. So, in  FIG.  1 A  the user selects the area in cells ‘A 4 ’ to ‘D 10 ’  132  and triggers the Sort capability getting the popup  164 . In that popup they select to do the sort by ‘Column D’ ‘Smallest to Largest’  154  and click the OK button  175  to get the sort result  182  in  FIG.  1 B . In the post-sort result  182  the columns A and B, which are values, have been resorted without the values changing. However, in columns C and D, which are formulas  184  ( 134  before sort) referencing cells, all of the values  182  have been corrupted and do not match any of the pre-sort values  132 , and many have ‘#REF!’ errors. This is because the formulas retain their pre-sort relative reference relationships (as shown in  184 ) rather than adjusting those references for the impact of the sort. From a simple user perspective all the formula values are corrupted and useless because none match their pre-sort values. 
       FIG.  2 A  and  FIG.  2 B  show the presort and post sort spreadsheet area larger to better show why the post-sort formulas referencing cells do not deliver the presort results. Looking at the row for ‘Week’ ‘ 3 ’  233  which moves from row ‘ 6 ’ to row ‘ 5 ’  273  shows how the formula shifts: 
       ‘= B 6− B 2’ 236 pre-sort
 
       ‘= B 5− B 1’ 276 post-sort
 
     Instead of changing those cell references to location of the pre-sort cells post-sort, the formula held the relative position relationships, in this situation moving the ‘B 2 ’ one row up to ‘B 1 ’ because the sort row moved one row up from row ‘ 6 ’  233  to row ‘ 5 ’  273 . That then generates the corrupted value of ‘1.945’  274  in ‘C 5 ’ rather than the correct value of ‘0.933’  234  in pre-sort ‘C 6 ’. The cell reference in cell ‘E 2 ’  225  from outside the sort block also is corrupted changing from the value ‘7’  225  pre-sort (referencing the value in cell ‘A 10 ’  251 ) to the value ‘2’  265  post-sort (still referencing the value in cell ‘A 10 ’  291 ). This is because the cell reference does not shift to the new location of the cell (‘A 7 ’  281 ) due to the sortation. Thus, exampling how formula value corruption happens both within and outside the sort block in conventional spreadsheets. 
       FIG.  3 A  and  FIG.  3 B  example the same formulas and sort in Google Sheets with the same corrupted output. It applies the same relative position relationship logic rather than our pre-sort to post-sort updating of the formula for the exact change in position of referenced cells which provides an uncorrupted recalculation. The user employs a similar popup sortation selection  362  triggering the sort in Sheets by clicking on the ‘Sort’ button  374 . The user gets a similarly corrupted outcome  385 . All of the other known spreadsheets corrupt formulas during sortation. None employ our sorting logic giving uncorrupted results for the sortation of formulas in one or many column sorts done once or done for a succession of different sorts. 
     Single Cell Reference Formulas 
       FIG.  4 A  and  FIG.  4 B  example for our technology using the same formulas and sorts as  FIG.  1 A  through  FIG.  3 B  exampled for Microsoft Excel and Google Sheets. However, in our technology none of the formulas are corrupted so all the values shown in the pre-sort  432  can be found in post-sort  483 . The pre-sort formulas  435  have all been changed by determining the post-sort location of each pre-sort cell referenced and updating it in the post-sort formulas  485 , so that the post-sort recalculation of each formula provides its pre-sort value. This is exampled in the reordering of the cell ‘D 7 ’  433  pre-sort to cell ‘D 4 ’  473  post-sort with no corruption of its ‘−21.8%’ value. That is because the pre-sort formula  436  is updated in  476  for the change in location of each of the referenced cells with the ‘C 7 ’ becoming ‘C 4 ’ and the two ‘C 6 ’ references becoming ‘C 5 ’ thereby retaining the same calculation pre and post the sort. 
     A similar post-sort update of pre-sort cell references is done for cells outside the sort block which reference cells inside the sort block. This is exampled for the value in cell ‘E 2 ’ of ‘ 7 ’  414  which in the pre-sort formula ‘=A 10 ’ giving the value of cell ‘A 10 ’ as shown in  441 . In the post-sort that cell reference is now ‘=A 7 ’ for the formula in cell ‘E 2 ’ and its value stays ‘ 7 ’  474  the value in cell ‘A 7 ’  481 . That is because the formula now refers to the value in cell ‘A 7 ’  481 , which is the post-sort relocation of the pre-sort cell ‘A 10 ’  441  shown in  FIG.  4 A . Our technology also ensures that cells outside the sort block area  432  referenced in formulas within the cell block area (e.g., ‘B 2 ’ in the formulas in  435 ) retain that same cell reference and value after their cell location is shifted. In this example as the rows were sorted on column ‘D’‘Lowest to Highest’  454  in the cell range  483 , all the formulas in the sort area referencing ‘D’  435  continued to reference ‘B 2 ’ after the sort in the formulas in  485  in  FIG.  4 B . 
     Our technology sorts the same whether the values the user is sorting by (i.e., the column or columns ordering the sort) are data or formula values. In the sort done in  FIG.  4 A  and  FIG.  4 B  the sort is done on column ‘D’  484  which happens to be a have formulas referencing cells in other rows, some of which will then change post-sort. Those changes are handled by our technology and as we will example later would make no difference for any additional sorts done on these cells or the related cells. 
     The user sortation process (i.e., sort setup) by which our noncorrupted formula sort is done is conceptually using the same process (i.e., using similar menu, ribbon and/or popup selections) as in Microsoft Excel or Google Sheets examples but with a very different outcome. In this embodiment using a sorting popup  464  where the user in this example selects column ‘D’ to be sorted ‘Lowest to Highest’  454  by clicking the ‘Ok’ button  466  to get the sort order from ‘−21.8%’ to ‘7.6%’ shown in  484 . The user did nothing different process-wise (versus a regular spreadsheet but gets a noncorrupted outcome for cells with formulas referencing cells within the sort block and referencing cells outside the sort block. 
     Cell Range Formulas 
     The sorting of formulas referencing ranges face corruption problems within the existing spreadsheets.  FIG.  5 A  through  FIG.  5 D  example that problem in Microsoft Excel. In this example the user has sorted the block of cells in ‘A 8 ’ to ‘E 13 ’  542  (blown up in  FIG.  5 C ) using the sort popup  563  to sort the range by ‘Column E’ clicking the ‘OK’ button  574  to get the post-sort block  592  (blown up in  FIG.  5 D ). You can see that none of the pre-sort values in columns ‘A’ and ‘B’  556  have been corrupted in the post-sort  576 , however all of the formula values which changed rows post-sort  578  have been corrupted relative to their pre-sort values  558 . None of the sorted range function formulas shown in column ‘C’  594  and in column ‘E’  595  that shifted rows  578  (rows  9  through  13 ) adjusted their range formulas post sort shifting of the cells in their range. Rather than going in depth on why the conventional spreadsheets get the corrupted values, we instead will example how our technology retains the correct recalculated values by atomizing the ranges, shifting them for the impact of the sorts, and then as desired for formula simplicity recombining the post-sort atomized cell references back into ranges where possible. 
       FIG.  6 A  and  FIG.  6 B  example for our technology the same formulas and sorts as  FIG.  5 A  through  FIG.  5 D  exampled for Microsoft Excel. As you can see none of the post-sort cell values  656 ,  657 ,  658  and  659  are corrupted relative to their pre-sort values  652  despite the cells in columns ‘C’  657 , ‘D’  658 , and ‘E’  659  containing cell referencing formulas as shown in  653 ,  654 ,  655  and  665 . Column ‘C’  657  examples an uncorrupted sort of formulas referencing cell ranges, ‘B 3 :D 3 ’ in  653 , outside the sort block area  652 . Column ‘E’  659  examples uncorrupted sort of formulas referencing cell ranges entirely or partially within the sort area (block). For example, ‘=D 13 /AVERAGE(D 10 :D 12 )’ in  665 , references a cell range ‘D 10 :D 12 ’ entirely within the sort block area  652  while ‘=D 10 /AVERAGE(D 7 :D 9 )’ in  655 , references a cell range ‘D 7 :D 9 ’ partially in and partially out of the sort block area  652  with no value corruption. Column ‘D’  658  examples formulas  654  with individual cells within and outside of the sort block area  652  showing that areas employing values, individual cell references and range cell references are successfully sorted together by our technology. As previously mentioned our technology makes doing a sort, for example in popup  683  clicking ‘Ok’  693  as simple as conventional spreadsheets but without any corruption of formula generated values. And the same is true for the simpler UI direct from the more standard menu-based sorts. 
     Our technology also handles situations where the sort (key) results in more than one sort column row cells having the same value (i.e., ties). In those situations where a single key or multi-key sort results in tied “values” our technology can still identify the post-sort location for each pre-sort cell in the sort area. That can be done by our technology many different ways such as having implicit additional sorts determining the locations, having hidden indexes determining the locations or other approaches. These result in our technology being able to determine the location of each cell in the sort area pre-sort and post-sort and therefore correct formulas to retain integrity of the repositioned cell references in all sort situations (e.g., including ties). 
       FIG.  7 A  through  FIG.  9 B  illustratively examples steps of the range formula atomization, sorting and recombining done by our technology recognizing that our application may perform the operations differently to achieve these and similar outcomes. For example, in some settings where the range will not change during the sort our app can forgo the atomization and recombination steps. In other embodiments our technology may not do the recombination or use different recombination approaches (e.g., horizontally first, vertically first or algorithms giving the least number of ranges and values). 
       FIG.  7 A  and  FIG.  7 B  example for our technology atomizing the cell reference ranges in the sort block area  763 . The formula shown in cell ‘G 8 ’ (for cell ‘C 8 ’) atomizes ‘B 3 :D 3 ’  746  to ‘B 3 ,C 3 ,D 3 ’  776  (in  FIG.  7 B ) which is a range of cells  723  entirely outside of the sort block area  763 . The formula shown in cell ‘I 10 ’  758  (for cell ‘E 10 ’) atomizes ‘D 7 :D 9 ’ to ‘D 7 ,D 8 ,D 9 ’ in cell ‘I 10 ’  788  (in  FIG.  7 B ). This atomized a range of cells  744  partially inside and partially outside of the sort block area  763  (shown in  FIG.  7 A ). And the formula shown in cell ‘ 13 ’  768  (for cell ‘E 13 ’) atomizes ‘D 10 :D 12 ’  768  to ‘D 10 ,D 11 ,D 12 ’  798  (in  FIG.  7 B ), which is a range of cells  756  entirely inside of the sort block area  763  (shown in  FIG.  7 A ). 
       FIG.  8 A  and  FIG.  8 B  example our technology sorting the cells ‘Highest to Lowest’ for column ‘E’  844  as the user selected in popup  683  (in  FIG.  6 A ) and retaining the post-sortation uncorrupted values  884  due to our technology adjusting the cell reference values for the sort. Our technology works for cells entirely outside ( 836  to  886 ) the sort block area, cells partially in and partially out ( 848  to  888 ) of the sort block area, and cells entirely in ( 858  to  878 ) the sort block area. 
       FIG.  9 A  and  FIG.  9 B  example one embodiment of our technology recombining the atomized ranges within the formulas. Recombining any set of two or more adjacent cells into a range as exampled for all the ‘SUM(B 3 ,C 3 ,D 3 )’ formulas in  956  transforming to ‘SUM(B 3 :D 3 )’ formulas in  986 . As also exampled in the ‘AVERAGE(D 8 ,D 11 ,D 12 )’ formula in  968  transforming to the ‘AVERAGE(D 8 ,D 11 :D 12 )’ formula in  998 . In different embodiments our technology uses different recombination approaches ranging from no recombination to algorithmic approaches such as combining vertically first, horizontally first, or trying different combinations to find the least number of references in the ranges. 
       FIG.  10 A  through  FIG.  11 C  examples how our technology continues to work for additional sorts.  FIG.  10 A  takes the outcome from the sort done in  FIG.  6 A  and  FIG.  6 B  and does an additional sort on the same sort block  1053  ( 656 ,  657 ,  658 , and  659  in  FIG.  6 B ) to get an uncorrupted outcome  1058  (in  FIG.  10 B ). The user is now doing a sort on the values in column ‘D’  1054  as shown in the popup sort selector  1074  sorting ‘Lowest to Highest’. When they hit ‘Ok’  1085  they get the values in  1058  sorted by the values in column ‘D’  1059 . 
       FIG.  11 A  through  FIG.  11 C  then examples the outcomes of the two different sorts starting with the original values  1153  sorted by column ‘A’ values  1151  in  FIG.  11 A . Then in  FIG.  11 B  the sort block area  1173  is sorted by column ‘E’ values  1175 . And finally, in  FIG.  11 C  the sort block area  1183  is sorted by column ‘D’ values  1182 . Throughout these sorts all the values are uncorrupted as our technology correctly adjusts the formulas for the pre- to post-sort location differences. ‘Column C’  1162  examples outward referencing formulas shown in  1177  ‘Formula(s) in Column C’ referencing a range ‘B 3 :D 3 ’  1122  outside the sort area automatically adjusting its formulas for the sort movements so the values  1162  remain uncorrupted through the first sort in  FIG.  11 B  and the second sort in  FIG.  11 C . ‘Column D’  1163  examples outward referencing formulas shown in  1178  ‘Formula(s) in Column D’ referencing another row in the cell sort area (block) or outside the sort area automatically resorting its formulas for the sort movements so the values  1163  remain uncorrupted through the first sort in  FIG.  11 B  and the second sort in  FIG.  11 C . ‘Column E’  1164  examples outward referencing formulas shown in  1179  ‘Formula(s) in Column E’ referencing a range with other rows fully in the cell sort area (block) or partially outside the sort area (block) automatically resorting its formulas adjusting for the sort movements so the values  1164  remain uncorrupted through the first sort in  FIG.  11 B  and the second sort in  FIG.  11 C . The user could continue to do sorts and change the sort areas and the values will remain uncorrupted throughout their actions with embodiments of our technology. 
     Multi-Column Sorts 
     Our technology can handle as many columns of sorts (keys) as there are columns in the sort area.  FIG.  12 A  through  FIG.  17 B  examples a color coded two column sort example first done in Microsoft Excel and then done in our technology. It examples a cell outside the sort area (inward) with a formula referencing cells within the sort block area and a cell within the sort block area (outward) with two different range functions (SUM and AVERAGE), algebraic operators, and cell formula usage. It examples how our technology is compatible with the color referencing of cells done in traditional spreadsheets. We also color filled the cells to match the color coding of the formulas to make it much easier to see how our technology works with the more complicated sorting of the cells. 
       FIG.  12 A  and  FIG.  12 B  example the Microsoft Excel handing of this conceptual example which has two cells with formulas, ‘D 3 ’  1233  and ‘D 7 ’  1243 . The two formula cells are color filled violet with the other cells referenced in the formulas color filled blue, green, orange, and red. The user has triggered a two-column sort in popup  1273 , sorting first by the highlighted values below ‘Heading_ 1 ’ and then in the event of ties by the values in ‘Heading_ 2 ’ in the sort block area  1252 . As in our prior examples when the user clicks ‘OK’  1285  to trigger the sort, the results they get in values are corrupted. The value ‘ 10134 ’ in cell ‘D 3 ’  1237  in  FIG.  12 B  not equaling its original value of ‘10697’ in cell ‘D 3 ’  1233  in  FIG.  12 A , and ‘#DIV/ 0 !’ in cell ‘D 11 ’  1257  in  FIG.  12 B  not equaling its original value of ‘1834.2’ in cell ‘D 7 ’  1243  in  FIG.  12 A . In this example it is easy to see the Microsoft Excel technology has not adjusted the formulas  1238  and  1258  for the atomization (separation) of the blue, green and orange color block ranges referenced in the pre-sort formulas  1234  and  1244 . 
       FIG.  13 A  through  FIG.  17 B  examples how our technology handles the multiple (composite key) sort induced atomization of the cell ranges and shifting of referenced cells. It also examples how our technology color codes opened cell formulas to match the cell color highlighting (in our examples the color filling) as traditional spreadsheets (e.g., Microsoft Excel and Google Sheets) do. We have done that simultaneously for both formulas to reduce the number of figures realizing in a spreadsheet users only have one formula opened at a time and in our examples it is like both are open at once exhibiting the color coding of the formulas and the referenced cells. 
       FIG.  13 A  and  FIG.  13 B  example the before and after of sortation by the highlighted values below ‘Heading_ 1 ’ and then in the event of ties by the values in ‘Heading_ 2 ’ in the sort block area  1343 . The user sort  1374  done in popup  1384  has headers  1365 . Each of the formulas  1315  and  1335  for the cells ‘D 3 ’  1314  and ‘D 7 ’  1334  are color coded as if opened to match the referenced cells  1332 ,  1344 ,  1351  and  1363 . We have then colored the two cells ‘D 3 ’  1314  and ‘D 7 ’  1334  violet to make them easy to see.  FIG.  13 B  shows the result of the sort by ‘Heading_ 1 ’  1346  and ‘Heading_ 2 ’  1347  giving uncorrupted values in  1349  (outside the sort area) and  1359  (which has moved as a result of the sort from cell ‘D 7 ’  1334  in  FIG.  13 A  to cell ‘D 11 ’  1359  in  FIG.  13 B ) 
       FIG.  14 A  and  FIG.  14 B  illustratively example the atomization (separation) of the cell ranges in the formulas for ‘D 3 ’ ( 1424  before and  1465  after) and ‘D 7 ’ ( 1435  before and  1467  after).  FIG.  15 A  and  FIG.  15 B  example the sortation of the cells with the atomized cell ranges and the formula adjustments for post-sort cell positions. Cell ‘D 3 ’ ( 1523  before and  1563  after) does not change position because it is outside the sort area but its formulas ( 1526  before and  1566  after) change with the shifting (repositioning due to reordering) of the different cells referenced. Cell ‘D 7 ’ ( 1533  before) in  FIG.  15 A  moves to ‘D 11 ’( 1583  after) in  FIG.  15 B  and exhibits substantial change in its formulas ( 1538  before and  1588  after). Neither cell has corrupted its recalculated value due to the automatic shifting of formula cell references in our technology. 
       FIG.  16 A  and  FIG.  16 B  example an embodiment of our technology with a collapsing algorithm that only collapses three or more block contiguous cells as exampled by the collapsing of orange ‘C 7 :D 8 ’ in formula  1666  from the formula  1626  and the collapsing of green ‘B 9 :C 10 ’ in formula  1688  from the formula  1643 .  FIG.  17 A  and  FIG.  17 B  example an embodiment of our technology with a collapsing algorithm that collapses two or more block contiguous cells as exampled by the collapsing of orange ‘C 7 :D 8 ’ and ‘C 12 :D 12 ’ in formula  1766  from the formula  1726  and the collapsing of blue ‘A 5 :B 5 ’, blue ‘A 11 :B 11 ’, blue ‘A 8 :B 8 ’, green ‘B 9 :C 10 ’ and green ‘B 14 :C 14 ’, in formula  1788  from the formula  1743 . Our technology supports other algorithmic approaches to collapsing the post-sorted atomized values and determinations of situations where ranges can bypass the atomization and collapsing steps because they will not change in the sort. 
     In the example in  FIG.  13 A  through  FIG.  17 B  the sort was on cells with data values, but our technology works for single (simple key) or multi-sorts (composite key) on cells with formulas that are numeric, date (e.g., COUNT_DATE), Boolean (e.g., COUNT_TRUE) or text (e.g., COUNT_TEXT_UNIQUE) and on cell data that is numeric, date, Boolean or text. Our sort technology works on cells containing functions with cell references that can produce any type of cell value, for example an IF function formula. It also works on cells containing our formulaic data sourced from Non-Spreadsheet Cell (NSC) external data or formulaic data sourced from in-cell values, as we will example next. 
     Formulaic Data Sorts 
       FIG.  18 A  and  FIG.  18 B  example our sorting technology working for our formulaic data using its data retrievers, our formulaic data using cell references, and non-formulaic data in the formulas. This example uses formulaic data sourced from three different datasets, which could be three different external (NSC) tables, three in-cell datasets or some combination of externally (NSC) and in-cell sourced formulaic data. A North American charity volunteer has data from three different charities for the US, Canada and Mexico. They have constructed an analysis by Charity and Country but want to resort it from Highest to Lowest SUM of donations. To do so the user of our technology has highlighted the cells in ‘A 4 ’ to ‘D 12 ’ and initiated a sort with headers as shown in the popup  1854 . This use of headers then changes the sort highlight to the cells below the headers  1833 , as it would in a traditional spreadsheet. The user has selected a single column (simple key) sort from ‘Highest to Lowest’ for column of values under the header ‘Donations’ in the popup  1854 . When they click ‘Ok’  1865  our technology resorts the values in  1833  to those in  1883  based on the ‘Donations’ values with no corruption of the values. Our technology successfully sorts and recalculates the formulaic data from the three different datasets in column ‘B’ as shown by the ‘Formula in Column B’  1885  post-sort compared to the ‘Formula in Column B’  1835  pre-sort. It also successfully sorts and recalculates (with no value corruption) the formulaic data function formulas using in-row cell references in column ‘C’ as shown by the ‘Formula in Column C’  1887  post-sort compared to the ‘Formula in Column C’  1837  pre-sort. Our technology then successfully sorts and recalculates the non-formulaic data formula values in column ‘D’ as shown by the ‘Formula in Column D’  1888  post-sort compared to the ‘Formula in Column D’  1838  pre-sort. 
       FIG.  19 A  and  FIG.  19 B  example our sorting technology working for our formulaic data using cell references outside (outward) of the sort area and non-formulaic data formulas. It again uses formulaic data sourced from three different datasets, which could be three different external tables, three in-cell datasets or some combination of externally and in-cell sourced formulaic data. The North American charity volunteer redid the previous analysis for dates after ‘ 1 / 1 / 20 ’ ‘C 2 ’  1923  and again wants to sort it from Highest to Lowest SUM of donations. To do so the user of our technology has highlighted the cells in ‘A 4 ’ to ‘D 12 ’ and initiated a sort with headers as shown in the popup  1953 . This use of headers then changes the sort highlight to the cells  1932  below the headers, as it would in traditional spreadsheet. The user again has selected a single column sort from ‘Highest to Lowest’ for column of values under the header ‘Donations’ in the sort popup  1953 . When they click ‘Ok’  1975  our technology resorts the values in  1932  to those in  1982  in  FIG.  19 B  based on the ‘Donations’ values with no corruption of the values. Our technology successfully sorts and recalculates the formulaic data from the three different datasets in column ‘B’ constrained to the value date{&gt;C 2 }’ from a cell outside the block sort area as  520  shown by the ‘Formula in Column B’  1984  post-sort compared to the ‘Formula in Column B’  1934  pre-sort. It also successfully sorts and recalculates (with no value corruption) the formulaic data function formulas using in-row and outside (outward) the block cell sort area cell references in column ‘C’ as shown by the ‘Formula in Column C’  1987  post-sort compared to the ‘Formula in Column C’  1937  pre-sort. Our technology then successfully sorts and recalculates the non-formulaic data formula values in column ‘D’ as shown by the ‘Formula in Column D’  1989  post-sort compared to the ‘Formula in Column D’  1939  pre-sort. In column ‘D’ our technology successfully handles the sum of the ranges as the sort alters the locations of the cells in the range. 
       FIG.  20 A  and  FIG.  20 B  example our technology successfully sorting and recalculating cells combining formulaic data in function formulas using cell references outside of the sort area and non-formulaic data formulas. It again uses formulaic data sourced from three different datasets, which could be three different external tables, three in-cell datasets or some combination of externally and in-cell sourced formulaic data. The North American charity volunteer does this analysis for dates after ‘ 1 / 1 / 20 ’ ‘C 2 ’  2023  going straight to the calculation of the ‘% Donations Total’ without first calculating the total of the ‘Donations’. They want to sort the results from Highest to Lowest ‘% Donations Total’. To do so the user of our technology has highlighted the cells in ‘A 4 ’ to ‘C 12 ’ and initiated a sort with headers as shown in the popup  2053 . This use of headers then changes the sort highlight to the cells below the headers  2032 , as it would in traditional spreadsheet. The user has selected in the popup  2053  a single column sort from ‘Highest to Lowest’ for column of values under the header ‘% Donations Total’. When they click ‘Ok’  2065  our technology resorts the values in  2032  to those in  2082  in  FIG.  20 B  based on the ‘% Donations Total’ values with no corruption of the values. Our technology successfully sorts and recalculates the formulaic data from the three different datasets in column ‘B’ constrained to the value ‘date{&gt;C 2 }’ from a cell outside the block sort area as shown by the ‘Formula in Column B’  2084  in  FIG.  20 B  post-sort compared to the ‘Formula in Column B’  2034  pre-sort in  FIG.  20 A . It also successfully sorts and recalculates (with no value corruption) the formulaic data function formulas using in-row and outside the block cell sort area cell references in column ‘C’ as shown by the ‘Formula in Column C’  2087  in  FIG.  20 B  post-sort compared to the ‘Formula in Column C’  2037  pre-sort in  FIG.  20 A . These formulas contain formulaic data and non-formulaic data functions algebraically combined. All of which are then sorted with no corruption of values. 
       FIG.  21 A  and  FIG.  21 B  example our technology successfully sorting and recalculating cells combining formulaic data in function formulas using cell references inside and outside of the sort area and non-formulaic data formulas. The formulaic data formulas use cell references from different rows, outward of the sort row within the sort area and outward of the sort area. Our cancer researcher is redoing his weekly cancer test results this time using formulaic data. The formulaic data allows skipping having columns for the ‘Gross wt’ and ‘Net wt’ as shown in  FIG.  6 A  and  FIG.  6 B . Instead, the user immediately does the ‘% change’ calculation using the formulaic data and the ‘Dish wt’ values in cells ‘B 3 :D 3 ’  2112 . That ‘% change’ calculation then uses cell values in its row and in the row above as exampled in the formula in ‘B 12 ’  2156 : 
       ‘=(gross_wt{wk{A12}}−SUM(B3:D3))−(gross_wt{wk{A 11 }}−
 
       SUM(B 3 :D 3 )))/(gross_wt{ wk{A 11}}−SUM( B 3: D 3))’
 
     which uses ‘A 12 ’ that is in the same row and two instances of ‘A 11 ’ that is in the row above. It also uses three instances of ‘B 3 :D 3 ’ which is outside the sort area  2141 . When the user triggers the sort  2174  on Column ‘C’ in popup  2163  it gives the uncorrupted results  2171  FIG. in  21 B. As previously described the formulas adjust for the post-sort new positions as shown in  2186  which previously was the formula in  2156  in  FIG.  21 A . The regular cell formulas in those respective rows,  2188  and  2158  also resort and our technology adjusts their formulas to keep their post-sort recalculations non-corrupted. The user could then do additional sorts of this sort block or involve one or all of the cells in this sort block in other sorts all of which would keep their values uncorrupted. All of this happens without the user having to do anything different in how they trigger doing a sort. Users of our technology no longer need to worry about sorting information with formulas using cell references and getting corrupted values. 
     Inward by Itself 
     Our prior descriptions and examples employing inward referencing formulas were in examples also employing outward referencing formulas. However, each of those technologies can operate by themselves.  FIG.  22 A  through  FIG.  23 B  examples inward referencing formulas working by themselves without outward referencing formulas. 
       FIG.  22 A  and  FIG.  22 B  examples an inward referencing formula pre-sort  2214  automatically transformed post-sort  2274  automatically adjusted for the new referenced cell location so that inward referencing cell formula value is not altered (i.e., corrupted) by the recalculation after the sort. The value in cell ‘E 2 ’ of ‘ 7 ’  2214  which in the pre-sort formula ‘=A 10 ’ giving the value of cell ‘A 10 ’ of ‘ 7 ’ shown in  2241 . In the post-sort that cell reference is now ‘=A 7 ’ for the formula in cell ‘E 2 ’  2274  and its value stays ‘ 7 ’ the value in cell ‘A 7 ’  2281 . That is because the formula now refers to the value in cell ‘A 7 ’  2281 , which is the post-sort relocation of the pre-sort cell ‘A 10 ’  2241  shown in  FIG.  22 A . Those inwardly referencing formulas can be simple cell references, as in cell ‘E 7 ’ with its value of ‘ 7 ’ and its formula  2214  in  FIG.  22 A  or can be more complicated formulas as exampled in  FIG.  23 A   2327  and  2337  referencing multiple cells partially or completely in the sort area. 
     Inward by Itself Cell Ranges 
     Embodiments of our technology automatically deliver uncorrupted values for the inward referencing formulas, where the inward reference has at least one cell range containing at least one cell within the sortation area (block).  FIG.  23 A  and  FIG.  23 B  examples an inward referencing cell range with all their cells within the sortation area and an inward refencing formula with part of the cell range within the sortation area. In this example we return to the charity user who in  FIG.  23 A  wants to sort the donations in the block of cells ‘A 7 ’ through ‘C 10 ’  2342 . To make it easy to see what is going on there is a corresponding block of cells ‘E 7 ’ through ‘G 10 ’  2346  which show the formulas of the cell block ‘A 7 ’ through ‘C 10 ’  2342 . This shows that all the cells contain values with no formulas. However, cell ‘C 3 ’  2323  contains a formula  2327  referencing cells within the sortation area  2342  and outside it to total the donations for ‘NA’, while cell ‘C 4 ’  2333  contains a formula  2337  referencing cells only within the sortation area  2342  to total the donations for ‘Asia’. In this example the user then sets up a three key sort  2355  in the sort selection popup  2356  and clicks ‘OK’  2366  to trigger the sort. This delivers the sortation area  2392  in  FIG.  23 B  and automatically transforms the inward range formulas  2377  ( 2327  in  FIG.  23 A  pre-sort) and  2387  ( 2337  in  FIG.  23 A  pre-sort) for cells ‘C 3 ’  2373  ( 2323  in  FIG.  23 A  pre-sort) and ‘C 4 ’  2383  ( 2333  in  FIG.  23 A  pre-sort) for the sorting of the referenced cells within the ranges. Our technology preserves the correct values in the cells because it has illustratively atomized the ranges, sorted the cells to retain the correct cell references post-sort as previously exampled. It then, as previously exampled, can collapse the sorted cells into adjacent cell ranges to simplify the formulas. 
     While we could continue to example mix and match combinations of the inward sorting capabilities previously exampled (e.g., formulaic data, different data sources, different formula combinations, etc.) for inward by itself, we will instead briefly example the outward outside the sort area by itself capabilities. Thereby minimizing the replication of previously exampled capabilities. 
     Some Particular Implementations—Outward Outside the Sort Area by Itself 
     Our prior descriptions and examples employing outward referencing cells outside the sort area also employing outward referencing formulas referring to at least one cell in another row within the sort block. However, each of those technologies can operate by themselves.  FIG.  24 A  and  FIG.  24 B  examples outward referencing formulas referencing cells outside the sort area working by themselves without outward referencing formulas referring to at least one cell in another row within the sort block. In this example the charity user wants to sort the donations and retain the Manager names for each of the countries in a setup where if the manager changes it can replicate throughout the data pre and post sorts. To do so they have set up cells ‘B 3 ’  2423  and ‘B 4 ’  2433  outside the sort area with the names of the managers and then referenced them in the sort area  2442  column ‘D’ as shown by the formulas  2448  in the formula exampling of the sort area column ‘D’. Note, all the other columns of the sort area  2442  contain values with no formulas as exampled by the lack of formulas in  2446  for columns ‘A’, ‘B’ and ‘C’. The user then decides to sort the data by descending ‘Donations’ then by ascending ‘Continent’ and then by ascending ‘Country’. To do so they highlight the cells in ‘A 5 ’ to ‘D 10 ’ and initiated a sort with headers  2456  as shown in the popup  2455 . This use of headers then changes the sort highlight to the cells below the headers  2442 , as it would in a traditional spreadsheet. The user then selects sorts of descending ‘Donations’, then ascending ‘Continent’, and then ascending ‘Country’  2454  in the popup  2455 . When they click ‘OK’  2466  our technology resorts the values in  2442  to those in  2492  in  FIG.  24 B  with no corruption of the ‘Manager’ values  2495 . This is because our outward outside the sort area technology has automatically adjusted the ‘Manager’ cell formulas  2498  so that any changes in location (from the pre-sort locations  2448 ) did not change the cells that they reference  2473  and  2483 . While the user can see that the other sort cells within the sort block  2492  (post-sort) and  2442  (pre-sort) have no formulas  2496  (post-sort) and  2446  (pre-sort). 
     While we could continue to example mix and match combinations of the outward outside the sort area sorting capabilities previously exampled (e.g., ranges, formulaic data, different data sources, different formula combinations, etc.) for its use in combination with the other outward variant, we will instead briefly example the technology supporting our capabilities. 
     Computer System 
       FIG.  25    is a block diagram of an example computer system, according to one implementation. Computer system  2510  typically includes at least one processor  2514  which communicates with a number of peripheral devices via bus subsystem  2512 . These peripheral devices may include a storage subsystem  2524  including, for example, memory devices and a file storage subsystem, user interface input devices  2538 , user interface output devices  2520 , and a network interface subsystem  2516 . The input and output devices allow user interaction with computer system  2510 . Network interface subsystem  2516  provides an interface to outside networks, including an interface to communication network, and is coupled via communication network to corresponding interface devices in other computer systems or in the cloud and usable for cloud applications. 
     User interface input devices  2538  may include a keyboard; pointing devices such as a mouse, trackball, touchpad, or graphics tablet; a scanner; a touch screen incorporated into the display; audio input devices such as voice recognition systems and microphones; and other types of input devices. In general, use of the term “input device” is intended to include all possible types of devices and ways to input information into computer system  2510  or onto communication network. 
     User interface output devices  2520  may include a display subsystem, a printer, a fax machine, or non-visual displays such as audio output devices. The display subsystem may include a touch screen, a flat-panel device such as a liquid crystal display (LCD), a projection device, a cathode ray tube (CRT), or some other mechanism for creating a visible image. The display subsystem may also provide a non-visual display such as via audio output devices. In general, use of the term “output device” is intended to include all possible types of devices and ways to output information from computer system  2510  to the user or to another machine or computer system. 
     Storage subsystem  2524  stores programming and data constructs that provide the functionality of some or all of the modules and methods described herein. These software modules are generally executed by processor  2514  alone or in combination with other processors. 
     Memory  2526  used in the storage subsystem can include a number of memories including a main random-access memory (RAM)  2530  for storage of instructions and data during program execution and a read only memory (ROM)  2532  in which fixed instructions are stored. A file storage subsystem  2528  can provide persistent storage for program and data files, and may include a hard disk drive, a floppy disk drive along with associated removable media, a CD-ROM drive, an optical drive, or removable media cartridges. The modules implementing the functionality of certain implementations may be stored by file storage subsystem  2528  in the storage subsystem  2524 , or in other machines accessible by the processor. 
     Bus subsystem  2512  provides a mechanism for letting the various components and subsystems of computer system  2510  communicate with each other as intended. Although bus subsystem  2512  is shown schematically as a single bus, alternative implementations of the bus subsystem may use multiple busses. 
     Computer system  2510  can be of varying types including a workstation, server, computing cluster, blade server, server farm, or any other data processing system or computing device. Due to the ever-changing nature of computers and networks, the description of computer system  2510  depicted in  FIG.  25    is intended only as one example. Many other configurations of computer system  2510  are possible having more or fewer components than the computer system depicted in  FIG.  25   . 
     Some Particular Implementations 
     Some particular implementations and features are described in the following discussion. The technology disclosed can be practiced in a variety of methods, devices, combination of devices or systems or a computer readable media impressed with program instructions that, when executed on hardware cause the hardware either to carry out a disclosed method or combine with the hardware to form a disclosed device or system. 
     Some Particular Implementations—Outward within Cell Block 
     One method implementation of the technology solves the problem of corrupting formulas and values when sorting rows containing formulas containing cell references outside (outward of) the sort row. Those formulas can refer to cells within the sorting area (e.g., block) or outside of the sorting area. Our technology determines the post-sort ordering of the rows in the sort area and then for each formula in the pre-sort block determines the location of each cell referenced in the formula among the cells in the post sort ordering and updates the formula for the referenced cell&#39;s new location. This is exampled in the cell formula  436  in  FIG.  4 A  changed automatically to  476  in  FIG.  4 B  by our sorting technology thereby ensuring the value  433  in  FIG.  4 A  is not changed by the sort as exampled by  473  in  FIG.  4 B . Thus retaining the pre-sort values uncorrupted in the post-sort recalculation of the values. The formulas that are in the block and refer to cells that are in another row in the block or outside the block, can be referred to as outward referencing formulas. 
     So, in this first example implementation the cell ‘D 7 ’  433  is an outward referencing formula because it references at least once (i.e., twice) the cell ‘C 6 ’ which is in another row of the sortation block  432 . Once the sortation takes place the new location of the row containing ‘C 6 ’ pre-sort is automatically used by our technology to update the formula, replacing in this example ‘C 6 ’ with ‘C 5 ’ as shown in the post-sort formula  476 . This and the change for the new location of the sorted row (i.e., ‘C 7 ’ changing to ‘C 4 ’) updates the cell formula to give the uncorrupted value. Outward referencing sort formulas are also exampled in different types of formulas and combinations of formulas in many of the figures that follow. 
     Some Particular Implementations—Inward 
     Our technology applies a similar pre-sort to post-sort referenced cell formula update to formulas outside the sort area (sort block) which reference cells within the sort area (sort block). This is exampled in  FIG.  4 A   414  automatically changing to  474   FIG.  4 B  post-sort. Where our technology automatically changed the reference to cell ‘A 10 ’  441  pre-sort to the post sort reference to cell ‘A 7 ’  481  (in  FIG.  4 B ). Those outside the sort area (sort block) inward referencing formulas (referencing cells inside the sort area) are automatically adjusted for the new referenced cell locations so that their cell values are not altered (i.e., corrupted) by the recalculation after the sort. Those inwardly referencing formulas can be simple cell references, as in cell ‘E 7 ’ and its formula  414  in  FIG.  4 A  or can be more complicated formulas  1315  as in cell ‘D 4 ’  1314  in  FIG.  13 A  which references multiple cells within the sort area via a range function and an algebraic cell reference. These inward referencing formulas can be by themselves, meaning referencing sort areas with no other formulas, or in combination with sort areas including our outward referencing formulas. 
     Some Particular Implementations—Outward Outside Sort Block 
     Our technology also ensures that cells outside the sort block area (e.g., outside  432  in  FIG.  4 A ) referenced in formulas within the cell block area (e.g., ‘B 2 ’  412  outside the sort area referenced by the formulas within the sort area shown in ‘Formula in Column C’  434 ) retain that same value after their cell location is shifted as done by the sort in  483  in  FIG.  4 B , and their formulas continue to reference the same cells (‘B 2 ’ in ‘Formula in Column C’ in  494 ). These outward referencing formulas referencing one or more cells outside the sort block automatically retain that correct cell reference with no work required of the sort user. The outward referencing formula can reference a single cell outside of the sorting block as in  FIG.  4 A  and  FIG.  4 B  column C cells in  432  (pre-sort) and  483  (post-sort), or multiple cells as exampled in  FIG.  11 A  through  FIG.  11 C  column ‘C’ cells  1162  (with formulas referencing ‘B 3 :D 3 ’  1122  outside of the sort area as shown in  1177 ). Our outward referencing formula technology works by itself, in any combination with the outward referencing formulas within the sort area and our inward referencing formulas technology. 
     Some Particular Implementations—Cell Ranges 
     Embodiments of our technology automatically deliver uncorrupted values for the sortation of outwardly referencing formulas, where the outward reference has at least one cell range containing at least one cell within the sortation area (block) but not in the sortation area (block) row of the referencing formula and/or containing at least one cell outside of the sortation block. Our technology automatically atomizes the pre-sort cell range or ranges into their individual cells as exampled in  FIG.  7 A  (pre-atomization) and  FIG.  7 B  (post-atomization) for automatic individual adjustment of their cell locations in the formula post-sort as exampled in  FIG.  8 A  (pre-sort) and  FIG.  8 B  (post-sort). This technology works on any of our outward and inward referencing technologies. 
     Additional embodiments of our technology automatically collapses the sorted individual cell locations into one or more ranges of adjoining cells, should post-sort there be adjoining cells in the range argument or arguments of the formula as exampled in  FIG.  9 A  (pre-collapse) and  FIG.  9 B  (post-collapse). Our technology supports different collapse thresholds for reforming ranges in outward and inward referencing formulas as exampled for a two adjacent cells threshold in  FIG.  17 B  and a three adjacent cells threshold in  FIG.  16 B . Adjoining cell collapsing rules or algorithms can vary by primary direction of combination (e.g., vertical then horizontal) or more algorithmic approaches to minimize the number of ranges (e.g., minimize number of range arguments). These different variants work for any of our outward and inward referencing technologies. 
     Our outward referencing cell range automatic atomization and sorting technology works for cells within the sort area (block) referencing cell ranges entirely within the sort block area (e.g.,  768  in  FIG.  7 A  becoming  878  in  FIG.  8 B ), partially in and partially out of the sort block area (e.g.,  758  in  FIG.  7 A  becoming  888  in  FIG.  8 B ) or entirely out of the sort block area (e.g.,  746  in  FIG.  7 A  becoming  886  in  FIG.  8 B ). Our technology ensures that cell range references are appropriately handled automatically to not corrupt their recalculated values. 
     Some Particular Implementations—Cell Ranges Inward 
     Similarly, an embodiment of our technology automatically delivers uncorrupted values for the sortation of inwardly referencing formulas, where the inward referencing formula outside the sortation area (block) has at least one cell range containing at least one cell within the sortation area (block).  FIG.  13 A  and  FIG.  13 B  examples an inward formula referencing a range entirely within the sort area where the post-sort value  1349  is uncorrupted by the sortation (relative to the pre-sort value  1314 ) by our technology.  FIG.  13 A  examples the related pre-sort formula  1315  and  FIG.  16 B   1666  or  FIG.  17 B   1766  example the post-sort formula. Our technology automatically atomizes the pre-sort cell range or ranges into their individual cells as exampled in  FIG.  14 A   1424  (pre-atomization) and  FIG.  14 B   1465  (post-atomization) for automatic individual adjustment of their cell locations in the formula post-sort as exampled in  FIG.  15 A   1526  (pre-sort) and  FIG.  15 B   1566  (post-sort). Delivering the inward post sort inward range readjusted for the change in cell locations from the sort summarized in  FIG.  13 A  and  FIG.  13 B . 
     Our technology also ensures that cell range references are appropriately handled by our technology to not corrupt their recalculated values when only part of the inward referenced cell range is within the sort block and the rest is outside the sort block as exampled in  FIG.  11 A  through  FIG.  11 C   1145 / 1146 ,  1165 / 1166 , and  1185 / 1186 . As previously discussed our technology supports different collapse thresholds for reforming ranges in inward referencing formulas as exampled for a two adjacent cells threshold in  FIG.  17 B  and a three adjacent cells threshold in  FIG.  16 B . Adjoining cell collapsing rules or algorithms can vary by primary direction of combination (e.g., vertical then horizontal) or more algorithmic approaches to minimize the number of ranges (e.g., minimize number of range arguments). 
     Some Particular Implementations—Multiple Sorts Working 
     Our technology continues to avoid formula and value corruption during the sorting of a block of rows when the user continues to do additional different sorts as exampled for a first and second sort in  FIG.  11 A  through  FIG.  11 C  for the different types of outward and inward referencing formulas. Our technology supports the ability to continue to do additional uncorrupted sorts for all the different cell range reference formula situations. The sorts can be done for different sort areas and different sets of single or composite key sorts with no corruption of the formulas and their values. 
     Some Particular Implementations—Sort Keys Using Outward Formulas 
     The embodiments of our technology ensure that if the keys used to determine the sort are cells containing outward referencing formulas that the sorts deliver uncorrupted values and automatically adjusted formulas for the sort movements, as exampled in  FIG.  4 A   432 / 435  and  FIG.  4 B   483 / 485  for individual cell referencing formulas and exampled in  FIG.  6 A  and  FIG.  6 B  for cell range referencing formulas. The keys work for all the different individual and combinations of outward referencing formulas for single and composite keys in our technology. 
     Some Particular Implementations—Using Formulaic Data Formulas 
     Our formula and value corruption avoiding sort technology works for our formulaic data sourced from Non-Spreadsheet Cell external data or in-cell data as exampled in  FIG.  18 A  through  FIG.  21 B . Wherein the formulaic data fields use their own ordered value retrievers as exampled in  FIG.  18 A   1835  and  FIG.  18 B   1885 , use a combination of their own ordered retrievers and cell references as in  FIG.  19 A   1934  and  FIG.  19 B   1984  and/or use cell references within formulaic data within functions as in  FIG.  19 A   1937  and  FIG.  19 B   1987 . The formulaic data can be combined in sorted cells with regular cell data as exampled in  FIG.  20 A   2037  and  FIG.  20 B   2087  and  FIG.  21 A   2156  and  FIG.  21 B   2186 . The formulaic data can also be utilized with all the different outward and inward cell referencing formulas referencing cell values and cell ranges in the sort area, outside the sort area and partially in and out of the sort area in our technology. The formulaic data can come from multiple different data sets of NSC external or in-cell data sources as exampled with the three different data sets exampled in  FIG.  18 A  through  FIG.  20 B . 
     Some Particular Implementations—Outward System or Computer Readable Media 
     The technology disclosed can be practiced as a method, system or computer readable media. A computer-implemented system implementation can include at least one processor and memory coupled to the processor, the memory holding program instructions that, when executed, implement a method simplifying the entry of values into a spreadsheet cell while organizing and constraining the values that can selected. This system implemented method can include any of the features described above. Alternatively, the computer-implement system can include logic configured so that selected actions that improve spreadsheet operation are carried out by the various segments of logic working individually or cooperatively. A non-transitory computer-readable medium holding program implementation can include instructions that, when executed on a processor, implement a method simplifying the entry of values into a spreadsheet cell while organizing and constraining the values that can selected. As with the system, the method implemented by instructions held by the computer-readable medium can include any of the features describe above. 
     Some Particular Implementations—Inward by Iteself 
     Our prior descriptions and examples employing inward referencing formulas were in examples also employing outward referencing formulas. However, each of those technologies can operate by themselves.  FIG.  22 A  through  FIG.  23 B  examples inward referencing formulas working by themselves without outward referencing formulas.  FIG.  22 A  and  FIG.  22 B  examples an inward referencing formula with a single cell reference automatically updating for the post-sort change in the pre-sort cell location. The pre-sort inward cell value and formula  2214  automatically transformed post-sort  2274  (values and formulas) so the reference to cell ‘A 10 ’  2241  pre-sort is automatically changes to ‘A 7 ’  2281  post-sort. The outside the sort area (sort block) inward referencing formulas (referencing cells inside the sort area) are automatically adjusted for the new referenced cell locations so that their cell values are not altered (i.e., corrupted) by the recalculation after the sort. Those inwardly referencing formulas can be simple cell references, as in cell ‘E 7 ’ and its formula  2214  in  FIG.  22 A  or can be more complicated formulas as exampled in  FIG.  23 A   2327  and  2337  referencing multiple cells partially or completely in the sort area. 
     Some Particular Implementations—Inward by Itself Cell Ranges 
     Embodiments of our technology automatically deliver uncorrupted values for the sortation of inward referencing formulas, where the inward reference has at least one cell range containing at least one cell within the sortation area (block).  FIG.  23 A  and  FIG.  23 B  examples inward referencing cell ranges with all their cells within the sortation area  2337  (pre-sort)/ 2387  (post-sort), and with part of the cell range within the sortation area  2327  (pre-sort)/ 2377  (post-sort). 
     Our technology atomizes the inward pre-sort cell range or ranges into their individual cells, sorts them and then collapses the resulting cells into ranges as previously exampled in  FIG.  13 A  through  FIG.  17 B . Embodiments of our technology automatically collapses the sorted individual cell locations into one or more ranges of adjoining cells, should post-sort there be adjoining cells in the range argument or arguments of the formula as exampled in  FIG.  16 A  through  FIG.  17 B . Our technology supports different collapse thresholds for reforming ranges in outward and inward referencing formulas as previously exampled for a two adjacent cells threshold in  FIG.  17 B  and a three adjacent cells threshold in  FIG.  16 B . Adjoining cell collapsing rules or algorithms can vary by primary direction of combination (e.g., vertical then horizontal) or more algorithmic approaches to minimize the number of ranges (e.g., minimize number of range arguments). This technology works on any of our outward and inward referencing technologies. 
     Some Particular Implementations—Outward Outside the Sort Area by Itself 
     Our prior descriptions and examples employing outward referencing cells outside the sort area also employed outward referencing formulas referring to at least one cell in another row within the sort block. However, each of those technologies can operate by themselves.  FIG.  24 A  and  FIG.  24 B  examples outward referencing formulas referencing cells outside the sort area working by themselves without outward referencing formulas referring to at least one cell in another row within the sort block. These capabilities work for single cell references, as exampled in  FIG.  24 A  and  FIG.  24 B , and multiple cells and/or range references, as exampled in  FIG.  11 A  through  FIG.  11 C   1162  and  1177 . 
     Some Particular Implementations—System and Computer Readable Memory 
     Other implementations may include a non-transitory computer readable storage medium storing instructions executable by a processor to perform any of the methods described above. Yet another implementation may include a system including memory and one or more processors operable to execute instructions, stored in the memory, to perform any of the methods described above. 
     While the technology disclosed is disclosed by reference to the preferred embodiments and examples detailed above, it is to be understood that these examples are intended in an illustrative rather than in a limiting sense. It is contemplated that modifications and combinations will readily occur to those skilled in the art, which modifications and combinations will be within the spirit of the innovation and the scope of the following claims.