Utilizing spreadsheet references with grouped aggregate views

• US 8,185,817 B2
• Filed: 06/18/2008
• Issued: 05/22/2012
• Est. Priority Date: 06/18/2008
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Collie, Robert C., Netz, Amir, Sanders, Paul J., Petculescu, Cristian
• Primary Examiner(s): Bashore, William
• Assistant Examiner(s): PHANTANA ANGKOOL, DAVID
• Application Number:
  US12/141,114
• Publication Number:
  US 20090319880A1
• Time in Patent Office: 1,434 Days
• Field of Search: 715/219, 715/503, 715/504, 715/508, 715/538, 715/220, 715/267, 715/810
• US Class Current: 715/219
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Various technologies and techniques are disclosed for utilizing spreadsheet references with grouped aggregate views. A grouped aggregate view feature enables a user to create a grouped aggregate view of data. A calculation feature enables the user to calculations for the grouped aggregate view of data that are based upon relative or absolute references to data in the grouped aggregate view. Input is received from a user to write a calculation within a first cell in a grouped aggregate view. Input is received from the user to select a second cell to reference when writing the calculation. The user is presented with available references that are relevant to data contained in the second cell. Input is received from the user to select one of the available references that are relevant for the second cell. The selected one of the available references is placed into the first cell.

Claims

1. A system for creating calculations within grouped aggregate views of spreadsheets:

a computing device comprising;

a grouped aggregate view feature which enables a user to create a grouped aggregate view of data;

a calculation feature which enables the user to create one or more calculations for the grouped aggregate view of data, the one or more calculations being based upon references to data in the grouped aggregate view, wherein the references to data in the grouped aggregate view include at least one relative reference to data and at least one absolute reference to data, the at least one relative reference takes into account its own location to determine which data to provide, the at least one absolute reference provides data at an absolute-specified location, wherein the calculation feature allows the user to select a first cell in which to place a respective one of the calculations, and also allows the user to select a second cell to reference as part of the respective one of the calculations; and

a toggle feature which allows the user to toggle between a list of available references to data for the second cell that can be selected as part of the one or more calculations, the available references to data being related to the data in the grouped aggregate view.

2. The system of claim 1, wherein the toggle feature is operable to display a list of absolute references to data and a list of relative references to data that can be selected as part of the one or more calculations.

3. The system of claim 1, wherein the calculation feature is operable to allow the user to directly enter into a selected cell a particular calculation containing an absolute reference to at least part of the data in the grouped aggregate view.

4. The system of claim 1, wherein the calculation feature is operable to allow the user to directly enter into a selected cell a particular calculation containing a relative reference to at least part of the data in the grouped aggregate view.

5. A method for enabling calculations to be written within a grouped aggregate view in a spreadsheet comprising the steps of:

receiving input from a user to write a calculation within a first cell in a grouped aggregate view in a spreadsheet;

receiving input from the user to select a second cell to reference when writing the calculation;

presenting the user with a list of available references to data in the spreadsheet that are relevant to data contained in the second cell, wherein the references to data include at least one relative reference to data and at least one absolute reference to data, the at least one relative reference takes into account its own location to determine which data to provide, the at least one absolute reference provides data at an absolute-specified location;

receiving input from the user to select one of the available references that are relevant for the second cell; and

putting the selected one of the available references into the first cell.

6. The method of claim 5, wherein the grouped aggregate view is a pivot table.

7. The method of claim 5, wherein the available references presented to the user include relative references that are relevant to the data of the second cell.

8. The method of claim 5, wherein the available references presented to the user include absolute references that are relevant to the data of the second cell.

9. The method of claim 5, wherein the available references presented to the user include relative references and absolute references that are relevant to the data of the second cell.

10. The method of claim 5, wherein prior to receiving input from the user to select one of the available references, receiving a selection from the user to toggle through the available references until a desired reference is reached.

11. The method of claim 5, further comprising when the user has finished creating the calculation in the first cell, automatically populating additional cells with a respective variation of the calculation that is appropriate for the additional cells.

12. A method for creating calculations for data in a grouped aggregate view comprising the steps of:

receiving input from a user to select a cell to reference when the user is writing a calculation in a particular cell of a spreadsheet;

determining a context of the selected cell;

providing the user with a list of a plurality of relative and absolute references to data in the spreadsheet that are relevant to the location of the selected cell;

allowing the user to toggle through the listed references and select a desired reference;

placing the desired reference that was selected by the user into the particular cell where the calculation is being written; and

when the user has finished creating the calculation in the particular cell, automatically populating additional cells with a respective variation of the calculation that is appropriate for the additional cells.

13. The method of claim 12, wherein the user can toggle through the offered references by pressing a keyboard shortcut.

14. The method of claim 12, wherein the keyboard shortcut is an [F4] key on a keyboard.

15. The method of claim 12, wherein the grouped aggregate view is a pivot table.