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

Posted on November 12th, 2009 in Charts and Graphs , Learn Excel - 14 comments

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:
Top X chart - Show top x values of a chart interactively

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

Top X chart - Data

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.

Top X chart - Show top x values of a chart interactivelyOnce 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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

14 Responses to “Top X chart – Show Top X values of a chart Interactively”

  1. m-b says:

    Completely off topic but in the screencast I noticed you have following setting in Excel set to ‘on':

    http://img263.imageshack.us/img263/2549/moveselection.gif

    If you switch it off life becomes much easier when doing Excel developing because your selection stays the same after pressing Enter. That should save you a few key strokes :-)

  2. Bill says:

    Why invent a new terminology? Isn’t this is a Pareto chart?

  3. Chandoo says:

    @m-b: Thanks, I will use this feature for future screencasts. It would save lot of time.

    @Bill… :) you are right and wrong. A Pareto chart, by definition, should also show the cumulative contribution which is absent in this chart. But this is pretty close to a pareto chart.

  4. […] using either pivot tables like above or use formulas like large & small. You can even set up dynamic charts to show top 10 values. or use Conditional formatting to highlight top 10 values. I just love […]

  5. ted says:

    good day Chandoo.

    i downloaded the file (http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/topx%20chart%20v1.xlsx) but when i opened it, a warning appeared:

    Excel cannot open the file ‘topx chart v1.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

    is there another way to download a good copy of the Top X chart?

    -ted

  6. Hui... says:

    @Ted
    That is an Excel 2007/10 file, It sounds like you are using Excel 2003/XP

  7. Pieter T says:

    Hi,

    I wonder what is the correct formula to make the source range variable and listen to a date range?
    So I want to have a cell with a start and stop date and the formula should accordingly show the topx values for within that month.

    Thanks

  8. Onttu says:

    I see when you try and show percents the chart fails and shows only 0’s, any way to fix this?

  9. […] & Dashboards: Dynamic range charts | Top x chart | Analyzing large datasets | KPI […]

  10. This works for a one dimensional table but doesn’t really answer the question – what if I want to dynamically add a series.
    If the above were a stacked column chart, with values changing through the horizontal axis, say time, how could you add additional series to ‘the stack’. Is this possible?
    Thanks
    Ben

    • Hui... says:

      @Ben

      This can’t be automated unless you either:

      1. Use VBA to add series

      2. Predefine enough series to cover all eventualities, define formulas that have NA() as a solution when the series aren’t selected and they won’t show up in the Chart

       

      • Thanks Hui, nearly there!
        The NA() method works for the series in the chart area but unfortunately not the legend which, on this chart, needs to be dynamic. Unfortunately this is a fairly busy chart so needs the Legend to hold the data. Do you have any idea how to prevent the chart from presenting legend entries for which there’s no data?
        Thanks again
        Ben

        • Hui... says:

          @Ben

          On the Chart delete the legend

          Then make the Plot area and Chart area have no color

          Then make a legend manually using formula and cells which are behind the Chart (You can move the chart out of the way)

          You can use similar formula to the series to put values in cells and Conditional Formatting for lines etc

          Messy but it can be done

          Alternatively to doing it behind the chart do it elsewhere and use a camera tool to place a snap shot in front of the chart

  11. Keith SHeridan says:

    Chandoo et all.

    Can this be done withing a pivot table/chart as well?

Leave a Reply