Solved

How Is This Excel Sheet Built (Animated Chart)?

Posted on 2013-06-12
4
375 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

7 Experts available now in Live!

Get 1:1 Help Now