I will explain thoroughly how to create pivot tables using one of the real life (engineering ) example using MTO(Material Take-off) list.
So let’s start pivoting using Microsoft excel 2010.
You can see video here quickly which will show all required steps to create this pivot table.
1. Insert a pivot table
2. Select the data
When you click on PivotTable above, you will get an option to select the data range. You can then drag the mouse to the required range which will be automatically updated the below window.
If you would like to create the table in separate sheet, click on “New worksheet” or select “Existing worksheet.” And then click “OK”
3. Select the columns to be processed
You will see below screen once you click on “OK” above. Now select the data columns you want to be summarized.
Here we will select 4 columns for example size1, area2,class rating, item group.
Note when you select “qty” column, it will be added in “values” section to sum the qty.
Then we get the table as below, which still needs to be processed to look better.
3. Format the data
Now we want the Area2, Size etc in tabular format rather than like a tree structure shown below. And this is a key which many miss to format the data.
To arrange the data in tabular format, click on “Options”. Click anywhere on pivot table area to enable “Pivot tools” options in the main tool bar.
Enable the Classic Pivot Table layout and click “OK”
Then you see data in tabular format
You will get the sub total like highlighted above in pivot table. We need to remove those if those are not required-
Deselect this highlighted option and then you get the final output-
You can see blank rows are shown after main area2 eg BF-CSP1, now further if you would like to create unique ids based on area2-size1-item group-class rating combination or if you would like to vlookup this data for some reason, you would require all those blank row below it to be filled in to process.
Let’s do it now. Right click on the field you want to be filled in for blank rows and select “Field settings” and then select “Repeat item labels”.
You can see the, all blank rows have been filled in with preceding value. Repeat for each column
Then we have final version like this
Further if we would like to create MTO for “valves” only, then we will process above data quite easily as we have everything in place. We will use concatenate function used in previous post.
We will filter the data first
It’s ready now!