How to create an Insurance Coverage Chart (Treemap?)

Occasional Contributor

I need to create an insurance coverage chart that shows policy amount ranges on the vertical axis, and date ranges (probably in years or quarter years) on the horizontal axis.  The boxes would have the names of the insurance companies, policy numbers, specific dates and amounts.

 

This is a basic one (not as much detail in boxes), but this is what I'm looking to do. 

 

Is this a "Treemap" chart?  Is there a tutorial on how to create something like this (rather than just manually plotting everything (which I could do, but it seems like a silly way to do something that I would THINK could be pulled from data)?  HELP!

 

Untitled.jpg

6 Replies

@Cheugy Thank you, but I've tried that and can't get it to do what I need.  I need dollar amounts on the vertical axis, years on the horizontal axis, then boxes with company names to stack on top of each other so they line up with the years on the bottom and the amounts on the side (like the photo I attached).

 

Not sure if I'm just not setting it up correctly, but I tried changing data within the instructions from the link you provided, and it doesn't do it.

Can you send the raw data? I can take a stab at it

@Cheugy 

Sample attached.  I need Amounts on the left axis, dates (years or quarter years) on bottom axis, then boxes for the policies to include name of insurance company, policy number, amount and specific date (although if I can just get name and policy number, I can add in other details after table is generated, I suppose).  IDEALLY, each insurance company should have it's own color, but, of course, I can do that manually after the fact.

 

GOOD LUCK!!!!!  HOPING YOU CAN FIGURE IT OUT!!!!

Hi @ilenea 

 

I think you stumped me on this one -_-

 

I reformatted the data and tried to replicate the treemap using stacked bar graphs. The overlaps and the large differences in pricing really made the data less meaningful. To visualize a value on y-axis and chronological x-axis, I think a scatter plot, heatmap, or line graph would be more meaningful and easier to do. I think with the current core functionality of excel, this wouldn't be possible without using a VBA script. 

 

Maybe some other brain child on here can help, sorry, tried my best Good luck!

Thank you for trying! I take comfort knowing that I'm not the only one stumped on this! One of us should write a program, lol! Looks like I'm going to be doing an art project manually (like we've done in the past)!