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.
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.
A radial chart to show distance vs. pace in Tour de France.