Solved

How Is This Excel Sheet Built (Animated Chart)?

Posted on 2013-06-12
4
389 Views
Last Modified: 2013-06-13
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
Comment
Question by:Animaldrummer04
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
Ken Butters earned 250 total points
ID: 39245036
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
 

Author Comment

by:Animaldrummer04
ID: 39245125
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
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39245370
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
 

Author Comment

by:Animaldrummer04
ID: 39245382
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel case statements 3 24
Vlookup nonexact IP address match 32 51
excel connection properties parameters grayed out 5 27
Excel VBA When using VLookup 6 28
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now