If you are going to use animation in your charts, you should do it to enhance the story your data is telling.
In this post I'll be looking at how the positions of teams in the English Premier League have evolved over the course of this season. Though there's hasn't been much change at the top, YNWA 🙂
NOTE 2: The charting and animation features in Excel aren't as slick as those provided by some JavaScript libraries. So the following animation may not be as smooth as something you've seen on the web.
How To Create Dynamic Chart Titles In Excel
The steps listed in this post to create the animated chart weren't considered in isolation from the others. I had to think about the whole process. What raw data do I need? How will that be aggregated to show what I want? Do I need to do any further processing of the aggregated data before it is charted? What type of chart to use? What data (and other information) would be useful to the viewer? What VBA do I need to automate all of this?
All of these things need to be thought about, and at least a rough answer found, before ploughing ahead. If you don't think the whole process through, you might find you get to brick wall where something just won't work as you hoped.
It's pretty easy to work this out since I know that to show a team's position in the league table I need to calculate their points scored from either winning or drawing (a tied game).
How To Make Your Graph Look Astonishingly Beautiful Quickly
As a tie breaker, if teams have equal points, you first work out their goal difference (goals scored - goals conceded), then look at goals scored.
To decide which team is higher on the table you now need to look at goal difference (GD) and if that is the same, then goals scored.
This is in a tabular format so that I can use a PivotTable to generate the data I need for my chart.
How To Add Moving Average Line In An Excel Chart?
Teams are in alphabetical order, I'll sort that out later before charting, and we can see their points, goal difference and goals scored.
Obviously the chart needs to show the points each team has. But as I already mentioned, it also needs to be able to distinguish which team is higher in the table based on goal difference and goals scored.
Using a clustered bar chart I can do all of this. Series 1 will be the actual points scored by a club. Series 2 will be the adjusted points accounting for goal difference and goals scored, and I can use the club crests as the labels for this series.
How Do I Replicate An Excel Chart But Change The Data?
To create the data I need, I create a table that uses GETPIVOTDATA to grab the values from the PivotTable. At this stage the table is still ordered alphabetically by club name.
Then using SORT I can get a table correctly sorted based on the 3rd column (points + goal difference + goals scored).
If I was only using the formula to calculate the value for the 3rd column you'd expect Liverpool to have 55 and 55.0377.
Create A Chart From Start To Finish
But because I'm plotting two series on the chart, and I don't want the labels for each series to overlap, I'm adding a small value to Points + GD + GS to nudge it along the x-axis a bit so it doesn't sit on top of the Series 1 label which will be the points for each team.
NOTE: I mentioned earlier that I chose small scaling factors for GD and GS. This is because I don't want to have values that visibly alter the length of the bars on the chart. I just need a very small difference to allow teams on the same points to be separated.
The chart now looks like this. Series 1 (in blue) will show the points for each team. The Series 2 bars are longer than Series 1 bars and will be used to display the club crests.
Quickly Create A Dynamic Ranked Bar Chart With Scroll Bar In Excel
Set the font color to white and the font size to 1. This effectively hides the numbers in the label so they don't obscure the club crests we'll be using.
With the chart selected, use the drop down in the top left of the Format section of the Ribbon to Select Series 1.
I want each bar to use the team colours. In some cases, for example Wolves, the bar will be their main colour - Old Gold - and the outline of the bar will be their highlight colour which is black.
How To Move An Embedded Excel 2010 Chart To Its Own Chart Sheet
To store all this data I'm using three Collections. The colour collections store the RGB values for the team's main colour and their highlight colour, and the third collection stores the name of the crest image file.
To create the animation the code needs to chart each week's data. To feed the selected week's data into the tables and hence into the chart, the code changes the filter on the pivot table. This filter is the value in cell I1 of our sheet.
So the data from the pivot table flows into the Calculated Table, which in turns feeds into the Sorted Table. The chart plots the data from the Sorted Table.
Online Chart & Graph Maker| Livegap
The order of teams in the Sorted Table is of course the same order as they are shown in the chart. As the code progresses through each week, the charted data changes and the axis labels correctly reflect the league table.
You can see in this image that Leicester and Man City have swapped positions from last week, but the bars associated with those teams are wrong. Leicester has Man City's sky blue and Man City's club crest, and vice versa.
Using the Sorted Table, the code knows that the top-most team in that table is the top team in the chart. The 2nd team in the table is 2nd in the chart etc.
How To Calculate Simple Moving Average In Excel
By working through each bar in turn from top to bottom, andusing the order of teams from the Sorted Table, we can apply the correct colours and crest to each bar.
There are 20 teams in the league so the code uses a variable counter to work through each one from 1 to 20.
The team name is the key to get the the RBG color value from the MainColour collection. The bar outline color is returned in the same manner from the HighlightColour collection.
How To Make A Graph In Excel (2023 Tutorial)
Then with the Series 2 labels, the code uses the team name to return the name of the crest image file from the Crests collection.
Once that is done the chart title can be updated and WeekNum, which tracks what week we are displaying data for, is incremented, then the code loops around and does it all again.
NOTE: The chart only shows 31 weeks of data because at time of writing only 31 weeks of the season have been played.
Ways To Make Beautiful Financial Charts And Graphs In Excel
By clicking the 'Animate' button, the chart will display all available week's data. You can stop the animation by clicking on the 'Stop Animation' button.
I've also added 'Previous Week' and 'Next Week' buttons that will display the previous week's data, and the next week's data. So you can step through each week and examine the league table in a little more detail.
Animation is not for the sake of it. If you are going to use it make sure it adds to the understanding of the message the data is telling.
Legends In Chart
In this case, you would normally see each week's league table in isolation and have to keep track in your mind of a team's position from week to week. This animation helps you visualize the changes in a team's fortunes more easily.
Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.
These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.
How To Animate Excel Chart In Powerpoint
When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.
Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excelmadeeasy: Add 3 Months Moving Average In Excel
Jitter introduces a small movement to the plotted points, making it easier to read and understand scatter plots particularly when dealing with lots of data.
Custom Excel Chart Label Positions using a dummy or ghost series to force the label position neatly above the columns of data
Filter and Cross Highlight Excel Charts like you can in Power BI using some Excel Power Pivot magic, regular charts and a Slicer.
Excel Animated Chart Vba — Excel Dashboards Vba
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
Use animation correctly to enhance the story your data is telling. Don't animate your
0 Comments
Posting Komentar