Google Search

Custom Search

Preparing Data for Stacked Column Chart in Excel

I recently had a situation where I have part measured repeatedly after rework. The number of times the each parts measured and which  measurement belongs to which rework is only can be identified by "time stamp" (the time of the measurement). Please refer to the sample data. Please note the data is sort by time stamp and part name. 

Now I need to a Stacked Column Chart like below. This is to have visual representation of the effect of rework. My original intention to use Qliksense or Tableau but excel can do the job with an additional table. 

Step 1: First sort the table by part and then by time stamp. 

Step 2: Add a column (column D) with the formula

="Measurement No" & COUNTIF(B$2:B2,B2)

Step 3: Drag the formula all the way.

Step 4: Now you can create the table for the Stacked Column Chart.
The table should be like the image below.

Add formula =SUMIFS($C:$C,$B:$B,J$1,$D:$D,$I2) in cell J2.

Fill the table with the formula.

Use insert → chart → stacked column to create the chart.


blogger templates | Make Money Online

Google Analytics Alternative ExpiresDefault access plus 1 year