How Is This Excel Sheet Built (Animated Chart)?

Hey all, I have a (probably) very simple question about an excel spreadsheet. I found the attached spreadsheet on Office.com as a downloadable template. When the user selects a different "type" (under the "type" drop down list at the top of the chart), the chart "animates" (for lack of a better term) when it transitions to showing the new series. How is this accomplished, and can I add this effect to any chart in Excel?

Also, how is the criteria selector at the top of the chart linked to the chart data? I see there is a hidden sheet with what looks like lookup tables, but I'm not sure what formulas were used to connect everything together.

The nice thing about using a table like this is that when you add rows to increase the size of the table, all of your formulas that reference the table automatically take into account the table in it's entirety.

So... if you select the table, and go under Table Design Tab.. you can see that the name of table is "issues". In order to refer to to a particular column of the table... like the 'Type' column... the syntax is something like =issues[Type]. This reference incorporates the entire column "Type".

Now if you go to the calculations sheet, and click on cell I16, you can see that it refers to and summarizes information from the issue table with the following formula:

note that :
issues[OPENED ON] refers to the OPENED ON column of the issues table
,issues[TYPE] refers to the TYPE column of the issues table and so on.

Some of the other items in this formula are named ranges. These can be examined in detail by going to the Formulas tab, and looking in the Name Manager. Here you can see specifically what range is referred to by a particular name.

Now that we see how these things are tied together via a Table and named ranges... you can look at the body of the chart.

Click on the middle of the chart to select it, then right click and choose "Select Data" from the pop up menu.

You can see "legend entries (series)... select any one of those to examine it closer.

For example if you select "Opened" and click on the edit button...
click to put your cursor in the series name. You will see that it gets the name of the series from the calulations sheet at cell $I$14.

If you look at the Series Values text box it contains this for values:
='Project-issue-tracker.xlsx'!opened

So to decipher this you have to know what "opened" is. That is defined as a named range in the Name Manager.

going back to Name Manager "opened" is defined as
=OFFSET(calculations!$I$16,,,days)

days in turn is defined as :
=calculations!$J$7

Now ... if you change the Days value (Above the Chart), that value is copied to Calculations Sheet Cell J7... where it has the name 'Days'.

The formulas throughout the rest of the sheet that use days... are automatically updated to reflect the new value.

Thank you for your in depth explanation of how the sheet works, very helpful. By animation I meant that the data on the chart smoothly transitions to the new series when a new "type" is selected. It almost slides into place. How do you replicate that?

**It just occurred to me that the transition might just be an effect in Excel 2013. I just opened the file in 2010 and it didn't do the sliding smooth transition. Oh well, I was really hoping that there was a way to incorporate java or something similar into excel.

I really want to make something like these charts in excel (interactive, smooth looking) and was thinking that if I knew what was powering that transition then I might be able to figure out how to make those java charts in Excel.

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…