Heat Map in Excel possible VBA

[towely321]

I need to make an interactive risk management heat map that allows the user to filter data for 5 fields that are in my spreadsheet. Month, year, company, risk, and risk subcategory. My data also includes the impact and probability of each type of risk. In the same interactive/dynamic way a pivot table works, I need the filtered data to be displayed in a heat map like so...with probability and impact as the axis (axi?) and the points labeled with the type of risk. I'm thinking VBA will be needed but my VBA skills are awful. I left some sample data below.

Does anybody know where I can start or have any ideas? Thanks!

Month	Year	Company	Risk	SubRisk	Impact	Prob
May	2010	A	MM	MM-1	1	.3
May	2009	C	HH	HH-1	2	.34
Jan	2009	A	MM	MM-2	4	.53
Nov	2009	D	PP	PP-2	2	.12

<tbody>
</tbody>

 

[RickXL]

Hi and welcome t the MrExcel Massage Board.

You probably won't need to resort to VBA. If you plot the chart using all the data then you can add AutoFilters and use those. Whatever you filter out will be removed from the chart automatically.

To make things easier I would put the chart and data on separate worksheets otherwise removing data lines can interfere with the chart.

Instead of an AutoFilter you could use a Table like I did. Select a cell wthin the data, go to Insert and choose Table. Tht will colour the lines and add the AutoFilter dropdowns.

Your data will look like this:

Excel 2013
	C
10
Sheet1

If you play with the all the options on the chart you should be able to get something quite close to your original chart. I added a gradient fill to the chart area and some Shapes for the axis labels. The annotation on the right hand side could have been done with Shapes as well but I pasted in your original for ease.

 

[towely321]

I think I understand what you're saying but I also think what I need is more complicated. That's on me for my poor explanation. From this example pivot, I want to filter the Month, Year, Company and risk. Once it is filtered, I would like to plot the Total M1 and Total M2 (Which are the averages) on a scatter plot like before. And then be able to do the same for different filters and the different types of risk.

 

[towely321]

[/URL][/IMG]