Top X chart – Show Top X values of a chart Interactively

Two charting principles we hear all the time are,

• Sort your chart data in a meaningful order.
• Show only relevant information, not everything – because un-necessary information clutters the chart.

Today we will learn a dynamic charting technique that will mix these two ideas in a useful way. I call this a Top X chart.

Note: This article uses the concepts from How to make chart data ranges dynamic. I suggest reading that article first if you haven’t.

What in the name of 3d square pie is a TopX chart?

A top-x chart is an interactive (or dynamic) chart which automatically sorts the data from top to bottom and displays only TOP ‘X’ items and showing the remaining balance as the last item. Users can adjust the value of ‘X’ and chart will be re-drawn to show more (or less) values.

See this example implementation:

How to make a Top X chart using Excel – 5 Step Tutorial

1. Have your data ready

It should be in 2 columns – first column – the attribute (for eg. customer name) and second column – the value. Lets assume the data is in range A1:B10.

2. Add 3 dummy columns

We need to add 3 dummy columns to this list. (you can do away with dummy columns if the list is sorted).

1. First dummy column – to make the values unique. We just take the value in column B and make it unique in Column C by adding a small incremental fraction to it. Something like =B1+10^-6*ROWS($B$1:B1) will do. [Help on ROWS formula]
2. Second dummy column – to get first X sorted customer names.
3. Third dummy column – to get first X sorted sales values. We use LARGE excel formula [14 more powerful excel formulas] for both these columns.
   It is your home work to figure out how to write these formulas.

3. Find a cell where user can input the X

Lets call it $F$2.

4. Update the dummy column formulas

We need to update the formulas in dummy columns 2 & 3 so that we can show “all remaining customers” as well.
To Do this, you can add an IF formula that would check if the number of the customer is >X and then just show “All remaining” with the sum of remaining values. Remember, your IF formula should be smart enough to show empty values if the row number is >X+1.

At this point, the data table should look something like this for X=5

5. Finally, select Dummy column 2 and 3, make a chart

We will re-visit our tutorial on how to make charts with dynamic ranges of data. We use the same concepts to make this interactive top x chart.

So make a named range pointing to the result of an OFFSET formula. If this sounds like turkish, I suggest getting a cup of coffee and reading the charts with dynamic ranges post. Now.

Once you have created the named range, just insert a new chart and use the named ranges as data sources. Format the chart a bit if needed and you should have a Sparkling Top X Chart, ready to fly.

Why Top X charts are cool?

• Top X charts let users play with them and find what they want. They are better than static versions.
• The show the necessary while hiding the rest.
• They show data in sorted order, which is awesome.
• You can easily build up on this concept to make them more presentable / fun. For eg. you can add a slider control and point it to cell F2.

Go ahead and download the Top X chart Template

Click here to download the topx chart template [Click here for Excel 2007 version, it is even more awesome] Play with it to learn how the formulas are working.

This is a slightly complicated chart, so beginners, you may want to jump around PHD and to get a grip on the key concepts.

What are your views on Top X Chart?

Please share your ideas and implementations suggestions using comments. I *love* to hear what you think about this.

Other Charts you can try:

Check out some of the excel dynamic charts to get inspired.