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.
Download Practice Workbook
If you want a free copy of the sample workbook covered during the presentation, kindly click the link below this paragraph.
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.
Step-by-Step Procedure to Create Animated Bar Chart Race in Excel
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.
- After that, pick the E4:F16 field.
- Later, press the Ctrl+V key.
- Finally, our model will display like below.
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.
- 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.
- 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.
Read More: How to Create Animated Charts in Excel (with Easy Steps)
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.
- 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.
Read More: How to Create Animation in Excel VBA (with Easy Steps)
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.
- 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.
- Finally, click the START button, and the intended output will appear below.
Read More: How to Animate Text in Excel (2 Efficient Methods)
You can make an Animated Bar Chart Race in Excel by following our steps. There are similar articles on ExcelDemy Website with similar information. Keep using them, and if you have any other ideas or better methods, please let me know.