Pivot Table

Pivoting is a very important tool used in extracting significant data, usually from a much larger sheet, which is otherwise very difficult to analyse.

SheetKraft contains the Pivot option which can be accessed by the Pivot/Aggregate Button present in the SheetKraft Toolbar ( See figure)

The following image shows an excel worksheet with row data. We will illustrate the use of Pivot operation in SheetKraft to extract the relevant data.

Case 1: Data field as Pivot Row

We are interested only about the Geography and the total amount that the firm is receiving from each Geography.

Step 1

Click on the Pivot/Aggregate Button and select the Pivot option to open this dialogue box.

Step 2

Select the data from which you want to construct the Pivot table. There are two ways by which you can select the data: 1)By manually selecting the data from the sheet. 2)By entering the cell indices of the starting and ending block, for example $A$1:$I$800.

You can select Change sheet if you want to select data from some other sheet. Also, click on the My data has headers checkbox if you have selected the headers with the data.

After selecting the data, click on OK.

Step 3

The dialogue box shown below, opens:

There are two blank fields provided namely, Rows and Columns. You can either set a data field as a Row or a Column in the Pivot table depending upon where you put it into. To put a data field into the blank spaces, you can simply Drag and Drop the respective field.

Since we are interested in the amount received based on Geography, we select the field pertaining to geography and pull the field into Rows.

Step 4

After selecting the Data fields, we need to define the field against which we are pivoting the data. To add a data field to the Σ Values tab, click on the + sign.

Clicking on the + sign opens a menu that lists all the fields in the input data, <Row Index> and <Custom>.

Select the required field. If <Custom> is selected, an item will be added to Σ Values where we can enter a custom formula.

If <Row Index> is selected, an item will be added to Σ Values where we can select a function which you want to operate on the Row Index.

Here we are interested in the Total amount the firm receives from particular Geography, we add the Amount data field.

Step 5

After you have added a field(s) into the Σ Values, you need to select the Function which you want to operate on the data.

Step 6

After you have selected the required data fields, click on Next. You will be asked where to save the Pivot table. To save the data in the Existing WorkSheet, you can input the index of the starting cell where you want to copy the pivot table. Or you can save the data in a New Worksheet.

Step 7

After entering your choice, click Finish.

Case 2: Data field as Pivot Column

To add a data field as Pivot Column repeat all the steps in Case 1 and in Step 3 Drag and Drop the required fields into Columns(instead of Rows).

The result is shown in figure below:

Case 3: Pivot Table Sorting (Sort by Label)

Repeat the steps step 1 to step 5 in Case 1.

Step 6

Click on the Sort Results checkbox.

Then a grid will appear at the bottom of the window with columns Group and Sort by.

Step 7

Select the label based on which sorting to be done by clicking the drop-down window in the Group column.

Step 8

The drop-down window in the Sort by column contains a list of all the Σ Values, "ascending" and "descending". If "ascending" or "descending" is selected the result will be sorted by label.

Suppose we are interested in getting the data sorted in descending order based on Geography. The function window will look like the figure shown below:

Step 9

After you have selected the required conditions for sorting, continue steps 6-7 in Case 1. The result is shown in figure below:

Case 4: Pivot Table Sorting (Sort by Value)

Repeat the steps step 1 to step 7 in Case 3.

Step 8

The drop-down window in the Sort by column contains a list of all the Σ Values. Select the Σ Value by which the particular group should be sorted.

After selecting the Σ Value, choose the sort order (ascending or descending).The function window will look like the figure shown below:

Step 9

After you have selected the required conditions for sorting, continue steps 6-7 in Case 1. The result is shown in figure below:

Case 5: Table with Multiple Pivot Rows

To add multiple data fields as Pivot Rows repeat all the steps in Case 1 and in Step 3 Drag and Drop the required fields into Rows.

The result is shown in figure below:

Case 6: 2D Pivot Table

To create a 2D Pivot Table repeat all the steps in Case 1 and in Step 3 Drag and Drop the required fields into both Rows and Columns. Both Rows and Columns can't have multiple fields.

The result is shown in figure below: