Using Pivot Tables

Last Updated: Apr 22, 2019 11:54AM PDT
Need to further analyze your Stitch data? If you or someone on your team is spreadsheet-savvy, consider using the pivot table function.

Note: This article applies to both our Stitch X and Stitch Classic Versions.

Pivot Tables Explained

Pivot tables are a tool for aggregating spreadsheet data according to your own custom rules. They can be applied on spreadsheets that consist of column headers with rows of data filled in beneath.

Pivot tables work best on unformatted data where all rows are filled in (unless the data for a particular cell does not exist). 

In this example, we'll apply a pivot table to the Payment History Report:

In the example above, note that the only missing cells are for PO Num. This is because not all orders have a PO Num.

Google Drive

To build a pivot table in Google Drive, select the data to be included then click Data > Pivot table... 

Use the Add field buttons to select a row to aggregate data by and a value to aggregate. In this example, payment Amount is being aggregated by Contact.

To include a breakdown by another field across columns, click Add field in the Columns section then select the field. In this example Payment Amount is being broken down by Payment Method across columns and by Contact across rows.

Excel for Mac

To build a pivot table in Excel (for Mac), select the data to be included. Navigate to the Data ribbon, click the arrow next to the Pivot Table icon then click Create Manual PivotTable...

From the PivotTable Builder, select Fields to be included. By default, fields are added to the Values box. Drag the Field you want to distribute across rows into the Row Labels box.

To add a set of columns based on another field, first check mark that Field, then drag it from the Values box into the Column Labels box.

Using Excel on a PC? Learn more about pivot tables at Microsoft Office's Support Center.

Use Cases

In the above example, the Payment History Report was pivoted to sum Payment Amounts by both Contact and Payment Method.

Other use cases for pivot tables with Stitch reports include:
Shipment Summary Report
Find out how many orders you've shipped with each carrier and how much you've spent with each carrier.

Row: Carrier
Value: Cost (SUM), Order ID (COUNT)

Outstanding Invoice Report
Find out how much your customers with outstanding invoices owe you. This is useful when a single contact may have multiple outstanding invoices.

Row: Contact
Value: Balance (SUM)

Variant Listing Associations
Find out how many listings you have linked to each variant, overall or by sales channel.

Row: Variant
Column: Sales Channel
Value: Listing Name (COUNT)

For more spreadsheet tricks, read about VLOOKUP.

Screenshots taken on 2016-03-29 in Microsoft Excel for Mac 2011 and in Google Sheets.