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:
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
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:
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.
- mid.chart: refers to the chart that goes in the middle. Use =INDEX(charts, linked_cell_for_scrollbar)
- left1, left2, left3: are the the neighbors to left. Just use =INDEX(charts, neighbor_number) pattern.
- right1, right2, right3: same as left
Step 5: Time for some linked pictures
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.
That is all. Your interactive chart slider thingy is ready now.
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.
Explores the bump in album sales after winning prestigious Grammy award.
Visually explore and compare flu trends between years & states. Inspired from Google flu trends.
Tour de France – Distance vs. Pace
A radial chart to show distance vs. pace in Tour de France.
13 Responses to “How to make an Interactive Chart Slider Thingy”
So cool Chandoo!! Thank you for sharing this 🙂
Hi Chandoo,
what's comapred?
It's a tpyo... that is what it is 😀
I fixed the file and images now. 🙂
Love it! Might I suggest the word 'Carousel' to replace 'Thingy'.
Thank you - this is outstanding. Can I share these example with my class - providing all credit and directing students to your website.
Hi Larry... Thanks for the comments. Yes, please go ahead 🙂
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.
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.
"Cover Flow" reminds me of Apple iPhone Launch 2007. Music Cover Flow.
Love your Work Chandoo.
Thanks AK 🙂 Yeah, I got the inspiration from iTunes Album view
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
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.
Want to develop a website. Digital Agency Mauritius is a web company that develop user friendly website. Contact Us for more information on https://web-companies.com/company