Jtdylktuy

Assume the following data:

X       | A | B | C | D | E | F

Group 1 | 1 | 0 | 0 | 2 | 0 | 0

Group 2 | 0 | 0 | 3 | 0 | 1 | 0

Group 3 | 0 | 1 | 0 | 0 | 0 | 2

Basically I measured 6 elements A to F, so I have 6 values. They belong to 3 different types, so I created 3 groups.

I would like to use a grouped bar chart so that I have only 6 bars, grouped by the groups. So in theory, one would see the data in following order: A D C E B F, where A and D belong together, C and E, and so on:

I managed to do this partially with a bar chart, but Excel shows A to F for all groups (the zero-valued elements are shown too). Hence, I now see 18 bars with 12 (zero) gaps: A B C D E F A B C ... F. Also, with my approach there is no gap between the groups.

How can I achieve a grouped chart that only shows the non-zero values? I would not mind using another strategy, if there is. Or even a VB script.

So far I found that Pivot charts come closest to what I tried. But I did not manage to get a spacing between the groups yet.

So in theory, one would see the data in following order: A D C E B F

Generate the 'sorted' table 1st.. then only draw the bar chart.

TLDR : draw bar chart from this (generated) table

        | F | B | E | C | D | A

Group 1 |   |   |   |   | 2 | 1

Group 2 |   |   | 1 | 3 |   | 

Group 3 | 2 | 1 |   |   |   | 

Step-by-step

1. clean the table. make all '0' become "".


2. get the column number for non '0' entry.


3. rank the number in step 2, divide by 10, then add the row number as integer.


4. rank generated step 3 numbers to get 1,2,3,4..


5. generate new table (part 1 of 2) from sorted "step 4" numbers


6. generate new table (part 2 of 2) using index match from "step 5" numbers


7. draw the bar graph from the new table.

assuming B1 is the "X" and H4 is the final data from OP's table, these are the detail step :

type in :

A6  ---->  cleaned

A11  ---->  column

A15  ---->  rank1

A19  ---->  rank2

A24  ---->  generate new table

A28  ---->  generate final table

Step 1 work :

C6  ---->  =C1

and drag until H6, then

C7  ---->  =IF(C2=0,"",C2)  

and drag until H9. Then for Step 2 :

C11  ---->  =IF(C7="","",COLUMN(C7))

and drag until H13. Then for Step 3 :

C15  ---->  =IFERROR(ROW(C11)+RANK(C11,$C11:$H11,1)/10,"")  

and drag until H17. For Step 4 :

C19  ---->  =IFERROR(RANK(C15,$C$15:$H$17,0),"")    

and drag until H21, then for Step 5 :

C23  ---->  =B23+1

and drag until H23, then

C25  ---->  =IFERROR(MATCH(C$23,$C19:$H19,0),"")

and drag until H27, then finally :

B30  ---->  =B7

and drag until F32, then

C29  ---->  =INDEX($C6:$H6,1,MAX(C$25:C$27))

and drag until H29, then

C30  ---->  =IFERROR(INDEX($C7:$H7,1,C25),"")   

and drag until H32.

Use B29:H32 table to generate your bar graph.
Hope it helps. ( :