• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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.

Thanks!

Andrew
Project-issue-tracker.xlsx
0
Animaldrummer04
Asked:
Animaldrummer04
  • 2
  • 2
1 Solution
 
Ken ButtersCommented:
I don't see anything that I would call animation when I change the items at the top of the chart... but I think I can help outline how the items above the chart are associated with the chart itself.

First ... note the table below the chart.  The table is formatted as an excel table.
See this link for how to make references to different parts of an excel table if you are not already familiar with the table syntax.
http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx

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:

=I15+COUNTIFS(issues[OPENED ON],H16,issues[TYPE],type,issues[PRIORITY],priority)

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.
0
 
Animaldrummer04Author Commented:
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.
0
 
Ken ButtersCommented:
ahh.. I am using Office 2010, and that is probably why I didn't see any animations.

Here is an article that describes how to turn them off.

http://sumtips.com/2012/07/turn-off-office-2013-animations.html

If you really wanted to confirm, you could turn them off temporarily to see if the chart animations are affected.  I think it's a pretty solid guess that they are.
0
 
Animaldrummer04Author Commented:
Thanks. I just wish there was a way for the user to incorporate the animation ability into shapes, tables, charts and graphs. (like control it with vba)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now