Animate Your Dashboards in Excel With This Free Template

Dashboards in Excel can update when a user makes a selection on a slicer or refreshes data. You can even use macros to automatically update a chart or dashboard for you. In this post, I’ll share with you a template that I’ve created that will allow you to effectively play your dashboard, updating it from one period to the next, and showing the change in the chart over time. Here it is in action:

The template has three sections: one for pivot tables, one for the data, and one for the dashboard. You can set the file up however you want, the main area that needs to remain largely the same is the dashboard sheet. Every chart on this sheet only will automatically get updated. And for it to work properly, all the charts need to be linked to the one timeline chart in here (i.e. there cannot be more than one). For information on how to set up your timeline (or any other slicer for that matter) so that you can link it to multiple charts, you’ll need to learn about how to adjust Report Connections in this post.

Once you’ve got the charts you want to be connected to the timeline, then it’s a matter of just updating the settings section on the Dashboard tab. This is off to the left, with the values that you need to enter/update highlighted in yellow.

These simply specify what date you want to start from, where you want to end at, and by which interval you want to jump (e.g. x days/months/years). Depending on the frequency you select, your dashboard can either play very quickly, or very slowly.

The last step is to just click the Animate Dashboard button at the end of the home tab:

Upon clicking this, the timeline will jump by the intervals you specified. No other changes will be made to any filters or slicers you have selected. The only changes will take place to the timeline, at which point, you should see something similar to the video posted at the top of this post.

You can download the free Animate Dashboard template for free, from here.

If you liked this free template that helps you animate your dashboards, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.