Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How Is This Excel Sheet Built (Animated Chart)?

Posted on 2013-06-12
Medium Priority
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.


Question by:Animaldrummer04
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 19

Accepted Solution

Ken Butters earned 1000 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.

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:

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

days in turn is defined as :

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.

Author Comment

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.
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.


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.

Author Comment

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)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

597 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