Modern spreadsheet arrays

• US 10,545,953 B2
• Filed: 11/01/2016
• Issued: 01/28/2020
• Est. Priority Date: 11/03/2015
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Becker, Andrew, Campbell, John, McDaid, Joseph
• Primary Examiner(s): Park, Grace
• Application Number:
  US15/340,643
• Publication Number:
  US 20170124142A1
• Time in Patent Office: 1,183 Days
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 3/04842 Selection of displayed objects or displayed text elements G06F3/0482 takes precedence
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

The present disclosure provides a novel approach for creating and referencing arrays as implemented in a spreadsheet application. Aspects of the present disclosure include, but are not limited to: referencing arrays, creating dynamic arrays, collapsing values of an array into a single cell, spilling values of an array to a table, referencing one or more values in collapsed arrays, visually indicating no spill errors while maintaining calculation capabilities thereon, and implicit intersection of data.

Claims

1. A computer-implemented method for resolving a cell to an array of values, the method comprising:

receiving a selection of a range of cells of a spreadsheet, wherein each cell comprises a value of a plurality of values;

receiving a selection to create an array;

creating the array that includes each of the plurality of values, wherein each of the plurality of values is stored in a cell of the range of cells;

receiving a selection to collapse the array into a single cell of the range of cells; and

collapsing the array in the single cell of the range of cells, wherein the plurality of values of the array are referenceable in the spreadsheet as one or more values selected from the group consisting of;

individual values within the single cell, a subset of values within the single cell, and the plurality of values within the single cell, and wherein collapsing the array empties each of the range of cells except the single cell including the array.

2. The computer-implemented method of claim 1, wherein the array of the plurality of values is spilled back over the range of cells of the spreadsheet from the single cell.

3. The computer-implemented method of claim 2, further comprising:

receiving a reference to a cell of the range of cells to which the array is spilled; and

displaying a value corresponding to the referenced cell.

4. The computer-implemented method of claim 2, further comprising:

determining whether one or more obstructing cells exist; and

based on a determination that at least one obstructing cell exists, provide an error.

5. The computer-implemented method of claim 4, wherein providing the error further comprises:

displaying a #NOSPILL error in the cell in which the array is stored.

6. The computer-implemented method of claim 1, further comprising:

receiving a structured reference to an element of the array; and

displaying a value corresponding to the structured reference of the array.

7. The computer-implemented method of claim 1, further comprising:

receiving a notification to perform at least one of an update to a value of the array and a recalculation of a formula associated with the array; and

updating the array to reflect the change.

8. The computer-implemented method of claim 7, wherein the change comprises modifying the value.

9. The computer-implemented method of claim 7, wherein the change comprises adding a new value to the array.

10. The computer-implemented method of claim 1, further comprising:

in response to collapsing the array into the single cell, displaying an array symbol in the single cell.

11. The computer-implemented method of claim 1, wherein the single cell is a cell in which the array is stored.

12. The computer-implemented method of claim 1, further comprising:

displaying an array symbol in the single cell.

13. A system comprising:

at least one processing unit; and

at least one memory storing computer executable instructions that, when executed by the at least one processing unit, cause the system to perform a method, the method comprising;

receiving a selection of a range of cells of a spreadsheet, wherein each cell comprises a value of a plurality of values;

receiving a selection to create an array;

creating the array that includes each of the plurality of values, wherein each of the plurality of values is stored in a cell of the range of cells; and

collapsing the array into a single cell of the plurality of cells, wherein the plurality of values of the array are referenceable in the spreadsheet as one or more values selected from the group consisting of;

as individual values within the single cell, as a subset of values within the single cell, and the plurality of values within the single cell, and wherein collapsing the array empties each of the range of cells.

14. The system of claim 13, further comprising:

spilling the array of the plurality of values from the single cell back over the range of cells of the spreadsheet;

receiving a reference to a cell within the range of cells; and

displaying a value corresponding to the referenced cell.

15. The system of claim 14, further comprising:

determining whether one or more obstructing cells exist; and

based on a determination that at least one obstructing cell exists, displaying an error.

16. The system of claim 13, further comprising:

receiving a structured reference to an element of the array; and

displaying a value corresponding to the structured reference of the array.

17. The system of claim 13, further comprising:

receiving a notification to perform at least one of an update to a value of the array and a recalculation of a formula associated with the array; and

updating the array to reflect one of the update and the recalculation.

18. The system of claim 13, further comprising:

in response to collapsing the array into the single cell, displaying an array symbol in the single cell.

19. A system comprising:

at least one processing unit; and

at least one memory storing computer executable instructions that, when executed by the at least one processing unit, cause the system to perform a method, the method comprising;

receiving a formula at a spreadsheet;

in response to receiving the formula, resolving the formula to an array, wherein the array comprises a plurality of values, and wherein each of the plurality of values is stored in a cell of a range of cells; and

collapsing the array into a single cell, wherein the plurality of values of the array are referenceable in the spreadsheet as one or more values selected from the group consisting of;

as individual values within the single cell, as a subset of values within the single cell, and the plurality of values within the single cell, and wherein collapsing the array empties each of the range of cells.

20. The system of claim 19, further comprising:

displaying a border around the single cell comprising the array.