Pivot Table#

Overview#

Pivot Table is a tool to reformat a data table by aggregating the individual items of a more extensive table within one or more discrete categories. By selecting index, columns, and value, the user can reformat the data table based on their needs. It is a good way to quickly group data and help you better understand the data.

Videos#

Examples#

Here is a step-by-step example of using Pivot Table to reformat data table.

  1. Prepare an Excel sheet with data to manipulate. The first row should be headers.

  2. Open Finplicity, and then click Data Basics > Pivot Table. The form of Pivot Table will appear, as shown below.

  3. Select the data table location. In this example, select the range A1:C12.

  4. Select the index column name. In this example, select “student id.”

  5. Select the discrete columns name. In this example, select “group.”

  6. Select the value column name. In this example, select “math score.”

  7. Select the Excel location for the output. In this example, select E1.

  8. Now you have finished all required fields in this form, and you can click “Run” to run Pivot Table or click “Save” to save this task. The result is shown as follows.

FAQ#

  • What if my data table doesn’t have index column?

You must select the index column in step 2. Otherwise, there will be a warning message pops up. You need to add an index column to the original data table in advance.

  • Can I have duplicate values in my index column or columns’ column?

You can have duplicate values either in index column or columns’ column. But two or more rows that have same values in both index column and column’s column are not allowed. For instance, using the above example, if two rows that have same “student id” and “group” exists in the data table, the data table cannot be transformed successfully. A warning message that “Index contains duplicate entries, cannot reshape” will pop up. You will need to pre-process the original data table.

On this page