Creating a Chart with grouped data sums

Occasional Visitor

Working in Windows 10, Excel Version 2110.

I am trying to setup a sales log for a foam shop that sells foam for seat cushions and other applications. I have no experience working in excel so i need a step by step explanation if possible.

The spreadsheet will have all our custom foam order data, with dimensions of custom ordered foam pieces (H x W x L), foam types (Density, Firmness), dates ordered, and a calculation of the volume (in board feet) for each piece.

How can i make a chart or table that selects for one like characteristic and groups data together to get useful comparative information about different sales figures to inform our product buying with sales trends?

For example:

    A chart showing the total sum of board feet for all Foam pieces entered as having the same thickness, for each distinct thickness, to show in a chart which thickness has the most board feet sold and how it stacks up against all other thicknesses. (I.E. total volume of all 5" thick foam=120BF, total of all 6" thick=300BF, 7" thick=254BF etc., represented in a bar graph with each bar being one distinct foam thickness with the bar height equal to the sum of all rows of the same foam height's calculated board feet.)

 

I'm sure this can be done but how? Do i have to generate each sum manually in a pivot table to reference those sums and create custom fields to label each bar in the chart for each thickness?

Or is there a way to selectively group data based on a common trait (I.E. All lines of a common thickness)?

1 Reply

@KeenanU I believe you can use pivot tables for this, but it goes too far to give you a step be step instruction here. Plenty of good tutorials are available on line. Just google "pivot table excel".

 

When you talk about "thickness", I assume you take about "height". A small pivot table can easily make the summary you described. Sum footage for each thickness and sort in descending order. See attached.

 

You loose me when you write "and how it stacks up to all other thicknesses (i.e. total volume of all 5" thick foam=120BF, total of all 6" thick=300BF, 7" thick=254BF etc.)". BF as in Board Foot?? Did som guess work in the attached file.