How to create an Insurance Coverage Chart (Treemap?)

Question

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!

cheugy · Answer

https://support.microsoft.com/en-us/office/create-a-treemap-chart-in-office-dfe86d28-a610-4ef5-9b30-362d5c624b68There's an ability to create treemaps in excel

ilenea · Answer

Cheugy&nbsp;Thank you, but I've tried that and can't get it to do what I need.&nbsp; 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).&nbsp;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. 😞

cheugy · Answer

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

ilenea · Answer

Cheugy&nbsp;Sample attached.&nbsp; 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).&nbsp; IDEALLY, each insurance company should have it's own color, but, of course, I can do that manually after the fact.&nbsp;GOOD LUCK!!!!!&nbsp; HOPING YOU CAN FIGURE IT OUT!!!!

cheugy · Answer

Hi&nbsp;ilenea&nbsp;&nbsp;I think you stumped me on this one -_-&nbsp;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.&nbsp;&nbsp;Maybe some other brain child on here can help, sorry, tried my best 😕 Good luck!

Forum Discussion

How to create an Insurance Coverage Chart (Treemap?)

6 Replies

Resources