How to Create Animated Bar Chart Race in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

One of the most helpful software you can use is Microsoft Excel. It is possible to do an endless number of things with a dataset by utilizing Excel’s capabilities and tools. In this article, we’ll go over the steps necessary to produce an animated bar chart race. A bar chart race is the appropriate graphic format to demonstrate a significant change in information over time. In light of this, the following tutorial will focus on the step-by-step process of creating an Animated Bar Chart Race in Excel.


What Is Animated Bar Chart Race?

Displaying time series data in an animated bar chart is a breeze with Bar Chart Race. It’s an easily digestible depiction of data evolution through time. Thus, bar diagram races are superficial. They provide a fascinating story that presents statistics in a new, intriguing, and coherent manner, inviting spectators to remark on or suggest the race’s various areas of interest.


How to Create Animated Bar Chart Race in Excel: Step-by-Step Procedure

Creating an Animated Bar Chart Race in Excel can be difficult if we do not know the proper steps. This article will walk you through the 4 stages required to create a dynamic bar chart race in Excel. First, we will construct a dataset for this demonstration. Eventually, we will plot the data into a bar chart. Finally, we will create the VBA code and assign the Macro to a button to initiate the animated chart. To quickly comprehend the technique, carefully follow these steps.


Step 1: Set up Data Model

The first and foremost step is to create a dataset for illustration purposes. In this article, we will consider the below dataset. The dataset we will work with has three columns titled Month, Sales(2020), and Sales(2021). It shows monthly sales for the years 2020 and 2021.

  • First, select the C4:D16 range.
  • Second, press Ctrl+C.

Set up Data Model

  • After that, pick the E4:F16 field.
  • Later, press the Ctrl+V key.
  • Finally, our model will display like below.

Set up Data Model for Creating an Animated Bar Chart Race


Step 2: Generate the Chart Data

We need another two columns to plot the data into the bar chart in this section. We will copy both Sales columns and paste them beside the dataset to be more specific in the E and F columns. Now, we plot a graph utilizing the columns created in the previous step. Here we will choose a Clustered Bar Chart. Please follow the below instructions to implement the chart.

  • Firstly, navigate to the Insert tab.
  • Second, choose the Insert Column or Bar Chart icon from the Charts group.

Generate the Chart Data

  • After that, click on the Clustered Bar icon from the 2-D Bar section.

  • As a result, an Empty chart will appear like the one below.

Generate the Chart Data

  • Next, click anywhere on the chart, go to the Chart Design tab, followed by Select Data.

  • Subsequently, the Select Data Source window will open.
  • At this point, type the Sheet Name with an Exclamation mark, then the range.
  • It is essential to use commas between several ranges.
  • Later, tap the Add icon from the Legend Entries section.

  • After the Edit Series window opens, write the range in the Series Name box with the proper syntax mentioned earlier and for the Series Values for column E.
  • Now, hit OK.

  • Similarly, do the same follow-up for column F, then hit OK.

  • Presently, go to the Select Data Source window, choose Edit from the Horizontal Axis Labels, and hit OK.

  • After the Axis Labels window opens, write the range in the box for column B and tap OK.

  • Subsequently, the chart will fetch data from the data model.
  • Now, click on the Plus icon and check Axes, Chart Title, Gridlines, and Legend for this demo.

Create Chart Data for Animated Bar Chart Race

Read More: How to Create Animated Charts in Excel


Step 3: Build a Macro for Animated Bar Chart Race

We will write some VBA code to be displayed like an animation. Here, we will declare a procedure labeled DynamicChart. This macro will fetch data for the chart from the C and D columns with maintaining a delay and paste it into the E and F columns. Thus, the graph will get the data. To accomplish the work, please follow the instructions below.

  • To begin, navigate to the Developer tab.
  • Secondly, click on Visual Basic.

Build a Macro for Animated Bar Chart Race

  • After that, choose Insert, followed by Module.

  • Later, input the following code in the Module box.
Sub DynamicChart()
    Const StartRange As Long = 5
    Dim LastRange As Long
    Dim RangeNo As Long
    LastRange = Range("C" & StartRange).End(xlDown).Row
    Range("E" & StartRange, "F" & LastRange).ClearContents
    DoEvents
    Application.Wait (Now + TimeValue("00:00:1"))
    For RangeNo = StartRange To LastRange
        DoEvents
        Range("E" & RangeNo, "F" & RangeNo).Value = Range("C" & RangeNo, "D" & RangeNo).Value
        Application.Wait (Now + TimeValue("00:00:1"))
        DoEvents
    Next RangeNo
End Sub
  • Finally, click the Save icon.

Build a Macro for Animated Bar Chart Race


Step 4: Generate a Button to Assign the Macro

Lastly, we will create a button to make this application more user-friendly. Here, we will build a START button and assign the previously built macros to the button.

  • Firstly, go to the Developer tab, followed by Insert.

Generate a Button to Assign the Macro

  • Second, draw a rectangle button in the space between the data model and the chart.
  • Subsequently, Assign Macro will come up.
  • Now, choose the macro, in this case, DynamicChart, and hit OK.

  • Next, please select the button and rename it. In this case, START.
  • To achieve this, mark the text and go to the Home tab.
  • After that, bold text from the Font group and make its size 20.

  • As a result, the button will look like the below demonstration.

Generate a Button to Assign the Macro for Animated Bar Chart Race

  • Finally, click the START button, and the intended output will appear below.

Generate a Button to Assign the Macro for Animated Bar Chart Race

Read More: How to Animate Text in Excel 


Download Practice Workbook

If you want a free copy of the sample workbook covered during the presentation, kindly click the link below this paragraph.


Conclusion

You can make an Animated Bar Chart Race in Excel by following our steps. There are similar articles on the ExcelDemy Website with similar information. Keep using them, and if you have any other ideas or better methods, please let me know.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

3 Comments
  1. This article was realy great and I apprecite it a lot. But I have a question: is it possible to do this with lines instead of bars?

    Thanks a lot.

  2. Another question. I hav seen other types of race charts (bar and lines, but horizontally. Thru the time. I saw that in Flourish. Can this be done in Excel?
    I guess it is needed to switch the axis, but else should be done?

    Thanks a lot.

    • Hello Roge,

      Thanks for your feedback! I understand you want to create a line chart race horizontally. Yes, it is possible to do so using Excel’s built-in animation features, VBA, or Flourish Studio. Though, I would recommend using Flourish Studio because it is so much easier.

      To create a line chart race horizontally with Excel data and Flourish Studio, follow these steps:

      1. Login into Flourish Studio.
      2. Create a new project. Scroll down and select any template.
      Line chart race-1
      3. Line up your Excel data and copy.
      Line chart race-2
      4. Go to the site again. Click Data and feed your Excel data by pasting.
      line chart race-3
      5. Now, you can preview your data and modify it according to your requirements.
      6. Finally, click on Export & Publish.
      line chart race-4
      If you still need to implement this in Excel, post your problem at our Exceldemy Forum with sample data. Our team will reach out to you as soon as possible.

      Regards,
      Yousuf Khan Shovon

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo