fbpx

How to make an Interactive Chart Slider Thingy

Share

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

Ok, I will be honest. I have no idea what to call it. May be Chart Cover Flow? But Interactive Chart Slider Thingy sounds so better. So let’s go with it. Here it is:

Interactive Chart Slider thingy - Dynamic Chart in Excel

In this article, let me explain the process for creating such an interactive Excel chart display.

What you need?

You need a few charts, some time and understanding of simple Excel formulas.

Got them all? Let’s roll.

Video – Interactive Excel Chart Slider

Although the instructions for this are not technical, You will benefit from watching this video before reading further.

Step by step tutorial – Interactive Chart Slider Thingy

Step 1: Position your charts in a range, one chart per cell

charts named range

Select a range of cells and resize them so that each cell can accommodate one chart. Place charts neatly, one per cell. Name this range as charts. Something like this:

Note: While this example shows how to do the ICST (Interactive Chart Slider Thingy, keep up) with 7 charts, you can apply this idea to any number of charts.

Step 2: Set up scrollbar form control

Exactly what it says on the box. Using Developer Ribbon > Insert, add a scorllbar form control to your worksheet.

Right click on the control to set its limits from 1 to 7 and link it to a cell.

Tip: If you can’t tell your developer ribbon from oven mitten, check out this excellent guide on Excel form controls.

Step 3: Calculate neighbors

For our ICST to work, we need to show selected chart in the middle and the neighbors to left & right. We can calculate the neighbor numbers from a given chart number n using simple formulas. I am leaving it to your imagination.

If you need some help, here is a demo of how the neighbor numbers:

calculations for chart neighbors - interactive chart slider

Step 4: Create 7 named ranges, one per chart cell

Now that we have neighbor numbers and charts range (from step 1), we can create 7 named ranges, one per chart.

  1. mid.chart: refers to the chart that goes in the middle. Use =INDEX(charts, linked_cell_for_scrollbar)
  2. left1, left2, left3: are the the neighbors to left. Just use =INDEX(charts, neighbor_number) pattern.
  3. right1, right2, right3: same as left

Step 5: Time for some linked pictures

Linked picture in Excel

What is that you are saying?
Are we there yet?
Yes, we are sweetie. Almost there.

Go back to your charts range and pick any cell. Copy it.

Go to the place where you want your final visual. Right click and select Paste Special > Linked Picture.

Tip: Here is a quick tutorial on Linked Pictures.

After you pasted any cell as linked picture,

  • Select the picture.
  • Go to formula bar and type =mid.chart the name of the selected chart
  • Copy this picture and paste again
  • Change reference to =left1
  • Continue 5 more times and use the names left2, left3, right1, right2, right3

Now, we have 7 pictures, one per each chart. They are all linked to the scrollbar, so if you slide it, you will see the images change.

Step 6: Format the picture links

This is the final step. We just need to format the picture links to mimic album coverflow look. The key steps are,

  • Shadow effect to make the chart look floating
  • Reflection effect
  • 3D rotation for non-active charts
  • Send to back to move the non-active charts behind

See this illustration to understand typical settings for one of the picture links.

How to format a picture link

That is all. Your interactive chart slider thingy is ready now.

Interactive Chart Slider thingy - Dynamic Chart in Excel

Download Interactive Chart Slider

Click here to download Excel file with full interactive chart slider example. Play with it to learn more. Customize the file or copy the ideas to your work.

If you end up creating something cool or funny, please share it in the comments so we all can learn.

More wacky and fun Excel charts

If you want to spice up your Excel chart life, you’ve come to the right place. Dim the lights, turn on soothing jazz and enjoy these goodies.

Grammy Bump Chart

Excel - Grammy bump chart

Explores the bump in album sales after winning prestigious Grammy award.

Flu trends chart

Visually explore and compare flu trends between years & states. Inspired from Google flu trends.

Tour de France – Distance vs. Pace

tour de france radial chart in excel

A radial chart to show distance vs. pace in Tour de France.

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

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.

two level data validation drop down excel

Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

12 Responses to “How to make an Interactive Chart Slider Thingy”

  1. shair says:

    So cool Chandoo!! Thank you for sharing this 🙂

  2. Jan Martens says:

    Hi Chandoo,
    what's comapred?

  3. Love it! Might I suggest the word 'Carousel' to replace 'Thingy'.

  4. Larry Mandelkehr says:

    Thank you - this is outstanding. Can I share these example with my class - providing all credit and directing students to your website.

  5. Kim says:

    Awesome, I have been following your site for a few years now and this is the coolest!
    As end of year reports are due this is going to work a treat!
    Big thanks and keep up all the great work.

  6. Craig says:

    This is wonderful. Very simple (after I watched your video). Not something I would have considered possible until I saw your work. I need to play with Charts more. Thank you.

  7. AK says:

    "Cover Flow" reminds me of Apple iPhone Launch 2007. Music Cover Flow.

    Love your Work Chandoo.

  8. Johnny C says:

    Hi.
    Unfortunately it works when I open it but when I wwent to save as an XLSB it says some features weren't supported and went a bit nuts - I can't select the centre chart. I'm using Office 365 Pro Plus which is probably a bit behind.

    That aside, can you add some code (hence i used XLSB) to hyperlink to the central chart when you double click? If I can get that working I can get the series and parse the SERIES function to select the source data. I tried the worksheet BeforeDouble click event but due to the selection problem I couldn't get very far. I have code elsewhere that has a list of all worksheets in a giant scrollable userform and use the doubleclick event in that for the list to jump to the currently selected item in the list.

    My thinking is to create a range with linked pictures of the worksheets and worksheet name in carousel/Aero style so I can get to worksheets more quickly. It's a bit of work but I have worksheets with >100 different pages I constantly jump around and a slidable carousel would make navigating a lot easier. I would add a button to update the list and formulae.

    Cheers
    Johnny

    • Chandoo says:

      Hey Johnny...

      Not sure why the chart wouldn't work with XLSB. I suggest using a dynamic named range as source of the data. That way, you just need to adjust the named range formula inputs to update chart. Also, picture links are by default hyperlinked. So if you double click on an picture link, Excel takes you to target range.

      Let me know if you need more guidance.

Leave a Reply