fbpx
Search
Close this search box.

Making Interaction Plots using Excel

Share

Facebook
Twitter
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.

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.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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