Automatic formatting of pivot table reports within a spreadsheet

• US 6,626,959 B1
• Filed: 06/14/1999
• Issued: 09/30/2003
• Est. Priority Date: 06/14/1999
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Conlon, Thomas P., Thompson, Michelle Lee, Moise, Wesner P.
• Primary Examiner(s): Feild, Joseph H.
• Assistant Examiner(s): YUAN, ALMARI ROMERO
• Application Number:
  US09/332,958
• Time in Patent Office: 1,569 Days
• Field of Search: 707/503, 707/517, 707/522, 707/523, 707/906, 715/503, 715/517, 715/522, 715/523, 715/906
• US Class Current: 715/210
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Automatically formatting a spreadsheet pivot table using one of a plurality of predefined formats that include banded report formats and tabular report formats. The pivot table is reformatted automatically, based on a user selection of a predefined banded table or tabular format. Alternatively, the format of an original pivot table or a reformatted pivot table may be changed based on user choices made from a plurality of formatting dialogs. The automatically and modified formats include field emphasis, positioning of group totals, and insertion of blank lines. The predefined formats include a variety of different font styles, colors, backgrounds and borders. A reformatted pivot table has an enhanced appearance, while maintaining the full-functionality associated with a spreadsheet pivot table. The underlying format of a reformatted pivot table is maintained, allowing a user to move, add, or delete fields and add or remove calculated data without altering the overall format of the reformatted pivot table.

Claims

1. A method for enabling a user to selectively automatically reformat a spreadsheet pivot table that was produced from a specified set of data, said spreadsheet pivot table having an original format and an original functionality, comprising the steps of:

(a) providing a user interface that enables a user to select a predefined banded report format to be applied to the pivot table from a plurality of predefined formats that include at least one of the predefined banded report format and a predefined tabular report format; and

(b) automatically reformatting the spreadsheet pivot table based on the predefined banded report format selected by the user, wherein a reformatted pivot table that is produced thereby maintains the original functionality, enabling the user to perform any pivot table operations on the reformatted pivot table that could be performed on the spreadsheet pivot table prior to the step of reformatting, including drilldown, filtering, changing fields to different pivot table regions, moving fields within regions, changing calculation functions, and any other direct manipulation operations that could be performed on the pivot table prior to the step of reformatting.

2. The method of claim 1, wherein the user interface comprises a dialog including a plurality of selectable thumbnail images that provide a visual representation of a format to be applied to the spreadsheet pivot table to achieve the reformatted pivot table.

3. The method of claim 1, wherein the plurality of predefined formats further include a standard format option that when selected, substantially returns a reformatted pivot table back to its original format.

4. The method of claim 1, wherein the pivot table comprises a plurality of data items organized by fields in a hierarchical relationship, and wherein the plurality of predefined formats include at least one banded report format that displays subtotals for a data item of a field in a line with the data item.

5. The method of claim 1, wherein the plurality of predefined formats include different options for at least one of a font style, a color, a background, and a border.

6. The method of claim 1, wherein the set of data includes a plurality of fields, and wherein the spreadsheet pivot table comprises a row region that may include one or more row fields and one or more data labels, a data region for displaying calculated numerical data associated with one or more fields, and a column region that may include one or more column fields, said step of automatically reformatting the pivot table into a selected banded report format comprising the steps of:

(a) pivoting the column fields to the row region; and

(b) pivoting the data labels to the column region.

7. A computer-readable medium comprising executable instructions for performing the method recited in claim 6.

8. The method of claim 1, wherein the pivot table comprises data sets that are grouped in hierarchical levels, said step of automatically reformatting the pivot table into a selected banded report format comprising the step of inserting a blank row between different hierarchical levels in the reformatted pivot table.

9. The method of claim 1, wherein a specific format is associated with the reformatted pivot table and is maintained even though a user changes data included within the reformatted pivot table.

10. A computer-readable medium comprising executable instructions for performing the method recited in claim 1.

11. A method for enabling a user to selectively modify a format of a spreadsheet pivot table that was produced from a specified set of data, said spreadsheet pivot table comprising a plurality of columns and rows that include data items, data labels, and calculated numerical data, the spreadsheet pivot table further having an original functionality, the method comprising the steps of:

(a) enabling a user to select a field to be emphasized in the spreadsheet pivot table;

(b) automatically identifying data items in the field that was selected, corresponding to said field; and

(c) modifying the format of the spreadsheet pivot table so that each data item in the field that was selected is emphasized in the spreadsheet pivot table by automatically moving said data item into a new row inserted above a previous row of said data item, wherein the pivot table as modified maintains its original functionality, enabling the user to perform drilldown, filtering, changing fields to different pivot table regions, moving fields within regions, changing calculation functions, and any other direct manipulation pivot table operations on said pivot table that could have been performed on the pivot table prior to its modification.

12. The method of claim 11, wherein any data items in columns to the left of and sharing the same row as the data item that was moved are also moved to the new row.

13. The method of claim 11, wherein the user can select the field to be emphasized by selecting a data item corresponding to the field.

14. The method of claim 11, wherein the spreadsheet pivot table includes a row region in which one or more field names from the set of data may be included, and wherein the user can select the field to be emphasized by selecting a field name in the row region.

15. The method of claim 11, further comprising the steps of:

(a) enabling a user to selectively add rows after each data item corresponding to the field that was selected; and

(b) inserting a blank row immediately above each data item except for a topmost data item in said field.

16. The method of claim 11, wherein each data item corresponding to the field that was selected relates to a hierarchical level that defines a group, further comprising the steps of:

(a) enabling a user to selectively display subtotals at a top of each group; and

(b) inserting a subtotal value corresponding to a calculation based on numerical data corresponding to the group in the same row as a data item for the field selected to be emphasized.

17. The method of claim 11, further comprising the steps of:

(a) enabling a user to selectively insert a page break after each data item of the field that was selected; and

(b) printing pages of the pivot table so that each printed page comprises data grouped on a single data item of said field, including data in hierarchical levels below the single data item, a first row on each page comprising field names of the spreadsheet pivot table.

18. A system for creating a predefined banded table report from a spreadsheet pivot table that was produced from a specified set of data, said spreadsheet pivot table having an original format and an original functionality, comprising:

(a) a memory in which a plurality of machine instructions are stored; and

(b) a processor, coupled to the memory and responsive to the machine instructions, the machine instructions, when executed by the processor, causing the processor to;

(i) provide a user interface that enables a user to select a predefined banded report format to be applied to the pivot table from a plurality of predefined formats that include at least one of the predefined banded report format and a predefined tabular report format; and

(ii) automatically reformat the spreadsheet pivot table based on the predefined banded report format selected by the user, wherein a reformatted pivot table that is produced thereby maintains the original functionality, enabling the user to perform any pivot table operations on the reformatted pivot table that could be performed on the spreadsheet pivot table prior to the reformatting, including drilldown, filtering, changing fields to different pivot table regions, moving fields within regions, changing calculation functions, and any other direct manipulation operations that could be performed on the pivot table prior to the step of reformatting.

19. The system of claim 18, wherein the set of data includes a plurality of fields, and wherein the spreadsheet pivot table comprises a row region that may include one or more row fields and one or more data labels, a data region for displaying calculated numerical data associated with one or more fields, and a column region that may include one or more column fields, and wherein execution of the machine instructions further causes the processor to automatically reformat the pivot table into a selected banded report format by performing the steps of:

(a) pivoting the column fields to the row region; and

(b) pivoting the data labels to the column region.