fbpx
Search
Close this search box.

Narrating the story of change using Excel charts – case study

Share

Facebook
Twitter
LinkedIn

Here are three questions you often hear from your boss:

  1. What changes are happening in our business and how do they look?
  2. Do you know how to operate this new coffee machine?
  3. Why does every list has 3 items?

Jokes aside, our urge to find change in environment predates cave drawing, slice bread and Tommy Lee Jones. So, today let’s examine a very effective chart that tells the story of change and re-create it in Excel.

How fast America changes its mind – Example chart

Here is a recent chart from Bloomberg, depicting how America has changed its mind about various social & political issues between 1787 & 2015. (Read the full article on Bloomberg)

how-fast-america-changes-its-mind

Applying these concepts to sample business data

Of course, our business data won’t span two and half centuries. So let’s look at something more realistic, like market share changes for 7 product categories over the last 15 years.

Here is a chart we will create in Excel.

market-share-changes-over-time

Narrating change over time story with Excel charts – Video tutorial

If you like the above chart, watch below video to learn how to create it. As the process is somewhat elaborate, I made a video explaining various steps and techniques. Watch it below (or click here to see it on our YouTube channel)

Download the Change over Time Excel Chart:

Please click here to download the workbook for this chart (Excel 2013 Version).

Please click here to download the workbook for this chart (Excel 2007/10 Version).

Examine the chart, formulas & formats to learn more.

How do you tell the story of change?

My favourite methods for narrating the story of change are,

What about you? What charts & techniques do you use to narrate the story of change using Excel? Please share your thoughts and ideas in the comments area.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

13 Responses to “Narrating the story of change using Excel charts – case study”

  1. Jared says:

    I get "[CELLRANGE]" all over the graph in the demo workbook. Is this a setting I have an excel or something related to how the graph was built?

  2. Chandoo says:

    Thanks Hui. I forgot to mention the 2013 part.

  3. Andrew says:

    "Of course, our business data won’t span two and half decades. "

    Should that be 2 and a half centuries? makes it sound much grander ...

  4. Rishil says:

    The market share changes was a good example to share.. Thanks. I believe Flash is an excellent technology for dealing with data visualization, and now there are tools online that can help you to create flash charts online for free.. try this http://askwiki.blogspot.com/2009/07/how-to-create-quality-charts-using.html

  5. Alida says:

    Hi Chandoo,

    I urgently need a template for Debtor Age Analysis with possible predictions / forecasts.

    Can you please assist?

    Kind regards,
    Alida

  6. ANAND says:

    Dear Chandoo,

    It is an excellent Graphical Analysis. No doubt Excel is " EXCEL ". We got imputes for data analysis by reading this blog.

    Have sharing !

    With regards.

  7. Fred says:

    Hi Chandoo,

    I think the formular could be simplified to =VLOOKUP(999,OFFSET(C$23,0,0,INT($K23/2+1.5)),1,TRUE)

    Best,

    Fred

  8. King Khan says:

    Awesome tools are available. Love it 🙂

  9. Jorge Cabral says:

    Hi Chandoo,

    I think the formula is not totally correct, or I did not quite understand the reading of this type of chart, like this:

    If in "Category 1" in "July-02" I put for example "1.5%", cell "C24", in cell "M23", corresponding to "January-02", should not appear the value of "0 , 0% ", and only in" July-02 "would it be" 1,5% "?

    Thanks
    Jorge

Leave a Reply