I manage a small business and use Google Sheets to store customers data. I want to filter and analyze that data at the end of the month for which I use pivot tables. Now, the problem is my product has different variables such as design, color and size and if problem arises when I have a customer with multiple orders say 3. I can't figure out the best way to do for which I'm here. One solution I have thought is to have as many rows for a customer as much as their order is but in that case sheet seems very redundant and inefficient. Can someone with the same scenario give me some ideas which doesn't involve much programming or advance knowledge of Google Sheets.


Your question suggests that you are having two technical problems:

First - you are confusing the difference between:

  • Recording transaction data
  • Presenting transaction information for analysis

Second - products variants (color, size, design):

  • how to take these into account when making sales.

Transaction Data

Each transaction should be recorded in a single row. However, this data can be displayed in a different format for analysis.

In the simplified example shown below, each transaction is recorded on a single line of the sheet.

  • Columns A, B and C record the Date, Invoice# and Customer
  • then "Transaction groups" of four columns (D,E,F,G; H,I,J,K; etc) record details of specific items for that transaction
    • Product ID
    • Qty
    • Unit price
    • Sales Value
  • You can have MANY "Transaction groups" on ther same row if you wish.

Transaction data Transaction data

Displaying Transaction Information

In the example below, a query is used to analyse the sample data (including the various "Transaction groups") to display one product per customer per row. This can be used in a pivot table.

=query({"Date","Order ID","Customer","Product ID","Qty","Unit price","Sales Value";Orders!A3:C8,Orders!D3:G8;Orders!A3:C8,Orders!H3:K8;Orders!A3:C8,Orders!L3:O8;Orders!A3:C8,Orders!P3:S8},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7 where Col4 is not null order by Col3, Col1")

Query Output

Query Output

Product variants

Your products have several variants (color, size, design).

Typically every product should have a unique code combining the variants. And there are advantages to using a consistent approach so that you can easily decipher/un-encode the meaning of a product code. For example, let's assume product X is available in 3 colours (Red, White, Blue), 3 sizes (Small, Medium, Large, and 2 designs (sHort, Long). There are 18 possible variants (3x3x2). Now you maybe you don't offer every variable for sale but each variant that you sell should have a unique product code.

For example, you might have use a format of - Product Code-Colour-Size-Design:

  • XRSH: Product X in Red, Small, Short
  • XRMH: Product X in Red, Medium, Short
  • XRLH: Product X in Red, Large, Short

Product Variants - An alternative

You don't mention how many products you sell, nor the importance of the variants in the success of your business. But a possible alternative might be to have a simple Product Code (product X) and then include the variants in the transaction record. Some like this:

Orders with variants

And then query for output like this:

=query({"Date","Order ID","Customer","Product ID","Color","Size","Design","Qty","Unit price","Sales Value";Orders_Variant!A3:C8,Orders_Variant!D3:J8;Orders_Variant!A3:C8,Orders_Variant!K3:Q8;Orders_Variant!A3:C8,Orders_Variant!R3:X8},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10 where Col4 is not null order by Col3, Col1")

query orders with variants

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.