arrow29 Comments
  1. Graham
    Oct 09 - 4:02 pm

    Ok, a remarkably simple but just awesome idea! Thanks.

  2. Hayley
    Oct 09 - 5:28 pm

    This is a wonderful post!!! There have been so many times where I had to do separate graphs to show changes for the same product over time because I was measure two vastly different pieces (e.g. # of transactions and $ sales). I’ve needed this and didn’t even know it!  Thank you!

  3. Bctyner
    Oct 09 - 5:35 pm

    I love your charting techniques posts! 

    How would you cope with values that are extremely variable before indexing? If the price of the commodity is sometimes very high and sometimes very low, the index won’t very accurately reflect later changes. Can you somehow homogenize the values to artificially deflate the variability?

    • Chandoo
      Oct 10 - 1:58 am

      Hi Bctyner…

      Thanks for your love.

      In such high volatility, you can use moving averages to plot the line. Assuming a value has cycle time of 7 days (for example: sport event ticket prices go up on weekends and go down on weekdays), you can calculate moving average of 7 days and then plot that to understand the true trend of your data.

      For more on moving averages see –

      Also, you can calculate standard deviation and show that as a data point on chart

  4. Vaibhav
    Oct 09 - 6:19 pm

    Its not just an excel technique but a great analysis technique too. Thanks a lot for sharing ! Love your post ( as always 😉 )

  5. exoskelet
    Oct 09 - 8:53 pm

    Hi Chandoo,

        thanks for nice article. But I’m wondering, how to visualize or analyze data as follows: Let’s imagine we have forecast of 3 materials for week10 till week 20, actualised during 7 weeks (w1 ~ w7). Could you point me to some guideline, how to visualize biggest drops or increases and such a things? Like a forecast history?
    I’m attaching example.

    Thanks for help. 

    • Chandoo
      Oct 10 - 2:07 am

      Hi Exoskelet.. You are welcome.

      If you have such data, a quick and easy method would be to calculate weekly fluctuation (change% with respect to previous week) and then apply conditional formatting to highlight very high or very low percentage changes. Like below:

      Conditional formatting - highlight drastic changes

      Download example file

  6. drodriguez910
    Oct 10 - 3:53 am

    Great post – thanks for the great advice.

  7. Kuldeep
    Oct 10 - 4:23 am


  8. Srini
    Oct 10 - 8:52 am

    Hi Chandoo
    Downloaded the workbook ,  can you please explain how to relate the box to the data , Tried doing it was not successful

    • Dean
      Oct 10 - 3:03 pm

      Ditto on Srini’s comment.  The whole dang thing is cool, but I couldn’t figure out the link in the data box. Please comment on that technique.

    • Chandoo
      Oct 10 - 3:25 pm

      Hi Srini & Dean,

      The linked data is called as picture link. Please see here to know how to use them:

  9. Colm
    Oct 10 - 11:23 am

    Another great post (but who’s surprised by that!).

    I run eBusiness activities for a major, global manufacturing company. I need to record and display progress but to do so without revealing any company secrets. I use indices to show %age of total revenue processsed through eBusiness as well as absolute revenue numbers.
    The starting point (=100) is the month I joined the company, so there’s no doubt about the value my team and I have added :-)  

  10. Roland
    Oct 10 - 3:15 pm


    Great article. I was wondering how you created the “View” page chart to show only columns A-J? 

  11. […] Using indexed charts to represent change Chandoo […]

  12. Srini
    Oct 19 - 7:43 am

    Thanks  a lot appreciate your good work

  13. Deepa
    Oct 23 - 9:22 am

    Hi Chandoo,

    i have a query please. Could you please tell me how i can create a secondary x axis. When i referred articles on the net, it mentioned that secondary x axis option is available in the ribbon under Axis. But i couldnt find it. Could you please help,


  14. Nishant
    Feb 14 - 6:28 am

    right click on the series, you want to have on secondary plot, select format data series, right side of the window you will see secondary axis that and you are done…!!

  15. Nishant
    Feb 14 - 6:31 am

    Hi Chandoo,
    Another great post..thank you sharing.
    I am not able to get the upward and downward arrows with the percentage in same cell, how do you do that??
    Do reply.

  16. […] some euphoric highs and gut-wrenching declines. Here’s a chart of the index and Nexflix, indexed to […]

  17. […] some euphoric highs and gut-wrenching declines. Here’s a chart of the index and Nexflix, indexed to […]

  18. Sara
    Jun 14 - 5:08 pm

    Hi, How would you handle a situation where the index date for one of the item which you index against has a value of 0 at time=0?

  19. Leandro Pinelli
    Jul 04 - 9:02 am

    Hello, votre billet m’a été très agréable à lire. Je repasserais sur votre site internet pour lire souvent vos billets.

  20. Shila
    Nov 24 - 3:11 pm

    Thanks. great!how you put the summary below the chart pls?

  21. Sandeep Reddy.
    Dec 28 - 7:40 am

    I want to monitor data changes in open interest of stocks on daily basis.Will this indexed chart work in such situations too.Please reply ASAP.Regards.

  22. […] Using Indexed Charts When Understanding Change: Indexed charts are useful to (a) understand change with respect to a bench mark, (b) compare values which are vastly apart and (c) understand growth (or non growth). […]

  23. Walter
    Jun 11 - 2:18 pm

    Great post thanks.

  24. Sayed Darwish
    Aug 18 - 2:47 pm

    Hi Chandoo, really like your website…
    What if there is a decrease in the following year. How can I calculate the result if the value in the new year (2012 above) is zero?.
    2011: 2
    2012: 0


Leave a Reply

Mobile Theme