CODEWITHSUNDEEP
excelcharts
Jerome Provensal
Jerome Provensal

Reputation: 1057

Group values into an "others bucket"

I have a list (30+) of users with the number of times those users were active per month in my system. I'm using an Excel chart to show which users have been consistently active over time. About one third of the users come consistently (several times a month) while others come one month and not another.

Unfortunately since I have 30+ users, the colors in the Excel chart are difficult to distinguish from one another. What I was thinking to remedy this was to only show the top 10 (or 15) users and group the others into an "others" or "catch-all" bucket and still shows it in the chart.

A sample image of the chart:

https://drive.google.com/file/d/0B_dh4cDXmimmZXA0X2IwOGJGSDA/edit?usp=sharing

What's the best way to do this?

Upvotes: 0

Views: 226

Answers (1)

pnuts
pnuts

Reputation: 59485

Add a column that differentiates 'Active' from 'Non-active'. In the sample I had to create overall B and C were the most active so I populated D2 down with: 

=IF(OR(B2="B",B2="C"),"Active","Non-active")

Adjust the formula if that distinction is made on a month by month basis. Create a Stacked Column PivotChart and in the PivotTable move the 'Active' together as necessary. Select each of the Non-active series (here only two for illustration) in the chart and apply the same colour fill to all.

SO25674303 example

Upvotes: 1

Related Questions