Turn a Simple Dataset into an Animated Bar Chart Using Google Sheets and Tableau (without code)

Hi, everyone!

In this article I will guide you on how to make an animated bar or ‘bar chart race’ using Google Sheets to store and manipulate the data and Tableau to also do some simple data manipulation and the actual plotting and animation of the data, no code needed.

An animated bar chart, or ‘bar chart race’ is an engaging way to represent data that changes over time, especially when values tend to surpass one another as time goes forward.

Animated bar charts or bar chart races have become very popular in social media as they display data in an interesting manner.

The Data (Google Sheets)

The first thing we need to do to create an animated bar chart is make sure we have our data in a format (or shape) appropriate for Tableau to make the plot the way we need it.

To be specific we need the data to be in a long format, meaning the table contains only columns that allow to identify every subject (or bar), the time period, and the value of the variable to plot. It is also important to note that we need What we need looks something like this:

Data used to build the fastest car record animated bar chart

Unfortunately most data usually comes either in wide format (one column per subject with values) or tidy format (one column per metric, one row per observation).

Data in tidy format (taken from the List of highest-grossing films in the United States by year in Wikipedia)

Now, I will quickly describe the process of taking a data table in tidy format as the one shown above to one with long data including 0s. For that I’ll be using the data table contained on the List of highest-grossing films in the United States by year article from Wikipedia.

First you need to take the original data into a Google Sheet, usually copying and pasting the original table works.

Next you’re going to want to select all the column with your ‘subjects’, in this case the Film column (B:B) and pasted it in a transposed way eliminating duplicates along the way.

You can eliminate duplicates by selecting a whole column (preferably a copy of the original column) and clicking on Data on the menu ribbon and selecting Remove duplicates.

To paste in a transposed manner you need to select a column of values, copy it, right-click on an empty cell and select Paste special -> Paste transposed

After this operation you should end with a table that looks like this:

Below the first new column write a =sumif formula that returns the value for that movie in the corresponding year for that row. In my case the formula on cell D2 looks like this: =sumif($B2, D$1, $C2). Since I locked some rows and columns in my formula I can go ahead and copy it for the whole range of rows in my newly created columns.

Last thing we need to do before moving on to Tableau is to fill down each movie’s value for the years after the movie came out. The way I did it was to duplicate the table starting on cell A47, and make a =SUM function that takes the sum of the whole movie column up to the corresponding year in that row. In my case the formula looks like this=sum(D$2:D2) but any way of achieving the desired result is fine. The final table needed looks something like this (values for each movie and year even if it’s 0, for the years before the movie came out) and a constant value for every movie for all the years after it came out. Feel free to remove the

Last Data Touches (Tableau)

On a new Tableau workbook connect to your Google Sheets connector, this will open a window or tab in your browser asking you to authenticate, do so and return to tableau after it’s finished.

Select the Google Sheets file you want to connect to tableau and click on Connect.

Once you have the data in Tableau, on the Data Source tab you can hide the original movie and gross columns (or straight remove it from Google Sheets before connecting to the file, up to you).

Next, select all the columns you created in Google Sheets, right-click any of them and select Pivot

You should end with a table like this. Feel free to rename the “Pivot Field Names” and “Pivot Field Values” columns to any name you prefer, in my case I’ll use “Movie” and “Box-office Gross”

The visualization

First, create a calculated field with the formula RANK_UNIQUE(MAX([Box-office Gross])), this will allow the animated bars to always be ranked and ‘race’ each other.

Next, take this new Rank measure to the Rowsfield and the Movie dimension to both the Label and Color fields

Then right-click on the Rank pill on the Rows field, select Discrete, right-click on it again and select Compute using -> Movie

Next, drag the Box-office gross measure to the Columns field and make sure it has the MAX aggregation method selected

You should have a chart like this after following all the previous steps.

Drag the Year dimension to the Pages field and drag the Rank pill from the Rows field to the Filters field holding cmd (mac) or ctrl (windows) as you do so. In order to also keep it in the Rows field. When the filter dialogue displays select the numbers 1 through 10 so only the top 10 movies are displayed at any given moment.

Next, go to Format -> Animations

On the Animations panel switch the Workbook Default to “On” and the Duration to “1.00 seconds (Slow)”

Drag the MAX(Box-office Gross) pill to the Filters Field (holding ctrl or cmd so you copy it and don’t remove it from the Columns field. In the filter dialogue select “All values”, make the filter work in “At least” mode and input 1 in the box above the slider. This will prevent movies that haven’t been released yet to be shown in the visualization.

When you dragged the Year dimension to the Pages field a new panel should have appeared to the right of your animation, maybe below or above the panel identifying the color of each movie. With this panel you control the flow of the animations (or the turning of the “pages”). Select the button with three thick lines stacked so the animation goes in the fastest setting possible and when you’re ready click on the right arrow button next to the square/stop button.

If all steps above were done correctly you should already be able to see something like this:

Lastly for some aesthetic adjustments you can:

1. Add the box office gross value to the labels in each movie

2. Format the box-office gross measure as a currency with shorter format

3. Hide the axes and headers for the measures in the Rows and Columns fields

4. Remove the gridlines from the chart

5. Change the title of the Sheet

6. Add a reference line (by pane level) that moves to mark the highest bar automatically

7. Create an area annotation so the year (or any other values you want)can also be displayed dynamically inside the visualization.

And VOILÀ you should have a visualization like this after following all this instructions.

I hope this article can help you level your visualizations and that you learned how to create an animated bar chart or ‘bar chart race’. Feel free to comment or DM should you have any questions, I’ll try to get back to you as soon as I can.

Have a great year!