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) Ribbon

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. 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. Data

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. Data

Step 3

The dialogue box shown below, opens: Data

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. Data

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. Data

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

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

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. Data

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

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. Data

The table below explains each of these functions:

Function Use
Sum This function simply adds all the values pertaining to one entity. For example, in our selected example Sum adds the profit values belonging to one customer.
Count This function counts the number of data items belonging to one entity. For example, in our selected example Count gives the number of profit values belonging to one customer.
Average This function calculates the mean of all the data values pertaining to one entity. For example, in our selected example Average gives the mean of all the profit values belonging to one customer.
Max This function finds the maximum value among various data items belonging to one entity. For example, in our selected example Max gives the maximum profit value belonging to one customer.
Min This function finds the minimum value among various data items belonging to one entity. For example, in our selected example Min gives the minimum profit value belonging to one customer.
Std Dev This function calculates the standard deviation of the data items belonging to one entity.
Count numbers This function counts the number of numerical data present in data items belonging to one entity.
Count unique It counts the number of unique values in the data items belonging to one entity.
All Items It displays all the items of the selected field belonging to one entity.
Unique Items It displays all the unique items of the selected field belonging to one entity.
First It displays the first data value of the selected field belonging to one entity.
Last It displays the last data value of the selected field belonging to one entity.

Since we are interested in getting the total amount, the function we have to use is Sum. You can also give a custom name to the column being formed (under Header). 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. Data

Step 7

After entering your choice, click Finish. Data

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). Data

The result is shown in figure below: Data

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. Data

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

Step 7

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

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. Data 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: Data

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: Data

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. Data After selecting the Σ Value, choose the sort order (ascending or descending).The function window will look like the figure shown below: Data

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: Data

Case 5: Table with Multiple Pivot Rows

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

The result is shown in figure below: Data

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. Data

The result is shown in figure below: Data