How do I manipulate my data in excel and create a funnel chart in excel with raw data?-CodePudding

Is there a way I can create a funnel chart, split by gender using the data in the table attached. i.e. 6 candidates were new applicants, 6 candidates made it to recruiter screen, 5 candidates made it to first interview, 4 candidates made it to final interview and 2 candidates made it to offer?

CodePudding user response：

Refer to screenshots/ below:

Regular funnel charts:

=SUM(1*(FILTER(E$9:E$17,--($C$9:$C$17=$D19))>0))

Inverted funnels

Labels/data

=OFFSET($I$8,0,-COLUMNS($E21:E21) 1,1,1)


=SUM(1*(FILTER(INDEX($E$9:$I$17,0,MATCH(E$21,$E$8:$I$8,0))>0,--($C$9:$C$17=$D22))))

Graphs

REVISED: "How to have both genders showing on same funnel chart?"

Attempt 1 HACK (Bascially 2 graphs with some gradient shading tricks etc)

Attempt 2 - nailed it ☺

Note - this simply requires making the one gender (female in this case) all 'negative', multiply by -1 the relevant values to plot...

Here's a read-only copy of the (macro-free) file, for convenience...