fbpx

Making Interaction Plots using Excel

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Yesterday I read about interaction plots on junk charts where he points out the merits of an interaction plot. Interaction plots show interaction effects between 2 factors. For eg. you can show how your product sales have changed between year 1 and year 2 using an interaction plot like below:

interaction plots

As you can see, interaction plot is a simple line chart with several series. You can easily make this in Excel. Here is a simplified tutorial to help you get started.

Step 1: Select the data and insert a line chart

This is simple, just insert a line chart from the data.

Step 2: Go to the line chart “data” and change rows to columns

You require this step only if your data is oriented differently.
switch rows / columns - interaction plot in excel

Step 3: Format the interaction chart

Since excel colors each line series differently, you need to change the colors, add labels, adjust the label source (from data to series) and then format grid lines etc.

That is all, your interaction plot is ready to go.

Download interaction plot template for excel and play with it

Click here to download the interaction chart template for excel and use it to make your own interaction plots.

Points to consider when you are making interaction plots,

  • Interaction plots can be too messy if you have a lot of series. Generally they loose the appeal after 6-7 series of data.
  • Chart formatting with more series of data can be a pain too. Use F4 key if you are found repeating same steps.
  • Make sure you don’t color individual series differently. You can use same color and label instead. It looks a lot better that way.
  • In cases like last year vs. this year or budget vs. actual, you can even use clustered bars. See more examples on budget vs. actual charts for inspiration.

What is your opinion about interaction plots?

I think they are good for small data samples. I have personally not used them, but I like the idea and will use them when there is an an opportunity. What about you? Have you used them in any professional setting? How did your audience feel about the chart? Tell us using the comments.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
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

Chandoo is an awesome teacher
5/5

– Jason

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

100 Excel Formulas List

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

20 Excel Templates

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

IRR and data tables in Excel

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

  • Introduction to IRR & XIRR functions
  • Calculate rate of return from a set of cash-flows with XIRR
  • Simulating purchase or terminal value changes with data tables
  • Apply conditional formatting to visualize the outputs
  • Common issues and challenges faced when using XIRR

8 Responses to “Making Interaction Plots using Excel”

  1. Jon Peltier says:

    If you have many series, you could split the chart into multiple panels, as in the example in How to Build a Simple Panel Chart.

  2. Ross says:

    Hi Guys,

    Should this chart be a table...

  3. AoverT says:

    Nice post. You'll find interaction charts are used a lot in the biomedical sciences to show when changes in particular outcomes, let's say the effect of a drug, are dependent on other factors, for example age or sex.
    Jon, I absolutely agree that panels make for a really time efficient and data rich method for visualising interactions across many variables.
    Ross, I'm not sure about a table beingt he best option. To me, the chart would help the reader understand the nature of the interaction much better than a table. A table with a sparklines/microcharts may be even better. I suppose it comes down to what question you're answering with the chart. Are you more interested in the exact figures (in which case I totally agree with you) or is the main story the interaction (in which case I'd go with the charts or table with sparklines).

  4. Chandoo says:

    @Jon.. good suggestion.

    @Ross: yeah, you can make a table, As AoverT pointed out, tables lack visual appeal, so chart might more effective to prove a point.

    @AoverT: Very good remarks. Thanks for sharing your experience.

  5. Chris says:

    Chandoo,

    you wouldnt' know how to do that version of interaction chart easily in excel
    http://flowingdata.com/2009/11/10/do-we-need-more-teachers/sat-scores/
    Do I miss sth or is it really necessary to fake the axis through dummy series? i'm not seeing the forrest for the trees right now

  6. Harish Negi says:

    Oh, it is cool information. Will try it. thanks

  7. ines says:

    Hello, thank you for your explanation!
    I was wondering if it was possible to have on the same graph (and the same columns) another point indicating the mean and also SEM?
    Thanks!!

Leave a Reply