Excel - heat maps

Hi

Thanks for this.

In direct relation to this question but unrelated to transferring it to a heat map, I would really appreciate your help.

So as I advised, I have now collated all of the answers to a worldwide questionnaire, by country and region, wherein capabilities have been measured by particular subject/topic on a scale of none, limited, moderate, significant and extensive.

So my data source looks something like this

Country Region Audit private Audit public Audit O
Country1 Europe Extensive Moderate Signif
Country2 AsiaPac Limited None Limited
Country3 Europe Moderate None Extensiv
Country4 MENA None Signif Limited


There are over 100 countries and over 100 questions (i.e. audit private, audit public etc) that can be grouped by topic (e.g. audit, tax, etc) but because each question needs to be in a column without subheaders, I am unable to do this.

I want to show clearly where (countries but more specifically regions) have answered 'extensive' but when I pivot, it gives me only the count of answered, even when I filter to extensive on a question.

Could you please advise how best I go about analysing this data set to show the regions where we have extensive etc? I have tried filtering to extensive within the pivot but it still only gives me the count.

Rows - audit private, audit public, audit 0 (i.e. qus) and I've also dragged these into Values and put my Region in the Column...

Thanks so much in advance,

Lauren